postgres-migrator
Simple database migrations for PostgreSQL.
Getting started
Install
$ npm install postgres postgres-migrator
Example
const postgres = require("postgres");
const postgresMigrator = require("postgres-migrator");
const sql = postgres();
const migrator = postgresMigrator(sql);
const migrated = await migrator.apply();
Behavior
Migration is one-way. There is no ability to reverse a migration, as this is rarely used, and is better handled with a new migration that reverts a previous one.
Directories being migrated are expected to contain zero or more migration files. Any unrecognized file types will be ignored.
Migration files are sorted before being applied, so you can be certain
that 001.sql
will be applied before 002.sql
.
As you would expect, only unapplied migrations will be applied.
Each migration is run in a database transaction. Any errors
encountered are left uncaught and migration ceases at that point.
Migrations completed previously are left applied, and once the error
is corrected, migrator.apply
can be run again and migration will
continue from the last migration that was successfully applied.
Each migrator instance represents a directory of migration files. If you need to support multiple directories, you can create a migrator instance for each directory with a different table name.
Only the filename of the migration file is recorded, so relocating or renaming the directory will not affect the tracking.
Migrations are tracked using a database table named migrations
(configurable).
Migrations are assumed to be in the directory ./migrations
(configurable).
Migration files
SQL migration files
SQL migration files are just plain sql
, as you would expect. The
only restriction is that they have the extension ".sql" (lower case).
An example sql
migration file might be 010-users-table-add-email.sql
:
alter table users add column email text not null unique;
JavaScript migration files
JavaScript migration files are javascript
modules that export a
default function (can be async) that takes as its only argument an
instance of a postgres client (typically named sql
).
See the postgres documentation for its capabilities.
If a promise is returned, it will be await
ed.
The file must be in CommonJS (module.exports
) syntax.
An example javascript
migration file might be 010-users-table-add-email.js
:
module.exports = (sql) => {
return sql`alter table users add column email text not null unique`;
};
API
Creating a migrator object
const migrator = postgresMigrator(sql, { ...options });
The first argument must be a postgres client object.
The second argument is an optional options object with the following default properties:
const migrator = postgresMigrator(sql, {
table: "migrations",
directory: "./migrations",
});
Applying migrations
const migrated = await migrator.apply();
If needed, the return value is an array of migration files that were applied during that function call.
Testing if a file has been migrated
const exists = await migrator.has(filename);
Getting all migrated files
const migrated = await migrator.all();
Events
apply
after a migration file is applied
Emits migrator.on("apply", (file) => {
console.log("applied migration file:", file);
});
await migrator.apply();
Recommendations
Creating migration files
To reduce confusion, new migration files should be sorted after old ones. You can ensure this by using a shell function like the following to generate new migrations that are sorted by the timestamp when they were created:
function mkmigration() {
local name=$1
local ext=${2:-sql}
local ts=$(date +%Y%m%d%H%M%S)
touch "${PWD}/migrations/${ts}.${ext}"
}
This will allow you to do the following:
$ mkmigration users-table
$ ls migrations
20201225103045-users-table.sql
Obviously this could be easily tailored to your project, such as using a template for new migrations or a different way to establish proper sorting.
Command-line interface
I originally planned to include a command-line interface, but could not decide how to make it flexible enough. I'll leave it to you to decide how best to implement that for your project, but the following should be sufficient for most:
#!/usr/bin/env node
const postgres = require("postgres");
const postgresMigrator = require("postgres-migrator");
const sql = postgres(process.env.POSTGRES_URL);
const migrator = createMigrator(sql);
migrator.on("apply", (file) => {
console.log("applied migration file:", file);
});
migrator
.apply()
.catch((error) => {
console.error(error);
process.exit(1);
})
.finally(sql.end);
Obviously this could be easily tailored to your project, such as using a command-line parser to allow options to be configurable, or migrating multiple directories.
Contributing
Testing
Test files expect the environment variable POSTGRES_URL
to point to
your database. No existing tables will be affected, including
migrations
, as unique table names are used and dropped after the
test finishes.
A docker compose file is included which provisions a database that's configured as expected:
$ docker-compose up
To run tests:
$ npm test