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
This library aims to eliminate the tradeoff between developer experience and reliability. It's meant to be very easy to get started and write migrations, while also being as flexible as possible for almost any use-case.
With that in mind, here are some design decisions:
Down migrations (or "Undo" migrations in Flyway) are a nice idea, but in practice, they almost always end up being untested code that lives forever in your codebase, and almost certainly doesn't work. Instead of down migrations, @pgkit/migrator splits the use case for them into two:
- In development, migra is under the hood in order to generate diffs between the states represented by any migrations, and apply them after user confirmation. This allows for a much more powerful
gotofeature - so you can just donode migrate goto --name 123.yourmigration.sql. - In production, you just shouldn't use
downmigrations at all. If you need to drop a table that was created by a previous migration, just create a regular migration calleddrop-foo.sql. This way, your migration files can serve as a reliable history of all the changes you made in production.
Re 2. - of course, if you really want to shell into your production server and run node migrate goto ..., you still can. But this is not advised.
@pgkit/migrator aims to go beyond a "dumb" migration tool. That is, it uses migra to calculate the SQL required to get to target states - but it will never apply those diffs without confirming with the end-user first. One example of this is the goto feature above - but there's also check, repair, rebase, wipe and others.
When run as a CLI, the SQL that will be executed by one of these "smart" commands will be written to standard out, and wait for a "Y" to be entered into the terminal.
The package exposes a Migrator class, which has everything you need baked into it for most migration needs. But if you have a custom setup and still want to take advantage of @pgkit/migrator features, all you need to do is extend the class. Some examples
By default, migration files ending in .repeatable.sql are considered repeatable. The isRepeatable method can be overriden to change this
import {Migrator as Base} from '@pgkit/migrator'
export class Migrator extends Base {
isRepeatable(name: string) {
return name.startsWith('R_')
}
}The default definitions file lives in the parent folder of the migration scripts:
import {Migrator as Base} from '@pgkit/migrator'
export class Migrator extends Base {
get definitionsFiles() {
return `/path/to/definitions.sql`
}
}Have a look at the API docs for more methods that can be overriden.
Raw database migrations are a bad way for a human to understand what state the database is in. Say you're building a healthcare application. You might first create a patient table migration:
create table patient(id int, given_name text, family_name text, birth_date date);Later, you might need to store the patient's gender. You'd create a new migration adding the column:
alter table patient
add column gender text;Now the definition of the patient table is split across two files, and there's no one place in code to look to see what the patient table looks like. Instead of relying on an external tool with a custom UI to solve for this, you can use @pgkit/migrator's ability to sync the datbase with a definitions.sql file. After running each migration, you can run the definitions.updateFile command to update definitions.sql, which in the above example will result in a single statement describing the patient table. And it's not a custom schema definition language specific to this library. It's just SQL:
create table patient(id int, given_name text, family_name text, gender text);What's more, you can modify this definitions file to add or remove columns at will, then use the definitions.updateDb command to update your local database based on the definitions file while developing. Once you're satisfied with the state of your database, the create command will automatically a generate a migration file to make sure the individual migrations bring your production database to exactly the same state (the generated code should be committed and code-reviewed like any other, of course).
The baseline makes it easy to introduce @pgkit/migrator to an existing project. This can be used when your database is in a known-good state, as represented by the migration files. It will update the migrations table to mark all migrations up to a certain point as executed. This also serves as a reassurance the it will always be possible to upgrade to future versions, including if you need to override behaviour yourself.
There's a built in CLI for local use (or production, via a shell on your production server), and there's a tRPC router exposed so you can deploy it to an internal admin API if you like, with any auth solution you want.
This one is more subjective, since there isn't a one-to-one feature mapping between @pgkit/migrator and Flyway. Flyway is a great tool, but it has some pretty painful requirements to run - the main one being Java. @pgkit/migrator is a pure-nodejs tool (you might have some luck running through bun or deno too). The same tool is designed to work on your local machine and in production.
Some features of Flyway are missing at time of writing, though:
- Ant-style placeholders within
.sqlscripts. For these, you would need to use javascript migrations. - Java migrations! These won't be supported - but if you really want to write these, you might have some luck by extending the
Migratorclass (if you do this, please write a blog about it!) - Databases other than PostgreSQL.
- Code checking - though this might be covered by other tools in the pgkit family in future.
-
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.