November 8, 20263 min read

Manage DuckDB Schemas and Migrations with a Ptah Plugin

Use Ptah's official DuckDB plugin to inspect an embedded database, generate reviewable SQL migrations, apply them, and verify the result.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A DuckDB file can begin as a local analysis artifact and become part of a repeatable application workflow. When its tables change, a SQL file that can be reviewed and applied again is easier to manage than a collection of manual edits to individual database files.

Ptah supports DuckDB through the official DuckDB plugin. Install it separately, then use Ptah’s schema inspection and versioned migration commands against an embedded database file. The plugin knows how to read DuckDB’s catalog, plan its DDL, execute changes, and record migration history. This guide follows one table from its first migration to a new column and index. The supporting files record the released binaries and the complete results.

Install the DuckDB plugin

Use Ptah 0.25.0 or another release compatible with the provider. The commands below use plugin 0.2.0. Ptah checks the provider’s declared contract when it installs it.

ptah providers install duckdb --version 0.2.0
ptah providers check --provider-engine duckdb

The check reports compatible when Ptah can start the installed plugin. A duckdb:// URL then selects it automatically. The native DuckDB library lives in the plugin process, so the Ptah executable does not need a DuckDB driver or CGO.

Generate the first migration

Save the desired starting schema in schema-v1.sql:

CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  kind VARCHAR NOT NULL
);

Generate a migration against a new local file, review the generated pair in migrations/, and apply it:

ptah migrations generate --db-url duckdb://./demo.duckdb --schema-file schema-v1.sql --migrations-dir migrations --name create_events
ptah migrations up --db-url duckdb://./demo.duckdb --migrations-dir migrations --provider-engine duckdb

The generated up file creates main.events; the down file drops it. Keep both files in version control. Ptah records the applied version in the database, so later runs can identify pending migrations.

Add a timestamp and an index

The next desired schema adds an optional timestamp and an index on kind:

CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  kind VARCHAR NOT NULL,
  observed_at TIMESTAMP
);

CREATE INDEX events_kind_idx ON events (kind);

Generate another migration from the current database state:

ptah migrations generate --db-url duckdb://./demo.duckdb --schema-file schema-v2.sql --migrations-dir migrations --name add_event_timestamp_and_index --provider-engine duckdb

Its up file contains these statements:

ALTER TABLE "main"."events" ADD COLUMN "observed_at" TIMESTAMP;
CREATE INDEX "events_kind_idx" ON "main"."events" ("kind");

The paired down file drops the index and column. Review both directions before deployment. The SQL comes from the DuckDB provider’s comparison and rendering rules, while Ptah manages the migration files and revision workflow.

Apply the second migration, then inspect the live schema and migration status:

ptah migrations up --db-url duckdb://./demo.duckdb --migrations-dir migrations --provider-engine duckdb
ptah schema inspect --db-url duckdb://./demo.duckdb --format sql --provider-engine duckdb
ptah migrations status --db-url duckdb://./demo.duckdb --migrations-dir migrations --provider-engine duckdb

Inspection reads back the new column and index:

CREATE TABLE "main"."events" (
  "id" BIGINT NOT NULL,
  "kind" VARCHAR NOT NULL,
  "observed_at" TIMESTAMP,
  PRIMARY KEY ("id")
);
CREATE INDEX "events_kind_idx" ON "main"."events" ("kind");

The status reports two applied migrations and no pending work:

=== MIGRATION STATUS ===
Database: ***
Dialect: duckdb
Schema: main

Current Version: 1794147312
Total Migrations: 2
Applied Migrations: 2
Pending Migrations: 0
Out-of-order Migrations: 0
Status: ✅ Database is up to date

This is a local file workflow. It needs no database server or container. The provider also supports in-memory DuckDB databases and direct schema changes. It serves embedded DuckDB only; MotherDuck, DuckLake, and other network targets are outside its current scope. DuckDB permits one writer per database file, and the plugin reports file lock conflicts rather than hiding them. The provider limitations list unsupported schema changes and services.

If you already use DuckDB, start with the provider setup guide and choose either direct apply or versioned migrations. To see how the same provider API supports YDB and Spanner, or how to build a plugin for another database, read Add Your Database to Ptah with a Plugin.

Put Ptah to work

Example files

← All articles