根据文档,
我的设置如下:
这是最终模型。
sequelize.sync()
没有使用{force: true}
或{alter:true}
时,应该会忽略已经存在的表,并且只会创建/同步新的表。但是,至少有两种情况下,现有的表不会被完全忽略,并且引入了错误。我的设置如下:
sequelize.sync()
用作预迁移步骤,以创建模式中不存在的表sequelize.migrate()
用于修改现有的任何表。
name
和email
的User
模型。Email
具有唯一索引。const users = sequelizeClient.define('users', {
name: {
type: DataTypes.STRING,
allowNull: false,
},
email: {
type: DataTypes.STRING,
allowNull: false,
},
}, {
indexes: [
{
unique: true,
fields: ['email'],
},
],
});
没有迁移,因此预计使用 sequelize.sync()
创建表格。
一切都按预期进行。以下是生成的SQL脚本。
Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id" SERIAL , "name" VARCHAR(255) NOT NULL, "email" VARCHAR(255) NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE UNIQUE INDEX "users_email" ON "users" ("email")
第二步 向Users表中添加一个名为phonenumber
的新字段,并加上唯一索引。添加一次迁移操作,以更改表结构并创建该索引。预计sequelize.sync()
将忽略此表,但是迁移永远不会被执行,因为sequelize.sync()
抛出以下错误。
Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id" SERIAL , "name" VARCHAR(255) NOT NULL, "email" VARCHAR(255) NOT NULL, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE UNIQUE INDEX "users_phonenumber" ON "users" ("phonenumber")
{"_bitField":18087936,"_fulfillmentHandler0":{"name":"SequelizeDatabaseError","parent":{"name":"error","length":101,"severity":"ERROR","code":"42703","file":"indexcmds.c","line":"1083","routine":"ComputeIndexAttrs","sql":"CREATE UNIQUE INDEX \"users_phonenumber\" ON \"users\" (\"phonenumber\")"},"original":{"name":"error","length":101,"severity":"ERROR","code":"42703","file":"indexcmds.c","line":"1083","routine":"ComputeIndexAttrs","sql":"CREATE UNIQUE INDEX \"users_phonenumber\" ON \"users\" (\"phonenumber\")"},"sql":"CREATE UNIQUE INDEX \"users_phonenumber\" ON \"users\" (\"phonenumber\")"},"_trace":{"_promisesCreated":0,"_length":1},"level":"error","message":"Unhandled Rejection at: Promise "}
这是最终模型。
const users = sequelizeClient.define('users', {
name: {
type: DataTypes.STRING,
allowNull: false,
},
email: {
type: DataTypes.STRING,
allowNull: false,
},
phoneNumber: { // new field
type: DataTypes.STRING,
allowNull: false,
},
}, {
indexes: [
{
unique: true,
fields: ['email'],
},
{ // new index
unique: true,
fields: ['phoneNumber'],
},
],
});
有人能建议一个解决方法,使索引只在表不存在时创建
另一个用例是当您添加带注释的新字段时
fieldWithComment: {
type: DataTypes.STRING,
comment: 'my comment goes here',
},
生成的SQL语句中出现了错误,因为新列还不存在。
CREATE TABLE IF NOT EXISTS "users" (
"id" SERIAL,
"name" VARCHAR(255) NOT NULL,
"email" VARCHAR(255) NOT NULL,
"phonenumber" VARCHAR(255) NOT NULL,
"fieldWithComment" VARCHAR(255) , PRIMARY KEY ("id"));
COMMENT ON COLUMN "users"."fieldWithComment" IS 'my comment goes here';