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:
.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.
yarn global add pgsh
to make the pgsh
command available everywherepgsh init
to create a .pgshrc
config file in your project folder, beside your .env
file (see src/pgshrc/default.js
for futher configuration)pgsh
anywhere in your project directory (try pgsh -a
!).pgshrc
into version control. Why?
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
, ...)psql
, pg_dump
, etc.) are installed locallycp .env.example .env
docker-compose up -d
yarn test
. Note that this test suite will destroy allpgsh 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 psqlpgsh 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!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
pgsh dump <name?>
dumps the current database (or the named one if given) to stdoutpgsh restore <name>
restores a previously-dumped database as name from stdinpgsh 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.