# graphile-migrate: a roll-forward SQL migration tool for PostgreSQL > graphile-migrate applies plain SQL files to a PostgreSQL database, using a shadow database to check consistency and a watch mode to keep the schema in step with your edits. It is deliberately opinionated: roll-forward only, no custom DSL, and nothing that ties it to one application stack. **graphile/migrate** — Opinionated SQL-powered productive roll-forward migration tool for PostgreSQL. - Repository: https://github.com/graphile/migrate - Stars: 836 · Forks: 67 - Language: TypeScript - License: MIT - Published: 2026-10-20 · Updated: 2026-10-20 · Language: en - Canonical page: https://hysenlabs.com/projects/graphile-migrate ## Roll-forward only, and why the author dropped rollbacks graphile-migrate describes itself as an opinionated SQL-powered roll-forward migration tool for PostgreSQL, and the roll-forward part is the defining constraint. The stated reason is blunt: maintaining rollbacks is a chore, and in ten years of API development the author never ran one in production. The opinions list makes the rules explicit. Production issues should be fixed with additional migrations, while development can iterate on the current migration. Once a migration is signed off and deployable it should never be edited. The second opinion is about language. Migrations are written in SQL, with no custom DSL to learn, so all of PostgreSQL is available rather than the subset a framework chooses to expose. Sending SQL commands directly to PostgreSQL is the stated reason the tool is fully functional. The third opinion is portability: migration software should not be tied to a particular application stack, which is also how the tool positions itself against PostGraphile, where it works with any application but gains a schema that regenerates without a server restart when the database changes. ## The shadow database is the consistency check The mechanism that makes plain SQL safe is a second database. In development, graphile-migrate uses the main database and a shadow database. The shadow is used internally to test the consistency of the migrations and to perform other tasks. The idea is that the shadow holds the migrations applied in their committed order, so applying the current migration to it and comparing against a fresh rebuild shows whether your SQL is deterministic and whether the committed history still reproduces the same result. The drift concept follows directly from this. The shadow is unaffected by the iteration you have applied to your development database, so when the development database and the committed migrations disagree, the shadow is the reference that tells you which side is wrong. That is also why the README suggests dumping the shadow database rather than the main one: the shadow is the state that matches what production would become. Two operational details follow. All team members should run the same PostgreSQL version so the shadow dump matches for everyone, and Docker is offered as one way to achieve that without being required. The shadow database and the root database URL are development concerns, and the README states that in production you only need the graphile-migrate migrate command, unless you have actions that need the others. ## The current migration and the idempotency burden New work is composed inside what the documentation repeatedly calls the current migration. By default that is the file migrations/current.sql. If you prefer, you can delete that file and create a migrations/current/ folder instead, and place numbered SQL files in it that together make up the current migration. The rule that will affect your code most is idempotency. The current migration is expected to be able to run multiple times and produce the same result, and the documentation is explicit that this is the developer's responsibility. That expectation exists because watch mode reapplies the current migration continuously, so anything that is not idempotent either fails on the second pass or corrupts a development database that is cheap to reset but annoying to re-seed. The wider opinions set also constrains what belongs in a migration. Migrating data as well as DDL is acceptable, but it should be kept to fast operations or should trigger a background job. Roles are managed outside migrations, since they can be shared between databases. Certain schemas are managed by other tools and should not be interfered with, and graphile_worker is the example given. ## Setup: two databases and three environment variables The getting started path assumes a new database project, and it starts with a role and two databases, the application database and its shadow: ```bash createuser --pwprompt dbowner createdb myapp --owner=dbowner createdb myapp_shadow --owner=dbowner ``` Three URLs then describe the whole setup. The database URL is needed for most commands, the shadow database URL for the development-only commands commit, uncommit and reset, and the root database URL is a superuser connection to any other database, used to drop and recreate databases: ```bash export DATABASE_URL="postgres://dbowner:password@localhost/myapp" export SHADOW_DATABASE_URL="postgres://dbowner:password@localhost/myapp_shadow" export ROOT_DATABASE_URL="postgres://postgres:postgres@localhost/postgres" ``` The root URL is not a convenience. The commit and uncommit commands reset the shadow database, and reset needs permission to drop and recreate. Once the variables are in place, the project is initialised with: ```bash graphile-migrate init ``` The documentation suggests keeping the values in a file you can source later, and shows the watch loop that follows: ```bash . ./.env graphile-migrate watch ``` Because the environment lives in shell variables, a team that wants to share configuration needs to agree on where that file lives, and the docs mention a discussion of roles and permissions in issue #215 for the people who want that decided properly. ## Watch mode, transactions and SET LOCAL Watch mode is the loop the whole design serves. You edit a SQL file, the file is saved, and the database is updated in milliseconds, with the stated goal that local iteration should be easy and fast. The repository ships the watch build as a yarn script that compiles the CLI in TypeScript, with the package built from src/ into dist/ and exposed as the bin entry dist/cli.js. Two more opinions shape how your SQL runs. Migrations are automatically wrapped in transactions by default, and a migration that has to execute outside a transaction is marked explicitly, with enabling an ENUM in PostgreSQL given as the kind of non-DDL-safe operation that needs it. Migrations also should not pollute PostgreSQL global settings, so the guidance is to use SET LOCAL rather than SET, which keeps a session-level change from escaping the migration. Taken together these are not stylistic preferences. A migration that leaves a session setting behind will change the behaviour of the next migration in the same run, and one that silently commits half way through loses the atomicity that makes a failed migration recoverable by simply retrying it. ## Where graphile-migrate is the wrong tool The tool is a poor fit in several situations. If your team needs reversible migrations, for example because a release has to be rolled back quickly, the design argues against it rather than offering a second path. If your deployment pipeline replays migrations against a database that already contains data from an earlier tool, the SQL-first model means you would be writing that data by hand. If your database is not PostgreSQL, the entire premise disappears, since the SQL dialect, the shadow dump comparison and the SET LOCAL advice are all PostgreSQL specific. The alternative approach to weigh is not another tool with more features but a different shape of tool. Frameworks that generate SQL from a schema definition, or that own the migration lifecycle inside the application runtime, remove the manual SQL step but tie you to that framework and to whatever its definition format can express. graphile-migrate makes the opposite trade. It stays out of the application stack, gives you the full SQL surface, and charges you for it in idempotency discipline, a shadow database to maintain, and the responsibility for never editing a shipped migration. For database work that is slow by nature, the opinions list asks you to move it out of the migration entirely, either by keeping migrations to fast operations or by triggering a background job. ## Release candidates, MIT licence and the sponsorship model Two practical points on upkeep. The package version in package.json is 2.0.0-rc.7, and the recent releases are v2.0.0-rc.5 dated 2026-09-08, v2.0.0-rc.6 dated 2026-10-14 and v2.0.0-rc.7 dated 2026-10-15, so the 2.0 line is still in release candidate form rather than settled. The last push was on 2026-10-15. Adopting a release candidate means accepting that the 2.0 API may still move, and the project uses changesets with a changeset-version script and a changeset-publish script that publishes to npm with a tag, so release notes are maintained per change. The licence is MIT, so the code can be used in closed and open source products under its terms. Development is funded by sponsorship rather than by a single employer, with a sponsors page, a sponsors table in the README and a SPONSORS.md file in the tree. There is a Discord chat room badge and a Twitter badge for the GraphileHQ account, so the community channel for questions about the tool is a chat room rather than an issue tracker alone. ## Conclusion Adopt graphile-migrate if your team writes PostgreSQL DDL by hand, wants a fast edit and apply loop during development, and has accepted that rollbacks are not part of the tool. Do not adopt it if your workflow depends on reversible migrations, on changing a migration that has already shipped, or on a database other than PostgreSQL, since the whole model is built around one engine and one direction. Verify three things first: that every migration you write is idempotent, because the tool expects the current migration to be re-runnable; that your team members run the same PostgreSQL version so the shadow dump matches; and which commands you intend to allow in production, since only graphile-migrate migrate is needed there and the shadow and root database URLs are development concerns. The licence is MIT, and releases are currently 2.0.0 release candidates with the last push on 2026-10-15. ## FAQ ### What does graphile-migrate do that other migration tools do not? It applies plain SQL migrations to PostgreSQL with no custom DSL, and it checks them against a shadow database that reproduces the committed migration history so you can detect drift. Watch mode reapplies the current migration as you save files. ### Does graphile-migrate support rolling back a migration? No. It is roll-forward only, on the stated grounds that maintaining rollbacks is a chore. Production problems are fixed with additional migrations, and a migration that has been signed off as deployable should never be edited. ### Why does my migration have to be idempotent? The current migration is expected to run repeatedly with the same result, because watch mode reapplies it continuously. The documentation places that responsibility on the developer. ### Which environment variables does graphile-migrate need? DATABASE_URL for most commands, SHADOW_DATABASE_URL for the development-only commit, uncommit and reset commands, and ROOT_DATABASE_URL as a superuser connection used to drop and recreate databases. In production only the graphile-migrate migrate command is needed. ### Can graphile-migrate be used with a database other than PostgreSQL? The tool is written for PostgreSQL, and its SQL dialect, shadow dump comparison and SET LOCAL guidance all assume it. The documentation also states that migration software should not be tied to a particular application stack, which refers to the application framework rather than the database engine. ## Sources - [graphile/migrate on GitHub](https://github.com/graphile/migrate) - [Issues](https://github.com/graphile/migrate/issues) - [License: MIT](https://github.com/graphile/migrate/blob/main/LICENSE) - [README](https://github.com/graphile/migrate/blob/main/README.md) - [Releases](https://github.com/graphile/migrate/releases) --- Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/graphile-migrate