npm

Don't miss out on this year's npm Pride t-shirt designs!Order your favorites here »

pg-bump

3.0.0-beta.4 • Public • Published

pg-bump

An opinionated, minimalist SQL migration CLI for PostgreSQL.

Build Status codecov Greenkeeper badge dependencies Status devDependencies Status License: MIT

What is it?

pg-bump provides command line management of PostgreSQL database schema migrations authored in "Plain SQL". It presumes that a) you are checking schema migrations into source control and b) you are using environment variables for your application configuration. pg-bump is primarily intended for use as an executable in package.json scripts. Applying or reverting migrations is always run in a single transaction, ensuring atomic schema transitions.

Installation

To add pg-bump to your devDependencies do:

λ npm i -D pg-bump

It is possible to install pg-bump globally, but it's not recommended.

λ npm i -g pg-bump

Creating a Migration

The pg-bump create command generates a new .sql migration file in your migrations directory (defaults to ./migrations).

λ pg-bump create create_table_users
./migrations/<unix-time-in-ms>_create_table_users.sql
-- up 
 
--- 
 
-- down 

The file is split into two sections, up, and down, separated by a --- three-dash SQL comment. The separator comment is used by pg-bump to determine which section of code to execute while applying (up) or reverting (down) migrations. It is safe to delete the up and down comments, but the --- separator should be preserved.

./migrations/<unix-time-in-ms>_create_table_users.sql
-- up 
create table users (
  id         serial,
  username   text           unique not null,
  created_at timestamptz(6not null default now(),
  primary (id)
);
 
--- 
 
-- down 
drop table users;

Commands

pg-bump <command> [args]
 
Commands:
  create <filename>          Create a new migration file.
  up                         Apply pending migrations.
  down [--to|-t <filename>]  Revert applied migrations.
  status                     Show pending migrations
 
Options:
  --config             Relative path to optional configuration file.
                                                          [default: ".pgbumprc"]
  --connectionVar, -c  Connection string environment variable.
                                                       [default: "DATABASE_URL"]
  --journalTable, -j   Database table used to record migration history.
                                                     [default: "schema_journal"]
  --files, -f          Relative path to migrations directory.
                                                         [default: "migrations"]
  --help               Show help                                       [boolean]

.pgbumprc

pg-bump will attempt to find an optional .pgbumprc configuration file in the root of the current working directory. Here is an example with default settings:

{
  "connectionVar": "DATABASE_URL",
  "journalTable": "schema_journal",
  "files": "./migrations"
}

config.connectionVar

The environment variable that pg-bump will use to connect to your PostgreSQL server. Defaults to DATABASE_URL. Should you forgo configuring a connectionVar and/or exporting an environment variable, pg-bump will attempt to connect using the pg module's default behavior. You should be using dotenv, so if you include a connectionVar, e.g. POSTGRESL_URL, in your .env file, then pg-bump will use it to connect to PostgreSQL.

config.journalTable

The database table that pg-bump will use to record the names of applied migrations. Defaults to schema_journal. This table will be created for you by pg-bump.

create table schema_journal (
  applied_at timestamptz(6not null default now(),
  file_name  text unique not null
);

config.files

The directory in your project that pg-bump will use to generate and read .sql migration files. Defaults to ./migrations. This directory will be created for you by pg-bump.

project/
├─ node_modules/
├─ migrations/ <--
├─ package.json
├─ .env
└─ .pgbumprc

Example

package.json
{
  "name": "postgres-app",
  "version": "1.0.0",
  "description": "A node app that uses PostgreSQL.",
  "main": "index.js",
  "scripts": {
    "db:make": "pg-bump create",
    "db:up": "pg-bump up",
    "db:down": "pg-bump down",
    "db:status": "pg-bump status",
    "db:rebuild": "pg-bump down && pg-bump up"
  },
  "dependencies": {
    "dotenv": "^4.0.0"
  },
  "devDependencies": {
    "pg-bump": "^1.0.0"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}
.pgbumprc
{
  "connectionVar": "POSTGRES_URL",
  "journalTable": "pg_bump_migrations",
  "files": "./db/migrations"
}
.env
POSTGRES_URL=postgres://127.0.0.1:5432/postgres-app

Development

Environment Variables

After cloning the project repository, you'll need a .env file in the root of the project. See .env.example.

Docker

docker-compose can be used to start and stop a local PostgreSQL instance if you don't have a server running on your machine. If necessary, you can override docker-compose.yml.

Scripts
# spin up a PostgreSQL server 
npm run db:start
# run the tests 
npm test
# tear the server down 
npm run db:stop

install

npm i pg-bump

Downloadsweekly downloads

34

version

3.0.0-beta.4

license

MIT

homepage

github.com

repository

Gitgithub

last publish

collaborators

  • avatar
Report a vulnerability