optml-sql

XML-based schema generators for PostgreSQL and Cloudflare D1/SQLite. The PostgreSQL scripts remain generate_schema.py and generate_drizzle.py; the SQLite implementation is kept in separate *_sqlite.py files.

D1/SQLite

exampleschema_sqlite.xml demonstrates the format described by schema_sqlite.xsd. The generator accepts the existing PostgreSQL-oriented type names and maps them to SQLite affinities:

• SERIAL, integer types, and booleans become INTEGER.
• Decimal and floating-point types become REAL.
• UUID, date/time, and character types become TEXT.
• JSON/JSONB becomes validated JSON stored as TEXT.
• BYTEA/BLOB becomes BLOB.
• uuid_generate_v4()/uuid() and now() are translated to D1-compatible defaults.

Generate the bootstrap SQL for a new database:

python3 ./generate_schema_sqlite.py exampleschema_sqlite.xml --output schema.sql
npx wrangler d1 execute <database-name> --local --file=./schema.sql

Generate a Drizzle sqlite-core schema for the same XML:

python3 ./generate_drizzle_sqlite.py exampleschema_sqlite.xml --output drizzle_schema.ts

The bootstrap SQL is intentionally declarative. Re-running it does not update existing columns because SQLite has no ALTER TABLE ... ADD COLUMN IF NOT EXISTS equivalent.

Schema updates

Keep an immutable copy of the XML that matches the last deployed schema. Compare it with the desired schema to create a one-time migration:

python3 ./generate_migration_sqlite.py \
  schemas/0001_schema.xml \
  schema.xml \
  --output migrations/0002_update_projects.sql

npx wrangler d1 migrations apply <database-name> --local
npx wrangler d1 migrations apply <database-name> --remote

The migration generator:

• adds and removes tables and indexes;
• rebuilds tables for column, type, default, uniqueness, nullability, and foreign-key changes;
• preserves rows while rebuilding all dependent tables, preventing ON DELETE CASCADE data loss;
• preserves history tables and refreshes their triggers;
• copies renamed columns when the new column has renamedFrom="old_name";
• rejects a new NOT NULL column without a default, because existing rows cannot be populated safely.

Migration SQL contains no explicit BEGIN or COMMIT; D1 applies each Wrangler migration atomically. Apply each generated migration exactly once through Wrangler's migration tracking. Table renames and cyclic foreign-key rebuilds require a reviewed manual migration.

History tables

history="true" creates a history_<table> table plus insert, update, and delete triggers. Updates store only changed fields. Mark a high-frequency column nohistory="true" to omit it from update diffs. Removing a table or disabling history does not delete its history table.