graphile-migrate

Graphile Migrate distinguishes between two types of migrations:

1. The current migration: The non-committed migration that is actively being developed and executed by graphile-migrate watch. By default, this is defined in migrations/current.sql (or migrations/current/*.sql in folder mode).
2. Committed migration(s): The files that have been finalized using graphile-migrate commit. These are stored in migrations/committed/*.sql and are numbered to ensure a strict linear history.

To improve source control organization, you can include external SQL files in your current.sql (or current/*.sql in folder mode). These files must be located within the migrations/fixtures directory.

Use the syntax --!include <path_relative_to_fixtures> anywhere in your migration file. When the migration is processed (committed, watched, run, or compiled), the content of the included file is injected into the migration, wrapped in --! Included and --! EndIncluded comments.

-- migrations/current.sql

--!include functions/myfunction.sql

drop policy if exists access_by_numbers on mytable;
create policy access_by_numbers on mytable for update using (myfunction(4, 2) < 42);

To prevent hash mismatches, adhere to these whitespace rules:

• Trim and Trail: graphile-migrate trims files using String.prototype.trim and appends a single newline.
• Start: Files should never start with a newline or whitespace.
• End: Every file must end with exactly one newline.

Idempotency in Graphile Migrate means a migration can be run multiple times while ensuring the final database state remains consistent. This is critical for reliable deployment pipelines.

There are two primary ways to implement idempotent migrations:

1. Using built-in PostgreSQL clauses: Use clauses like IF EXISTS, IF NOT EXISTS, or CREATE OR REPLACE when available.
2. Using anonymous code blocks (do $$ ... $$): For commands that lack built-in idempotency (like RENAME), use a PostgreSQL do block to check for the existence of an object in information_schema or pg_catalog before executing the command.

Warning: While idempotent migrations ensure structural consistency, some commands (like DROP or CASCADE) may result in data loss. Exercise extreme care when using these patterns.

Because graphile-migrate watch executes the contents of current.sql whenever it changes, you can encounter drift. Drift occurs when your local database contains objects (functions, tables, etc.) that are no longer present in your current.sql file because you renamed or deleted them in the file without explicitly writing a DROP command.

To prevent drift:

• Avoid using IDE auto-save features that might trigger partial or intermediate states.
• Always write idempotent SQL. If you rename a function, include a DROP FUNCTION IF EXISTS old_name; in your current.sql to ensure the old version is removed from the local database.

Comments used by graphile-migrate follow specific casing and placement rules:

Header Comments

Used for metadata like --! Hash, --! Previous, and --! Message.

• Placement: Must be at the very top of the file.
• Casing: Must start with a Capital letter.
• Spacing: For committed migrations, there should be two newlines between the header section and the rest of the content.

Body Comments

Used for operational instructions like --! split or --! no-transaction.

• Placement: Must occur after the header section, at the top of the body.
• Casing: Must start with a lower case letter.

To prevent hash verification failures on Windows due to CRLF line endings, add a .gitattributes file to your repository to force LF line endings for migration files:

migrations/committed/*.sql text eol=lf
migrations/current.sql text eol=lf

After adding this, you may need to run git checkout-index --force --all or re-clone the repository to apply the changes to your working copy.

You can split a single migration file into multiple files using the --! split: name_of_file.sql comment.

Rules:

• Lines appearing before the first --! split are included in the first split (typically used for headers).
• Every split must be separated from the next split by a newline.
• An empty file in a split will result in two newlines (one for the file's own trailing newline and one for the split separator).

--! split: 001.sql
select 1;

--! split: 002-empty.sql


--! split: 003.sql
select 3;

Use graphile-migrate init to set up a new project. This command creates a .gmrc configuration file and a migrations folder.

Options:

• --help: Show help.
• -c, --config: Optional path to the .gmrc file (defaults to .gmrc[.js|.cjs]).
• --folder: Use a folder instead of a file for the current migration.