Node PostgreSQL patching utility.
To install pg-patch in your node project just run this command:
npm i pg-patch --save-dev
By default all patch files need to:
Be inside patch directory: pg-patch
(or in any subdirectory)
Follow naming convention: patch-$VERSION-$ACTION[-$DESCRIPTION].(sql|js)
, where:
$VERSION
- positive non-zero integer (leading zeros accepted)$ACTION
- up/rb for update to version and rollback from version respectively$DESCRIPTION
- any string matching [0-9a-zA-Z\-\_]+
Example of valid patch file names:
patch_1_up-update-to-version-1.sql
patch_1_rb-rollback-from-version-1.js
patch_2_up.sql
Above parameters can be configured.
Easiest way to use pg-patch is:
//use default configuration and patch DB to the newest version possible
require("pg-patch").run();
Above code would use default configuration settings and load DB connection settings from ENV variables.
Alternatively you could create patcher instance and run it separately:
let patcher = require("pg-patch").create();
//do something
patcher.run();
Both above examples have the same result.
You can both supply configuration for given run:
require("pg-patch").run(configObject);
As well as setting master configuration for pg-patch instance
let patcher = require("pg-patch").create(configObject);
If you specify both master and run configurations the run configuration properties have priority over master configuration ones:
let patcher = require("pg-patch").create({
a: 1,
b: 2
});
patcher.run({
a: 3
});
above code is equal to:
let patcher = require("pg-patch").create();
patcher.run({
a: 3,
b: 2
});
If you create .pgpatchrc.json
file pg-patch will use it as a source for initial configuration.
This configuration file needs to be in the same firectory from which node command is run.
Example of .pgpatchrc.json
:
{
"logLevel": "LOG",
"client": "postgres://user:password@host:port/database",
"dryRun": "LOG_ONLY"
}
Any pg-patch process returns a promise.
require("pg-patch").run(/*
any config
*/).then(function(){
//handle success
}, function(err){
//handle error
});
There are currently 3 ways in which pg-patch will try to connect to PostgreSQL.
This happens when no client is set in the configuration:
//the same for .run()
require("pg-patch").create({
//contains no client property
});
//the same for .run()
require("pg-patch").create({
client: clientConfig
});
Client configuration object work exactly as in pg package.
let clientConfig = {
user: 'foo', //env var: PGUSER
database: 'my_db', //env var: PGDATABASE
password: 'secret', //env var: PGPASSWORD
host: 'localhost', // Server hosting the postgres database
port: 5432, //env var: PGPORT
max: 10, // max number of clients in the pool
idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed
};
You can also use connection strings:
let clientConfig = 'postgres://user:password@host:port/database';
For more about pg.Client configuration check pg npm package.
let pg = require('pg');
let pgClientInstance = new pg.Client({
//configuration
});
//the same for .run()
require("pg-patch").create({
client: pgClientInstance
});
IMPORTANT: passed pg.Client instances are not closed automatically by pg-patch.
If You need to close them you can do this using promise handlers.
require("pg-patch").run({
client: pgClientInstance
}).then(function(){
pgClientInstance.end();
}, function(err){
pgClientInstance.end();
});
To perform migration one step at a time:
let pgPatch = require("pg-patch");
pgPatch.stepUp(/* configuration */); //migrate one version up
pgPatch.stepDown(/* configuration */); //migrate one version down
Similarly for the command line tool supply stepUp
/stepDown
flag:
pg-patch --stepUp
pg-patch --stepDown
Other configuration options can be passed as usual, but for obvious reasons targetVersion
will be ignored.
require("pg-patch").run({
patchDir: 'my-db-patches',
dbTable: 'public.myPatchControlTable'
});
require("pg-patch").run({
client: {
user: 'me',
database: 'my_db',
password: 'pass',
host: 'localhost',
port: 5432
}
});
require("pg-patch").run({
targetVersion: 10
});
To use pg-patch as a command line tool first install it globally:
npm i pg-patch -g
Afterwards its as easy as running:
pg-patch
Supply configuration by using command line arguments:
pg-patch --logLevel=INFO --client=postgres://user:password@host:port/database
List of possible configuration properties is the same as usual.
For detailed description about passing command line arguments see yargs
So you want more? Granted!
1.3.0
Sometimes having possibility to create more dynamic SQL patch data is beneficial and pg-patch
allows it in form of js patch files.
JS patch files have the same file name rules as SQL patch files... they just need to end with .js
.
They are normal node modules that need to export function which returns SQL string.
let sql;
/* create sql in any way you need to */
module.exports = function(){
return sql;
};
For example when you have a lot of repeating data:
let dictArray = [ /* a lot of strings */ ];
module.exports = function(){
return dictArray.map(function(v){
//just an example
//normally you would want to escape values and make only one insert
return `insert into dictionary VALUES ('${v}');`;
}).join("");
};
1.2.0
If you don't keep your patch data as files or access to these files is not supported by pg-patch
(for example FTP) you can supply such data by yourself:
let pgPatch = require("pg-patch");
pgPatch.run({
customPatchData: [
customPatchDataObj1,
customPatchDataObj2
/* ... */
]
})
where customPatchDataObjectX
needs to conform to given format:
//update 0 => 1
{
description: 'customDescription', //not required
action: 'UPDATE', //'UPDATE' or 'ROLLBACK'
version: '1', //version to update TO or rollback FROM
sql: 'select 1234;' //any valid SQL (without transaction statements)
}
Custom patch data objects can be supplied in any order.
PER_VERSION_STEP
(default)require("pg-patch").run({
transactionMode: 'PER_VERSION_STEP'
});
In this transaction mode when You want to change DB version by more than one version each update/rollback step will be contained in separate transaction block.
So if you want to move from version X to version X+5 and error happens during X+3:
SINGLE
require("pg-patch").run({
transactionMode: 'SINGLE'
});
In this transaction mode when You want to change DB version by more than one version all update/rollback steps will be contained in single transaction block.
So if you want to move from version X to version X+5 and error happens during X+3:
Each patch action step (ex. update action to version X) can be comprised of many patch files. Those files can be in ANY subdirectory of pg-patch.
If given action step has multiple patch files they will be run in order of ascending descriptions.
If two or more patch files for given action step have the same description it is assumed they can be run in any order.
So if update to X action has given patch files:
patch-X-up-want-this-first.sql
patch-X-up.sql
subdir1/patch-X-up-data-part-2.sql
subdir2/patch-X-up-data-part-1.sql
structure/patch-X-up-0001-structure.sql
data/patch-X-up-0002-data.sql
they will be joined in this order:
patch-X-up.sql
(no descriptions first)
structure/patch-X-up-0001-structure.sql
(subdirectories are ignored)
data/patch-X-up-0002-data.sql
subdir2/patch-X-up-data-part-1.sql
subdir1/patch-X-up-data-part-2.sql
patch-X-up-want-this-first.sql
Dry runs are basically test runs to verify validity of patch files (either manually or directly on DB). pg-patch supports two types of dry run:
LOG_ONLY
require("pg-patch").run({
dryRun: 'LOG_ONLY'
});
This WILL NOT execute any patch SQL on DB. Maintenance SQL required for pg-patch to work will still be run.
All patch SQL will be instead written to console on INFO
level.
TEST_SQL
require("pg-patch").run({
dryRun: 'TEST_SQL'
});
This WILL execute patch SQL on DB using transaction mode SINGLE
.
Patch process will fully rollback either on first error or after successful execution of patch SQL.
It is possible to set desired configuration level. (default: 'INFO')
require("pg-patch").run({
logLevel: 'SUCCESS' //valid values: 'DEBUG', 'LOG', 'INFO', 'WARN', 'SUCCESS', 'ERROR', 'NONE'
});
...as well as it being colorful: (default: true)
require("pg-patch").run({
enableColorfulLogs: false
});
By default all patch files need to match given regex template: ^patch-$VERSION-$ACTION(?:-$DESCRIPTION)?\\.(?:sql|js)$
Each $VAR
has distinct logic usage but for the regex purposes are shortcuts for:
$VERSION
— \\d+
$ACTION
.$ACTION
— up|rb
up
means "update TO $VERSION
" where rb
means "rollback FROM $VERSION
".$SOURCE
— \\d+
$TARGET
.$TARGET
— \\d+
$SOURCE
.$DESCRIPTION
— [0-9a-zA-Z\-\_]+
Important: template requires ($VERSION
AND $ACTION
) OR ($SOURCE
AND $TARGET
).
Those cannot be combined.
Double backslashes in above replacements are required due to how new Regex()
works.
Each of those $VARS
are then inserted are regex groups (that is the reason why $ACTION
can look like it looks).
So in case of default template ^patch-$VERSION-$ACTION(?:-$DESCRIPTION)?\\.(?:sql|js)$
the final regex is this:
^patch-(?:\d+)-(?:up|rb)(?:-(?:[0-9a-zA-Z-_]+))?\.(?:sql|js)$
Don't worry if You don't fully understand above. What matters that You can easily change how it works.
Patch files should only contain version and action:
require("pg-patch").run({
patchFileTemplate : '^$VERSION-$ACTION\\.sql$'
});
Patch files should REQUIRE a description and start with patch-:
require("pg-patch").run({
patchFileTemplate : '^patch-$VERSION-$ACTION-$DESCRIPTION\\.sql$'
});
1.1.0
pg-patch supports custom reporters. The easiest way to do this is to just supply notify method:
let pgPatcher = require("pg-patch");
let patcher = pgPatcher.create({
notify: [{
'^PROCESS:.*': function(data, params, combinedParams){
//do something with 'PROCESS:*' notifications
},
'^PATCH.*': function(data, params, combinedParams){
//do something with 'PATCH*' notifications
}
}]
})
above is a shortcut to creating basicReporter
:
let pgPatcher = require("pg-patch");
let basicReporter = new pgPatcher.reporters.basic({
'^PROCESS:.*': function(data, params, combinedParams){
//do something with 'PROCESS:*' notifications
},
'^PATCH.*': function(data, params, combinedParams){
//do something with 'PATCH*' notifications
}
});
let patcher = pgPatcher.create({
reporters: [
basicReporter
]
})
If you would like to check all currently possible messages please check lib/reporters/console-reporter.js
file.
client — Type: Object|String
Default: null
DB connection client / settings. See Connecting to the PostgreSQL.
customPatchData — Type: Array
Default: null
Supplies pg-patch
with custom patch data. See Custom patch data sources.
dbTable — Type: String
Default: public.pgpatch
pg-patch maintenance table to be used. Can also define schema: schema.table. If no schema
is passed public
is assumed.
dryRun — Type: String
Default: null
Run patch in dry run mode? See Dry runs.
enableColorfulLogs — Type: Boolean
Default: true
Should colors be used in log?
logLevel — Type: String
Default: INFO
Configures how much log information will be shown.
patchDir — Type: String
Default: pg-patch
Directory where patch files can be found.
patchFileTemplate — Type: String
Default: ^patch-$VERSION-$ACTION(?:-$DESCRIPTION)?\\.(?:sql|js)$
Patch file name template. See Custom patch file template.
sourceVersion — Type: Integer
Default: null
Version from which patch DB. When not passed current version is used.
IMPORTANT: Normally this should not be used as it breaks normal patching route. Use only when really needed.
targetVersion — Type: Integer
Default: null
Version to which patch DB. If not passed newest patch file version is used.
transactionMode — Type: String
Default: PER_VERSION_STEP
Transaction mode to be used when patching DB. See Transaction control.
BEGIN;
COMMIT;
ROLLBACK;
etc.) into your patch files.1
to version 5
in one file will not work.0
. So first patch file needs to update to version 1
.To test pg-patch simply run:
gulp test
ISC License
Copyright (c) 2016, Łukasz Drożdż
Permission to use, copy, modify, and/or distribute this software for any purpose with or without fee is hereby granted, provided that the above copyright notice and this permission notice appear in all copies.
THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.