如何使用 Sequelize 和 PostgreSQL 创建多对多关联映射表?"/>
如何使用 Sequelize 和 PostgreSQL 创建多对多关联映射表?
我有两张桌子,演员和电影。 ACTOR 和 MOVIE 具有多对多关系。一个演员可能参演了很多部电影,一部电影有很多演员参与。由于这两个表具有多对多关系,因此我创建了一个映射表 ACTOR_MOVIE_MAP。在 PostgreSQL 中,我可以使用以下代码创建表。
CREATE TABLE IF NOT EXISTS ACTOR (
PK_ACTOR SERIAL PRIMARY KEY NOT NULL,
ACTOR_ID VARCHAR(256) NULL,
MOVIE_ID VARCHAR(256) NULL);
CREATE INDEX MOVIE_ID_IDX ON ACTOR(MOVIE_ID);
CREATE TABLE IF NOT EXISTS MOVIE (
PK_MOVIE SERIAL PRIMARY KEY NOT NULL,
MOVIE_NAME VARCHAR(256) NOT NULL,
MOVIE_NO INT NOT NULL UNIQUE
CREATED_AT TIMESTAMP NULL,
UPDATED_AT TIMESTAMP NULL);
CREATE INDEX MOVIE_NO_IDX ON MOVIE(MOVIE_NO);
CREATE TABLE IF NOT EXISTS ACTOR_MOVIE_MAP (
PK_MAP SERIAL PRIMARY KEY NOT NULL,
FK_PK_ACTOR INT NULL,
FK_PK_MOVIE INT NULL,
CONSTRAINT fk_actor_map
FOREIGN KEY (FK_PK_ACTOR)
REFERENCES ACTOR(PK_ACTOR)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_movie_map
FOREIGN KEY (FK_PK_MOVIE)
REFERENCES MOVIE(PK_MOVIE)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
我想做同样的事情,但使用 ORM,使用 PostgreSQL 进行 Sequelize。我为此编写了以下代码:
型号:
电影.js
'use strict';
const {
Model
} = require('sequelize');
const ACTOR = require('./actor');
module.exports = (sequelize, DataTypes) => {
class MOVIE extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// define association here
MOVIE.belongsToMany(ACTOR, {
through: 'ACTOR_MOVIE_MAP',
onDelete: 'NO ACTION',
onUpdate: 'NO ACTION'
});
}
}
MOVIE.init({
PK_MOVIE: DataTypes.INTEGER,
MOVIE_NAME: DataTypes.STRING,
MOVIE_NO: DataTypes.INTEGER
}, {
sequelize,
tableName: 'MOVIE',
modelName: 'MOVIE',
indexes:[
{
fields:['MOVIE_NO']
}
]
});
return MOVIE;
};
actor.js
'use strict';
const {
Model
} = require('sequelize');
const MOVIE = require('./movie');
module.exports = (sequelize, DataTypes) => {
class ACTOR extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// define association here
ACTOR.belongsToMany(MOVIE, {
through: 'ACTOR_MOVIE_MAP',
onDelete: 'NO ACTION',
onUpdate: 'NO ACTION'
});
}
}
ACTOR.init({
PK_ACTOR: DataTypes.INTEGER,
ACTOR_ID: DataTypes.STRING,
MOVIE_ID: DataTypes.STRING
}, {
sequelize,
tableName: 'ACTOR',
modelName: 'ACTOR',
timestamps: false,
indexes:[
{
fields:['MOVIE_ID']
}
]
});
return ACTOR;
};
迁移:
2023xxxxxx-create-movie.js
'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable('MOVIE', {
// id: {
// allowNull: false,
// autoIncrement: true,
// primaryKey: true,
// type: Sequelize.INTEGER
// },
PK_MOVIE: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
MOVIE_NAME: {
allowNull: false,
type: Sequelize.STRING(256)
},
MOVIE_NO: {
allowNull: false,
unique: true,
type: Sequelize.INTEGER
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
async down(queryInterface, Sequelize) {
await queryInterface.dropTable('MOVIE');
}
};
2023xxxxxx-create-actor.js
'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable('ACTOR', {
// id: {
// allowNull: false,
// autoIncrement: true,
// primaryKey: true,
// type: Sequelize.INTEGER
// },
PK_ACTOR: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
ACTOR_ID: {
allowNull: true,
type: Sequelize.STRING(256)
},
MOVIE_ID: {
allowNull: true,
type: Sequelize.STRING(256)
}
});
},
async down(queryInterface, Sequelize) {
await queryInterface.dropTable('ACTOR');
}
};
由于直接创建了映射表,因此我没有为此创建任何迁移。 我已经编写了添加的代码,但是当我尝试迁移它时出现以下错误。
Loaded configuration file "server\config\db_config.json".
Using environment "development".
== 20230xxxxxx-create-actor: migrating =======
ERROR: syntax error at or near "false"
我不确定为什么会显示此错误。我哪里错了?
我创建了一个没有关联的简单表,并使用默认主键,由 Sequelize 提供的 createdAt 和 updatedAt 字段。这行得通,但是当尝试将其扩展到具有关联的多个表时,我被卡住了。
我已经单独检查了每张桌子。我能够创建表演员和电影。问题是何时创建映射表。我无法创建映射表。我正在创建一个映射表 ACTOR_MOVIE_MAP 因为 ACTOR 和 MOVIE 有多对多的关系。
回答如下:更多推荐
如何使用 Sequelize 和 PostgreSQL 创建多对多关联映射表?
发布评论