salsita / node-pg-migrate

Node.js database migration management for PostgreSQL

node-pg-migrate

The core maintainer of this project moved to @Shinigami92 (also core maintainer of FakerJS and core member of Vite).
The project is and remains under the MIT license.

Node.js database migration management built exclusively for postgres. (But can also be used for other DBs conforming to SQL standard - e.g. CockroachDB.)
Started by Theo Ephraim, then handed over to Salsita Software and now maintained by @Shinigami92.

Preconditions

• Node.js 18 or higher
• PostgreSQL 12.8 or higher (lower versions may work but are not supported officially)

If you don't already have the pg library installed, you will need to add pg as either a direct or dev dependency

npm add pg

Installation

npm add --save-dev node-pg-migrate

Installing this module adds a runnable file into your node_modules/.bin directory. If installed globally (with the -g option), you can run node-pg-migrate and if not, you can run ./node_modules/.bin/node-pg-migrate.js

Quick Example

Add "migrate": "node-pg-migrate" to scripts section of your package.json so you are able to quickly run commands.

Run npm run migrate create my-first-migration. It will create file xxx_my-first-migration.js in migrations folder.
Open it and change contents to:

exports.up = (pgm) => {
  pgm.createTable('users', {
    id: 'id',
    name: { type: 'varchar(1000)', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createTable('posts', {
    id: 'id',
    userId: {
      type: 'integer',
      notNull: true,
      references: '"users"',
      onDelete: 'cascade',
    },
    body: { type: 'text', notNull: true },
    createdAt: {
      type: 'timestamp',
      notNull: true,
      default: pgm.func('current_timestamp'),
    },
  });
  pgm.createIndex('posts', 'userId');
};

Save migration file.

Now you should put your DB connection string to DATABASE_URL environment variable and run npm run migrate up. (e.g. DATABASE_URL=postgres://test:test@localhost:5432/test npm run migrate up)

You should now have two tables in your DB 🎉

If you want to change your schema later, you can e.g. add lead paragraph to posts:

Run npm run migrate create posts_lead, edit xxx_posts_lead.js:

exports.up = (pgm) => {
  pgm.addColumns('posts', {
    lead: { type: 'text', notNull: true },
  });
};

Run npm run migrate up and there will be a new column in posts table 🎉

Want to know more? Read docs:

Docs

Full docs are available at https://salsita.github.io/node-pg-migrate

Explanation & Goals

Why only Postgres? - By writing this migration tool specifically for postgres instead of accommodating many databases, we can actually provide a full featured tool that is much simpler to use and maintain. I was tired of using crippled database tools just in case one day we switch our database.

Async / Sync - Everything is async in node, and that's great, but a migration tool should really just be a fancy wrapper that generates SQL. Most other migration tools force you to bring in control flow libraries or wrap everything in callbacks as soon as you want to do more than a single operation in a migration. Plus by building up a stack of operations, we can automatically infer down migrations (sometimes) to save even more time.

Naming / Raw Sql - Many tools force you to use their constants to do things like specify data types. Again, this tool should be a fancy wrapper that generates SQL, so whenever possible, it should just pass through user values directly to the SQL. The hard part is remembering the syntax of the specific operation, not remembering how to type "timestamp"!

License

MIT

node-pg-migrate's Issues

Setting options.database_url has no effect in MigrationRunner( options )

When using node-pg-migrate programmatically by invoking MigrationRunner (lib/runner.js) with a database_url string. For example:

var MigrationRunner = require('node-pg-migrate/lib/runner')
var options = {
  database_url: 'pg://testuser:testpw@localhost:5432/test_db',
  dir: 'migrations',
  migrations_table: 'pgmigrations',
  actions: 'up',
  num_migrations: Infinity,
  file: ''
};
var runner = new MigrationRunner(options);
  runner.run(function (err) {
    if (err) {
      process.exit(1);
    }
  }
};

This will not work as there is a bug in lib/db that ignores options.database_url

Running `down` migrations with count > 1 runs in the wrong order?

The docs say that "pg-migrate down {N} - runs N down migrations from the current state". This doesn't seem to be the case. Consider the following example:

src/server/db/migrations
├── 1462404518355_add_updated_at_function.js
└── 1462404783753_create_users_table.js

If I've run an up migration, my current state should be that of the 1462404783753_create_users_table migration – e.g. the add_updated_at_function migration ran, and then the create_users_table migration ran.

If I run a pg-migrate down 2, it tries to run the migrations in the creation order. This fails in this instance, as the users table (created in the second migration) uses the function created in the first as a trigger. Trying to drop the function before dropping the table that depends on it doesn't work.

Is this a bug, or am I mis-understanding how you intend for down migrations to be used? If it's a bug, I have a fix that I can submit as a PR (reverse-sort the to_run array on down migrations).

Different migration table name

I'd like to use something else (conflict with existing db).

Could you provide it as configuration option?

Cannot use embedded value in config

Hi

I use configuration with node-config but not with db field but with postgres.url field and when I run pg-migrate --config-value postgres.url up I have message The $DATABASE_URL environment variable is not set.. But when I try to use (for example) --config-value abc with database url it is working properly. So problem is that I cannot use embedded values from config.
Is I do something wrong or it is real issue?

Thank you for your product

Add configuration for 'id' shorthand, or be able to create own shorthand

Right now we have 'id' as a shorthand for { type: 'serial', primaryKey: true }. But in my application I would like to use uuid's instead, while I could of course specify that every single time, it is more DRY if I could just create my own shorthand.

This would require a sort of configuration file.

If this is something that seems to fit in the library, I could try and submit a PR.

Add support for specifying a migration template

I'd like to use this with TypeScript, and so having a way to provide my own template file (e.g. in a migration-template.ts) through a CLI parameter would be awesome.

For example the template I'd use would be:

import { MigrationBuilder } from 'node-pg-migrate';

export function up(pgm: MigrationBuilder) {
}

export function down(pgm: MigrationBuilder) {
}

Any concerns / thoughts?

migration creation should not require DATABASE_URL

Literally:

$ pg-migrate create social_contact_table
The $DATABASE_URL environment variable is not set.

It does not use it while creation, maybe exclude it?

Strict mode for safe migrations

It would be cool to have a similar feature like this https://github.com/ankane/strong_migrations

It forces you to explicitly mark downtime-creating migrations as safe.

It could be implemented as an optional config flag.

Why do you need callback at all?

If you are doing only synchronous operations until run() called then the interface of your migrations could be just:

exports.up = function (pgm) {
    pgm.something(...);
    pgm.somethingElse(...);
}

Lock concurrent migrations

If 2 apps starting and running own migrations, need to create some sort of lock table or something like this (maybe postgres provides user locks not sure).
This is all to make sure for each migration table at each moment of time only one migration process is executing.

DROP lowerCamelCase column fails

I'm trying to have a migration that drops a column from a table. The column is fromPrice. When adding a column or creating a table, the column names are wrapped in strings, where as when dropping, the column names are just inserted, causing case to be ignored.

root@dev:/var/www# pg-migrate up
> Migrating files:
> - 1482855033613_journey_package
### MIGRATION 1482855033613_journey_package (up) ###
BEGIN;
ALTER TABLE "journey"
  DROP fromPrice;
CREATE TABLE "journey_package" (
  "id" serial PRIMARY KEY,
  "journeyId" integer NOT NULL REFERENCES journey (id) ON DELETE CASCADE,
  "title" text NOT NULL,
  "description" text,
  "photo" integer,
  "fromPrice" integer
);
INSERT INTO pgmigrations (name, run_on) VALUES ('1482855033613_journey_package', NOW());
COMMIT;


> Rolling back attempted migration ...
error: column "fromprice" of relation "journey" does not exist

Wouldn't it be possible to just rewrite this line as following (quickfix):

actions: columns.map(function (column) { return '"'+column+'"'; }).join(',\n').replace(/^/gm, '  DROP '),

---

Currently I just use

pgm.dropColumns("journey", [
	"\"fromPrice\""
]);

to avoid the problem but I'd rather see a more permanent solution.

issue with references in postgres 9.6

Hi, somehow in the generated code, I get an issue with the references (foreign keys) in postgresql.

By example I have 2 tables

  pgm.createTable("tbl1", {
    id: { type: 'serial', notNull: true, primaryKey: true },
    other_field: { type: 'smallint', notNull: true }
  });
  pgm.createTable("tbl2", {
    id: { type: 'serial', notNull: true, primaryKey: true },
    tbl1_id: { type: 'serial', notNull: true, references: 'tbl1(id)' },
    description: { type: 'text', notNull: true }
  })

Then the generated SQL will be generated (I might omit some of the code since I want to point out only one thing).

BEGIN;
CREATE TABLE "tbl1" (
  "id" serial PRIMARY KEY NOT NULL,
  "other_field" smallint NOT NULL
);
CREATE TABLE "tbl2" (
  "id" serial PRIMARY KEY NOT NULL,
  "tbl1_id" serial NOT NULL REFERENCES "tbl1(id)",
  "description" text NOT NULL
);

COMMIT;

The "tbl1_id" serial NOT NULL REFERENCES "tbl1(id)" gives an error on tbl1(id). This should generate something like "tbl1_id" serial NOT NULL REFERENCES "tbl1"("id") in order to work. Maybe it's a particularity in postgresql 9.6.

Impossible to use schemas

Generated quotation marks around table names (CREATE TABLE 'mytable') make it impossible to use schemas, as

CREATE TABLE 'myschema.mytable';

will actually create a table with a dot in public schema.

db.init(conenction_string) should be db.init(connection_string)

var pg = require('pg');
//or native libpq bindings
//var pg = require('pg').native

var connection_string = process.env.DATABASE_URL;
var client;
var client_active = false;

module.exports.init = function(conenction_string){
  client = new pg.Client(connection_string);
}

Declaration of multiple column in a single primary key

We should be able to declare a primary key on multiple column.
Here an example:

  // Linked table declarations
  pgm.createTable('test', {
    test  : {
      type       : 'bigserial',
      primaryKey : true,
      notNull    : true
    },
    test2      : {
      type       : 'bigserial',
      primaryKey : true,
      notNull    : true
    }
  });

Actual SQL

CREATE TABLE "test" (
  test bigserial PRIMARY KEY NOT NULL, 
  test2 bigserial PRIMARY KEY NOT NULL
);

Here sql expected

CREATE TABLE "test"
(
  test bigserial NOT NULL,
  test2 bigserial NOT NULL,
  CONSTRAINT test_pkey PRIMARY KEY (test, test2)
)

Currently we've got an error:

> Rolling back attempted migration ...
error: les clés primaires multiples ne sont pas autorisées pour la table « provider_user »
    at Connection.parseE (/opt/node_modules/node-pg-migrate/node_modules/pg/lib/connection.js:534:11)
    at Connection.parseMessage (/opt/node_modules/node-pg-migrate/node_modules/pg/lib/connection.js:361:17)
    at Socket.<anonymous> (/opt/node_modules/node-pg-migrate/node_modules/pg/lib/connection.js:105:22)
    at Socket.emit (events.js:107:17)
    at readableAddChunk (_stream_readable.js:163:16)
    at Socket.Readable.push (_stream_readable.js:126:10)
    at TCP.onread (net.js:538:20)

Adding 'alterType' action

Hello
Please, add possibility to alterType
Thank you

Default value

I have this code:

pgm.createTable('users', {
    id: { type: 'bigserial', primaryKey: true },
    created_at: { type: 'timestamp ' },
    updated_at: { type: 'timestamp ' },
    deleted_at: { type: 'timestamp ' }
});

pgm.alterColumn('users', 'created_at', { default: 'CURRENT_TIMESTAMP' });
pgm.alterColumn('users', 'updated_at', { default: 'CURRENT_TIMESTAMP' });
pgm.alterColumn('users', 'deleted_at', { default: 'CURRENT_TIMESTAMP' });

But i get the error:
error: date/time value "current" is no longer supported
at Connection.parseE (/home/ivan/.npm/lib/node_modules/node-pg-migrate/node_modules/pg/lib/connection.js:534:11)
at Connection.parseMessage (/home/ivan/.npm/lib/node_modules/node-pg-migrate/node_modules/pg/lib/connection.js:361:17)
at Socket. (/home/ivan/.npm/lib/node_modules/node-pg-migrate/node_modules/pg/lib/connection.js:105:22)
at Socket.emit (events.js:107:17)
at readableAddChunk (_stream_readable.js:163:16)
at Socket.Readable.push (_stream_readable.js:126:10)
at TCP.onread (net.js:538:20)

Any idea how i can set current_timestamp as default?

Run migration on change

When creating procedure it make sence to run migration each time or when file changes.
So it could be good idea to get file hash and store it in migrations table (or mtime, but hash seems better).
And execute migration each time file with procedure changes.

ssl cli flag

Hi,

Thanks for writing this awesome tool!

How do you feel about adding ssl as a cli flag?

eg. node_modules/.bin/pg-migrate up --ssl=true

This need mainly arises from using heroku + heokru postgres.
Heroku requires you to connect to heroku postgres with ssl but provides an enviroment variable DATABASE_URL without ?ssl=true.

"node-pg-migrate down" doesn't work when the "down" method is inferred

Create a migration with no down method:

exports.up = function(pgm) {
  pgm.createTable("foo", { bar: "id" });
};

Run pg-migrate up and it shows:

### MIGRATION 1494965078152_foo (UP) ###
BEGIN;
CREATE TABLE "foo" (
  "bar" serial PRIMARY KEY
);
INSERT INTO "public"."pgmigrations" (name, run_on) VALUES ('1494965078152_foo', NOW());
COMMIT;

Then run pg-migrate down and it shows:

### MIGRATION 1494965078152_foo (UP) ###
BEGIN;
DROP TABLE "foo";
INSERT INTO "public"."pgmigrations" (name, run_on) VALUES ('1494965078152_foo', NOW());
COMMIT;

Note that is says it is running the UP migration, runs the command from the correct down migration, and then INSERT's instead of DELETE's from the pgmigrations table.

The problem seems to stem from the switch at line 59 of migrations.js. When the down migration is inferred this.up == this.down (see line 103 of migrations.js) so it always run the up action.

Support for function operations

Is there interest in having support for function operations added to this library? For example renaming, creating, deleting, etc, or is the sql helper good enough here?

Permit array of arrays in unique constrant to allow multiple unique constraints

{
  constraints: {
    unique: [
      [ 'column1', 'column2'],
      ['column3', 'column4']
    ]
  }
}

Should produce two unique constraints, but it doesn't. Instead I get column "undefined" named in key does not exist.

errors on peerDependency when using [email protected]

yarn check reports error when [email protected] is used:

error node-pg-migrate#pg@^4.3.0 doesn't satisfy found match of [email protected]
error Found 1 errors.

Changing peerDependencies in package.json make it go away:

  "peerDependencies": {
    "pg": "^4.3.0 || >=5.0.0 <7.0.0"
  },

Allow peerDependency with pg > 7.0.0

I found that node-pg just release a new build yesterday with [email protected] and this project's peerDependency is not matched the latest tag of node-pg in that case

npm ERR! argv "/home/travis/.nvm/versions/node/v4.5.0/bin/node" "/home/travis/.nvm/versions/node/v4.5.0/bin/npm" "install" "-g" "mocha" "node-pg-migrate" "pg" "eslint"
npm ERR! node v4.5.0
npm ERR! npm  v2.15.9
npm ERR! code EPEERINVALID
npm ERR! peerinvalid The package [email protected] does not satisfy its siblings' peerDependencies requirements!
npm ERR! peerinvalid Peer [email protected] wants pg@>=4.3.0 <7.0.0

Is there any reason to add this restriction: pg@>=4.3.0 <7.0.0

rendering current schema

How hard do you think it would be to render out the current schema of the database, in the way active record does. or if it's even in the scope of this project

addTypeValue's `after` option is using BEFORE instead of AFTER

node-pg-migrate/lib/operations/types.js

Line 46 in bd497d8

const afterClause = after ? ` BEFORE ${after}` : '';

should this say AFTER?

Schema Support in Migrations Table

I noticed that support has been added for specifying the postgres schema in creates and queries. However, I noticed the there is no way to specify the schema for the migrations table (default 'pgmigrations'). 'public' appears to be hardcoded schema in runner.js. It would be great if the migrations table could be assigned a schema.

Plan migrations

It would be great to have a optional argument like "plan" to print out the generated sql without running it.

createType generates malformed SQL

The following command:

pgm.createType('some_type', ['val1', 'val2', 'val3']);

Will generate the following SQL:

CREATE TYPE some_type AS ENUM ('val1', val2', val3');

Note the missing ' before val2 and val3.

Docs & npm package out of date

I pulled down the package from npm/yarn yesterday and followed the instructions to set the configuration. However, I kept getting the error: The $DATABASE_URL environment variable is not set.

However, I had set up my config/default.json as such:

{
  "port": 3000,
  "db": {
    "user": "postgres",
    "password": "",
    "host": "localhost",
    "port": 5432,
    "name": "astromercury_dev"
  }
}

When I looked at the source in node_modules I realized that what i has was out of date with what is listed on github. Just thought I'd let y'all know.

Correct work with custom schemas

Hi
Thank you much for your tool

When I try to create table using this syntax I get error schema "my_schema" does not exist.
I don't know how to do it better: auto-CREATE SCHEMA IF NOT EXISTS or create methods to create/drop schemas, but I think it will be very useful.

Thanks, Alik

How to set length for string column?

I know there is no docs, that's why i am asking.
How would you set the limit for string type column, and how would you set the type, 'string' or 'character'?

Thanks, Ivan.

deleted

deleted

Fails with preceeding/existing migrations

When creating a second migration and attempting to migrate up to add it, the task fails because the first migration was already run. It seems to be running (and failing) rather than skipping previously successful/complete migrations.

I tried with the --no-check-order option, but got the same results.

I've got two migrations currently, one was created and run successfully a while ago. The second was created recently. When running the second, it sees the first and fails. Checking the pgmigrate database table does in fact show the first/initial migration there. Timestamps match.

I was hesitant to file an issue because this seems like user-error, but searching StackOverflow and the web doesn't turn up similar findings. Am I doing something wrong, or is this a legit bug?

> pg-migrate up

> Rolling back attempted migration ...
Error: Not run migration 1491784040558_create-table-users is preceding already run migration 1491784040558_create-table-users.js

default value false has issues

When setting a default value for a column, currently we have to set the default to be 'false' but it should be able to accept the javascript boolean false

Best way to set ON DELETE constraints?

I could use pgm.sql to create the tables manually, but I'd like something more elegant. Does the tool provide a better way of doing this?

Programmatic API

Hey. I want to integrate with Gulp, but I need programmatic API.

Could you implement it and fill main field in package.json?

Add support for ignoring files

It would be neat if it was possible to have node-pg-migrate ignore certain files.
I tend to forget to disable swap files when editing migrations using vim, and often find node-pg-migrate failing due to not being able to process the vim swapfile (As it attempts to read and process every file in the migrations directory).

Using same migrations for multiple schemas

I am trying to build a multi tenant app using postgresql schemas. Every customer of my SAAS service will have same table structure, but different schema.

I don't see a way to specify schema to use while running migrations. Is it possible?

question: add columns to all tables

sorry I never used this library, but some migrations are about add columns or drop columns in all the tables, or all tables except a list of tables.
there are functions in this library for that ?

pg not listed as a dependency

I installed node-pg-migrate globally using $ npm install node-pg-migrate -g and got the following error when running $ pg-migrate create add-users:

module.js:340
    throw err;
          ^
Error: Cannot find module 'pg'
    at Function.Module._resolveFilename (module.js:338:15)
    at Function.Module._load (module.js:280:25)
    at Module.require (module.js:364:17)
    at require (module.js:380:17)
    at Object.<anonymous> (/usr/local/lib/node_modules/node-pg-migrate/lib/db.js:5:10)
    at Module._compile (module.js:456:26)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)
    at Module.require (module.js:364:17)

Looking at the package.json I noticed that pg is listed as a devDependency. Should this be changed to a dependency?

Typo in lib/db.js causes custom connection string to be ignored.

Line 13, conenction_string should read connection_string.

Support role operations

It'd be useful to support role operations (e.g. creating roles). Is there any interest in this?

Support for `using` alter table

ALTER TABLE the_table ALTER COLUMN col_name TYPE integer USING (col_name::integer)

I tried something like this

exports.up = function(pgm) {
  pgm.alterColumn('pull_request_reviews', 'repo_member_id', {
    type: 'BIGINT',
    using: '(pull_request_reviews::bigint)'
  });
};

How do we set the default value for timestamp

Im trying to put in some audit fields,

Is there a way to set the default values for those timestamp fields?

Release latest changes as 2.4.0

Can you release master as 2.4.0?

'redo' command

Adding a redo command would replace this flow:

• up
• down
• up

Where one tries out if both up and down migrations work. Is that something you'd want in?

Help maintaining this project

I haven't been using postgres in my day to day work for quite a while so I'm not actually using this tool.

If anyone who is actively using it wants to help maintain it and evolve it, let me know!

Quoting problem

This migration is not working because table name in 'CREATE TABLE' statement is being quoted but table name in 'REFERENCES' clause is not being quoted.

And this one is working because of manual quoting:

Migrating down doesn't use quotes around table name

I was playing with a schema (which I'm not going to use) which has a table name in camel case like somethingElseMap. The up migration does the right thing. The down migration fails with error: table "somethingelsemap" does not exist. If I run the command in psql, I have to do DROP TABLE "somethingElseMap".