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.
There are only a couple requirements:
- your project reads its database configuration from the environment
- it uses a
.envfile to do so in development.
|Language / Framework||
pgsh can help even more if you use knex for migrations.
yarn global add pgshto make the
pgshcommand available everywhere
pgsh initto create a
.pgshrcconfig file in your project folder, beside your
src/pgshrc/default.jsfor futher configuration)
- You can now run
pgshanywhere in your project directory (try
- It is recommended to check your
.pgshrcinto version control. Why?
URL vs split mode
There are two different ways pgsh can help you manage your current connection (
url(default) when one variable in your
.envhas your full database connection string (e.g.
.envhas different keys (e.g.
- Make sure the postgres client and its associated tools (
pg_dump, etc.) are installed locally
cp .env.example .env
docker-compose up -d
- 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.
pgsh initgenerates a
.pgshrcfile for your project.
pgsh urlprints your connection string.
pgsh psql <name?> -- <psql-options...?>connects to the current (or named) database with psql
pgsh currentprints the name of the database that your connection string refers to right now.
pgsh list <filter?>prints all databases, filtered by an optional filter. Output is similar to
git branch. By adding the
-aoption you can see migration status too!
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
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
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 upmigrates 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
pgsh force-upre-writes the
knex_migrationstable 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_migrationstable 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 validatecompares the
knex_migrationstable to the configured migrations directory and reports any inconsistencies between the two.