Postgres migration tool

You don't rollback, if your step forward has altered data you may end with some messed up relations :sob:. 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
• [x] Remove dispensable dependencies