@pgkit/migrator
Advanced tools
A smart cli migration tool for postgres, using pgkit.
Features:
create table ... statements.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.rebase migrations - rewrite and squash migrations past a certain point to consolidate working changes into one.check migrations to see if your database state matches what it should be based on the list of migrationsrepair to update the database to match the state described by your migrationsbaseline - mark an existing database as up-to-date, making it easy to introduce this tool to existing projects, and avoids worry about version updates.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:
goto feature - so you can just do node migrate goto --name 123.yourmigration.sql.down migrations at all. If you need to drop a table that was created by a previous migration, just create a regular migration called drop-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:
.sql scripts. For these, you would need to use javascript migrations.Migrator class (if you do this, please write a blog about it!)up - Apply pending migrationscreate - Create a new migration filelist - List migrations, along with their status, file path and contentlatest - Get the latest migrationcheck - Verify that your database is in an expected state, matching your migrationsrepair - 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 itgoto - Go "back" to a specific migration. This will calculate the diff required to get to the target migration, then apply itbaseline - 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 filedefinitions.updateDb - Update the database from the definitions filedefinitions.updateFile - Update the definitions file from the databaseunlock - Release the advisory lock for this migrator on the database. This is useful if the migrator is stuck due to a previous crashwipe - 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 helpCreate 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 helpList 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 helpGet the latest migration
latest [flags...]--skip-check - Skip checking that migrations are in a valid state-h, --help - Show helpVerify that your database is in an expected state, matching your migrations
check [flags...]-h, --help - Show helpIf 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 helpGo "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 helpBaseline 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. Use list to see available migrations.-h, --help - Show helpRebase 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. Use list to see available migrations.-h, --help - Show helpGet the path to the definitions file
definitions.filepath [flags...]-h, --help - Show helpUpdate the database from the definitions file
definitions.updateDb [flags...]-h, --help - Show helpUpdate the definitions file from the database
definitions.updateFile [flags...]-h, --help - Show helpRelease 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 helpWipe the database - remove all tables, views etc.
wipe [flags...]-h, --help - Show helpQuery 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 helpRight 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.
FAQs
PostgeSQL migration tool
The npm package @pgkit/migrator receives a total of 2,237 weekly downloads. As such, @pgkit/migrator popularity was classified as popular.
We found that @pgkit/migrator demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago.Ā It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
A critical vm2 sandbox escape can allow untrusted JavaScript to break isolation and execute commands on the host Node.js process.
Research
Five malicious NuGet packages impersonate Chinese .NET libraries to deploy a stealer targeting browser credentials, crypto wallets, SSH keys, and local files.
Security News
pnpm 11 turns on a 1-day Minimum Release Age and blocks exotic subdeps by default, adding safeguards against fast-moving supply chain attacks.