how-to

Seed data

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

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 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:

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

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

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

Apply seeds to an environment

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

Expected output includes:

=== 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:

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:

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

Expected output includes:

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:

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

The command exits with code 2 (see 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:

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.
Wiring seeds into a scripted environment setup: Native commands.
A seed run failed: Troubleshooting.