Why use Sequelize?
Sometimes you want your team to use an ORM. Often you need to run migrations. Sequelize provides ORM features and a much needed migrations library that allows us to create producible and version controlled database schemas.
Setup your project
Step 1: Create an npm project in your desired directory
$ npm init
Step 2: Install the packages
$ npm install --save sequelize sequelize-cli pg pg-hstore
Step 3: Create a new database
You need to have postgres installed on your machine for this. If you do not have postgres you can use brew to install it: $ brew install postgres
Now create a new database called here called “user-demo-db”
$ createdb user-demo-db
Step 4: Initialize your sequelize directories
$ node_modules/.bin/sequelize init
Now what? Using Sequelize
You have two options to create database tables: We can either create our database tables with the psql shell using raw queries or we can use sequelize to manage our database.
When to use “sync” and when to use “migrations”
Using sequelize, there are two options for creating tables: 1. Using sync or 2. Using migrations.
In most tutorials you will see code using sync() or even sync({force: true}). This is completely fine in a test database since this option will completely overwrite your database based on your model files. Note: this means you can destroy data with this option.
Why use migrations?
With migrations, we are incrementally changing the database tables and will retain a record of all these changes in our “migration” files. This record is a valuable record of our changes and will allow us to avoid destroying our data.
Step 5 : Create your first model by generating your first migration files
Here we are creating a new model by generating both the initial model file AND the migration file for a sample model User with some attributes firstName;lastName;email
* Note: we are using the singular “User” and not the plural “Users”. This will be an ORM convention that you will need to get used to.
$ node_modules/.bin/sequelize model:generate --name User --attributes firstName:string,lastName:string,email:string
Look inside your new model file:
It should export a new “model” using sequelize.define("User", ...) with the attributes that you specified in the command line arguments.
* Note that the default id field is INTEGER and not something cool like UUID, its your code now, you can change this.
Look inside your new migration file:
It should use the queryInterface function createTable('User', ...) with attributes that you specified in the command line arguments plus three fields that sequelize will add to every table id, createdAt and updatedAt
* One thing that is key to note is that by default you table name is pluralized
Step 6: Run the migration
Before you run this migration, you may change the migration/model file to edit your attributes.
$ node_modules/.bin/sequelize db:migrate
Sequelize will create a SequelizeMeta table in your database to keep track of which migrations have been run. That also means you can create many other models before running this command and it will run all not-yet-run migrations
Check your migration
After you run your migration you can check your table status with psql shell or pgadmin
$ psql user-demo-db
psql> \d "Users" <- note the “s”; note the double quotes
Get used to this pattern, you will use this a lot.
(Optional) Create an instance of your model – User
Note that we use the singular User instead of the Users from the actual table
node> var models = require('./db/models')
node> models.create.User({
firstName: "John", lastName: "Doe", email: "jd@gmail.com"})
Step 7: How to create a 1:Many (1:M) relationship
Lets create a model called Task that can only have one User. 1(User):Many(Tasks).
Create your migration files and migrate your database to add the new table
$ node_modules/.bin/sequelize model:generate --name task --attributes body:string,done:boolean
$ node_modules/.bin/sequelize db:migrate
Now we will need to associate the Task to the User in some way.
According to the docs we need to run Task.belongsTo(User)
However if we add this to the db/models/Task.js file in the associate function, our file now fails to load correctly when accessing db/models within the node shell. We can open up our node shell and do this manually.
node> const models = require('./db/models')
node> models.Task.belongsTo(models.User)
What happened?
BelongsTo associations are associations where the foreign key for the one-to-one relation exists on the source model.
We ran the code <source>.belongsTo(<target>) with having the appropriate “foreign key” on our source model (Our Task model is missing the column UserId). Instead of undoing our migration, this is a perfect time to learn how to do a database migration.
Step 8: Fix our 1:M relationship
This time we only need the migration file, and not the model generated file.
$ node_modules/.bin/sequelize migration:generate --name add_userid_to_task
Adding a foreign key field to a model – Task
We want to create a new column UserId on our Task model/table that represents a foreign key relationship. The docs explain that all we need is a references key with the correct values
Your migration file will look something like this: code
return queryInterface.addColumn(
SOURCE_TABLE_NAME, // "Tasks"
TARGET_FIELD_NAME, // "UserId"
{
type: Sequelize.INTEGER,
reference: {
model: TARGET_TABLE_NAME, // "Users"
key: "id"
}
onDelete: 'cascade
}
)
Important Notes:
- we have to match the type of the target field (“integer” in our case above, “UUID” if you wanted to create an advanced
Usermodel) - Your source “table” name is likely the plural version of your “model” name. You can get the model name with
node> require('../models')[model_name].getTableName() - Your target field name should be “title-case” based on sequelize convention, so if you’re targeting
idonUser, your target field name isUserId
Step 9: Remember to migrate your database!
$ node_modules/.bin/sequelize db:migrate
More:
Blog Post: How to configure sequelize with heroku
Blog Post: How to define Sequelize associations using migrations