Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/launchbadge/sqlx/llms.txt

Use this file to discover all available pages before exploring further.

SQLx migrations are plain SQL files with a numeric version prefix. You create them with the sqlx migrate add command, edit them in any text editor, and apply them with sqlx migrate run. SQLx records every applied migration in the _sqlx_migrations table so subsequent runs only execute scripts that are still pending.
All CLI commands require a database URL. Set DATABASE_URL in your environment or in a .env file at the project root:
DATABASE_URL=postgres://postgres@localhost/mydb
You can also pass it explicitly with --database-url <URL> on any command.

​
Creating migration files

Use sqlx migrate add to generate a new migration file. SQLx writes the file to migrations/<timestamp>_<name>.sql and prefixes it with the current UTC time in YYYYMMDDHHmmss format so files sort in creation order. 
sqlx migrate add create_users
# Creates: migrations/20240101120000_create_users.sql
The generated file contains a placeholder comment: 
-- Add migration script here
Replace it with your DDL: 
CREATE TABLE users (
    id      BIGSERIAL PRIMARY KEY,
    email   TEXT NOT NULL UNIQUE,
    name    TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

​
Simple vs. reversible migrations

By default, sqlx migrate add creates a single .sql file. This is a simple (non-reversible) migration — it can be applied but not reverted. To create a reversible migration that has both an apply and a revert script, pass the -r flag: 
sqlx migrate add -r add_email_index
# Creates:
#   migrations/20240102090000_add_email_index.up.sql
#   migrations/20240102090000_add_email_index.down.sql
Once you add your first reversible migration, all subsequent sqlx migrate add calls in that project also create .up.sql / .down.sql pairs, even without -r.
Edit each file independently: 
CREATE INDEX idx_users_email ON users (email);

​
Running migrations

sqlx migrate run compares the _sqlx_migrations table against the files in migrations/ and applies every pending script in version order: 
sqlx migrate run
# Applied migrations/20240101120000 create users (12.4ms)
# Applied migrations/20240102090000 add email index (3.1ms)
If no migrations are pending, the command exits cleanly without output.

​
Reverting migrations

sqlx migrate revert runs the .down.sql file for the most recently applied reversible migration: 
sqlx migrate revert
# Applied 20240102090000/revert add email index
Call it again to step back one more version. Reverting a simple (non-reversible) migration is not supported — only migrations created with -r can be reverted.

​
Checking migration status

sqlx migrate info lists all known migrations, whether each one is applied, and when it was run: 
sqlx migrate info
# 20240101120000/installed create users
# 20240102090000/installed add email index
# 20240115143000/pending  create posts

​
Using a custom migrations directory

Pass --source to any sqlx migrate subcommand to read migration files from a directory other than ./migrations: 
sqlx migrate run --source db/schema/migrations
sqlx migrate info --source ../shared/migrations

​
Running migrations programmatically

The sqlx::migrate!() macro embeds all files from ./migrations at compile time and returns a Migrator ready to run against any connection or pool: 
use sqlx::PgPool;

async fn run_migrations(pool: &PgPool) -> Result<(), sqlx::Error> {
    sqlx::migrate!().run(pool).await?;
    Ok(())
}
To use a different directory, pass the path as a string literal relative to CARGO_MANIFEST_DIR: 
sqlx::migrate!("db/migrations").run(&pool).await?;
For a path resolved at runtime, use Migrator::new(): 
use sqlx::migrate::Migrator;
use std::path::Path;

let migrator = Migrator::new(Path::new("./migrations")).await?;
migrator.run(&pool).await?;

​
What the _sqlx_migrations table tracks

SQLx creates _sqlx_migrations automatically on the first run. Each row records:
  • version — the numeric prefix from the filename
  • description — the human-readable name from the filename
  • checksum — SHA-384 hash of the migration SQL; changing an applied file causes an error on the next run
  • installed_on — timestamp of when the migration was applied
  • execution_time — duration in nanoseconds
  • success — whether the migration completed without error
Do not edit the SQL content of a migration that has already been applied to any environment. SQLx checksums every applied migration and will refuse to run if the file has changed since it was applied.

​
Opting out of transactions

By default, SQLx wraps each migration in a transaction. To run a migration outside a transaction — for example, when using CREATE INDEX CONCURRENTLY in PostgreSQL — add -- no-transaction as the first line of the file: 
-- no-transaction
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
See the migrations overview for a description of the migrate feature flag required to use the sqlx::migrate!() macro and the Migrator type.

Build docs developers (and LLMs) love

Get started for freeTalk to us