Sequelize is a promise-based Node.js ORM for Postgres, and I totally didn't copy this from the documentation XD
Here's some common usages to get you started
With sequelize, what we want to do is utilise the principle of "migrations" to create and update your content at different points of development (as and when tables are updated). Simply put it's files with timestamps, and when applying new migration files, previously applied migration files will not be applied again.
Having to copy, paste, keep track and change the name of the file can be really painful. Hence you might want to use the code generator here, sequelize-cli to make migrations a seamless experience.
Let's try to create a Users table and a UserRoles table (representing the roles from that a User entity can hold)
Run the following in your terminal:
npx sequelize-cli model:create \
--name UserRoles \
--attributes name:string
From the above, we are creating the UserRoles table, with a column name as varchar type (translated as string here).
How about attributes like id ? Don't we have to add that? Let's look at db/migrations/xxxxxx-create-user-roles.js:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('UserRoles', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
name: {
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('UserRoles');
}
};Turns out the id is automatically added by the code generator, so there is no need to add this property ourselves.
Looking at the generated code, we could limit the name column, since roles are system administered, and not long strings naturally. We could also prevent it from being empty since there are no "empty" roles in this system we are creating.
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('UserRoles', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
name: {
allowNull: false, // ADDED this Property - Cannot be null
type: Sequelize.STRING(50) // EDITED this Property - Varchar with 50 char limit
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('UserRoles');
}
};With the above change, we should be good to go with creating the Users table next. Run npx sequelize-cli db:migrate from your terminal to create the table above.
Run the following in your terminal
npx sequelize-cli model:generate \
--name Users \
--attributes username:string,passwordHash:string,passwordSalt:string,userRoleId:integer
Looking at the generated db/migrations/xxxxxx-create-users.js, here's what we should get:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
username: {
type: Sequelize.STRING
},
passwordHash: {
type: Sequelize.STRING
},
passwordSalt: {
type: Sequelize.STRING
},
userRoleId: {
type: Sequelize.INTEGER
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Users');
}
};There's no indication of a foreign key constraint. Let's correct the userRoleId property to implement this constraint:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
username: {
allowNull: false,
type: Sequelize.STRING
},
passwordHash: {
allowNull: false,
type: Sequelize.STRING
},
passwordSalt: {
allowNull: false,
type: Sequelize.STRING
},
userRoleId: {
allowNull: false,
type: Sequelize.INTEGER,
references: { // Add this for foreign key constraints
model: 'UserRoles',
key: 'id'
},
onUpdate: 'cascade'
// onDelete: 'cascade' // Not necessary unless if this entity should be deleted if the linked entity in UserRoles is deleted
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Users');
}
};The above should be good! Run npx sequelize-cli db:migrate to create the table with the foreign key as seen above.
Curious on other supported types in sequelize? See here.
EXTRA: Numeric and decimal types are equivalent. Just putting it out there for usage on donation quantity :D .
What if we forgot to add a column to a table added from a earlier migration file? No fear, let's create a new migration file to add the missing content.
npx sequelize-cli migration:generate \
--name add-user-columns
This should generate the db/migrations/xxxxxx-add-user-columns.js migration file with the following content:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
/*
Add altering commands here.
Return a promise to correctly handle asynchronicity.
Example:
return queryInterface.createTable('users', { id: Sequelize.INTEGER });
*/
},
down: (queryInterface, Sequelize) => {
/*
Add reverting commands here.
Return a promise to correctly handle asynchronicity.
Example:
return queryInterface.dropTable('users');
*/
}
};It doesn't have any additional content to insert our new columns now - let's add in the configuration to our new columns.
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.sequelize.transaction((t) => {
return Promise.all([
queryInterface.addColumn('Users', 'name', { // Table name and column name respectively
allowNull: false, // Similar property structure as we used earlier on
type: Sequelize.STRING
}, { transaction: t }),
queryInterface.addColumn('Users', 'weight', {
allowNull: false,
type: Sequelize.DECIMAL(10, 2) // Precision of 10 digits, scaled to 2 decimal places
}, { transaction: t })
])
})
},
down: (queryInterface, Sequelize) => {
return queryInterface.sequelize.transaction((t) => {
return Promise.all([
queryInterface.removeColumn('Users', 'name', { transaction: t }),
queryInterface.removeColumn('Users', 'weight', { transaction: t })
])
})
}
};Note that in up, the function performs what is required to add the column and the required property with the table name, similar to creating a table. For down, removal of the columns are required, as it should represent the reverting of this migration file.
After running npx sequelize-cli db:migrate, the new columns would have been added. Make sure you update the new columns within your model as seen here - src/models/users.js
How about seeding data? To add new users, we might need at least one admin user. Let's create one default user through "seeding" the data automatically when the tables are first created.
npx sequelize-cli seed:generate \
--name default-admin
From db/seeders/xxxxxx-default-admin.js, there should not be much code yet. Let's add the following content for creating the default admin user and the related role.
'use strict';
module.exports = {
async up (queryInterface, Sequelize) {
const adminRoleId = '1';
await queryInterface.bulkInsert('UserRoles', [{
id: adminRoleId,
name: 'admin',
createdAt: new Date(),
updatedAt: new Date()
}, {
id: '2',
name: 'staff',
createdAt: new Date(),
updatedAt: new Date()
}], {}).then(() => {
return queryInterface.bulkInsert('Users', [{
name: 'John Smith',
username: '[email protected]',
passwordSalt: '',
passwordHash: '',
userRoleId: adminRoleId,
weight: 62.57,
createdAt: new Date(),
updatedAt: new Date()
}], {});
})
},
async down (queryInterface, Sequelize) {
await queryInterface.bulkDelete('Users', null, {});
await queryInterface.bulkDelete('UserRoles', null, {});
}
};Run npx sequelize-cli db:seed:all and viola, then admin user should be inserted in your database now.
Beyond migration - adding and updating of tables' properties - we can use sequelize ORN capabilities to make retrieval of content easier through associations. Let's use the examples from before.
First, we need to establish an association on the models generated from the prior examples. Go to src/models/users.js. You should see the following:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Users = sequelize.define('Users', {
name: DataTypes.STRING,
username: DataTypes.STRING,
passwordHash: DataTypes.STRING,
passwordSalt: DataTypes.STRING,
userRoleId: DataTypes.INTEGER,
name: DataTypes.STRING,
weight: DataTypes.DECIMAL(10, 2),
}, {});
Users.associate = function(models) {
};
return Users;
};Add the association between UserRoles and Users ||| Users belongs to UserRoles (conversely, UserRoles has many Users and we can add that too!)
'use strict';
module.exports = (sequelize, DataTypes) => {
const Users = sequelize.define('Users', {
name: DataTypes.STRING,
username: DataTypes.STRING,
passwordHash: DataTypes.STRING,
passwordSalt: DataTypes.STRING,
userRoleId: DataTypes.INTEGER,
name: DataTypes.STRING,
weight: DataTypes.DECIMAL(10, 2),
}, {});
Users.associate = function(models) {
Users.belongsTo(models.UserRoles, { // Added association here
foreignKey: 'userRoleId', // Note the foreignKey should be indicated
as: 'role' // This creates an alias for methods that we will use in a bit
})
};
return Users;
};Once the above is established, you can query for Users, and UserRoles through Users in code (note that we are using the seeded content above).
'use strict;'
const db = require('../models/index')
...
db.Users.findAll({
where: {
id: '1'
}
}).then(users => {
if (users.length > 0) {
console.log(users[0]); // You should get "John Smith" here
const admin = users[0];
return admin.getRole().then(function(role){ // `getRole` is available from the alias of `role` that we set earlier
console.log(role); // John Smith has an admin role that is retrieved here
});
}
});There are many association concepts out there that makes content retrieval and hydration much more easier. Do explore them here :D
For documentation for createTable, see link here