Evolve and gate a direct schema

Use this page after the default quick start. It continues from that tutorial’s schema.sql and app.db, and rebuilds them below if you have already cleaned up. You will add a column, inspect the ALTER TABLE Ptah derives, apply it, and prove the database has not drifted from the file.

Prerequisites

• Your terminal is in ptah-quick-start.
• app.db contains the users table from the default quick start.
• schema.sql still describes that table.

If you already removed the directory, rebuild that state here rather than working through the quick start again. The commands are its first and third steps:

Bash

mkdir ptah-quick-start
cd ptah-quick-start
cat > schema.sql <<'SQL'
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
SQL
ptah schema apply --schema-file schema.sql --db-url sqlite://app.db --auto-approve

PowerShell

New-Item -ItemType Directory ptah-quick-start | Out-Null
Set-Location ptah-quick-start
@'
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
'@ | Set-Content schema.sql
ptah schema apply --schema-file schema.sql --db-url sqlite://app.db --auto-approve

1. Change the desired schema

Replace schema.sql with this version:

Bash

cat > schema.sql <<'SQL'
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,
    email      TEXT NOT NULL,
    created_at TEXT
);
SQL

PowerShell

@'
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,
    email      TEXT NOT NULL,
    created_at TEXT
);
'@ | Set-Content schema.sql

You changed the destination shape. Ptah derives the SQL needed to reach it from the live database.

2. Review the evolution plan

ptah schema apply --schema-file schema.sql --db-url sqlite://app.db --dry-run

Expected output on standard output:

Planned schema changes:
ALTER TABLE "users" ADD COLUMN "created_at" TEXT;

The plan alters the existing table; it does not create another table or replay the original statement.

3. Apply and verify the change

ptah schema apply --schema-file schema.sql --db-url sqlite://app.db --auto-approve
ptah schema drift --schema-file schema.sql --db-url sqlite://app.db

Expected output on standard output:

Schema apply completed successfully.
No schema drift detected.

Caution

--auto-approve is appropriate for this disposable SQLite file. Review a saved plan or use an interactive approval before applying to a shared database.

4. Read the column back from the database

The apply reported success and drift reported none. Both are Ptah’s own verdicts; this reads the table itself:

ptah db read --db-url sqlite://app.db

Expected output includes, on standard output:

CREATE TABLE "users" (
  "id" INTEGER PRIMARY KEY,
  "email" TEXT NOT NULL,
  "created_at" TEXT
);

The column is there, in app.db rather than in schema.sql.

5. Use drift as a CI gate

The same check belongs in automation, with the URL coming from the environment: ptah schema drift --schema-file schema.sql --db-url "$DATABASE_URL".

Branch on the exit status, not on captured prose:

Exit	Meaning
0	The live database matches the desired schema.
1	Drift exists. The report includes the highest severity and findings.
2	Ptah could not decide because the invocation, source, or connection failed.

CI shows the GitHub Action and shell forms. Compare and detect drift covers severities, formats, and remediation paths.

Clean up

Bash

cd ..
rm -rf ptah-quick-start

PowerShell

Set-Location ..
Remove-Item -Recurse -Force ptah-quick-start

Next steps

• Save, sign, and verify a plan.
• Adopt an existing database.
• Generate versioned migration files from the same desired schema.