sqerge

sqerge

A forward only PostgreSQL migration tool. Uses Postgres.js library under the hood.

Get Started

• Install NPM package

  npm i sqerge -D
  

• Install Postgres.js (may install as peerDependency automatically on NPM v7+)

  npm i postgres
  

• Choose a migration directory

  mkdir foo
  

• Create some migration files

  The filename prefix represents the execution order, a unique integer followed by a hyphen. After the hyphen, it's all yours. Files not matching the prefix are ignored.

  -- file: ./foo/1-bar.sql
  
  CREATE TABLE person (
    "id" SERIAL PRIMARY KEY,
    "name" text NOT NULL
  );
  

  -- file: ./foo/2-biz.sql
  
  ALTER TABLE person
    ADD COLUMN "age" smallint;
  

  In addition to .sql, migration files can also end in .js or .mjs for ES Modules. JavaScript files must default export a function. The 1st argument is a Postgres.js instance.

  // file: ./foo/3-baz.js
  
  module.exports = async (sql) => {
    await sql`
      INSERT INTO person
        ("name", "age")
      VALUES
        ('Luke Skywalker', '21');
    `;
  };
  

• Execute migration

  Command:

  PGHOST=localhost PGPORT=5438 PGUSER=jonathan PGPASSWORD=iliketurtles PGDATABASE=dev npx sqerge ./foo
  

  Output:

  [sqerge] 3 file(s) found in '/Users/jonathan/Desktop/foo'
  [sqerge] file 1 (1-bar.sql): executed
  [sqerge] file 2 (2-biz.sql): executed
  [sqerge] file 3 (3-baz.js): executed
  

Environment Variables

• Use Postgres.js environment variables for your database connection.

• ROLE (optional)

  Execute migration with the specified database role. This is helpful for when your LOGIN role has a NOINHERIT membership to another role containting privileges required for your migration. If ROLE is provided, sqerge will execute SET ROLE for the session and then execute your migration files.