# scribase db pull

Reads the schema of a live database and writes the difference from your local
migrations as a new migration file. Use it to adopt a database that was changed
by hand (or in the Studio) back into version control.

## Usage

```sh
# Pull from a Scribase environment (needs a writer role on the organization)
scribase db pull <org> <project> <env> [--dir ./migrations]

# Pull from any Postgres URL
scribase db pull --db-url postgresql://user:pass@host:5432/app [--dir ./migrations]
```

`SCRIBASE_DB_URL` is used when neither an environment nor `--db-url` is given.
For an environment, the CLI asks the control API for the environment's
connection string (`GET .../environments/{env}/connection`) and connects to it
directly, so the database must be reachable from your machine.

## How it works

1. The remote schema is dumped with `pg_dump --schema-only --no-owner --no-privileges`.
2. If the migrations directory is empty or missing, the cleaned dump becomes the
   first migration.
3. Otherwise a temporary shadow database is created with `initdb`, every local
   migration is applied to it in version order, and it is dumped the same way.
4. The two dumps are compared object by object (tables column by column,
   indexes, constraints, defaults, policies, functions, views, triggers,
   sequences, enum labels, comments). Only the difference is written.
5. The remote ledger (`orion.schema_migrations`) is compared with the local
   files, and versions that exist on only one side are reported.

If nothing differs, no file is written.

## Options

| Flag | Description |
|---|---|
| `--db-url <url>` | Pull from this Postgres URL instead of an environment |
| `--dir <path>` | Migrations directory (default `migrations`) |
| `--schema <a,b>` | Schemas to pull, comma-separated (default `public`) |
| `--name <name>` | File name suffix (default `remote_schema`) |
| `--shadow-db-url <url>` | Use this empty database as the shadow instead of a temporary cluster |
| `--dry-run` | Print the migration instead of writing it |
| `--mark-applied` | Record the new file as applied in the remote `orion.schema_migrations` |

## File naming

The new file continues the directory's numbering: `003_remote_schema.sql` after
`002_...`, keeping the existing zero padding. A directory that uses timestamp
versions, or an empty one, gets a UTC timestamp such as
`20260924120000_remote_schema.sql`. Either shape is accepted by
[`scribase migrate`](https://docs.scribase.com/docs/cli/migrate.md).

## Destructive changes

Anything that would delete data (a dropped table, column, schema, extension, or
type) is written commented out with a `-- REVIEW` marker, and the command
prints how many there are. Uncomment only the ones you intend to apply. Other
removals (indexes, policies, constraints, functions, views, triggers) are
written as `DROP ... IF EXISTS`.

## Marking the pull as applied

The remote already has the schema you just pulled. Without `--mark-applied`,
a later `scribase migrate --apply` would try to run the new file there and fail
on objects that already exist. `--mark-applied` inserts the file's version,
name, and SHA-256 into the remote ledger, which is exactly what the migration
runner writes after applying a file itself.

## Requirements and limits

- `pg_dump`, `psql`, `initdb`, and `pg_ctl` on `PATH`, or in the directory set by
  `SCRIBASE_PG_BIN_DIR`. `pg_dump` must be at least the remote server's major
  version.
- The temporary shadow gets the Supabase roles (`anon`, `authenticated`,
  `service_role`) and `auth.uid()`, `auth.role()`, `auth.jwt()` and
  `auth.users` stubs, so typical app migrations apply. If your migrations need
  extensions that are not installed locally, pass `--shadow-db-url` pointing at
  an empty database that has them.
- Grants and ownership are not pulled.
- A changed composite type or table storage options are flagged with
  `-- REVIEW` rather than rewritten.

## Example

```sh
scribase db pull acme store production --dir ./migrations
# Wrote migrations/004_remote_schema.sql (6 statement(s)).

# Review the file, then record it as applied on the remote:
scribase db pull acme store production --dir ./migrations --mark-applied
```