chaodoze/sequelize-pg-utilities

An opinionated set of database utilities that manage creating and connecting to a Postgres database

sequelize-pg-utilities

An opinionated set of database utilities that manage creating and connecting to a Postgres database

Related Projects

• sequelize-test-helpers — Mocks and helpers that simplify unit testing of Sequelize models.

Branches

Branch	Status	Coverage	Notes
develop			Work in progress
master			Latest stable release

Prerequisites

This library assumes:

1. You are using NodeJS 8.10+
2. You are using Sequelize to manage interactions with Postgresql

Install

Add sequelize-pg-utilities as a dependency:

npm i sequelize-pg-utilities

sequelize-pg-utilities has one dependency.

• pgtools

Example

Configuration

Typically a Sequelize project will include a config/config.json file with entries as follows:

{
  "development": {
    "username": "my-dev-user",
    "password": "my-dev-password",
    "database": "my-project-development"
  },
  "test": {
    "username": "my-test-user",
    "password": "my-test-password",
    "database": "my-project-test"
  },
  "production": {
    "username": "my-production-user",
    "password": "my-production-password",
    "database": "my-project-prod"
  }
}

When your application starts you'll need to construct correct database configuration values as a mix of the above config file, selected environment variables, and sensible defaults.

To do this simply create a configuration object as follows:

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

const { name, user, password, options } = configure(config)

These configuration values can then be passed in to Sequelize as follows:

const sequelize = new Sequelize(name, user, password, options)

Environment Variables Supported

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 and the extracted values will take priority over other values.

Initialisation of a database

In development and test environments, you'll need your server to create a database the first time it runs. To do this you can make an initialiser 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 {
    const result = await initialise()
    console.log(result.message)

    // now do whatever else is needed to start your server
  } catch (err) {
    console.error('Could not start server', 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)

On each retry it will wait for a progressively longer period of time, starting with 2 seconds, and increasing the delay by 2 seconds each retry.

The result object has two properties:

{
  dbNew: false, // or true if a new database was created?
  message: 'More information' // some clarifying text.
}

In production it assumes your database already exists.

Configuring migrations

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

The config is an object in the form:

{
  [env]: {
    username,
    password,
    database,
    dialect,
    host,
    port,
    operatorsAliases
  }
}

Use the migrationConfig function to generate configuration details to suit Sequelize CLI's needs.

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)

Then in .sequelizerc file do this:

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 content of the 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. This gets returned as logging in the configs.

SSL Options

If you supply ssl options in your config.json file then these will be injected into your configuration as dialectOptions and the ssl option will be set to true.

Note this is not used by makeInitialiser as it's assumed that you are only using ssl in production and you won't be trying to create your database from within your code when in production. A future release may address that however.

Development

Prerequisites

• NodeJS — Version 8.10 or better.

Test it

• npm test — runs the unit tests.
• npm run test:coverage — runs the unit tests with coverage reporting.
• npm run test:mutants — runs the mutation tests

Lint it

npm run lint

Contributing

Please see the contributing notes.