Note: @pgkit/migrator is being re-written. Docs are mostly updated.
A cli migration tool for postgres, using pgkit.
There are already plenty of migration tools out there - but if you have an existing project that uses pgkit, this will be the simplest to configure. Even if you don't, the setup required is minimal.
By default, the migration scripts it runs are plain .sql files. No learning the quirks of an ORM, and how native postgres features map to API calls. It can also run .js or .ts files - but where possible, it's often preferable to keep it simple and stick to SQL.
This isn't technically a cli - it's a cli helper. Most node migration libraries are command-line utilities, which require a separate database.json or config.json file where you have to hard-code in your connection credentials. This library uses a different approach - it exposes a javascript function which you pass a client instance into. The javascript file you make that call in then becomes a runnable migration CLI. The migrations can be invoked programmatically from the same config.
Contents
A migration tool for postgres, using pgkit
Features:
- SQL-first - write migrations using plain-old SQL. Just write
create table ...statements. - Flexible - migrations can also be written in javascript or typescript for more dynamic use-cases.
- DDL generation - read and write from a definitions file, making it easy to see what your whole schema looks like.
- Smart
create- tinker with your database manually, then automatically create a migration file based on the drift. -
goto: Automatic "down" migrations. Uses migra to go "back" to a specific migration. -
rebasemigrations - rewrite and squash migrations past a certain point to consolidate working changes into one. -
checkmigrations to see if your database state matches what it should be based on the list of migrations -
repairto update the database to match the state described by your migrations -
baseline- mark an existing database as up-to-date, making it easy to introduce this tool to existing projects, and avoids worry about version updates. - Ready for distributed systems - database-level advisory locking makes it safe for multiple servers to run migrations at the same time
- Footgun-protection - any destructive changes require explicit confirmation
- Transaction support - apply all your migrations if they succeed, or none of them if any fail
npm install @pgkit/migrator
You can run it out of the box as a CLI:
npx @pgkit/migrator --help
-
up- Apply pending migrations -
create- Create a new migration file -
list- List migrations, along with their status, file path and content -
latest- Get the latest migration -
check- Verify that your database is in an expected state, matching your migrations -
repair- If your migrations are not in a valid state, this will calculate the diff required to move your database to a valid state, and apply it -
goto- Go "back" to a specific migration. This will calculate the diff required to get to the target migration, then apply it -
baseline- Baseline the database at the specified migration. This forcibly edits the migrations table to mark all migrations up to this point as executed. Useful for introducing the migrator to an existing database. -
rebase- Rebase the migrations from the specified migration. This deletes all migration files after this point, and replaces them with a squashed migration based on the calculated diff required to reach the current database state. -
definitions.filepath- Get the path to the definitions file -
definitions.updateDb- Update the database from the definitions file -
definitions.updateFile- Update the definitions file from the database -
unlock- Release the advisory lock for this migrator on the database. This is useful if the migrator is stuck due to a previous crash -
wipe- Wipe the database - remove all tables, views etc. -
sql- Query the database. Not strictly related to migrations, but can be used for debugging. Use with caution!
Apply pending migrations
up [flags...]
-
--step <number>- Apply this many migrations; Exclusive minimum: 0 -
--to <string>- Only apply migrations up to this one -
-h, --help- Show help
Create a new migration file
create [flags...]
-
--content <string>- SQL content of the migration. If not specified, content will be generated based on the calculated diff between the existing migrations and the current database state. -
--name <string>- Name of the migration file. If not specified, a name will be generated based on the content of the migraiton -
-h, --help- Show help
List migrations, along with their status, file path and content
list [flags...]
-
--output <string>- Result properties to return; Enum: name,path,content,object (default: "object") -
--query <string>- Search query - migrations with names containing this string will be returned -
--result <string>- Which result(s) to return; Enum: first,last,one,maybeOne,all (default: "all") -
--status <string>- Filter by status; Enum: pending,executed -
-h, --help- Show help
Get the latest migration
latest [flags...]
-
--skip-check- Skip checking that migrations are in a valid state -
-h, --help- Show help
Verify that your database is in an expected state, matching your migrations
check [flags...]
-
-h, --help- Show help
If your migrations are not in a valid state, this will calculate the diff required to move your database to a valid state, and apply it
repair [flags...]
-
-h, --help- Show help
Go "back" to a specific migration. This will calculate the diff required to get to the target migration, then apply it
goto [flags...]
-
--name <string>- Name of the migration to go to. Use "list" to see available migrations. -
-h, --help- Show help
Baseline the database at the specified migration. This forcibly edits the migrations table to mark all migrations up to this point as executed. Useful for introducing the migrator to an existing database.
baseline [flags...]
-
--purge-disk- Delete files subsequent to the specified migration (optional) -
--to <string>- Name of the migration to baseline to. Uselistto see available migrations. -
-h, --help- Show help
Rebase the migrations from the specified migration. This deletes all migration files after this point, and replaces them with a squashed migration based on the calculated diff required to reach the current database state.
rebase [flags...]
-
--from <string>- Name of the migration to rebase from. This migration will remain, all subsequent ones will be replaced with a squashed migration. Uselistto see available migrations. -
-h, --help- Show help
Get the path to the definitions file
definitions.filepath [flags...]
-
-h, --help- Show help
Update the database from the definitions file
definitions.updateDb [flags...]
-
-h, --help- Show help
Update the definitions file from the database
definitions.updateFile [flags...]
-
-h, --help- Show help
Release the advisory lock for this migrator on the database. This is useful if the migrator is stuck due to a previous crash
unlock [flags...]
-
-h, --help- Show help
Wipe the database - remove all tables, views etc.
wipe [flags...]
-
-h, --help- Show help
Query the database. Not strictly related to migrations, but can be used for debugging. Use with caution!
sql [flags...]
-
--doublequote <string>- Character to use in place of " - use to avoid having to do bash quote-escaping (optional) -
--method <string>- Enum: any,many,one,maybeOne,query,anyFirst,oneFirst,maybeOneFirst (optional) (default: "any") --query <string>-
--singlequote <string>- Character to use in place of ' - use to avoid having to do bash quote-escaping (optional) -
-h, --help- Show help
Right now, the built-in CLI is configured via environment variables.
| Environment Variable | Description | Default Value |
|---|---|---|
| PGKIT_CONNECTION_STRING | postgresql client connection string | postgresql://postgres:postgres@localhost:5432/postgres |
| PGKIT_MIGRATIONS_PATH | Path to folder containing migraitons scripts | ${cwd}/migrations |
| PGKIT_MIGRATIONS_TABLE_NAME | Name for table to store migration history in | migrations |
In future, a pgkit.config.ts file will (probably) be supported.