pgsh: PostgreSQL tools for local development

Finding database migrations painful to work with? Switching contexts a chore? Pull requests piling up? pgsh helps by managing a connection string in your .env file and allows you to branch your database just like you branch with git.

Prerequisites

There are only a couple requirements:

• your project reads its database configuration from the environment
• it uses a .env file to do so in development.

See dotenv for more details, and The Twelve-Factor App for why this is a best practice.

| Language / Framework | .env solution | Maturity | | -------------------- | --------------- | -------- | | javascript | dotenv | high |

pgsh can help even more if you use knex for migrations.

Installation

1. yarn global add pgsh to make the pgsh command available everywhere
2. pgsh init to create a .pgshrc config file in your project folder, beside your .env file (see src/pgshrc/default.js for futher configuration)
3. You can now run pgsh anywhere in your project directory (try pgsh -a!)
4. It is recommended to check your .pgshrc into version control. Why?

URL vs split mode

There are two different ways pgsh can help you manage your current connection (mode in .pgshrc):

• url (default) when one variable in your .env has your full database connection string (e.g. DATABASE_URL=postgres://...)
• split when your .env has different keys (e.g. PG_HOST=localhost, PG_DATABASE=myapp, ...)

Running tests

1. Make sure the postgres client and its associated tools (psql, pg_dump, etc.) are installed locally
2. cp .env.example .env
3. docker-compose up -d
4. Run the test suite using yarn test. Note that this test suite will destroy all databases on the connected postgres server, so it will force you to send a certain environment variable to confirm this is ok.

Command reference

• pgsh init generates a .pgshrc file for your project.
• pgsh url prints your connection string.
• pgsh psql <name?> -- <psql-options...?> connects to the current (or named) database with psql
• pgsh current prints the name of the database that your connection string refers to right now.
• pgsh or pgsh list <filter?> prints all databases, filtered by an optional filter. Output is similar to git branch. By adding the -a option you can see migration status too!

Database branching

Read up on the recommended branching model for more details.

• pgsh clone <from?> <name> clones your current (or the from) database as name, then (optionally) runs switch <name>.
• pgsh create <name> creates an empty database, then runs switch <name> and optionally migrates it to the latest version.
• pgsh switch <name> makes name your current database, changing the connection string.
• pgsh destroy <name> destroys the given database. This cannot be undone. You can maintain a blacklist of databases to protect from this command in .pgshrc

Dump and restore

• pgsh dump <name?> dumps the current database (or the named one if given) to stdout
• pgsh restore <name> restores a previously-dumped database as name from stdin

Migration management (via knex)

pgsh provides a slightly-more-user-friendly interface to knex's migration system.

• pgsh up migrates the current database to the latest version found in your migration directory.

• pgsh down <version> down-migrates the current database to version. Requires your migrations to have down edges!

• pgsh force-up re-writes the knex_migrations table entirely based on your migration directory. In effect, running this command is saying to knex "trust me, the database has the structure you expect".

• pgsh force-down <version> re-writes the knex_migrations table to not include the record of any migration past the given version. Use this command when you manually un-migrated some migations (e.g. a bad migration or when you are trying to undo a migration with missing "down sql").

• pgsh validate compares the knex_migrations table to the configured migrations directory and reports any inconsistencies between the two.