Database Migrations

Migrations are SQL files in your project's volcano/migrations/ directory. Volcano applies them three ways:

• Tracked migration runs, through the API. The database records each file it has applied, so a file runs once. Volcano rehearses the pending files on a short-lived branch and takes a backup before it touches your database.
• Git auto deploy, with apply_migrations on. Each push to the production branch starts a tracked run for each database its commit has files for, with source: git_deploy, and deploys the code only after every run succeeds. See Apply migrations on push.
• The CLI, with volcano deploy --with-migrations, volcano cloud databases migration up, and volcano migrations deploy. The CLI does not use tracked runs yet: every file runs again on each call. See Run migrations with the CLI.

Apply migrations with a tracked run

Send the files to the database's migration runs:

curl -X POST "https://api.volcano.dev/projects/$PROJECT_ID/databases/main_db/migration-runs" \
  -H "Authorization: Bearer $VOLCANO_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Idempotency-Key: deploy-4f2a9c1" \
  -d '{
    "files": [
      {"name": "001_create_notes.sql", "sql": "CREATE TABLE notes (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), title text NOT NULL);"},
      {"name": "002_add_notes_pinned.sql", "sql": "ALTER TABLE notes ADD COLUMN pinned boolean NOT NULL DEFAULT false;"}
    ]
  }'

The response is 202 with the run queued:

{
  "id": "7f3a1c2e-5d4b-4a91-8c6f-2b1e9d0a4c73",
  "database": { "type": "database", "id": "0b9c3f5e-2a41-4d7e-9f0a-6c8e1d2b3a45", "name": "main_db" },
  "status": "queued",
  "phase": "plan",
  "source": "api",
  "files": [
    { "name": "001_create_notes.sql", "outcome": "pending" },
    { "name": "002_add_notes_pinned.sql", "outcome": "pending" }
  ],
  "warnings": [],
  "created_at": "2024-01-01T00:00:00Z",
  "updated_at": "2024-01-01T00:00:00Z"
}

Poll the run until status is succeeded or failed:

curl "https://api.volcano.dev/projects/$PROJECT_ID/databases/main_db/migration-runs/$RUN_ID" \
  -H "Authorization: Bearer $VOLCANO_TOKEN"

{
  "id": "7f3a1c2e-5d4b-4a91-8c6f-2b1e9d0a4c73",
  "database": { "type": "database", "id": "0b9c3f5e-2a41-4d7e-9f0a-6c8e1d2b3a45", "name": "main_db" },
  "status": "succeeded",
  "phase": "apply",
  "source": "api",
  "backup_name": "before-migration-7f3a1c2e-5d4b-4a91-8c6f-2b1e9d0a4c73",
  "files": [
    { "name": "001_create_notes.sql", "outcome": "applied" },
    { "name": "002_add_notes_pinned.sql", "outcome": "applied" }
  ],
  "warnings": [],
  "started_at": "2024-01-01T00:00:02Z",
  "completed_at": "2024-01-01T00:01:40Z",
  "created_at": "2024-01-01T00:00:00Z",
  "updated_at": "2024-01-01T00:01:40Z"
}

Send the same files every time, including the ones already applied. The run skips those (already_applied) and applies only the new ones. With Idempotency-Key, a retried request returns the run it started instead of starting another. A database runs one migration run at a time; starting a second answers 409 with code migration_run_in_progress and the run_id to wait for. Files that break a rule answer 400 and start nothing: code invalid_layout for a path that does not map to the database, and invalid_file for a name that is repeated or too long. The run lists its files in the order they run, whatever order you send them in.

A database over its storage allowance takes no run or baseline, just as it takes no writes over a connection. Both answer 400 with a message that starts storage limit reached. Reclaim space with DROP or TRUNCATE over a connection, or upgrade, then start the run again.

Runs need the project owner's account to be active. While it is read_only or inactive, starting a run is refused, and a run started before that fails with owner_not_active when Volcano next picks it up, or before it takes the backup or applies, whichever comes first. Files it already applied stay applied, and nothing further runs.

An apply already under way stops before its next file and fails with owner_not_active. The file running then finishes, and the files applied before the stop stay applied: read the history to see which. One hold lets an apply that has applied files finish them, even when it retries: an account held read_only because it no longer fits its plan's limits. Your apps keep serving through that hold, so the run does not leave them a partly migrated schema.

In CI, call the API with a full project access token. A read_only token can read the history and runs but cannot start a run.

Tracked runs need the databases.migrations capability. Where it is unavailable, starting a run or a baseline answers 404 with code feature_unavailable; reading the history and runs works either way. The API reference lists every endpoint and status.

What a run does

A run moves through four phases. phase says which one it is on, or which one it ended on.

If a file fails while it is applied to the database, its transaction rolls back and the run stops there. The files before it stay applied and recorded, and the ones after it are not_run; fix the failing file and start a run again. Before it runs a file, apply reads the history again, and the outcomes follow what it finds: a file that another session applied or baselined after the run planned ends already_applied instead of running.

If the connection drops while a file runs, or one of Volcano's own statements around the file fails, the file's transaction rolls back too, but the file did not fail. The run retries it, and the file reads pending meanwhile. If the run ends without applying it, the file is not_run, and the run's code is connection_failed or internal_error. A marked file has no transaction to roll back, so if the connection drops while it runs, or a statement of Volcano's fails after it, the file reads unknown from then on. It stays unknown even if a later attempt stops before the file or fails it, until an attempt applies it or the history records it. A later attempt that fails it leaves its error on the unknown file. A marked file also reads unknown while Volcano runs it.

A run can end in apply before it confirms whether a file ran: it used every attempt (attempts_exhausted), Volcano ended it (operator_failed), or the file's COMMIT failed or was cut short. That file's outcome is unknown, and it may have committed. Each file commits with its history record, so the history says whether it ran, and the next run applies it only if the history does not record it. A marked file's record commits after it, so the history may lack a marked file that ran. While a run retries, the files it has not applied read pending again, or already_applied if the history now holds them.

Backups, branches, and your plan

On SUPERAGENT, a run that reaches backup takes a backup, charged as a full copy of the database like one you take. It does not count against your backup allowance. Volcano keeps up to 20 migration backups per database and deletes older ones, even before their expires_at. To keep a pre-migration state for its full retention, take a backup yourself before the run. See what backups cost.

HOBBY has no backups, so its runs skip the backup and report a backup_skipped warning. The rehearsal still runs. The rehearsal branch counts toward the database's branch allowance while it exists; when the database is at its allowance, the run fails with branch_limit before it touches the database. Delete a branch and start the run again.

The statements a run executes, on the rehearsal branch and on the database, do not count toward your database request allowance, and reaching that allowance does not stop a run. Only the storage allowance does, as described above.

In local mode, there are no branches or backups: runs apply directly and report both rehearsal_skipped and backup_skipped.

Warnings

A run keeps at most 100 warnings. Those about the run, such as backup_skipped, are kept ahead of those about files.

Write migration files

your-project/
  volcano/
    migrations/
      001_create_notes.sql
      002_add_notes_pinned.sql

• Order. Files run in byte order of their names. Use numbered prefixes (001_, 002_) so the order is the one you mean.
• One transaction per file. A file may hold several statements, and they commit together or not at all. A file cannot COMMIT, ROLLBACK, or use savepoints, and cannot COPY from the client; use INSERT. Such a file fails with migration_failed, and its error says why.
• Statements that cannot run in a transaction go alone in a marked file. See Run a statement outside a transaction.
• Never edit an applied file. The history keeps each file's checksum, and a run that finds an applied file changed fails with checksum_mismatch before anything runs. Add a new file instead. Converting line endings between CRLF and LF, or adding or removing a byte order mark, does not count as a change.
• Limits. Up to 1,000 files in a request, each up to 1 MiB, and 4 MiB in all. The history may hold up to 1,000 files too.

Run a statement outside a transaction

Some statements refuse to run inside a transaction, such as CREATE INDEX CONCURRENTLY, which builds an index without blocking writes to the table. Put the statement alone in a file whose first line is -- volcano:no-transaction:

-- volcano:no-transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS notes_created_at_idx ON notes (created_at);

Volcano runs the file outside a transaction, then records it in the history. Nothing rolls the file back, so:

• One statement per file. Postgres runs statements sent together as one transaction, where these statements fail with migration_failed.
• Make it safe to run again. If the statement succeeds but its history record does not commit, the file reads unknown and the run's next attempt runs it again. Use IF NOT EXISTS and IF EXISTS.
• Name the index. A failed CREATE INDEX CONCURRENTLY leaves its index behind, marked invalid. Before the file runs again, Volcano drops an invalid index with the file's index name on its table, and after the file runs it checks that the index is valid. Another session's build of that index is invalid until it finishes, so Volcano drops the index only while it holds the table lock every build holds, and only if the index is still invalid. The drop takes a brief ACCESS EXCLUSIVE lock on the table, bounded by the lock limit. If another session holds the table past the lock limit, is still finishing its build, or renames the index while Volcano drops it, the attempt fails with locked, nothing is dropped, and the run tries again. Volcano cannot take that lock on a materialized view, so an invalid index on one fails the file with migration_failed and the DROP INDEX CONCURRENTLY statement to run before you try again. A run refuses a file that builds an unnamed index with invalid_file.
• First line only. A run refuses a file with the marker on any other line with invalid_file.
• No COPY. COPY can run inside a transaction, so leave a COPY file unmarked, and use INSERT for data from the client. A run refuses a marked file that starts with COPY with invalid_file.

A baseline runs no file, so it records marked files without these checks.

The file's statement has no 5-minute file limit: the 15-minute phase limit bounds it. Its lock waits still time out after 10 seconds, except in a CREATE INDEX CONCURRENTLY. That statement waits, without blocking reads or writes, for transactions writing to its table, and then for every older transaction anywhere in the database, so only the phase limit bounds its waits. One long transaction elsewhere, such as a session left idle in transaction, can hold the build until the phase limit ends the run. Only CREATE INDEX CONCURRENTLY is exempt: a marked REINDEX ... CONCURRENTLY or DROP INDEX CONCURRENTLY keeps the 10-second limit, and so does Volcano's drop of a failed build's index, which waits for queries on the table. A failed REINDEX ... CONCURRENTLY can leave an invalid index named <name>_ccnew, which Volcano does not drop; drop it yourself with DROP INDEX CONCURRENTLY.

Several databases in one project

With more than one database, put each database's files in a directory named after it:

volcano/
  migrations/
    main_db/
      001_create_notes.sql
    analytics/
      001_create_events.sql

Send each database its own directory's files, named with the directory:

{
  "files": [{ "name": "main_db/001_create_notes.sql", "sql": "CREATE TABLE notes (id uuid PRIMARY KEY);" }]
}

The history records the file name without the directory. Every file in a request takes the same form, and a directory must be the database's own; one level of directories is the most a request accepts.

Time limits

These are starting values and may change as Volcano learns how long real migrations take.

A file can set its own lock wait with SET LOCAL lock_timeout. A file that waits too long for a lock is retried later; meanwhile the run stays running. A run gets four attempts.

Adopt an existing database with a baseline

A database migrated before you used tracked runs already reflects its files, and running them again would fail. Record them as applied without running them:

curl -X POST "https://api.volcano.dev/projects/$PROJECT_ID/databases/main_db/migrations/baseline" \
  -H "Authorization: Bearer $VOLCANO_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"files": [{"name": "001_create_notes.sql", "sql": "CREATE TABLE notes (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), title text NOT NULL);"}]}'

{
  "recorded": ["001_create_notes.sql"]
}

Later runs apply only files the baseline did not record. Repeating a baseline records nothing new. A file the history holds with a different checksum answers 409 with code checksum_mismatch, and nothing is recorded. A baseline is refused while a run is queued or running, and no run starts while a baseline records.

A baseline does not queue. While another request is changing the database, or the database holds the baseline up for more than a few seconds, such as with a lock on the history, it answers 409 with code database_busy and records nothing. Try again.

Read the history

curl "https://api.volcano.dev/projects/$PROJECT_ID/databases/main_db/migrations" \
  -H "Authorization: Bearer $VOLCANO_TOKEN"

{
  "history": [
    {
      "name": "001_create_notes.sql",
      "checksum": "sha256:9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08",
      "status": "applied",
      "applied_at": "2024-01-01T00:01:20Z",
      "run_id": "7f3a1c2e-5d4b-4a91-8c6f-2b1e9d0a4c73"
    }
  ]
}

The history lives in your database, in the _volcano_migrations.history table, so it moves with the database: a branch has its parent's history, and a restore takes the history back with the data. Do not change or drop anything in the _volcano_migrations schema yourself, except to bring a history back under its bound, as below.

List a database's recent runs with GET .../migration-runs; it returns the 50 newest, without their files.

Volcano reads at most 1,000 history entries, as many files as one run may carry. It writes one entry per file, so a history grows past that only when files that ran leave your directory, or rows are written to the table by hand. Reading a larger history answers 409 with code history_too_large. A baseline is refused with the same code, and a run fails with it.

To bring it back under the bound, delete the entries of files that are no longer in your migrations directory, while no run is queued or running. Your directory holds at most 1,000 files, so that leaves at most 1,000 entries:

DELETE FROM _volcano_migrations.history
WHERE name IN ('001_create_notes.sql', '002_add_tags.sql');

No run sends those files, so their entries only raised missing_file warnings. A file you add again under a deleted name runs as a new one.

Recover a lost history

If the history table is dropped from a database where migrations ran, Volcano can no longer tell which files were applied. The rest of the _volcano_migrations schema shows that a history existed, so rather than run the files again, Volcano refuses: reading the history answers 409, and every run fails at plan, both with code history_missing.

Dropping the whole _volcano_migrations schema is not supported. Nothing is left to show that migrations ran, so the database reads as new, and the next run applies every file again. If that happened, record a baseline as below before you start a run.

{
  "error": "the migration history of this database was lost; record a baseline of the files that were already applied with POST /projects/{id}/databases/{databaseName}/migrations/baseline",
  "code": "history_missing"
}

Rebuild the history with a baseline:

1. Work out which files the database already reflects. The newest succeeded run is a good start: get it, and take the files whose outcome is applied or already_applied. Check them against the schema.
2. Record a baseline with exactly those files, with the content they had when they ran. The baseline recreates the table and records them as baselined.
3. Start a run with all your files. Only the ones the baseline did not record run.

A file you include that never ran is recorded and never runs. A file you leave out runs again.

Why a run failed

A failed run's error.code says why:

A run that has not ended can carry error too: why its last attempt failed while it waits to retry, or operator_failed while Volcano ends it.

The failed file carries the database's own error, with its SQLSTATE and line, as does an unknown marked file that a later attempt failed. That message can quote values from your file or your data, so anyone who can read the project's migration runs can see it.

Run migrations with the CLI

The CLI applies migrations without tracking them. Every file runs again on each --with-migrations deploy and each migration up --all: write files to be safe to repeat, such as CREATE TABLE IF NOT EXISTS, or leave the flag out once they have run.

volcano deploy --with-migrations applies every migration as part of a whole project deploy, after the variables and before the functions. volcano cloud deploy --with-migrations -d mydb does the same for a cloud database, and picks the database itself when your volcano-config.yaml declares exactly one. See the CLI deploy reference. Use the commands below to apply migrations on their own.

Cloud databases

# Deploy all migrations to a specific database
volcano cloud databases migration up --all -d mydb

# Deploy a specific migration
volcano cloud databases migration up -d mydb -f 001_create_users

# Deploy using full path
volcano cloud databases migration up -d mydb -f volcano/migrations/001_create_users.sql

The -d (or --database) flag is required, and you must pass either --all/-a or -f/--file. The database must exist and be active, and you must be logged in with volcano login.

Local development

When you run volcano start, a default database named app is created. Use the top-level volcano migrations deploy command:

# Deploy all migrations to the default "app" database
volcano migrations deploy --all -d app

# Deploy a specific migration
volcano migrations deploy -d app -f 001_create_users

-d is required here as well, along with --all/-a or -f/--file. Docker must be running with the Volcano containers started.

One statement per file

The CLI sends each file as one query, so a file must hold exactly one SQL statement. A file with more than one is refused before any of it runs. Split a multi-step change into several numbered files.

When a file fails, the CLI stops there and runs nothing after it. Fix the file and run again:

# Database doesn't exist
$ volcano cloud databases migration up --all -d nonexistent
Error: failed to get database "nonexistent": HTTP 404: database not found

# Database not ready
$ volcano cloud databases migration up --all -d mydb
Error: database "mydb" is not active (status: provisioning)

# Migration not found
$ volcano cloud databases migration up -d mydb -f missing_migration
Error: migration "missing_migration" not found in volcano/migrations/
available migrations: 001_create_users.sql, 002_add_posts.sql

Other migration tools

golang-migrate sends each file as one query, so a file with more than one statement is refused. Add x-multi-statement=true to the database URL to have it send them one at a time. That mode splits on every ;, including one inside a $$ function body, so apply such functions with a tracked run or psql -f instead.

Migration examples

Creating a table

-- volcano/migrations/001_create_notes.sql
CREATE TABLE notes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- volcano/migrations/002_index_notes_user_id.sql
CREATE INDEX idx_notes_user_id ON notes(user_id);

Enabling row level security

-- volcano/migrations/003_enable_rls.sql
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

-- volcano/migrations/004_notes_select_own.sql
-- Users can only see their own notes
CREATE POLICY notes_select_own ON notes
    FOR SELECT USING (auth.uid() = user_id);

-- volcano/migrations/005_notes_insert_own.sql
-- Users can only insert notes for themselves
CREATE POLICY notes_insert_own ON notes
    FOR INSERT WITH CHECK (auth.uid() = user_id);

A tracked run could hold all three in one file, applied together.

Adding a column

-- volcano/migrations/006_add_pinned.sql
ALTER TABLE notes ADD COLUMN is_pinned BOOLEAN DEFAULT FALSE;

Queries that ran before the migration

Volcano keeps the statements your application runs prepared on its database connections. A migration that changes a table's columns, such as adding one behind SELECT * or changing a type, makes Postgres refuse those statements with cached plan must not change result type. Volcano then prepares them again on every connection, so nothing needs restarting:

• Outside a transaction, the statement runs again on its own and returns the new columns. Your application sees no error.
• Inside a transaction, the error reaches your application once, because Postgres has already aborted the transaction. Roll back and run the transaction again.
• When your driver cached the columns itself, as drivers that prepare a statement once and reuse it do, the error reaches your application once even outside a transaction, as it would without Volcano. Anything the statement wrote is rolled back, and the driver drops its copy, so running the statement again is safe and returns the new columns. The exception is a SELECT that calls a function which writes, on a volcano_full_access connection: its writes are kept.

See also

• Branching
• Backups and restore
• Creating Databases
• Row Level Security
• Database Roles