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:
- edit our config file to
export our object
- 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
- Change your username to your system username (probably not
root)
- 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
