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

# Database

export const ProOnly = ({button = 'Get started for free', description = 'Try our fully managed solution to access this advanced feature.', source = 'documentation', title = 'Only in Mage Pro.'}) => <div className="block my-4 px-5 py-4 overflow-hidden rounded-xl flex gap-3 border border-emerald-500/20 bg-emerald-50/50 dark:border-emerald-500/30 dark:bg-emerald-500/10">
    <div style={{
  display: 'flex',
  alignItems: 'center',
  width: '100%'
}}>
      <div className="text-sm prose min-w-0 text-emerald-900 dark:text-emerald-200" style={{
  flex: 1
}}>
        <span className="font-semibold">{title}</span>
        <p className="normal">{description}</p>
      </div>

      <div> </div>

      <div style={{
  height: 32,
  position: 'relative'
}}>
        <a target="_blank" rel="noopener noreferrer" className="group px-4 py-1.5 relative inline-flex items-center text-sm font-medium rounded-full" href={`https://cloud.mage.ai/sign-up?source=${source}`}>
          <span className="absolute inset-0 bg-primary-dark dark:bg-primary-light/10 border-primary-light/30 rounded-full dark:border group-hover:opacity-[0.9] dark:group-hover:border-primary-light/60">
          </span>

          <div className="mr-0.5 space-x-2.5 flex items-center">
            <span className="z-10 text-white dark:text-primary-light">
              {button}
            </span>

            <svg width="3" height="24" viewBox="0 -9 3 24" className="h-5 rotate-0 overflow-visible text-white/90 dark:text-primary-light">
              <path d="M0 0L3 3L0 6" fill="none" stroke="currentColor" strokeWidth="1.5" strokeLinecap="round"></path>
            </svg>
          </div>
        </a>
      </div>
    </div>
  </div>;

export const ProButton = ({href, label = 'Get started with Mage Pro for free', source = 'documentation'}) => <div style={{
  height: 32,
  position: 'relative'
}}>
    <a target="_blank" className="group px-4 py-1.5 relative inline-flex items-center text-sm font-medium rounded-full" href={href ?? `https://cloud.mage.ai/sign-up?source=${source}`}>
      <span className="absolute inset-0 bg-primary-dark dark:bg-primary-light/10 border-primary-light/30 rounded-full dark:border group-hover:opacity-[0.9] dark:group-hover:border-primary-light/60">
      </span>

      <div className="mr-0.5 space-x-2.5 flex items-center">
        <span class="z-10 text-white dark:text-primary-light">
          {label}
        </span>

        <svg width="3" height="24" viewBox="0 -9 3 24" class="h-5 rotate-0 overflow-visible text-white/90 dark:text-primary-light">
          <path d="M0 0L3 3L0 6" fill="none" stroke="currentColor" stroke-width="1.5" stroke-linecap="round"></path>
        </svg>
      </div>
    </a>
  </div>;

By default, Mage uses sqlite to store orchestration data (trigger, pipeline run, and block run). To use a different database, you can set the `MAGE_DATABASE_CONNECTION_URL` environment variable.

In production, you can set the environment variable in the corresponding Terraform script.

## PostgreSQL

`export MAGE_DATABASE_CONNECTION_URL=postgresql+psycopg2://user:password@host:port/dbname`

To use a specific `schema_name` in PostgreSQL:

`export MAGE_DATABASE_CONNECTION_URL="postgresql+psycopg2://user:password@host:port/dbname?options=-c%20search_path%3Dschema_name"`

### Get credentials from AWS Secret Manager

You can also instruct Mage to fetch your Postgres DB credentials from AWS Secret Manager. You will need to
set the following environment variables:

| Variable                | Value                                                                |
| ----------------------- | -------------------------------------------------------------------- |
| `AWS_DB_SECRETS_NAME`   | name of secret in AWS Secret Manager                                 |
| `AWS_DEFAULT_REGION`    | AWS region the secret is stored in                                   |
| `AWS_ACCESS_KEY_ID`     | AWS access key id (not needed if using IAM role to authenticate)     |
| `AWS_SECRET_ACCESS_KEY` | AWS secret access key (not needed if using IAM role to authenticate) |

The expected format of the secret is JSON string with the following keys:

* username
* password
* engine (must be `postgres`)
* host
* port
* dbname

## MSSQL (not officially supported)

`export MAGE_DATABASE_CONNECTION_URL="mssql+pyodbc://?odbc_connect=DRIVER={ODBC Driver 18 for SQL Server};SERVER=host;DATABASE=dbname;UID=user;PWD=password;ENCRYPT=yes;TrustServerCertificate=yes;"`

## Troubleshoot database migration failures

<ProOnly source="database" />

Mage uses Alembic migrations for orchestration tables such as triggers, pipeline runs, block runs, users, secrets, and audit events. If the server fails during startup with an error like `alembic`, `sqlalchemy.exc`, `DROP INDEX`, `CREATE INDEX`, `alembic_version`, or `Can't locate revision`, use the Mage database CLI before manually editing the database.

Start with a read-only diagnosis:

```bash theme={"system"}
mage db diagnose
```

If your project uses the default SQLite database, run the command from the project directory or pass the project path:

```bash theme={"system"}
mage db diagnose --project-path /path/to/project
```

The command prints the database dialect, redacted database URL, revision stored in the `alembic_version` table, migration head in the installed Mage code, recommendations, and any pending revisions that have not been migrated yet.

When the database revision is not present in the installed migration scripts, `diagnose` also inspects schema evidence registered for a baseline anchor and each subsequent revision in the instrumented migration tail. The anchor verifies selected baseline tables and structures, and each supported revision verifies a structural fingerprint that can include column types and nullability, primary and foreign keys, and index columns and uniqueness. Mage reports each evidence revision as `applied`, `missing`, or `partial`. If the registered evidence is contiguous through the installed head and forms one applied prefix, the output identifies the latest schema-evidenced candidate and the first missing evidenced revision.

The candidate is a diagnostic boundary, not proof that every migration before the baseline anchor succeeded. Mage does not emit a stamp command from schema evidence alone. Inspect the earlier migration files and live schema before changing `alembic_version`; if the evidence has a gap, a partial fingerprint, an applied revision after a missing revision, no applied baseline, or a non-linear migration graph, Mage does not report a candidate at all.

To list every Alembic revision available in the installed Mage code:

```bash theme={"system"}
mage db history
```

This is read-only and does not connect to or change the database.

To inspect the current schema for a specific database table:

```bash theme={"system"}
mage db schema <table>
```

If your database uses a non-default schema, pass it explicitly:

```bash theme={"system"}
mage db schema <table> --schema <schema>
```

This prints the table columns, types, nullability, primary key columns, defaults, indexes, and foreign keys. Use it to confirm whether a failed migration already created or removed the table objects referenced by the error before you decide to roll back or stamp a revision.

### Run pending migrations

If the database is behind the installed Mage code, run:

```bash theme={"system"}
mage db migrate
```

To migrate to a specific Alembic revision:

```bash theme={"system"}
mage db migrate --revision <revision>
```

This runs migration SQL and uses Mage's database migration lock so multiple processes do not migrate the same database at the same time.

### Roll back a failed or incompatible migration

If a migration was applied and you need to revert it, roll back one migration:

```bash theme={"system"}
mage db rollback --revision -1
```

Or roll back to a specific revision:

```bash theme={"system"}
mage db rollback --revision <revision>
```

Review the migration file before rolling back in production. A rollback can drop columns, indexes, tables, or enum values depending on the migration.

Rollback statements often need exclusive locks on orchestration tables. For example, a downgrade that drops columns from the `user` table can wait while the running Mage server has active sessions using that table. The CLI applies a PostgreSQL lock timeout so the command fails instead of waiting forever:

```bash theme={"system"}
mage db rollback --revision -1 --lock-timeout-seconds 10
```

To inspect active PostgreSQL sessions that may be blocking the rollback:

```bash theme={"system"}
mage db locks
```

For an overall statement cap, also pass:

```bash theme={"system"}
mage db rollback --revision -1 --lock-timeout-seconds 10 --statement-timeout-seconds 120
```

If rollback times out waiting for locks, stop the Mage server or other workers connected to the orchestration database, then retry the rollback. After the rollback finishes, run `mage db diagnose` before restarting Mage.

### Stamp a revision without running SQL

Use `stamp` only when the database schema already matches the target revision but the `alembic_version` metadata is missing or wrong:

```bash theme={"system"}
mage db stamp <revision>
```

For non-interactive recovery:

```bash theme={"system"}
mage db stamp <revision> --yes
```

`stamp` changes Alembic metadata without creating, dropping, or altering database objects. It can help when a migration partially completed outside Alembic, but it can also hide real schema drift if used incorrectly. If you see an error such as `index "..." does not exist`, confirm whether the rest of the migration's expected schema changes are already present before stamping past that revision.

After independently verifying that the PostgreSQL schema matches the intended known revision, replace the exact unknown marker with a compare-and-swap recovery command. For example:

```bash theme={"system"}
mage db stamp <independently_verified_revision> \
  --replace-unknown-revision <current_unknown_revision> \
  --yes
```

Back up the database and stop migration-capable Mage processes before running this command in production. `--replace-unknown-revision` is supported only for PostgreSQL and requires `--yes`; the target must exist in the installed migration scripts, the database must contain exactly one `alembic_version` row, that row must still equal the supplied unknown revision, and the supplied current revision must remain unknown to the installed code. Mage performs the replacement transactionally and makes no change if any check fails. This recovery option changes only the revision marker; it does not apply migration SQL.

After replacing the unknown marker, apply the missing migrations and verify the result:

```bash theme={"system"}
mage db migrate
mage db diagnose
```

### Typical recovery flow

1. Back up the database before making changes in production.
2. Run `mage db diagnose`.
3. If `diagnose` reports an unknown revision and a schema-evidenced candidate, verify all earlier migrations and the live schema independently; do not stamp from the candidate alone.
4. If `diagnose` cannot infer a candidate, run `mage db history` and inspect the surrounding migration files; do not guess a revision.
5. If the error names a table, index, or constraint, run `mage db schema <table>` to inspect the live table.
6. If the database is behind, run `mage db migrate`.
7. If the latest migration is bad for your deployment, run `mage db rollback --revision -1`, then pin or deploy a Mage version with a compatible migration chain.
8. If a PostgreSQL schema independently matches the intended known revision but `alembic_version` contains an unknown revision, run `mage db stamp <revision> --replace-unknown-revision <unknown_revision> --yes`. For other marker problems, use the normal `mage db stamp <revision>` command only after verifying the schema.
9. Run `mage db diagnose` again and restart Mage.