sequelize-pg-utilities
Sequelize, and the Sequelize CLI, use slightly different database configuration objects, and neither provide a mechanism for creating a Postgresql database. I work with Sequelize a lot in my day-job so decided to write a simple utility suite that manages the different configuration needs any project using Sequelize inevitably needs.
When your application starts yo need to construct correct database configuration values as a mix of a config file, selected environment variables, and sensible defaults.
sequelize-pg-utilities is an opinionated set of database utilities that, together, simplify creating and connecting to a Postgres database via Sequelize, or the Sequelize CLI, via a common, and minimal, set of config file settings. You can use environment variables to override the config file settings, as well as supply a range of detailed programmatic settings that get passed on to Sequelize directly. If no values are supplied and a value is needed by Sequelize then this defines sensible defaults. Any values not needed and which have no value are simply removed from the config object sent to Sequelize.
Related Projects
sequelize-test-helpers— Mocks and helpers that simplify unit testing ofSequelizemodels.
Prerequisites
This library assumes:
- You are using NodeJS 8.10+
- You are using
Sequelizeto manage interactions withPostgresql
Install
Add sequelize-pg-utilities and pg as dependencies:
npm i pg sequelize-pg-utilitiesNote: pg is a peer-dependency of sequelize-pg-utilities.
Usage Scenarios
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_URLThe database url, if provided, will override many of the belowDBsettings.DB_NAMEThe database name — You may also supply a default (see below)DB_USERThe database user — no defaultDB_PASSThe database password — no defaultDB_POOL_MAXThe maximum number of database connections — Defaults to5DB_POOL_MINThe minimum number of database connections — Defaults to1DB_POOL_IDLEThe database idle time — Defaults to10000msDB_HOSTThe database host — Defaults to'localhost'DB_PORTThe database port — Defaults to5432DB_TYPEThe 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.
The following environment variables remain unset if they don't appear, or get set in config/config.json.
DB_POOL_ACQUIREThe database pool'sacquirevalue.DB_POOL_EVICTThe database pool'sevictvalue.
Regarding DATABASE_URL
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 required by the Sequelize CLI is an object in the form:
{
[env]: {
username,
password,
database,
dialect,
host,
port,
// optionally also the following
pool: {
acquire: 20000,
evict: 15000,
min: 5,
max: 15,
idle: 10000
},
protocol,
ssl: {
rejectUnauthorized: false,
ca: 'some-root-cert',
key: 'some-client-key',
cert: 'some-client-certificate'
}
}
}Use the migrationConfig function to generate configuration details to suit Sequelize CLI's needs from your common config file, or environment variables.
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')
}Function Signature
The configure, makeInitialiser, and migrationConfig functions all have an identical signature.
They each accept the following parameters.
-
config: The content of theconfig/config.jsonfile. Required, no default.Configuration file is in the form:
{ [env]: { // everything is optional but these are usually set here username, password, database, dialect, host, port, // less often used but optionally also the following benchmark, clientMinMessages, native, omitNull, pool: { acquire: 20000, evict: 15000, min: 5, max: 15, idle: 10000 }, protocol, // if you have ssl specifics ssl: { rejectUnauthorized: false, ca: 'some-root-cert', key: 'some-client-key', cert: 'some-client-certificate' }, replication, timezone } }
-
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. -
logger: You can pass in a logger function here for Sequelize to use. Optional, default isfalse, meaning don't log anything. This gets returned asloggingin the configs. -
options: optional additional configuration that is passed through to Sequelize.These match to the options listed at sequelize/sequelize/lib/sequelize.js#126, which at the time of this writing are:
dialectModule,dialectModulePath,define,query,schema,set,sync,quoteIdentifiers,transactionType,typeValidation,hooks,pool.validate,retry.match,retry.max
Other additional options are ignored.
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
Branches
| Branch | Status | Coverage | Audit | Notes |
|---|---|---|---|---|
develop |
 |
 |
 |
Work in progress |
main |
 |
 |
 |
Latest stable release |
Development Prerequisites
Test it
npm test— runs the unit tests.npm run test:unit:cov— runs the unit tests with coverage reporting.
Lint it
npm run lintContributing
Please see the contributing notes.
![dependabot[bot] avatar](https://avatars.githubusercontent.com/in/29110?v=4 "dependabot[bot]")
![greenkeeper[bot] avatar](https://avatars.githubusercontent.com/in/505?v=4 "greenkeeper[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent