The Background
I'm building a project with SequelizeJS, a popular ORM for NodeJS. When designing a schema, there appears to be two tactics:
My understanding is that #1 is better for rapid prototyping, but that #2 is a best practice for projects that are expected to evolve over time and where production data needs to be able to survive migrations.
This question pertains to tactic #2.
The Question(s)
My tables have relationships which must be reflected through foreign keys.
How do I create tables with foreign key relationships with one another through the Sequelize QueryInterface?
What columns and helper tables are required by Sequelize? For example, it appears that specific columns such as createdAt or updatedAt are expected.
How do I create tables with foreign key relationships with one another through the Sequelize QueryInterface?
The .createTable()
method takes in a dictionary of columns. You can see the list of valid attributes in the documentation for .define()
, specifically by looking at the [attributes.column.*]
rows within the params table.
To create an attribute with a foreign key relationship, use the "references" field.
For example, the following would create a users
table, and a user_emails
table which references the users table:
queryInterface.createTable('users', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
}
}).then(function() {
queryInterface.createTable('user_emails', {
userId: {
type: Sequelize.INTEGER,
references: { model: 'users', key: 'id' }
}
})
});
What columns and helper tables are required by sequelize? For example, it appears that specific columns such as createdAt or updatedAt are expected.
It appears that a standard model will expect an id
, updatedAt
, and createdAt
column for each table.
queryInterface.createTable('users', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
createdAt: {
type: Sequelize.DATE
},
updatedAt: {
type: Sequelize.DATE
}
}
If you set paranoid
to true
on your model, you also need a deletedAt
timestamp.