0.0.30 • Public • Published

Postgres migration tool

Build Status Coverage Status GitHub license GitHub release Dependencies

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.


npm install --save pg-upmig


yarn add pg-upmig


Either use environment variables


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 (!, 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
  // 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.




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
  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:

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"

// 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(;

  const options = {};

  if (critical) { = 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

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

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


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



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. string Latest migration name excluding timestamp.
history number Number of performed migrations.



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.


  • [ ] Custom logger implementation
  • [x] Remove dispensable dependencies

Package Sidebar


npm i pg-upmig

Weekly Downloads






Unpacked Size

54 kB

Total Files


Last publish


  • come2daddy