@lobomfz/db

SQLite database with Arktype schemas and typed Kysely client for Bun.

Install

bun add @lobomfz/db

Usage

import { Database, generated, type } from "@lobomfz/db";

const db = await Database.open({
	path: "data.db",
	schema: {
		tables: {
			users: type({
				id: generated("autoincrement"),
				name: "string",
				email: type("string").configure({ unique: true }),
				"bio?": "string", // optional → nullable in SQLite
				active: type("boolean").default(true),
				created_at: generated("now"), // defaults to current time
			}),
			posts: type({
				id: generated("autoincrement"),
				user_id: type("number.integer").configure({ references: "users.id", onDelete: "cascade" }),
				title: "string",
				published_at: "Date", // native Date support
				tags: "string[]", // JSON columns just work
				metadata: type({ source: "string", "priority?": "number" }), // validated on write by default
				status: type.enumerated("draft", "published").default("draft"),
			}),
		},
		indexes: {
			posts: [{ columns: ["user_id", "status"] }, { columns: ["title"], unique: true }],
		},
	},
	pragmas: {
		journal_mode: "wal",
		synchronous: "normal",
	},
});

// Fully typed Kysely client — generated/default fields are optional on insert
await db.kysely.insertInto("users").values({ name: "John", email: "[email protected]" }).execute();

const users = await db.kysely.selectFrom("users").selectAll().execute();
// users[0].active     → true
// users[0].created_at → Date

Booleans, dates, objects, arrays — everything round-trips as the type you declared. The schema is the source of truth for table creation, TypeScript types, and runtime coercion.

API

generated("autoincrement"); // auto-incrementing primary key
generated("now"); // defaults to current timestamp, returned as Date
type("string").default("pending"); // SQL DEFAULT
type("string").configure({ unique: true }); // UNIQUE
type("number.integer").configure({ references: "users.id", onDelete: "cascade" }); // FK

JSON columns are validated against the schema on write by default. To also validate on read:

await Database.open({
	// ...
	validation: { onRead: true }, // default: { onRead: false }
});

Migrations

Schema changes are applied automatically on startup. Database.open(...) is async because migrations run before it resolves: the library compares your Arktype schema against the actual SQLite database and applies the minimum set of operations to bring them in sync. No migration files, no version tracking — the database itself is the source of truth.

What's supported

| Change | Strategy | | -------------------------------- | ------------------------ | | New table | CREATE TABLE | | Removed table | DROP TABLE | | New nullable column | ALTER TABLE ADD COLUMN | | New NOT NULL column with DEFAULT | ALTER TABLE ADD COLUMN | | Removed column | Table rebuild | | Type change | Table rebuild | | Nullability change | Table rebuild | | DEFAULT change | Table rebuild | | UNIQUE added/removed | Table rebuild | | FK added/removed/changed | Table rebuild | | Index added | CREATE INDEX | | Index removed | DROP INDEX |

Table rebuilds follow SQLite's recommended procedure: create a new table with the target schema, copy data from the old table, drop the old table, rename the new one. Foreign keys are disabled during rebuilds and validated via PRAGMA foreign_key_check before committing.

Safety rules

• Adding a NOT NULL column without DEFAULT to a table with data throws an error
• Changing a nullable column to NOT NULL without DEFAULT throws if any row has NULL in that column
• Nullable-to-NOT-NULL with DEFAULT uses COALESCE to fill existing NULLs
• Without a guided migration, column renames are treated as drop + add (data in the old column is not preserved)

Refusing destructive changes

By default the diff applies whatever brings the database in sync, including drops. Set failOnDataLoss to turn dropping a table, dropping a column, or a data-discarding type change into a thrown DataLossError instead — the database is left untouched.

await Database.open({
	// ...
	failOnDataLoss: true,
});

Configuring a migration (see below) routes through the guided path instead, where the drops are intrinsic to the restructure. The gate does not apply there; the drops are logged via console.warn so they are still visible.

Guided data migrations

The automatic diff can rename or restructure a table only by dropping the old shape. When you need to preserve and transform the data — rename a column, split one column into two, convert a type — pass a migration with a stable id and a run function. It receives the same typed Kysely client and runs inside the migration transaction.

await Database.open({
	path: "data.db",
	schema: {
		tables: {
			users: type({ id: generated("autoincrement"), name: "string", email: "string" }),
		},
	},
	migration: {
		id: "rename-user-fields",
		run: async ({ db }) => {
			const rows = await db
				.selectFrom("users")
				.select(["id", sql<string>`full_name`.as("full_name")])
				.execute();

			for (const row of rows) {
				await db.updateTable("users").set({ name: row.full_name }).where("id", "=", row.id).execute();
			}
		},
	},
});

The migration runs as expand then contract, all in one transaction:

1. New tables and new columns are added (new columns nullable, so the backfill can write them).
2. Your run backfills and transforms the data, reading old columns with raw sql and writing the new ones through the typed client.
3. The schema is reconciled to the target: leftover columns and tables are dropped (logged via console.warn) and NOT NULL is tightened.

run already executes inside the migration transaction, so do not open your own with db.transaction(...). Your reads and writes are already atomic with the rest of the migration; if run throws, the whole transaction rolls back and the database is left exactly as it was. Referential integrity is checked with PRAGMA foreign_key_check before the commit, even when foreign_keys is disabled.

Each id runs at most once. The applied set lives in a __migrations table; a fresh database records the id as a baseline without running it (the schema is created directly). Editing the body of an already-applied migration throws MigrationDriftError — migrations are immutable once applied, so give new logic a fresh id.

License

MIT