Pomp

Very basic postgres one-way migrations.

CLI Usage

Pomp expects a folder named 'migrations' in the folder you execute it from (or the environment variable POMP_WD), containing SQL files with naming like "1234-my-new-table.sql". The number at the start of each must be unique.

It will use standard psql environment variables to connect, or POSTGRES_URL if it exists.

There are a few different commands that you can run.

npx pomp new my-new-table

'new' will create a new sql file in the migrations folder with the current unix timestamp as its version number and then attempt to open the EDITOR environment variable (or vi if there is no EDITOR environment variable.)

npx pomp version

'version' will show the latest local and remote version numbers.

npx pomp pending

'pending' shows which local migrations need to be applied. it will exit with code 1 if there are migrations to run, otherwise 0.

npx pomp run

'run' will execute any local migration files that are missing in the postgres database.

npx pomp skip 1 2 3 4

'skip' will register the supplied migration versions as having completed without actually running them.

You can also get this information by running npx pomp help

JS Usage

You can use Pomp from your application by importing it and supplying functions to read and run the queries.

import { Pomp } from 'pomp';
import postgres from 'postgres';
import { readdir, readFile } from 'fs/promises';

const conn = postgres('postgres://localhost');

const pomp = new Pomp({
    // runSqlQuery should return an array of result rows
    async runSqlQuery(text) {
        return await conn.unsafe(text);
    }
    // listLocalMigrations returns an array of
    // each migration that can be applied.
    async listLocalMigrations() {
        return await readdir('migrations');
    }
});

With that created you can run the various operations on it:

// run all pending local migrations, the supplied function is
// called with the 'name' supplied to listLocalMigrations
await pomp.runMigrations(async name => (
    await readFile(`migrations/${name}`, 'utf-8')
));

// get a list of the migrations that need to run
await pomp.pendingMigrations();

// run a single migration with its contents,
// and mark it as completed
await pomp.runMigrations(3, 'create table my_new_table (id serial);');

// mark a migration version as having completed
await pomp.skipMigration(3);

// get the latest remote and local database version
await pomp.latestVersions();

// create an array of local migrations
await pomp.listLocalMigrations();

// create an array of migrations from the database
await pomp.listRemoteMigrations();