# Seed data

Apply one-off, environment-scoped SQL seed files with ptah seed and track them in schema_seeds.

Source: https://docs.ptah.run/v0.8.0/operate/seed-data/

Use `ptah seed` to load one-off setup rows — development fixtures, demo
accounts, initial admin users — into an environment without putting them in
migration history. You need a built `ptah` binary, a reachable database, and a
directory of seed files.

`ptah seed` is the imperative data path: it runs each matching SQL file once
and records it in the `schema_seeds` table. For lookup tables whose exact
contents Ptah should converge on every migration, use
[declarative reference data](../../versioned/reference-data/) instead.

## Name the seed files

Seed files follow the `NNN_description.env.sql` convention inside a seeds
directory (default `./seeds`):

- `NNN` is a numeric version; files apply in version order.
- `env` selects the environment: `002_demo_users.dev.sql` applies only with
  `--env dev`, and `001_countries.all.sql` applies in every environment.
- A `.sql` file in the directory that does not match the convention fails the
  run before anything is applied.

Starting state for the steps below:

```text
seeds/
  001_countries.all.sql
  002_demo_users.dev.sql
app.db
```

```sql
-- seeds/001_countries.all.sql
INSERT INTO countries (code, name) VALUES ('US', 'United States');
INSERT INTO countries (code, name) VALUES ('DE', 'Germany');
```

```sql
-- seeds/002_demo_users.dev.sql
INSERT INTO users (email, display_name) VALUES ('dev@example.com', 'Dev User');
```

## Apply seeds to an environment

```bash
ptah seed --db-url "sqlite://app.db" --env dev
```

Expected output includes:

```text
=== SEED ===
Database: sqlite://app.db
Dialect: sqlite
Seeds directory: seeds
Environment: dev

Matching seeds: 2
Applied seeds: 2
Skipped seeds: 0
Seeds completed successfully.
```

Re-running the same command is a no-op, because both files are recorded in
`schema_seeds`:

```text
Matching seeds: 2
Applied seeds: 0
Skipped seeds: 2
Database seed data is already up to date.
```

Add `--verbose` to list which files were applied or skipped, and
`--seeds-dir <path>` when the directory is not `./seeds`.

## Verify

Query the tracker table:

```bash
sqlite3 app.db "SELECT seed_path, env FROM schema_seeds ORDER BY seed_path;"
```

Expected output includes:

```text
001_countries.all.sql|dev
002_demo_users.dev.sql|dev
```

## The tracker table

`ptah seed` creates `schema_seeds` when at least one seed file matches the
environment and the table is absent, and leaves an existing table in place. It
holds the same columns on every engine: `seed_path`, `env`, `checksum` and
`applied_at`.

SQL Server, Oracle and Spanner do not take the statement the other engines get,
so each has its own:

- On SQL Server the statement is guarded with `IF OBJECT_ID(...) IS NULL`, and
  `applied_at` is `DATETIME2`. In T-SQL, `TIMESTAMP` is a row version the
  server fills in, not a point in time.
- On Oracle the table is created from a PL/SQL block that ignores `ORA-00955`
  (name already used). The same block runs on Oracle 21, which has no
  `IF NOT EXISTS`, and on Oracle 23.
- On Spanner the statement is the portable one with two types changed:
  `checksum` is `TEXT` and `applied_at` is `TIMESTAMPTZ`. Its PostgreSQL
  interface has neither `CHAR(64)`, which arrives as `bpchar`, nor `TIMESTAMP`.

## Protect production-like environments

`--env prod` and `--env production` are refused unless `--allow-prod` is set:

```text
error: refusing to seed protected environment "prod" without --allow-prod
```

The command exits with code 2 (see [Exit codes](../../reference/exit-codes/)).
Adjust the protected set with repeatable `--protected-env` flags, and add
repeatable `--protected-table` flags to require `--allow-prod` whenever a seed
file targets a named existing table.

## Edit an applied seed

`schema_seeds` records a SHA-256 checksum of each file's bytes alongside its
path, and the next run reads it. A seed file that changed after it was applied
is refused rather than reported as skipped:

```text
error: error applying seeds: seed 001_countries.all.sql changed after it was
applied: recorded checksum 85d0..., current 5bbc...; add a new seed file with
the change, or pass --force to re-apply this one
```

The command prints that on one line, and the two checksums are full SHA-256 hex
digests; both are wrapped and elided here.

Adding a new seed file is the normal answer, for the same reason it is with
migrations: the rows the old file wrote are already in the database, and the new
file says what changes about them. `--force` re-applies the edited file and
records its new checksum.

## Re-run seeds

- `--force` re-runs seeds that are already recorded in `schema_seeds`, and is
  what gets past the checksum refusal above. Plain `INSERT` statements then hit
  duplicate-key errors on tables with primary or unique keys.
- `--idempotent` treats a duplicate-key conflict as already-applied data,
  using a per-file savepoint, so `--force --idempotent` re-runs cleanly over
  existing rows. The savepoint is spelled for the engine: SQL Server gets
  `SAVE TRANSACTION` and `ROLLBACK TRANSACTION`, Oracle gets `SAVEPOINT` and
  `ROLLBACK TO SAVEPOINT`, and neither has a statement that releases one. The
  flag is refused on ClickHouse, which has neither transactions nor savepoints.

## Limitations

- Seed files are plain SQL applied once per environment; there are no down
  files and no rollback command.
- Seeds are outside migration history: `ptah migrations hash`, `validate`, and
  the revision table do not cover them.

## Next steps

- Ptah should own a table's exact contents:
  [Reference data](../../versioned/reference-data/).
- Wiring seeds into a scripted environment setup:
  [Native commands](../../reference/native-commands/).
- A seed run failed: [Troubleshooting](../troubleshooting/).