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.
See dotenv for more details, and The Twelve-Factor App for why this is a best practice. pgsh can help even more if you use knex for migrations. To get up and running:
sudo yarn global add pgshto make thepgshcommand available everywherepgsh initto create a.pgshrcconfig file in your project folder, beside your.envfile (seesrc/pgshrc/default.jsfor futher configuration)- You can now run
pgshanywhere in your project directory (trypgsh -a!) - It is recommended to check your
.pgshrcinto version control
There are two different ways pgsh can help you manage your current connection (mode in .pgshrc):
url(default) when one variable in your.envhas your full database connection string (e.g.DATABASE_URL=postgres://...)splitwhen your.envhas different keys (e.g.PG_HOST=localhost,PG_DATABASE=myapp, ...)
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 psqlpgsh currentprints the name of the database that your connection string refers to right now.pgshorpgsh list <filter?>prints all databases, filtered by an optional filter. Output is similar togit 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 thefrom) database as name, then (optionally) runsswitch <name>.pgsh create <name> 8324 ;creates an empty database, then runsswitch <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 stdin
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 havedownedges! -
pgsh force-upre-writes theknex_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 theknex_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 theknex_migrationstable to the configured migrations directory and reports any inconsistencies between the two.