Declarative Schema

A Declarative Schema is the intended shape of your database, written in SQL. shki compiles it in a disposable Shadow Database, snapshots the result, and diffs that Snapshot against the latest one recorded in the Journal.

Shadow Database configuration

Declarative Schema compilation requires PostgreSQL execution in a Shadow Database.

By default, shki uses managed embedded PostgreSQL. You can pin the embedded PostgreSQL major version on commands that compile a Declarative Schema:

shki generate create_users --pg-version 16

Supported embedded major versions are 14, 15, 16, 17, and 18; the default is 18. Match it to the version you run in production so the compiled shape reflects the same server behavior.

On first use, the embedded server binaries are downloaded from the GitHub releases of theseus-rs/postgresql-binaries. That download honors the standard HTTPS_PROXY, HTTP_PROXY, ALL_PROXY, and NO_PROXY environment variables (lowercase variants too). If the download is refused with a 403, see Embedded PostgreSQL download fails.

For CI, locked-down environments, or teams that want explicit provisioning, configure an external Shadow Database:

export SHKI_SHADOW_DATABASE_URL='postgres://user:pass@localhost:5432/shki_shadow'

The Shadow Database is disposable. shki resets user schemas before applying the Declarative Schema. shadow_database_url must not be the same as database_url, and an external shadow must be marked as Shki-owned:

COMMENT ON DATABASE shki_shadow IS 'shki:shadow';

PostgreSQL extensions

Declare extensions in the schema before objects that use their types. Shki tracks the extension and preserves extension-defined column types as custom types.

CREATE EXTENSION postgis;
CREATE TABLE places (location geometry(Point, 4326) NOT NULL);

CREATE EXTENSION vector;
CREATE TABLE embeddings (embedding vector(3) NOT NULL);

The Shadow Database image must have each declared extension installed. Embedded PostgreSQL does not provide PostGIS or pgvector; configure an external, Shki-owned PostgreSQL image that includes them. Extension type modifiers, such as vector(3), halfvec(384), and geometry(Point, 4326), are retained in Snapshots and detected by schema diffs.

Concurrent index builds

Declare an index CREATE INDEX CONCURRENTLY to ask for PostgreSQL’s non-blocking build — the way to add an index to a large, live table without taking a write-blocking lock:

CREATE TABLE scan (id bigint PRIMARY KEY, captured_at timestamptz NOT NULL);
CREATE INDEX CONCURRENTLY scan_captured_at_idx ON scan (captured_at);

The keyword is a creation strategy, not schema state: PostgreSQL doesn’t record it in its catalogs, and shki doesn’t record it in Snapshots. It changes how shki generate writes the migration — the index build is split into its own shki:no-transaction migration, after a confirmation prompt — not what the schema is. Adding or removing CONCURRENTLY on an index that already exists diffs as no change.

During compilation the keyword is stripped before the schema is applied to the Shadow Database (the shadow apply runs in one implicit transaction, which CONCURRENTLY refuses; on a fresh shadow there is nothing to build online anyway).

The index name is optional, as in plain SQL. An unnamed concurrent index — CREATE INDEX CONCURRENTLY ON hello (id) — is given a name following Postgres’s own convention (hello_id_idx; expressions contribute expr), which becomes the index’s name in the generated migration.

Directory Schemas

A Declarative Schema can be a single SQL file or a directory with a canonical main.sql entrypoint.

schema/
  main.sql
  tables/
    users.sql

-- schema/main.sql
\i tables/users.sql

Only \i include directives are supported in v1. Include paths are resolved relative to the including file, and include cycles are rejected.