> ## Documentation Index
> Fetch the complete documentation index at: https://docs.reasonos.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database migrations

> Version your SQL schema with Flyway-style migrations for PostgreSQL, MySQL, and SQLite, driven by rbs migrate.

rbs ships a built-in SQL migration engine: versioned and repeatable `.sql` files
live in your package, a `migration_database` declaration in the build file names
the database they belong to, and `rbs migrate` applies and tracks them. It
supports **PostgreSQL, MySQL, and SQLite**, with checksummed history, a
database-side lock so concurrent migrators cannot interleave, and a dry-run mode.

## Declaring a database

```python theme={null}
load("@rbs//migration/rules.rbs", "migration_database")

migration_database(
    name = "app_db",
    database_url_env = "DATABASE_URL",
    migrations_dir = "migrations",      # relative to this package
    create_if_not_exists = True,
)
```

| Attribute              | Type        | Default                   | Required | Meaning                                                                                               |
| ---------------------- | ----------- | ------------------------- | -------- | ----------------------------------------------------------------------------------------------------- |
| `name`                 | string      | —                         | yes      | The name `rbs migrate` commands address — a plain name, not a `//label`.                              |
| `database_url_env`     | string      | —                         | yes      | The environment variable holding the connection string. The URL itself never appears in a build file. |
| `migrations_dir`       | string      | `"migrations"`            | no       | Directory of `.sql` files, relative to the declaring package.                                         |
| `schema`               | string      | `"public"`                | no       | Target schema.                                                                                        |
| `history_table`        | string      | `"rbs_migration_history"` | no       | Table where applied migrations are recorded.                                                          |
| `create_if_not_exists` | bool        | `False`                   | no       | Create the database first if it does not exist.                                                       |
| `placeholders`         | string dict | `{}`                      | no       | `${key}` → value substitutions applied to migration SQL.                                              |

<Note>
  `migration_database` registers configuration — it is not a build target. It
  produces nothing under `rbs build`, and you address it by **name**
  (`rbs migrate apply app_db`), from any directory in the workspace: the command
  loads every package, so the declaration is found wherever it lives.
</Note>

## Migration files

Two kinds of file live in `migrations_dir`:

* **Versioned** — `V{unix_timestamp}__{description}.sql`. Runs exactly once, in
  version order (e.g. `V1709141200__create_users_table.sql`).
* **Repeatable** — `R__{description}.sql`. Re-runs whenever its checksum
  changes — the natural home for views, functions, and grants.

Always create files with the CLI rather than hand-naming them, so the version
stamp is correct:

```bash theme={null}
rbs migrate create app_db "create users table"        # V<now>__create_users_table.sql
rbs migrate create --repeatable app_db "permissions"  # R__permissions.sql
```

Placeholders from the declaration substitute into the SQL as `${key}`:

```sql theme={null}
CREATE TABLE ${schema_name}.users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
```

An unresolved `${...}` token fails the migration and points back at the
`placeholders` map on the rule.

## Selecting the database

The connection string comes from the environment variable named by
`database_url_env`, read when you invoke `rbs migrate`:

```bash theme={null}
export DATABASE_URL="postgres://user:pass@localhost:5432/myapp?sslmode=disable"
rbs migrate apply app_db
```

The engine is inferred from the URL scheme — no engine attribute exists:

| Scheme                         | Engine     |
| ------------------------------ | ---------- |
| `postgres://`, `postgresql://` | PostgreSQL |
| `mysql://`                     | MySQL      |
| `sqlite://`, `file:`           | SQLite     |

SQLite makes a handy local fast path: `export DATABASE_URL=file:./dev.db` runs
the same migration files against a local file.

### Per-environment databases

The simplest per-environment story is exporting a different URL in each context
— your shell, CI, a deploy pipeline. Because migrations against a cluster
database are usually run over a port-forward or from inside the network, this
composes naturally with a Kubernetes deploy:

```bash theme={null}
kubectl -n myapp port-forward pod/postgres-0 55432:5432 &
DATABASE_URL="postgres://myapp:${POSTGRES_PASSWORD}@127.0.0.1:55432/myapp" \
  rbs migrate apply app_db
```

The global `-e` flag (or `RBS_ENV`) selects one of your
[declared environments](/build/environments) before workspace files
execute, and the `env` module is available in every build file — so a package
can register a different configuration per environment, for example a different
variable name:

```python theme={null}
migration_database(
    name = "app_db",
    database_url_env = "DATABASE_URL_STAGING" if env.name() == "staging" else "DATABASE_URL",
)
```

```bash theme={null}
rbs migrate apply app_db -e staging     # reads $DATABASE_URL_STAGING
```

## Commands

```bash theme={null}
rbs migrate create   <name> <description>   # new migration file (--repeatable)
rbs migrate info     <name>                 # status of every migration
rbs migrate validate <name>                 # consistency check — run this in CI
rbs migrate apply    <name>                 # apply pending migrations (--dry-run, --out-of-order)
rbs migrate baseline <name> <version>       # adopt an existing database
rbs migrate repair   <name>                 # fix a broken history
```

### apply

Applies all pending versioned migrations, in order, plus any repeatable
migrations whose checksum changed. Behavior worth knowing:

* **Each migration runs in its own transaction.** On failure the migration is
  recorded as failed and execution stops.
* A migration whose leading comments contain `-- rbs:no-transaction` runs its
  statements individually **outside** any transaction — for statements like
  `CREATE INDEX CONCURRENTLY` that refuse one.
* **Concurrent applies are safe.** A database-side lock serializes migrators,
  so two CI jobs applying at once cannot interleave.
* A pending migration versioned **lower** than one already applied is rejected
  — the classic stale-branch-merged-late case. Pass `--out-of-order` to apply
  it anyway, or re-create it with a fresh timestamp.
* `--dry-run` prints the SQL that would run without touching the database (and
  skips the lock).

### validate

Checks that reality matches the files on disk, exiting non-zero on any issue —
put it in CI:

* files modified after being applied (checksum mismatch)
* files deleted after being applied
* previously failed migrations that need attention

### baseline

Adopting rbs migrations on a database that already has its schema? Baseline
marks all versioned migrations up to and including the given version as applied
**without executing them**:

```bash theme={null}
rbs migrate baseline app_db 1709141200
```

### repair

The recovery command, for after you have fixed a broken migration or
deliberately edited an already-applied file. It removes history entries for
failed migrations (so they can be retried) and realigns stored checksums with
the current files on disk.

## A typical workflow

```bash theme={null}
rbs migrate create app_db "add orders table"    # 1. create the file
$EDITOR migrations/V*__add_orders_table.sql     # 2. write the SQL
rbs migrate apply app_db --dry-run              # 3. review what will run
rbs migrate apply app_db                        # 4. apply
rbs migrate info app_db                         # 5. confirm
```

And in CI, before deploying:

```bash theme={null}
rbs migrate validate app_db && rbs migrate apply app_db
```