A happy dog (imaged derived from a photo by the author)

Flexible and consistent configuration of Sequelize and Postgres

Published in

ITNEXT

4 min readAug 13, 2018

Postgresql is a very popular SQL server and for good reasons; it’s brilliant at what it does, it handles JSON and HSTORE data structures, so gives you MongoDB powers with all the associated benefits of a robust SQL server, it’s free and open-source, it’s well supported, and it is the default database provided by Heroku.

Sequelize is the most popular NodeJS Object Relational Mapping (ORM) library, also for good reason. It’s powerful, expressive, and mature.

When developing applications that use Sequelize and Postgres I’ve found myself using the same basic pattern to ensure there is a database connection available, over and over again. I’ve published sequelize-pg-utilities to consolidate those patterns into a single library. It’s very simple, its only dependency is pgtools which it uses to automatically create the application’s database if required.

Configuration

Firstly, applications that rely on a database require some configuration. The Sequelize way is to stick config in config/config.json, which is rather limiting. You need environment variables to be merged in over the standard configuration file options in order to allow for custom configuration. Also you need sensible default values for configuration options that are not present in the config/config.json file.

The configure function accepts the parsed config/config.json file and returns a configuration object whose values include, where appropriate, environment variable values and sensible defaults.

So to create a configured sequelize instance you can do:

const { configure } = require('sequelize-pg-utilities')
const config = require('path/to/config/config.json')const { name, user, password, options } = configure(config)const sequelize = new Sequelize(name, user, password, options)

The following environment variables take precedence over whatever is defined in config/config.json.

DATABASE_URL The database url, if provided, will override many of the below DB settings.
DB_NAME The database name — You may also supply a default (see below)
DB_USER The database user — no default
DB_PASS The database password — no default
DB_POOL_MAX The maximum number of database connections — Defaults to 5
DB_POOL_MIN The minimum number of database connections — Defaults to 1
DB_POOL_IDLE The database idle time — Defaults to 10000 ms
DB_HOST The database host — Defaults to ‘localhost’
DB_PORT The database port — Defaults to 5432
DB_TYPE The database type — Defaults to ‘postgres’ — This library is written with Postgres in mind so please don’t change this unless you know what you are doing.

If you supply the DATABASE_URL environment variable, as Heroku and other paas systems generally do, then the configure function will extract most of what it needs from that. The extracted values will take priority over subsequent values.

Initialising a database

Often you’ll want your application to create its database the first time it runs, especially in development and test environments. To do this you can make an appropriately configuredinitialiser using the makeInitialiser function.

const { makeInitialiser } = require('sequelize-pg-utilities')
const config = require('path/to/config/config.json')const initialise = makeInitialiser(config)const start = async () => {
  try {
    await initialise()    // now do whatever else is needed
    // to start your server
  } catch (err) {
    console.error(err)
    process.exit(1)
  }
}

You can set the number of retries by passing it in as a parameter to initialise. The default is 5.

const result = await initialise(10)

Configuring the Sequelize CLI

The Sequelize CLI requires that you define a .sequelizerc file at the root of your project that exports data such as config, migrations-path, and models-path.

The config is in the form:

{
  [env]: { username, password, database, options }
}

You can use the migrationConfig function to generate configuration details to suit Sequelize CLI’s needs.

Somewhere in your code, create a migrationConfig.js file as follows:

const { migrationConfig } = require('sequelize-pg-utilities')
const config = require('path/to/config/config.json')module.exports = migrationConfig(config)

Your .sequelizerc file becomes:

const path = require('path')module.exports = {
  config: path.resolve(__dirname, 'path', 'to', 'migrationConfig.js'),
  'migrations-path': path.resolve(__dirname, 'migrations'),
  'models-path': path.resolve(__dirname, 'src', 'models')
}

Options

The configure, makeInitialiser, and migrationConfig functions all have an identical signature. They accept the following parameters.

config: The parsed config/config.json file. Required, no default.
defaultDbName: If the database name is not set in an environment variable, and if the config file does not define a database name, then use this as the database name. Optional, no default.
operatorsAliases: Sequelize recommends you don’t use operators aliases, but if you want to you can set them here. Optional, default is false.
logger: You can pass in a logger function here for Sequelize to use. Optional, default is false, meaning don’t log anything.

Summary

You can simplify and make consistent your Sequelize / Postgres configuration and initialisation using sequelize-pg-utilities, which exposes the following functions:

configure: Uses data from a Sequelize config file as well as environment variables and sensible defaults to create a standard configuration object.
makeInitialiser: Creates a configured, asynchronous, retrying, database initialise function that you can use to create your database if it does not already exist.
migrationConfig: a function that produces the correctly formatted configuration option needed for a .sequelizerc file.