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.