@cardstack/node-pg-migrate v3.0.0-rc2-cardstack.1
node-pg-migrate
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, now maintained by Salsita Software.
Looking for v2 docs?
see v2 branch.
Installation
$ 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 node-pg-migrate
and if not, you can run ./node_modules/.bin/node-pg-migrate
Usage
You can specify your database connection information using config.
// config/default.json
{
"db": "postgres://postgres:password@localhost:5432/database"
}
or
// config/default.json
{
"db": {
"user": "postgres",
"password": "",
"host": "localhost",
"port": 5432,
"database": "database"
}
}
You could also specify your database url by setting the environment variable DATABASE_URL
.
DATABASE_URL=postgres://postgres@localhost/database node-pg-migrate
You can specify custom JSON file with config (format is same as for db
entry of config file), for example:
// path/to/config.json
{
"user": "postgres",
"password": "",
"host": "localhost",
"port": 5432,
"database": "database"
}
If a .env file exists, it will be loaded using dotenv (if installed) when running the node-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:
node-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.node-pg-migrate up
- runs all up migrations from the current state.node-pg-migrate up {N}
- runs N up migrations from the current state.node-pg-migrate down
- runs a single down migration.node-pg-migrate down {N}
- runs N down migrations from the current state.node-pg-migrate redo
- redoes last migration (runs a single down migration, then single up migration).node-pg-migrate redo {N}
- redoes N last migrations (runs N down migrations, then N up migrations).
Configuration
You can adjust defaults by passing arguments to node-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
)create-schema
- Create the configured schema if it doesn't exist (defaults tofalse
)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
)create-migrations-schema
- Create the configured migrations schema if it doesn't exist (defaults tofalse
)migrations-table
(t
) - The table storing which migrations have been run (defaults topgmigrations
)ignore-pattern
- Regex pattern for file names to ignore (e.g.ignore_file|\..*|.*\.spec\.js
)migration-file-language
(j
) - Language of the migration file to create (js
orts
)timestamp
- Treats number argument to up/down migration as timestamp (running up migrations less or equal to timestamp or down migrations greater or equal to timestamp)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.)single-transaction
- Combines all pending migrations into a single transaction so that if any migration fails, all will be rolled back (defaults totrue
, to switch it off supply--no-single-transaction
on command line).no-lock
- Disables locking mechanism and checks (useful for DBs which does not support SQL commands used for locking)
See all by running node-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 node-pg-migrate up
(pg will take it into account)
JSON Configuration
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
,ignore-pattern
- same as aboveeither
url
oruser
,password
,host
(defaults to localhost),port
(defaults to 5432),database
- for connection details
Locking
node-pg-migrate
automatically checks if no other migration is running. To do so, it uses an advisory lock.
Transpiling Babel or Typescript
You can use babel or typescript for transpiling migration files. It requires a little setup to use:
- Update
scripts
section in yourpackage.json
to contain'migrate': 'node migrate.js'
Create
migrate.js
file with contents:// require('babel-core/register')( { ... your babel config ... } ); // require('ts-node').register( { ... your typescript config ... } ); require('./node_modules/node-pg-migrate/bin/node-pg-migrate');
Uncomment/Use either babel or typescript hook and adjust your config for compiler. You can then use migration as usual via e.g.
npm run migrate up
. :tada:
Programmatic API
Alongside with command line, you can use node-pg-migrate
also programmatically. It exports runner function,
which takes options argument with following structure (similar to command line arguments):
databaseUrl
string or object - Connection string or client config which is passed to new pg.ClientmigrationsTable
string - The table storing which migrations have been runmigrationsSchema
string - The schema storing table which migrations have been run (defaults to same value asschema
)schema
string - The schema on which migration will be run (defaults topublic
)dir
string - The directory containing your migration filescheckOrder
boolean - Check order of migrations before running themdirection
enum -up
ordown
count
number - Number of migration to runtimestamp
boolean - Treatscount
as timestampignorePattern
string - Regex pattern for file names to ignorefile
string - Run only migration with this nametypeShorthands
object - Object with column type shorthandssingleTransaction
boolean - Combines all pending migrations into a single transaction so that if any migration fails, all will be rolled back (defaults totrue
)createSchema
boolean - Creates the configured schema if it doesn't existcreateMigrationsSchema
boolean - Creates the configured migration schema if it doesn't existnoLock
boolean - Disables locking mechanism and checksdryRun
boolean
Defining Migrations
When you run node-pg-migrate create
a new migration file is created that looks like this:
exports.shorthands = undefined;
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.
shorthands
is optional for column type shorthands. You can specify custom types which will be expanded to column definition
(e.g. for exports.shorthands = { id: { type: 'uuid', primaryKey: true }, createdAt: { type: 'timestamp', notNull: true, default: new PgLiteral('current_timestamp') } };
it will in pgm.createTable('test', { id: 'id', createdAt: 'createdAt' });
produce SQL CREATE TABLE "test" ("id" uuid PRIMARY KEY, "createdAt" timestamp DEFAULT current_timestamp NOT NULL);
).
These shorthands are inherited from previous migrations. You can override/change value by simply defining new value for given shorthand name,
if will be used in current and all following migrations (until changed again).
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.
Automatic Down Migrations
If exports.down
is not present in a migration, node-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 node-pg-migrate that the down migration is impossible.
Async Migrations
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();
});
};
Migration methods
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.
Creating & Altering Tables / Columns
pgm.createTable( tablename, columns, options )
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)temporary
bool - default falseifNotExists
bool - default falseinherits
string - table(s) to inherit fromconstraints
object - table constraintscheck
string - sql for a check constraintunique
string or array of strings or array of array of strings - names of unique columnsprimaryKey
string or array of strings - names of primary columnsexclude
string - sql for an exclude constraintdeferrable
boolean - flag for deferrable table constraintdeferred
boolean - flag for initially deferred deferrable table constraintforeignKeys
object or array of objects - foreign keys specificationcolumns
string or array of strings - names of columnsreferences
string - names of foreign table and column namesonDelete
string - action to perform on deleteonUpdate
string - action to perform on updatematch
string -FULL
orSIMPLE
like
string - table(s) to inherit fromcomment
string - adds comment on table
Reverse Operation: dropTable
pgm.dropTable( tablename, options )
Drop existing table - postgres docs
Arguments:
pgm.renameTable( tablename, new_tablename )
Rename a table - postgres docs
Arguments:
Reverse Operation: same operation in opposite direction
pgm.alterTable( tablename, options )
Alter existing table - postgres docs
Arguments:
tablename
string - name of the table to alteroptions
object - options:levelSecurity
string -DISABLE
,ENABLE
,FORCE
, orNO FORCE
pgm.addColumns( tablename, new_columns )
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
pgm.dropColumns( tablename, columns, options )
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)options
object - options:
Aliases: dropColumn
pgm.renameColumn( tablename, old_column_name, new_column_name )
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
pgm.alterColumn( tablename, column_name, column_options )
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 true or NULL if falseallowNull
boolean - sets NULL if true (alternative tonotNull
)using
string - adds USING clause to change values in columncollation
string - adds COLLATE clause to change values in columncomment
string - adds comment on column
pgm.addConstraint( tablename, constraint_name, expression )
Add a named column constraint - postgres docs
Arguments:
tablename
string - name of the table to alterconstraint_name
string - name for the constraintexpression
string or object - constraint expression (raw sql) or see constraints section of create table
Aliases: createConstraint
Reverse Operation: dropConstraint
pgm.dropConstraint( tablename, constraint_name, options )
Drop a named column constraint - postgres docs
Arguments:
tablename
string - name of the table to alterconstraint_name
string - name for the constraintoptions
object - options:
pgm.renameConstraint( tablename, old_constraint_name, new_constraint_name )
Rename a constraint - postgres docs
Arguments:
tablename
string - name of the table to alterold_constraint_name
string - current constraint namenew_constraint_name
string - new constraint name
Reverse Operation: same operation in opposite direction
pgm.createIndex( tablename, columns, options )
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:
Aliases: addIndex
Reverse Operation: dropIndex
pgm.dropIndex( tablename, columns, options )
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
Extension Operations
pgm.createExtension( extension )
Install postgres extension(s) - postgres docs
Arguments:
extension
string or array of strings - name(s) of extensions to install
Aliases: addExtension
Reverse Operation: dropExtension
pgm.dropExtension( extension )
Un-install postgres extension(s) - postgres docs
Arguments:
extension
string or array of strings - name(s) of extensions to install
Type Operations
pgm.createType( type_name, values )
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
pgm.dropType( type_name )
Drop a custom data type - postgres docs
Arguments:
type_name
string - name of the new type
pgm.renameType( type_name, new_type_name )
Rename a data type - postgres docs
Arguments:
pgm.addTypeAttribute( type_name, attribute_name, attribute_type )
Add attribute to an existing data type - postgres docs
Arguments:
type_name
string - name of the typeattribute_name
string - name of the attribute to addattribute_type
string - type of the attribute to add
pgm.dropTypeAttribute( type_name, attribute_name, options )
Drop attribute from a data type - postgres docs
Arguments:
type_name
string - name of the typeattribute_name
string - name of the attribute to dropoptions
object - options:ifExists
boolean - default false
pgm.setTypeAttribute( type_name, attribute_name, attribute_type )
Set data type of an existing attribute of data type - postgres docs
Arguments:
type_name
string - name of the typeattribute_name
string - name of the attributeattribute_type
string - new type of the attribute
pgm.addTypeValue( type_name, value, options )
Add value to a list of enum data type - postgres docs
Arguments:
pgm.renameTypeAttribute( type_name, attribute_name, new_attribute_name )
Rename an attribute of data type - postgres docs
Arguments:
type_name
string - name of the typeattribute_name
string - name of the attribute to renamenew_attribute_name
string - new name of the attribute
Role Operations
pgm.createRole( role_name, role_options )
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
booleanlimit
number -password
string -encrypted
boolean - default truevalid
string - timestampinRole
string or array of strings - role or array of rolesrole
string or array of strings - role or array of rolesadmin
string or array of strings - role or array of roles
Reverse Operation: dropRole
pgm.dropRole( role_name )
Drop a role - postgres docs
Arguments:
role_name
string - name of the new role
pgm.alterRole( role_name, role_options )
Alter a role - postgres docs
Arguments:
pgm.renameRole( old_role_name, new_role_name )
Rename a role - postgres docs
Arguments:
Function Operations
pgm.createFunction( function_name, function_params, function_options, definition )
Create a new function - postgres docs
Arguments:
function_name
string - name of the new functionfunction_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:
function_options
object - options:definition
string - definition of function
Reverse Operation: dropFunction
pgm.dropFunction( function_name, function_params, drop_options )
Drop a function - postgres docs
Arguments:
function_name
string - name of the function to dropfunction_params
array - seedrop_options
object - options:
pgm.renameFunction( old_function_name, function_params, new_function_name )
Rename 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
Trigger Operations
pgm.createTrigger( table_name, trigger_name, trigger_options )
Create a new trigger - 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 of strings -INSERT
,UPDATE[ OF ...]
,DELETE
orTRUNCATE
constraint
boolean - creates constraint triggerfunction
string - the name of procedure to executefunctionArgs
array - parameters of the procedurelevel
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
pgm.dropTrigger( table_name, trigger_name, drop_options )
Drop a trigger - postgres docs
Arguments:
table_name
string - name of the table where the trigger livestrigger_name
string - name of the trigger to dropdrop_options
object - options:
pgm.renameTrigger( table_name, old_trigger_name, new_trigger_name )
Rename 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
Schema Operations
pgm.createSchema( schema_name, schema_options )
Create a new schema - postgres docs
Arguments:
Reverse Operation: dropSchema
pgm.dropSchema( schema_name, drop_options )
Drop a schema - postgres docs
Arguments:
pgm.renameSchema( old_schema_name, new_schema_name )
Rename a schema - postgres docs
Arguments:
Domain Operations
pgm.createDomain( domain_name, type, options )
Create a new domain - postgres docs
Arguments:
domain_name
string - name of the new domaintype
string - type of the new domainoptions
object - options:
Reverse Operation: dropDomain
pgm.dropDomain( domain_name, drop_options )
Drop a domain - postgres docs
Arguments:
pgm.alterDomain( domain_name, type, options )
Alter a domain - postgres docs
Arguments:
pgm.renameDomain( old_domain_name, new_domain_name )
Rename a domain - postgres docs
Arguments:
Sequence Operations
pgm.createSequence( sequence_name, type, options )
Create a new sequence - postgres docs
Arguments:
sequence_name
string - name of the new sequenceoptions
object - options:temporary
boolean - addsTEMPORARY
clauseifNotExists
boolean - addsIF NOT EXISTS
clausetype
string - type of the sequenceincrement
number - sets first value of sequenceminvalue
number or boolean - sets minimum value of sequence orNO MINVALUE
(on false or null value)maxvalue
number or boolean - sets maximum value of sequencee orNO MAXVALUE
(on false or null value)start
number - sets first value of sequencecache
number - sets how many sequence numbers should be preallocatedcycle
boolean - addsCYCLE
orNO CYCLE
clause if option is presentowner
string or boolean - sets owner of sequence or no owner (on false or null value)
Reverse Operation: dropSequence
pgm.dropSequence( sequence_name, drop_options )
Drop a sequence - postgres docs
Arguments:
pgm.alterSequence( sequence_name, options )
Alter a sequence - postgres docs
Arguments:
sequence_name
string - name of the new sequenceoptions
object - options:type
string - type of the sequenceincrement
number - sets first value of sequenceminvalue
number or boolean - sets minimum value of sequence orNO MINVALUE
(on false or null value)maxvalue
number or boolean - sets maximum value of sequencee orNO MAXVALUE
(on false or null value)start
number - sets first value of sequence (no effect until restart)restart
number or boolean - sets first value of sequence or usingstart
value (on true value)cache
number - sets how many sequence numbers should be preallocatedcycle
boolean - addsCYCLE
orNO CYCLE
clause if option is presentowner
string or boolean - sets owner of sequence or no owner (on false or null value)
pgm.renameSequence( old_sequence_name, new_sequence_name )
Rename a sequence - postgres docs
Arguments:
old_sequence_name
string - old name of the sequencenew_sequence_name
string - new name of the sequence
Operator Operations
pgm.createOperator( operator_name, options )
Create a new operator - postgres docs
Arguments:
operator_name
string - name of the new operatoroptions
object - options:procedure
string - name of procedure performing operationleft
string - type of left argumentright
string - type of right argumentcommutator
string - name of commutative operatornegator
string - name of negating operatorrestrict
string - name of restriction procedurejoin
string - name of join procedurehashes
boolean - addsHASHES
clausemerges
boolean - addsMERGES
clause
Reverse Operation: dropOperator
pgm.dropOperator( operator_name, drop_options )
Drop a operator - postgres docs
Arguments:
pgm.createOperatorClass( operator_class_name, type, index_method, operator_list, options )
Create a new operator class - postgres docs
Arguments:
operator_class_name
string - name of the new operator classtype
string - data type of the new operator classindex_method
string - name of the index method of operator classoperator_list
array - of operator objectsoptions
object - options:
Reverse Operation: dropOperatorClass
pgm.dropOperatorClass( operator_class_name, index_methoddrop_options )
Drop a operator class - postgres docs
Arguments:
operator_class_name
string - name of the operator class to dropindex_method
string - name of the index method of operator classdrop_options
object - options:
pgm.renameOperatorClass( old_operator_class_name, index_method, new_operator_class_name )
Rename a operator class - postgres docs
Arguments:
old_operator_class_name
string - old name of the operator classindex_method
string - name of the index method of operator classnew_operator_class_name
string - new name of the operator class
pgm.createOperatorFamily( operator_family_name, index_method )
Create a new operator family - postgres docs
Arguments:
operator_family_name
string - name of the new operator familyindex_method
string - name of the index method of operator family
Reverse Operation: dropOperatorFamily
pgm.dropOperatorFamily( operator_family_name, index_methoddrop_options )
Drop a operator family - postgres docs
Arguments:
operator_family_name
string - name of the operator family to dropindex_method
string - name of the index method of operator familydrop_options
object - options:
pgm.renameOperatorFamily( old_operator_family_name, index_method, new_operator_family_name )
Rename a operator family - postgres docs
Arguments:
old_operator_family_name
string - old name of the operator familyindex_method
string - name of the index method of operator familynew_operator_family_name
string - new name of the operator family
pgm.addToOperatorFamily( operator_family_name, index_method, operator_list )
Rename a operator family - postgres docs
Arguments:
operator_family_name
string - name of the operator familyindex_method
string - name of the index method of operator familyoperator_list
array - of operator objects
pgm.removeFromOperatorFamily( operator_family_name, index_method, operator_list )
Rename a operator family - postgres docs
Arguments:
operator_family_name
string - name of the operator familyindex_method
string - name of the index method of operator familyoperator_list
array - of operator objects
Operator List Definitions
Some functions for defining operators take as parameter operator_list
which is array of objects with following structure:
type
string -function
oroperator
number
number - indexname
string - name of operator or procedureparams
array - list of argument types of operator or procedure
Policies
pgm.createPolicy( tableName, policyName, options )
Create a new policy - postgres docs
Arguments:
tableName
string - name of the table to alterpolicyName
string - name of the new policyoptions
object - options:command
string -ALL
,SELECT
,INSERT
,UPDATE
, orDELETE
role
string or array - the role(s) to which the policy is to be appliedusing
string - SQL conditional expression for visibility checkcheck
string - SQL conditional expression for insert/update check
Reverse Operation: dropPolicy
pgm.dropPolicy( tableName, policyName, options )
Drop a policy - postgres docs
Arguments:
tableName
string - name of the table where the policy ispolicyName
string - name of the policy to deleteoptions
object - options:ifExists
boolean - drops policy only if it exists
pgm.alterPolicy( tableName, policyName, options )
Alter a policy - postgres docs
Arguments:
tableName
string - name of the table where the policy ispolicyName
string - name of the policy to alteroptions
object - options:role
string or array - the role(s) to which the policy is to be appliedusing
string - SQL conditional expression for visibility checkcheck
string - SQL conditional expression for insert/update check
pgm.renamePolicy( tableName, policyName, newPolicyName )
Rename a policy - postgres docs
Arguments:
tableName
string - name of the table where the policy ispolicyName
string - old name of the policynewPolicyName
string - new name of the policy
Views
pgm.createView( viewName, options, definition )
Create a new view - postgres docs
Arguments:
viewName
string - name of the new viewoptions
object - options:temporary
boolean - default falsereplace
boolean - default falserecursive
boolean - default falsecolumns
string or array - use if you want to name columns differently then inferred from definitioncheckOption
string -CASCADED
orLOCAL
definition
string - SQL of SELECT statement
Reverse Operation: dropView
pgm.dropView( viewName, options )
Drop a view - postgres docs
Arguments:
pgm.alterView( viewName, options )
Alter a view - postgres docs
Arguments:
viewName
string - name of the view to alteroptions
object - options:checkOption
string -CASCADED
,LOCAL
ornull
to drop
pgm.alterViewColumn( viewName, columnName, options )
Alter a view column - postgres docs
Arguments:
viewName
string - name of the view to altercolumnName
string - name of the column to alteroptions
object - options:default
string - default value of column
pgm.renameView( viewName, newViewName )
Rename a view - postgres docs
Arguments:
Materialized Views
pgm.createMaterializedView( viewName, options, definition )
Create a new materialized view - postgres docs
Arguments:
viewName
string - name of the new materialized viewoptions
object - options:ifNotExists
boolean - default falsecolumns
string or array - use if you want to name columns differently then inferred from definitiontablespace
stringstorageParameters
object - key value pairs of Storage Parametersdata
boolean - default undefined
definition
string - SQL of SELECT statement
Reverse Operation: dropMaterializedView
pgm.dropMaterializedView( viewName, options )
Drop a materialized view - postgres docs
Arguments:
pgm.alterMaterializedView( viewName, options )
Alter a materialized view - postgres docs
Arguments:
viewName
string - name of the view to alteroptions
object - options:cluster
string - index name for clusteringextension
string - name of extension view is dependent onstorageParameters
object - key value pairs of Storage Parameters
pgm.renameMaterializedView( viewName, newViewName )
Rename a materialized view - postgres docs
Arguments:
pgm.renameMaterializedViewColumn( viewName, columnName, newColumnName )
Rename a materialized view column - postgres docs
Arguments:
viewName
string - name of the view to altercolumnName
string - current column namenewColumnName
string - new column name
pgm.refreshMaterializedView( viewName, options )
Refreshes a materialized view - postgres docs
Arguments:
Miscellaneous Operations
pgm.sql( sql )
Run raw sql -- with some optional very basic mustache templating
Arguments:
pgm.func( sql )
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
pgm.db.query
and pgm.db.select
Allows to run DB queries with same DB connection migration is running See pg.Client.query
Returns promise with either result of query or returned rows of query (in case of select
).
Column Definitions
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)collation
string - collation of data typeunique
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 nulldefault
string - adds DEFAULT clause for column. Accepts null, a literal value, or apgm.func()
expression.check
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 columnmatch
string -FULL
orSIMPLE
deferrable
boolean - flag for deferrable column constraintdeferred
boolean - flag for initially deferred deferrable column constraintcomment
string - adds comment on column
Data types & Convenience Shorthand
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 } });
Using schemas
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
);
Explanation & Goals
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"!
License
The MIT License (MIT)
Copyright (c) 2016 Jan Dolezel <dolezel.jan@gmail.com>
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.