SQLite

The file is the database, so there is no connection URL, no credentials and no container. That makes @db-x/sqlite-library the fastest way to try DB-X — and the strictest about what a diff can express, because SQLite's ALTER TABLE is far narrower than Postgres'.

Runnable example: examples/sqlite.

Experimental

DB-X is an early prototype. Do not point it at a database you care about.

Connect

import { Column, Index, Sqlite, Table } from '@db-x/sqlite-library'

export default (
  <Sqlite name="todos-db" file="./todos.db">
    <Table name="todos">
      <Column name="id" type="integer" primaryKey />
      <Column name="title" type="text" notNull />
      <Column name="done" type="integer" notNull default="0" />
      <Index name="idx_todos_done" columns={['done']} />
    </Table>
  </Sqlite>
)

apply shells out to sqlite3, so it needs sqlite3 on your PATH. preview renders and diffs offline — it does not touch the file.

Components

Component	What it manages
<Sqlite file>	The database file. No user/database split — the path is the whole connection.
<Table>	The table, with <Column> and <Index> children.
<Column>	Name, type, notNull, unique, default, and from for renames.
<Index>	A named index over one or more columns, optionally unique.
<SeedData>	Raw idempotent SQL.

Friendly type names are resolved to SQLite storage classes, so serial, boolean, uuid and timestamptz are accepted and stored as INTEGER or TEXT. serial + primaryKey becomes INTEGER PRIMARY KEY AUTOINCREMENT.

What the diff cannot do

SQLite has no ALTER COLUMN at all. Changing a column's type, default or nullability needs a create-copy-drop-rename table rebuild, which is not implemented yet (#56). Rather than emit SQL that cannot run, the plan fails with an explicit message:

SQLite can't ALTER COLUMN "title" on table "todos" (type change);
needs a table rebuild — not supported yet.

Adding, renaming and dropping columns all work, as do index changes. Two further refusals exist because SQLite itself would reject the statement mid-apply — a DROP COLUMN is refused at plan time when the column is a primary key, carries a UNIQUE constraint, or is still covered by a declared index. Dropping that index in the same edit is fine: the DROP INDEX is emitted first.

default is raw SQL, so a literal string carries its own quotes — default="'blue'", not default="blue". The unquoted form is rejected at plan time with the exact replacement in the message, because SQLite would otherwise accept it and then fail with "default value of column is not constant".

Safety

Destructive changes need --allow-destructive. Before one runs, DB-X copies the database file into .dbx/snapshots via sqlite3 .backup — a full copy every time, structure and rows, since SQLite has no schema-only equivalent.

sqlite3 todos.db "select * from todos;"   # inspect at any point
pnpm apply --yes --allow-destructive      # snapshots first, then applies
pnpm restore --yes                        # roll the file back

Drift

db-x refresh reads PRAGMA table_info, PRAGMA index_list and PRAGMA index_info through sqlite3 -readonly. The read-only flag is load-bearing: plain sqlite3 missing.db creates an empty database, so a drift check without it would quietly manufacture the very file it was asked to look for.

Deleting the .db file is therefore reported honestly as "the table is gone", and preview plans a fresh CREATE TABLE rather than an ALTER against nothing.

Repairable drift is written into state. A column added or dropped by hand, or an index rebuilt over different columns, becomes a change preview plans and apply runs.

Unrepairable drift is reported, not written. A column whose type, default, nullability or primary key moved out of band cannot be fixed without a table rebuild (#56), so refresh names it and leaves the recorded spec alone:

[table:todos] WARN todos.done: type is TEXT, expected INTEGER — needs a table rebuild (#56)
[table:todos] WARN todos.color: default is 'red', expected 'blue' — needs a table rebuild (#56)
~ table:todos   @db-x/sqlite-library:table   drift: columnDrift

Folding that into the recorded columns would make the next diff hit the ALTER COLUMN refusal above and take preview down for the whole deployment, over a change no JSX edit can fix. Repair the table by hand and the next refresh clears the warning.

The comparison is normalised against the SQL this library writes, so an in-sync database stays quiet: serial resolves to INTEGER, a primary key suppresses NOT NULL, and DEFAULT (datetime('now')) reads back as datetime('now'). unique is the one attribute not compared — SQLite keeps it in an sqlite_autoindex_* entry rather than in table_info.

Next

• PostgreSQL — the same schema shape with full ALTER support.
• Examples — every runnable example in the repo.