Database lifecycle

Use this when your app has crossed from "one query" to "real tables and migrations." Start local with PGlite, keep the schema in Drizzle, and use kovo db to move to reviewed Postgres posture.

Start with PGlite#

Local development starts with a real SQL engine and no external service:

import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';

export async function createDb() {
  const client = new PGlite('.kovo/pglite');
  await client.waitReady;
  return drizzle({ client });
}

The default starter path stores that database under .kovo/pglite unless you override it with KOVO_DATA_DIR.

Define the schema#

Keep domain ownership on the table definition:

import { kovo } from '@kovojs/drizzle';
import { integer, pgTable, text } from 'drizzle-orm/pg-core';

export const products = pgTable(
  'products',
  {
    id: text('id').primaryKey(),
    name: text('name').notNull(),
    stock: integer('stock').notNull(),
  },
  kovo(() => ({ domain: 'product' })),
);

That domain annotation is what lets the rest of the framework connect writes to query refreshes.

Seed dev data#

The examples keep seeding simple: create the tables, then insert the rows you want to demo:

export const SCHEMA_DDL = `
  create table if not exists products (
    id text primary key,
    name text not null,
    stock integer not null
  );
`;

export const SEED_PRODUCTS = `
  insert into products (id, name, stock)
  values ('p1', 'Aero Wireless Keyboard', 5);
`;

Run the DDL once, then seed the rows your local app needs. The commerce and CRM examples use this same pattern for fast demo data.

Generate and run migrations#

Once the schema stops being throwaway, move to reviewed SQL files:

kovo db generate --schema src/schema.ts --migrations migrations
kovo db migrate --schema src/schema.ts --driver pglite --data-dir .kovo/pglite --migrations migrations

generate writes additive *.sql files you review. migrate applies them and records whether each file was applied or skipped.

Move to Postgres#

When the app needs the real runtime posture, switch the URLs instead of rewriting the app:

KOVO_ADMIN_DATABASE_URL=postgres://admin@db:5432/app?sslmode=verify-full \
KOVO_RUNTIME_DATABASE_URL=postgres://app@db:5432/app?sslmode=verify-full \
kovo db provision --schema src/schema.ts --migrations migrations

Use the admin URL for setup and only as a check fallback, never in the app process. Give the live app the ordinary runtime URL plus a dedicated KOVO_DB_SYSTEM_URL for framework replay/auth storage. Your DBA can pre-create kovo_system as a least-privilege login; provision adopts it and installs its exact grants. kovo db check prefers that system authority. Reader and writer roles default to kovo_reader and kovo_writer, or you can override them with KOVO_DB_READER_ROLE and KOVO_DB_WRITER_ROLE.

Every Postgres URL must explicitly name its login, database, and decimal port. Remote URLs must use a DNS hostname and exact sslmode=verify-full; Kovo refuses IP literals, every query user/database override, remote query host/port overrides, weaker TLS modes, and ambient PG* destination or identity fallback before it opens a pool. This keeps credentials off plaintext or certificate-unverified connections. Only exact 127.0.0.1, an exact query-host ::1, and validated Unix-socket URLs may omit TLS for local development. Write the IPv6 control as postgres://app@localhost:5432/app?host=%3A%3A1; bracketed authority [::1] is rejected because pinned pg passes those brackets to DNS. Express a Unix socket as a URL with explicit identity and port, for example postgres://app@localhost:5432/app?host=%2Fvar%2Frun%2Fpostgresql.

Check the posture#

Make the live database prove it matches the schema and grants:

KOVO_DB_SYSTEM_URL=postgres://kovo_system@db:5432/app?sslmode=verify-full \
KOVO_RUNTIME_DATABASE_URL=postgres://app@db:5432/app?sslmode=verify-full \
kovo db check --schema src/schema.ts

On a healthy database the command reports STATUS ok with issues=0. On an empty or drifted database it fails closed with a posture report instead of guessing. The check also proves both URLs reach the same database on the same writable primary. Use KOVO_ADMIN_DATABASE_URL instead when a deployment cannot expose a dedicated system login to CI.

Handle failure#

The common failures are operational:

• check against an unprovisioned database fails.
• migrate rejects a changed migration file instead of silently reapplying it.
• provision fails when the admin URL or roles are wrong for the target database.

Treat those as contract failures between schema, migrations, and the live database. Fix the drift. Do not edit history out from under an applied migration.

Next#

• Postgres authz policy — see the runtime posture the DB commands install.
• Configuration & environment — keep the DB URLs and roles straight across environments.