Postgres migration tool

You don't rollback, if your step forward has altered data you may end with some messed up relations 😭. Going forward only is a way to force you into thinking the migration as a whole.

• Install
• API

Install

npm install --save pg-upmig

or

yarn add pg-upmig

Configuration

Either use environment variables

UPMIG_PATH=./migrations
UPMIG_TABLE=pg_upmig
UPMIG_REJECT=false
UPMIG_DEBUG=false
UPMIG_SSL=true

or use .upmigrc.js to set global options:

/** Quick tip to load .env file in local environment for example **/
const fs = require("fs");

try {
    fs.readFileSync(".env") // read config file
    .toString("utf8") // buffer to string
    .split(/\n|\r|\r/) // break by new line
    .filter(item => /^\s*([\w.-]+)\s*=\s*(.*)?\s*$/.test(item)) // keep key / val
    .map(item => {
        const match = item.match(/^\s*([\w.-]+)\s*=\s*(.*)?\s*$/);
        if (!Object.prototype.hasOwnProperty.call(process.env, match[1])) process.env[match[1]] = match[2].replace(/^("|')(.*)\1$/, "$2"); // set env without overwritting
    });
} catch (error) {}
/** End of tip **/

module.exports = {
  migrations: "./migrations", // Where to store migrations files
  table: "pg_upmig", // Table name where migrations history is stored
  debug: false
};

Migrations folder tree view

.
+-- migrations
|   +-- sql
|   |   +-- 1600775947530_create-table.sql
|   +-- 1600775947530_create-table.js
|   +-- template.stub

Programatic migration

You can modify template.stub or any JS migration file to suit your needs as long as it stays a CommonJS module exporting a function which returns a Promise.

module.exports = (client, method) => {
  // client default to node-postgres https://www.npmjs.com/package/pg
  // method default to "query"
  return new Promise((resolve, reject) => {
    // your stuff;
  });
}

You may want to use your specific client query builder (API) instead of raw SQL queries. You may also want to skip sql file creation (API)

SQL queries

The SQL files in sql directory are placeholders for you to write migrations steps.

Usage

API

Usage

const upmig = require("pg-upmig");

const migration = new upmig({
  options: {
    migration: "./migrations",
    table: "pg_upmig",
    transactionnal: true,
    queryMethod: "query",
    connectMethod: "connect",
    endMethod: "end",
    resultKey: "rows",
    debug: false
  }
});

// Perform a migratiion over the firsts
// two pending migrations
migration.up({
  steps: 2
})
.then((result) => {
  // do something
})
.catch((error) => {
  // log error
});

Class constructor parameter

Key	Description
options	Global options
connection	Connection parameters for default node-postgres client
client	Custom postgres client instance

Global options

Key	Type	Description	Default
table	string	Migrations table in which are stored performed migrations.	pg_upmig
migrations	string	Migrations path where migration files are stored.	./migrations
transactionnal	boolean	Either to use or not transaction with migration queries.	true
queryMethod	string	Client method to perform raw SQL queries.	query
connectMethod	string	Client method to connect to database.	connect
endMethod	string	Client method to close connection.	end
resultKey	string	Client query result object key.	rows
steps	number	Limits how many pending migrations to perform. When used with to, stops on the first condition reached.	0
to	number	Define a timestamp (part of migration filname) up where to perform pending migrations. When used with to, stops on the first condition reached	0
debug	boolean	Enable debug	false

Connection parameters

See node-postgres documentation.

Custom postgres client

You can use any postgres client able to perform raw queries:

• node-postgres
• knex
• sequelize
• more...

Example with knex:

const upmig = require("pg-upmig");
const knex = require("knex");

// Instanciate knex to connect with postgres driver
const client = knex({
    client: "pg",
    connection: {
        host : process.env.PGHOST,
        user : process.env.PGUSER,
        password : process.env.PGPASSWORD,
        database : process.env.PGDATABASE,
        port: process.env.PGPORT,
        ssl: String(process.env.UPMIG_SSL).toLowerCase()==="false" ? false : { rejectUnauthorized: String(process.env.UPMIG_REJECT).toLowerCase()==="true"}
    }
});

// Instanciate pg-upmig with knex as client
const migration = new upmig({
  options: {
    migrations: "./admin-migrations",
    table: "pg_upmig_admin",
    queryMethod: "raw",
    connectMethod: null,
    endMethod: "destroy"
  },
  client
});

// Perform pending migrations till the first "critical" migration
// Could be the last migration before a breaking change
async function migrateTillCritical () {
  const migrate = await migration.pending();

  const critical = migrate.pending.find(item => /critical/i.test(item.name));

  const options = {};

  if (critical) {
    options.to = critical.ts;
  }

  return migration.up(options);
}

...

// async/await
async function customFunction () {
  // some stuff

  // try-catch is somewhere anyhow =D
  const performedMigrations = await migrateTillCritical();

  // we are done
  migration.release();
}

// or using promise
function customFunction () {
  migrateTillCritical()
  .then((performedMigrations) => {
    // do something...
  })
  .catch((error) => {
    // log error
  })
  .finally(() => {
    // we are done
    migrations.release();
  });
}

pg-upmig.create(name[, nosql])

Promise

Creates timestamped migration files (sql file creation is avoided when nosql is true) using template.stub as a template. Filename collision is handled even if this case should not happen.

Returns the full name of created file : 1600775947530_create-table

pg-upmig.pending([options])

Promise

Lists pending migrations. options is optionnal.

Returns an object:

Key	Type	Description
pending	array	Array of objects representing pending migrations files.
pending[].filename	string	Pending migration filename without extension.
pending[].ts	number	Pending migration timestamp.
pending[].name	string	Pending migration name excluding timestamp.
last	object	Latest migration performed
last.ts	number	Latest migration timestamp.
last.name	string	Latest migration name excluding timestamp.
history	number	Number of performed migrations.

pg-upmig.up([options])

Promise

Performs pending migrations till number of steps or timestamp isn't reached. options is optionnal.

Specific options

Key	Type	Description
steps	number	Limits how many pending migrations to perform. When used with to, stops on the first condition reached.
to	number	Define a timestamp (part of migration filname) up where to perform pending migrations. When used with to, stops on the first condition reached

Returns an array of objects representing migration file:

Key	Type	Description
filename	string	Migration filename without extension.
ts	number	Migration timestamp.
name	string	Migration name excluding timestamp.

Todo

• Custom logger implementation
• Remove dispensable dependencies