@cardstack/node-pg-migrate NPM

node-pg-migrate

Downloads Licence

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"
}

// 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 to public)
create-schema - Create the configured schema if it doesn't exist (defaults to false)
database-url-var (d) - Name of env variable with database url string (defaults to DATABASE_URL)
migrations-dir (m) - The directory containing your migration files (defaults to migrations)
migrations-schema - The schema storing table which migrations have been run (defaults to same value as schema)
create-migrations-schema - Create the configured migrations schema if it doesn't exist (defaults to false)
migrations-table (t) - The table storing which migrations have been run (defaults to pgmigrations)
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 or ts)
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 to true, 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 to true, 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 above
either url or user, 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 your package.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.Client
migrationsTable string - The table storing which migrations have been run
migrationsSchema string - The schema storing table which migrations have been run (defaults to same value as schema)
schema string - The schema on which migration will be run (defaults to public)
dir string - The directory containing your migration files
checkOrder boolean - Check order of migrations before running them
direction enum - up or down
count number - Number of migration to run
timestamp boolean - Treats count as timestamp
ignorePattern string - Regex pattern for file names to ignore
file string - Run only migration with this name
typeShorthands object - Object with column type shorthands
singleTransaction boolean - Combines all pending migrations into a single transaction so that if any migration fails, all will be rolled back (defaults to true)
createSchema boolean - Creates the configured schema if it doesn't exist
createMigrationsSchema boolean - Creates the configured migration schema if it doesn't exist
noLock boolean - Disables locking mechanism and checks
dryRun 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 table
columns object - column names / options -- see column definitions section
options object - table options (optional)
- temporary bool - default false
- ifNotExists bool - default false
- inherits string - table(s) to inherit from
- constraints object - table constraints
  - check string - sql for a check constraint
  - unique string or array of strings or array of array of strings - names of unique columns
  - primaryKey string or array of strings - names of primary columns
  - exclude string - sql for an exclude constraint
  - deferrable boolean - flag for deferrable table constraint
  - deferred boolean - flag for initially deferred deferrable table constraint
  - foreignKeys object or array of objects - foreign keys specification
    - columns string or array of strings - names of columns
    - references string - names of foreign table and column names
    - onDelete string - action to perform on delete
    - onUpdate string - action to perform on update
    - match string - FULL or SIMPLE
- like string - table(s) to inherit from
- comment string - adds comment on table

Reverse Operation: dropTable

`pgm.dropTable( tablename, options )`

Drop existing table - postgres docs

Arguments:

tablename string - name of the table to drop
options object - options:
- ifExists boolean - drops table only if it exists
- cascade boolean - drops also dependent objects

`pgm.renameTable( tablename, new_tablename )`

Rename a table - postgres docs

Arguments:

tablename string - name of the table to rename
new_table object - new name of the table

Reverse Operation: same operation in opposite direction

`pgm.alterTable( tablename, options )`

Alter existing table - postgres docs

Arguments:

tablename string - name of the table to alter
options object - options:
- levelSecurity string - DISABLE, ENABLE, FORCE, or NO FORCE

`pgm.addColumns( tablename, new_columns )`

Add columns to an existing table - postgres docs

Arguments:

tablename string - name of the table to alter
new_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 alter
columns array of strings or object - columns to drop (if object, uses keys)
options object - options:
- ifExists boolean - drops column only if it exists
- cascade boolean - drops also dependent objects

Aliases: dropColumn

`pgm.renameColumn( tablename, old_column_name, new_column_name )`

Rename a column - postgres docs

Arguments:

tablename string - name of the table to alter
old_column_name string - current column name
new_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 alter
column_name string - column to alter
column_options object - optional new column options
- default string or null - null, string
- type string - new datatype
- notNull boolean - sets NOT NULL if true or NULL if false
- allowNull boolean - sets NULL if true (alternative to notNull)
- using string - adds USING clause to change values in column
- collation string - adds COLLATE clause to change values in column
- comment 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 alter
constraint_name string - name for the constraint
expression 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 alter
constraint_name string - name for the constraint
options object - options:
- ifExists boolean - drops constraint only if it exists
- cascade boolean - drops also dependent objects

`pgm.renameConstraint( tablename, old_constraint_name, new_constraint_name )`

Rename a constraint - postgres docs

Arguments:

tablename string - name of the table to alter
old_constraint_name string - current constraint name
new_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 alter
columns string or array of strings - columns to add to the index
options 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 index
- where string - raw sql for where clause of index
- concurrently boolean - create this index concurrently
- method string - btree | hash | gist | spgist | gin

Aliases: addIndex Reverse Operation: dropIndex

`pgm.dropIndex( tablename, columns, options )`

Drop an index - postgres docs

Arguments:

tablename string - name of the table to alter
columns string or array of strings - column names, used only to infer an index name
options 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 type
values 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:

type_name string - name of the type to rename
new_type_name string - name of the new type

`pgm.addTypeAttribute( type_name, attribute_name, attribute_type )`

Add attribute to an existing data type - postgres docs

Arguments:

type_name string - name of the type
attribute_name string - name of the attribute to add
attribute_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 type
attribute_name string - name of the attribute to drop
options 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 type
attribute_name string - name of the attribute
attribute_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:

type_name string - name of the type
value string - value to add to list
options object - options:
- ifNotExists boolean - default false
- before string - value before which the new value should be add
- after string - value after which the new value should be add

`pgm.renameTypeAttribute( type_name, attribute_name, new_attribute_name )`

Rename an attribute of data type - postgres docs

Arguments:

type_name string - name of the type
attribute_name string - name of the attribute to rename
new_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 role
role_options object - options:
- superuser boolean - default false
- createdb boolean - default false
- createrole boolean - default false
- inherit boolean - default true
- login boolean - default false
- replication boolean - default false
- bypassrls boolean
- limit number -
- password string -
- encrypted boolean - default true
- valid string - timestamp
- inRole string or array of strings - role or array of roles
- role string or array of strings - role or array of roles
- admin 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:

role_name string - name of the new role
role_options object - see

`pgm.renameRole( old_role_name, new_role_name )`

Rename a role - postgres docs

Arguments:

old_role_name string - old name of the role
new_role_name string - new name of the role

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 function
function_params array - parameters of the new function
Either array of strings or objects. If array of strings, it is interpreted as is, if array of objects:
- mode string - IN, OUT, INOUT, or VARIADIC
- name string - name of argument
- type string - datatype of argument
- default string - default value of argument
function_options object - options:
- returns string - returns clause
- language string - language name of function definition
- replace boolean - create or replace function
- window boolean - window function
- behavior string - IMMUTABLE, STABLE, or VOLATILE
- onNull boolean - RETURNS NULL ON NULL INPUT
- parallel string - UNSAFE, RESTRICTED, or SAFE
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 drop
function_params array - see
drop_options object - options:
- ifExists boolean - drops function only if it exists
- cascade boolean - drops also dependent objects

`pgm.renameFunction( old_function_name, function_params, new_function_name )`

Rename a function - postgres docs

Arguments:

old_function_name string - old name of the function
function_params array - see
new_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 live
trigger_name string - name of the new trigger
trigger_options object - options:
- when string - BEFORE, AFTER, or INSTEAD OF
- operation string or array of strings - INSERT, UPDATE[ OF ...], DELETE or TRUNCATE
- constraint boolean - creates constraint trigger
- function string - the name of procedure to execute
- functionArgs array - parameters of the procedure
- level string - STATEMENT, or ROW
- condition string - condition to met to execute trigger
- deferrable boolean - flag for deferrable constraint trigger
- deferred 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 lives
trigger_name string - name of the trigger to drop
drop_options object - options:
- ifExists boolean - drops trigger only if it exists
- cascade boolean - drops also dependent objects

`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 lives
old_trigger_name string - old name of the trigger
new_trigger_name string - new name of the trigger

Schema Operations

`pgm.createSchema( schema_name, schema_options )`

Create a new schema - postgres docs

Arguments:

schema_name string - name of the new schema
schema_options object - options:
- ifNotExists boolean - adds IF NOT EXISTS clause
- authorization string - alternative user to own new schema

Reverse Operation: dropSchema

`pgm.dropSchema( schema_name, drop_options )`

Drop a schema - postgres docs

Arguments:

schema_name string - name of the schema to drop
drop_options object - options:
- ifExists boolean - drops schema only if it exists
- cascade boolean - drops also dependent objects

`pgm.renameSchema( old_schema_name, new_schema_name )`

Rename a schema - postgres docs

Arguments:

old_schema_name string - old name of the schema
new_schema_name string - new name of the schema

Domain Operations

`pgm.createDomain( domain_name, type, options )`

Create a new domain - postgres docs

Arguments:

domain_name string - name of the new domain
type string - type of the new domain
options object - options:
- default string - default value of domain
- collation string - collation of data type
- notNull boolean - sets NOT NULL if true (not recommended)
- check string - sql for a check constraint for this column
- constraintName string - name for constraint

Reverse Operation: dropDomain

`pgm.dropDomain( domain_name, drop_options )`

Drop a domain - postgres docs

Arguments:

domain_name string - name of the the domain to drop
drop_options object - options:
- ifExists boolean - drops domain only if it exists
- cascade boolean - drops also dependent objects

`pgm.alterDomain( domain_name, type, options )`

Alter a domain - postgres docs

Arguments:

domain_name string - name of the new domain
options object - options:
- default string - default value of domain
- collation string - collation of data type
- notNull boolean - sets NOT NULL if true or NULL if false
- allowNull boolean - sets NULL if true (alternative to notNull)
- check string - sql for a check constraint for this column
- constraintName string - name for constraint

`pgm.renameDomain( old_domain_name, new_domain_name )`

Rename a domain - postgres docs

Arguments:

old_domain_name string - old name of the domain
new_domain_name string - new name of the domain

Sequence Operations

`pgm.createSequence( sequence_name, type, options )`

Create a new sequence - postgres docs

Arguments:

sequence_name string - name of the new sequence
options object - options:
- temporary boolean - adds TEMPORARY clause
- ifNotExists boolean - adds IF NOT EXISTS clause
- type string - type of the sequence
- increment number - sets first value of sequence
- minvalue number or boolean - sets minimum value of sequence or NO MINVALUE (on false or null value)
- maxvalue number or boolean - sets maximum value of sequencee or NO MAXVALUE (on false or null value)
- start number - sets first value of sequence
- cache number - sets how many sequence numbers should be preallocated
- cycle boolean - adds CYCLE or NO CYCLE clause if option is present
- owner 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:

sequence_name string - name of the the sequence to drop
drop_options object - options:
- ifExists boolean - drops sequence only if it exists
- cascade boolean - drops also dependent objects

`pgm.alterSequence( sequence_name, options )`

Alter a sequence - postgres docs

Arguments:

sequence_name string - name of the new sequence
options object - options:
- type string - type of the sequence
- increment number - sets first value of sequence
- minvalue number or boolean - sets minimum value of sequence or NO MINVALUE (on false or null value)
- maxvalue number or boolean - sets maximum value of sequencee or NO 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 using start value (on true value)
- cache number - sets how many sequence numbers should be preallocated
- cycle boolean - adds CYCLE or NO CYCLE clause if option is present
- owner 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 sequence
new_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 operator
options object - options:
- procedure string - name of procedure performing operation
- left string - type of left argument
- right string - type of right argument
- commutator string - name of commutative operator
- negator string - name of negating operator
- restrict string - name of restriction procedure
- join string - name of join procedure
- hashes boolean - adds HASHES clause
- merges boolean - adds MERGES clause

Reverse Operation: dropOperator

`pgm.dropOperator( operator_name, drop_options )`

Drop a operator - postgres docs

Arguments:

operator_name string - name of the operator to drop
drop_options object - options:
- ifExists boolean - drops schema only if it exists
- cascade boolean - drops also dependent objects
- left string - type of left argument
- right string - type of right argument

`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 class
type string - data type of the new operator class
index_method string - name of the index method of operator class
operator_list array - of operator objects
options object - options:
- default boolean - adds DEFAULT clause
- family string - type of left argument

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 drop
index_method string - name of the index method of operator class
drop_options object - options:
- ifExists boolean - drops schema only if it exists
- cascade boolean - drops also dependent objects

`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 class
index_method string - name of the index method of operator class
new_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 family
index_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 drop
index_method string - name of the index method of operator family
drop_options object - options:
- ifExists boolean - drops schema only if it exists
- cascade boolean - drops also dependent objects

`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 family
index_method string - name of the index method of operator family
new_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 family
index_method string - name of the index method of operator family
operator_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 family
index_method string - name of the index method of operator family
operator_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 or operator
number number - index
name string - name of operator or procedure
params 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 alter
policyName string - name of the new policy
options object - options:
- command string - ALL, SELECT, INSERT, UPDATE, or DELETE
- role string or array - the role(s) to which the policy is to be applied
- using string - SQL conditional expression for visibility check
- check 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 is
policyName string - name of the policy to delete
options 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 is
policyName string - name of the policy to alter
options object - options:
- role string or array - the role(s) to which the policy is to be applied
- using string - SQL conditional expression for visibility check
- check 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 is
policyName string - old name of the policy
newPolicyName 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 view
options object - options:
- temporary boolean - default false
- replace boolean - default false
- recursive boolean - default false
- columns string or array - use if you want to name columns differently then inferred from definition
- checkOption string - CASCADED or LOCAL
definition string - SQL of SELECT statement

Reverse Operation: dropView

`pgm.dropView( viewName, options )`

Drop a view - postgres docs

Arguments:

viewName string - name of the view to delete
options object - options:
- ifExists boolean - drops view only if it exists
- cascade boolean - drops also dependent objects

`pgm.alterView( viewName, options )`

Alter a view - postgres docs

Arguments:

viewName string - name of the view to alter
options object - options:
- checkOption string - CASCADED, LOCAL or null to drop

`pgm.alterViewColumn( viewName, columnName, options )`

Alter a view column - postgres docs

Arguments:

viewName string - name of the view to alter
columnName string - name of the column to alter
options object - options:
- default string - default value of column

`pgm.renameView( viewName, newViewName )`

Rename a view - postgres docs

Arguments:

viewName string - old name of the view
newViewName string - new name of the view

Materialized Views

`pgm.createMaterializedView( viewName, options, definition )`

Create a new materialized view - postgres docs

Arguments:

viewName string - name of the new materialized view
options object - options:
- ifNotExists boolean - default false
- columns string or array - use if you want to name columns differently then inferred from definition
- tablespace string
- storageParameters object - key value pairs of Storage Parameters
- data boolean - default undefined
definition string - SQL of SELECT statement

Reverse Operation: dropMaterializedView

`pgm.dropMaterializedView( viewName, options )`

Drop a materialized view - postgres docs

Arguments:

viewName string - name of the view to delete
options object - options:
- ifExists boolean - drops view only if it exists
- cascade boolean - drops also dependent objects

`pgm.alterMaterializedView( viewName, options )`

Alter a materialized view - postgres docs

Arguments:

viewName string - name of the view to alter
options object - options:
- cluster string - index name for clustering
- extension string - name of extension view is dependent on
- storageParameters object - key value pairs of Storage Parameters

`pgm.renameMaterializedView( viewName, newViewName )`

Rename a materialized view - postgres docs

Arguments:

viewName string - old name of the view
newViewName string - new name of the view

`pgm.renameMaterializedViewColumn( viewName, columnName, newColumnName )`

Rename a materialized view column - postgres docs

Arguments:

viewName string - name of the view to alter
columnName string - current column name
newColumnName string - new column name

`pgm.refreshMaterializedView( viewName, options )`

Refreshes a materialized view - postgres docs

Arguments:

viewName string - old name of the view
options object - options:
- concurrently boolean - default false
- data boolean - default undefined

Miscellaneous Operations

`pgm.sql( sql )`

Run raw sql -- with some optional very basic mustache templating

Arguments:

sql string - SQL query to run
args object - (optional) key/val of arguments to replace

`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 type
unique boolean - set to true to add a unique constraint on this column
primaryKey boolean - set to true to make this column the primary key
notNull boolean - set to true to make this column not null
default string - adds DEFAULT clause for column. Accepts null, a literal value, or a pgm.func() expression.
check string - sql for a check constraint for this column
references string - a table name that this column is a foreign key to
onDelete string - adds ON DELETE constraint for a reference column
onUpdate string - adds ON UPDATE constraint for a reference column
match string - FULL or SIMPLE
deferrable boolean - flag for deferrable column constraint
deferred boolean - flag for initially deferred deferrable column constraint
comment 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)

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.

db database migrate migration migrations migrator db-migrate sql pg postgre postgres postgresql extensible expandable programatic programable api

@types/pg lodash mkdirp yargs config dotenv

@everything-registry/sub-chunk-156 @zalastax/nolb-_card @infinitebrahmanuniverse/nolb-_card

3.0.0-rc2-cardstack.1

8 years ago

3.0.0-rc2-cardstack.0

8 years ago

2.23.1-cardstack.0

8 years ago