Node.js database migration management built exclusively for postgres.
$ npm install 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 pg-migrate
and if not, you can run ./node_modules/.bin/pg-migrate
You can specify your database connection information using config.
// config/default.json
{
"db": "postgres://postgres:password@localhost:5432/name"
}
or
// config/default.json
{
"db": {
"user": "postgres",
"password": "",
"host": "localhost",
"port": 5432,
"name": "name"
}
}
You could also specify your database url by setting the environment variable DATABASE_URL
.
DATABASE_URL=postgres://postgres@localhost/name node-pg-migrate
You can specify custom JSON file with config (format is same as for db
entry of config file)
If a .env file exists, it will be loaded using dotenv (if installed) when running the pg-migrate binary.
Depending on your project's setup, it may make sense to write some custom grunt tasks that set this env var and run your migration commands. More on that below.
The following are the available commands:
pg-migrate create {migration-name}
- creates a new migration file with the name you give it. Spaces and underscores will be replaced by dashes and a timestamp is prepended to your file name.pg-migrate up
- runs all up migrations from the current state.pg-migrate up {N}
- runs N up migrations from the current state.pg-migrate down
- runs a single down migration.pg-migrate down {N}
- runs N down migrations from the current state.pg-migrate unlock
- unlocks migrations (if previous up/down migration failed and was not automatically unlocked).
You can adjust defaults by passing arguments to pg-migrate
:
-
config-file
(f
) - The file with migration JSON config (defaults to undefined) -
schema
(s
) - The schema on which migration will be run (defaults topublic
) -
database-url-var
(d
) - Name of env variable with database url string (defaults toDATABASE_URL
) -
migrations-dir
(m
) - The directory containing your migration files (defaults tomigrations
) -
migrations-schema
- The schema storing table which migrations have been run (defaults to same value asschema
) -
migrations-table
(t
) - The table storing which migrations have been run (defaults topgmigrations
) -
check-order
- Check order of migrations before running them (defaults totrue
, to switch it off supply--no-check-order
on command line). (There should be no migration with timestamp lesser than last run migration.)
See all by running pg-migrate --help
.
Most of configuration options can be also specified in config file.
For SSL connection to DB you can set PGSSLMODE
environment variable to value from list other then disable
.
e.g. PGSSLMODE=require pg-migrate up
(pg will take it into account)
You can use config or your own json file with configuration (config-file
command line option).
Available options are:
-
migrations-dir
,migrations-schema
,migrations-table
,check-order
- same as above -
either
url
or [user
], [password
],host
(defaults to localhost),port
(defaults to 5432),name
- for connection details -
type-shorthands
- for column type shorthandsYou can specify custom types which will be expanded to column definition (e.g. for
module.exports = { "type-shorthands": { id: { type: 'uuid', primaryKey: true }, createdAt: { type: 'timestamp', notNull: true, default: new require('node-pg-migrate').PgLiteral('current_timestamp') } } }
it will inpgm.createTable('test', { id: 'id', createdAt: 'createdAt' });
produce SQLCREATE TABLE "test" ("id" uuid PRIMARY KEY, "createdAt" timestamp DEFAULT current_timestamp NOT NULL);
).
pg-migrate
automatically checks if no other migration is running. To do so, it locks the migration table and enters comment there.
There are other options how to do it, but I choose this one (see #88).
In some circumstances it is possible that lock will not be released (Error message - Error: Unable to fetch migrations: Error: Another migration is already running
).
In that case you need to run pg-migrate unlock
to release the lock again.
When you run pg-migrate create
a new migration file is created that looks like this:
exports.up = function up(pgm) {
}
exports.down = function down(pgm) {
}
pgm
is a helper object that provides migration operations and run
is the callback to call when you are done.
IMPORTANT
Calling the migration functions on pgm
doesn't actually migrate your database. These functions just add sql commands to a stack that is run.
If exports.down
is not present in a migration, pg-migrate will try to automatically infer the operations that make up the down migration by reversing the operations of the up migration. Only some operations have automatically inferrable equivalents (details below on each operation). Sometimes, migrations are destructive and cannot be rolled back. In this case, you can set exports.down = false
to tell pg-migrate that the down migration is impossible.
In some cases, you may want to perform some async operation during a migration, for example fetching some information from an external server, or inserting some data into the database. To make a migration block operate in async mode, just add another callback argument to the function signature. However, be aware that NONE of the pgm operations will be executed until run()
is called. Here's an example:
exports.up = function up(pgm, run) {
doSomethingAsync(function() {
run();
});
}
Another way how to perform some async operation is to return Promise from up
or down
function. Example:
exports.up = function(pgm) {
return new Promise(resolve => {
// doSomethingAsync
resolve();
});
}
The pgm
object that is passed to each up/down block has many different operations available. Each operation is simply a function that generates some sql and stores it in the current pgm context.
By default, each migration will be run in a transaction. To disable transactions for a specific migration, call pgm.noTransaction()
This is required for some SQL operations that cannot be run within a transaction. It should be used carefully.
Create a new table - postgres docs
Arguments:
tablename
[string] - name for the new tablecolumns
[object] - column names / options -- see column definitions sectionoptions
[object] - table options (optional)inherits
[string] - table to inherit from
Reverse Operation: dropTable
Drop existing table - postgres docs
Arguments:
tablename
[string] - name of the table to drop
Rename a table - postgres docs
Arguments:
tablename
[string] - name of the table to renamenew_table
[object] - new name of the table
Reverse Operation: same operation in opposite direction
Add columns to an existing table - postgres docs
Arguments:
tablename
[string] - name of the table to alternew_columns
[object] - column names / options -- see column definitions section
Aliases: addColumn
Reverse Operation: dropColumns
Drop columns from a table - postgres docs
Arguments:
tablename
[string] - name of the table to altercolumns
[array of strings or object] - columns to drop (if object, uses keys)
Aliases: dropColumn
Rename a column - postgres docs
Arguments:
tablename
[string] - name of the table to alterold_column_name
[string] - current column namenew_column_name
[string] - new column name
Reverse Operation: same operation in opposite direction
Alter a column (default value, type, allow null) - postgres docs
Arguments:
tablename
[string] - name of the table to altercolumn_name
[string] - column to altercolumn_options
[object] - optional new column optionsdefault
[string or null] - null, stringtype
[string] - new datatypenotNull
[boolean] - sets NOT NULL if trueusing
[string] - adds USING clause to change values in column
Add a named column constraint - postgres docs
Arguments:
tablename
[string] - name of the table to alterconstraint_name
[string] - name for the constraintexpression
[string] - constraint expression (raw sql)
Aliases: createConstraint
Reverse Operation: dropConstraint
Drop a named column constraint - postgres docs
Arguments:
tablename
[string] - name of the table to alterconstraint_name
[string] - name for the constraint
Create a new index - postgres docs
Arguments:
tablename
[string] - name of the table to altercolumns
[string or array of strings] - columns to add to the indexoptions
[index options] - optional options:name
[string] - name for the index (one will be inferred from table/columns if undefined)unique
[boolean] - set to true if this is a unique indexwhere
[string] - raw sql for where clause of indexconcurrently
[boolean] - create this index concurrentlymethod
[string] - btree | hash | gist | spgist | gin
Aliases: addIndex
Reverse Operation: dropIndex
Drop an index - postgres docs
Arguments:
tablename
[string] - name of the table to altercolumns
[string or array of strings] - column names, used only to infer an index nameoptions
[index options] - optional options:name
[string] - name of the index to drop
Install postgres extension(s) - postgres docs
Arguments:
extension
[string or array of strings] - name(s) of extensions to install
Aliases: addExtension
Reverse Operation: dropExtension
Un-install postgres extension(s) - postgres docs
Arguments:
extension
[string or array of strings] - name(s) of extensions to install
Create a new data type - postgres docs
Arguments:
type_name
[string] - name of the new typevalues
[array of strings or object] if an array the contents are possible values for an enum type, if an object names and types for a composite type
Aliases: addType
Reverse Operation: dropType
Drop a custom data type - postgres docs
Arguments:
type_name
[string] - name of the new type
Create a new role - postgres docs
Arguments:
role_name
[string] - name of the new rolerole_options
[object] - options:superuser
[boolean] - default falsecreatedb
[boolean] - default falsecreaterole
[boolean] - default falseinherit
[boolean] - default truelogin
[boolean] - default falsereplication
[boolean] - default falsebypassrls
[boolean]limit
[number] -password
[string] -encrypted
[boolean] - default truevalid
[string] - timestampinRole
[string or array] - role or array of rolesrole
[string or array] - role or array of rolesadmin
[string or array] - role or array of roles
Reverse Operation: dropRole
Drop a role - postgres docs
Arguments:
role_name
[string] - name of the new role
Alters a role - postgres docs
Arguments:
role_name
[string] - name of the new rolerole_options
[object] - see
Renames a role - postgres docs
Arguments:
old_role_name
[string] - old name of the rolenew_role_name
[string] - new name of the role
Create a new function - postgres docs
Arguments:
-
function_name
[string] - name of the new function -
function_params
[array] - parameters of the new functionEither array of strings or objects. If array of strings, it is interpreted as is, if array of objects:
mode
[string] -IN
,OUT
,INOUT
, orVARIADIC
name
[string] - name of argumenttype
[string] - datatype of argumentdefault
[string] - default value of argument
-
function_options
[object] - options:returns
[string] - returns clauselanguage
[string] - language name of function definitionreplace
[boolean] - create or replace functiondelimiter
[string] - delimiter for definitionwindow
[boolean] - window functionbehavior
[string] -IMMUTABLE
,STABLE
, orVOLATILE
onNull
[boolean] -RETURNS NULL ON NULL INPUT
parallel
[string] -UNSAFE
,RESTRICTED
, orSAFE
-
definition
[string] - definition of function
Reverse Operation: dropFunction
Drop a function - postgres docs
Arguments:
function_name
[string] - name of the the function to dropfunction_params
[array] - seedrop_options
[object] - options:ifExists
[boolean] - drops function only if it existscascade
[boolean] - drops also dependent objects
Renames a function - postgres docs
Arguments:
old_function_name
[string] - old name of the functionfunction_params
[array] - seenew_function_name
[string] - new name of the function
Create a new function - postgres docs
Arguments:
table_name
[string] - name of the table where the new trigger will livetrigger_name
[string] - name of the new triggertrigger_options
[object] - options:when
[string] -BEFORE
,AFTER
, orINSTEAD OF
operation
[string or array] -INSERT
,UPDATE[ OF ...]
,DELETE
orTRUNCATE
constraint
[boolean] - creates constraint triggerfunction
[string] - the name of procedure to executelevel
[string] -STATEMENT
, orROW
condition
[string] - condition to met to execute triggerdeferrable
[boolean] - flag for deferrable constraint triggerdeferred
[boolean] - flag for initially deferred deferrable constraint trigger
definition
[string] - optional definition of function which will be created with same name as trigger
Reverse Operation: dropTrigger
Drop a trigger - postgres docs
Arguments:
table_name
[string] - name of the table where the trigger livestrigger_name
[string] - name of the the trigger to dropdrop_options
[object] - options:ifExists
[boolean] - drops trigger only if it existscascade
[boolean] - drops also dependent objects
Renames a trigger - postgres docs
Arguments:
table_name
[string] - name of the table where the trigger livesold_trigger_name
[string] - old name of the triggernew_trigger_name
[string] - new name of the trigger
Run raw sql -- with some optional very basic mustache templating
Arguments:
sql
[string] - SQL query to runargs
[object] - (optional) key/val of arguments to replace
Inserts raw string, which is not escaped
e.g. pgm.func('CURRENT_TIMESTAMP')
to use in default
option for column definition
Arguments:
sql
[string] - string to not be escaped
The createTable
and addColumns
methods both take a columns
argument that specifies column names and options. It is a object (key/value) where each key is the name of the column, and the value is another object that defines the options for the column.
type
[string] - data type (use normal postgres types)unique
[boolean] - set to true to add a unique constraint on this columnprimaryKey
[boolean] - set to true to make this column the primary keynotNull
[boolean] - set to true to make this column not nullcheck
[string] - sql for a check constraint for this columnreferences
[string] - a table name that this column is a foreign key toonDelete
[string] - adds ON DELETE constraint for a reference columnonUpdate
[string] - adds ON UPDATE constraint for a reference column
Data type strings will be passed through directly to postgres, so write types as you would if you were writing the queries by hand.
There are some aliases on types to make things more foolproof: (int, string, float, double, datetime, bool)
There is a shorthand to pass only the type instead of an options object:
pgm.addColumns('myTable', { age: 'integer' });
is equivalent to
pgm.addColumns('myTable', { age: { type: 'integer' } });
There is a shorthand for normal auto-increment IDs:
pgm.addColumns('myTable', { id: 'id' });
is equivalent to
pgm.addColumns('myTable', { id: { type: 'serial', primaryKey: true } });
Instead of passing string as name to pgm
functions, you can pass an object with keys schema
and name
. E.g.
pgm.createTable( {schema: 'my_schema', name: 'my_table_name'}, {id: 'serial'});
will generate
CREATE TABLE "my_schema"."my_table_name" (
"id" serial
);
Why only Postgres? - By writing this migration tool specifically for postgres instead of accommadating 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"!
The MIT License (MIT)
Copyright (c) 2016 Jan Dolezel <[email protected]>
Copyright (c) 2014 Theo Ephraim
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.