Using PostgreSQL with Sequelize - setup, sync, and migrations

There are varying opinions about what type of database to use for a given project, with two popualr choices being relational database systems (like MySQL or PostgreSQL) and NoSQL systems (like MongoDB). There are some great articles which discuss the difference, but as an app grows larger and requires more structured data, it’s likely you’ll want a robust, relational database system to use. This is where Sequelize comes in.

Unlike databases built using MongoDB/Mongoose (which are easy-to-use, non-relational, flat document storage solutions), Sequelize is a popular ORM for PostgreSQL, SQL, MySQL and SQL server. Acting as a “middle man” piece of software, one would use Sequelize as a full database setup/querying package to interface with the relational DB systems just mentioned without knowing their specific syntax and integration aspects.

Coming from using MySQL and Postgres as stand-alone database systems, I think Sequelize is a large (some may say bloated) yet powerful solution, and one which I’ve only recently began to use for a project at work. I found the documentation is slightly lacking for people just starting with Sequelize, and I found a few useful nuggets which I wanted to note here for anyone who may find them useful.

This post aims to cover some basic Postgres and Sequelize setup with Node.js, how to generally use them in development and production, and how to manage the slightly more complex aspects - specifically migrations.

I will assume knowledge of setting up a Node.js project and having used relational and non-relational database systems at some level before.

General project setup

As mentioned, I’ve not been using this for a long period of time so I’ve only had chance to use it in a couple of specific situations.

The basic overview of tools to have on hand are Git, Node, a local instance of Postgres, and some sort of UI software to view your database system. I used DBeaver for this UI software which is free and has a load of features to view all different types of database systems (including Postgres).

Installing Postgres

To start with, you’ll want to install Postgres on your local machine. While developing locally, you can use your local instance of Postgres to save effort, time, cost etc. Your local DB instance will be accessed by Sequelize to perform your operations. To install Postgres on a Mac, run:

brew install postgresql

Once installed, follow this link for a great overview of how to interface with your local Postgres database via the CLI tool. This will enable you to create users, databases, tables, etc. You’ll need to create a user and database via the CLI, and once that’s done you can open your local database in DBeaver to run other queries and add tables, schemas etc. if you feel more comfortable that way.

Aside from a local installation, at some point you’ll probably want to link your app to a production database. A lot of providers like Digital Ocean and Heroku offer free Postgres database you can use, giving you all the connection details you need to add in to your DBeaver also. For the majority of this post though I’ll focus on local systems.

Installing Sequelize and the basics

Once you have your PostgreSQL database setup on your machine, you’ll want to integrate with Sequelize. With your Node project open in your terminal:

npm i sequelize pg pg-hstore

The above installation of Sequelize and Postgres is covered in the official docs. The rest of the docs take you through how to get a basic connection setup to your DB of choice (our local Postgres in this case), which is great to get a feeling for how Sequelize works from a top level. This includes setting up a connection, creating a model, and performing a query. If not already done so, I’d suggest having a play with these parts of Sequelize before carrying on with the slightly more advanced way of using the CLI tool (which I’ll cover below).

The reason why I recommend going through the docs first is that the majority of the docs cover the usage on how to work with Sequelize and setup at a basic level by using the code examples provided. This works great to get an understanding, but later I found it much better to use the Sequelize CLI which is used for more advanced features like migrations, which I’ll discuss later.

When following the docs and not using the CLI, you are required to set up your models, and code a sequelize.sync() function to initialize your database. This sync() function looks through the models you’ve manually created (as shown in the docs in the “Getting started” section) and replicates your defined database model to your database.

Using Sequelize Sync

Something to look out for is that when syncing the database using sequelize.sync(), the database structure (columns, types etc.) won’t update automatically if you decide to change them from within your model. For example, if you’ve made your model for a Users table and decide you want a refreshToken column, this won’t update the database with your new addition. When changing database structure, you’re instead required to add a force options parameter to the sync function:

sequelize.sync({ force: true })

Using this {force: true} parameter, the database completely empties it’s self, and re-adds the new database structure into the database. This also removes all the data contained in the database, which is far from ideal if you’re using test data and it gets deleted each time your Nodemon fires a server restart.

After a few Google sessions and discovering ways to stop data from being deleted, you’ll come across the concept of Sequelize migrations. I’ll cover these a little more later in this post, but essentially they are files which provide ways to instruct the database to change it’s structure without deleting data.

Using Sequelize Sync without deleting data

Aside from migrations, I found another great way of swiftly updating your database using sequelize.sync() in the early development stages of a project without deleting data. It’s not documented on the Sequelize website, and also not easy to find with a quick Google:

sequelize.sync({ alter: true })

The {alter: true} parameter will mean you can change the database structure and data, run the sync function, and your data is kept untouched. This is a recent release by Sequelize in V4 I believe (don’t quote me on that), and works like a charm!

It’s worth noting that this is great for development, but when going to production it’s recommended that migrations are used instead as they are a safer and more useful solution when dealing with production level data.

Sequelize CLI and migrations

So what are migrations? They exist in many database systems, and have been around for a long time - it’s nothing new to Sequelize. They are way of programatically updating a database structure (or schema) to a desired state. To take the previous example of the Users table and the end goal of adding a refreshToken column - instead of dropping the whole table and updating the table structure, migrations allow us to program logic to allow these structure changes to happen - i.e. telling the database to “add new column - refreshToken”, ultimately being performed in a Postgres query.

Migrations in Sequelize can only happen while using the CLI, which is one reason to start using the CLI from the beginning of a project, as migrations are harder to integrate to a project if you don’t start with a CLI/migration setup (as you’ll be required to empty your DB manually and find a way to import the data again after a new DB is created). Aside from migrations, the CLI is great for a few things, including initializing a project (generating files to link your models, migrations etc. together), and means you have the structure of Sequelize generated and managed for you. In my opinion this makes it easier than managing the files and structure manually. To start off, install the sequelize CLI:

npm i sequelize-cli -g --save

Once installed, go to the root directory of your project in your terminal and run:

sequelize init

This will initialize your database directories and files, including config, migrations, models and seeders.

The CLI docs say to install this in your project root, but I installed in another location which is separated into another directory from my root. I felt this made my file strucutre easier to understand, but it doesn’t make much difference.

Once this has been initialized, you can run a number of Sequelize commands from this location. The avaliable commands are viewable on the Github page, but I’ll focus on the migrations here. You’ll notice a /models directory has been created with the Sequelize CLI, and the index.js file inside here is the “root model”. This file searches the files inside the /models directory and imports them.

Whereas using the sequelize.sync() function in the docs is good in development (especially with the previously mentioned alter parameter which retains data), the CLI tool means that you use migrations to kind of manually sync your database with your desired schema/model. A migration file is automatically created when you create a model:

sequelize model:create --name Users --attributes "first_name:string, last_name:string, email:string

Running the above will create your model and migration file. The model file looks like a normal Sequelize model (as shown in the docs) and the migration file will look something like:

// models/index.js

"use strict"
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable("users", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      first_name: {
        type: Sequelize.STRING,
      },
      last_name: {
        type: Sequelize.STRING,
      },
      email: {
        type: Sequelize.STRING,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    })
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable("users")
  },
}

You’ll notice the migration files automatically add the createdAt and updatedAt columns which are really useful.

At this point (assuming you’ve not created a database with the sync function before), you must run the migration script in the CLI to “migrate” the database changes to the live database by running:

sequelize db:migrate

Afterwards you may want to update your database structure again - let’s say you want to add a new column for refreshToken on the Users table. You’ll then need to create a new migration file using the CLI:

sequelize migration:generate --name addRefreshToken

Once you have your new migration file you’ll need to update it to define how you want the database to change:

// migrations/160889123456-addRefreshToken.js

module.exports = {
  up: function (queryInterface, Sequelize) {
    // up function is defining the new database structure
    queryInterface.addColumn("Users", "refreshToken", Sequelize.BOOLEAN)
  },

  down: function (queryInterface, Sequelize) {
    // down function will revert the changes in case we need to roll back
    queryInterface.removeColumn("Users", "refreshToken")
  },
}

The up() and down() functions are in all migration files, and as shown above, it’s a simple case of the up function defining the new change, and the down function defining how to undo the change if needed.

Once you’re finished updating your migration file, you can run sequelize db:migrate and it will run the migration process again! Repeat this process when ever you’d like your database updating.

As you’re running these migrations manually on the CLI, there’s no need to run sync when you start your server. Running the sync function is great for development because you can see instant results, especially with the alter param, but migrations are generally used to update the production database once your application has been pushed to remote environments. Migration scripts can be ran from npm scripts so it makes it super useful.

Using environment variables with Sequelize

This root model file is also responsible for connecting to the Postgres database:

// models/index.js

...
var env = process.env.NODE_ENV || 'development';
var config = require(__dirname + '/../config/config.json')[env];
var db = {};

if (config.use_env_variable) {
  var sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  var sequelize = new Sequelize(
    config.database,
    config.username,
    config.password,
    config
  );
}

...

This connection code allows you to input your connection information in the config.json file which was also created with the CLI tool, and makes the DB connection. You’re able to insert each part of the connection information like the host, database, password, port etc. or you can add an entire connection URI like those supplied by services like Heroku or Digital Ocean:

// config/config.json

{
  "development": {
    "use_env_variable": "DATABASE_URL",
    "dialect": "postgres",
    "dialectOptions": {
      "ssl": {
        "require": true
      }
    },
    "ssl": true
  },
...
}

The great part about this setup provided by the CLI tool is that it gives you the ability to easily use environment variables to add connection infromation to your code. You’ll likely want to keep your important DB passwords and other info out of source control, so using an .env file or similar when in development is crucial.

Environment variables in production environments are often entered seperately into the service you’re hosting with. Heroku for example, have thesr variables set from their dashboard.

From the example above with the DATABASE_URL, if this was supplied in the config.json file as:

// config/config.json
{
  "development": {
    "use_env_variable": "DATABASE_URL",
    "dialect": "postgres",
...

Then you could have your connection information use that in your connection to retrive that from the process.env.DATABASE_URL automatically:

// models/index.js

...
var sequelize = new Sequelize(process.env[config.use_env_variable], config);
...

Thanks for reading!

This only really touches the surface of what Sequelize can do, but the areas discussed here are what I felt were not well documented elsewhere.

I have further material I noted down about other features of Sequelize such as working with associations and seeders. I may get these added to a follow up post about Sequelize soon depending on how many people find this useful, so drop me an email or tweet if you’d be interested.