Why?

Sometimes you want your team to use an ORM.

Setup

Step 1: Create your npm project

$ npm init

Step 2: Install the packages

$ npm install --save sequelize sequelize-cli pg pg-hstore

Step 3: Create a new database

Lets create a local database (this depends on your target). If you have postgres installed use:

$ createdb user-demo-db

Step 4 : Create a .sequelizerc file

This will be used by sequelize for determining which paths to create directories

var path = require('path')

module.exports = {
  'config': path.resolve('config', 'config.js'),
  'migrations-path': path.resolve('db', 'migrations'),
  'models-path': path.resolve('db', 'models'),
  'seeders-path': path.resolve('db', 'seeders'),
}

* we are using config.js because javascript will allow us to access environment variables. We will typically not want to store password sensitive strings in our production app.

Step 5: Initialize your sequelize directories

$ node_modules/.bin/sequelize init

Step 6: Fix config.js

Since we used .js instead of .json we have to:

1. edit our config file to export our object
2. Your generated sequelize index.js file will default to using the development database so change development database field to the name of your database user-demo-db 
3. Change your username to your system username (probably not root)
4. Also make sure to change the dialect to postgres. 

* later, we will want to use environment variables to access passwords

Now what? Using Sequelize

We can either create our database tables with the psql shell using raw queries or we can use sequelize to manage our database.

In this article we will use sequelize and if we do, we now have two options: 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 as it will completely overwrite changes you make in your “model” files.

We will use migrations for one big reason. Migrations will create “migration files” for all database changes where we can get valuable source controlled information.

Step 7 : 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 8: 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 9: Create a 1:Many 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.

– docs

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 11: 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 User model)
• 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 id on User, your target field name is UserId

Step 12: Remember to migrate your database!

$ node_modules/.bin/sequelize db:migrate

 

Resources:

Code Sample

Docs

How to define Sequelize associations using migrations