SQLite Driver

SQLite migration driver with automatic table recreation for limited ALTER TABLE support

Capabilities

Type Mappings

Auto-Increment

SQLite uses INTEGER PRIMARY KEY for auto-increment:

const User = model("users", {
  id: int().primaryKey().autoIncrement(),
});

Generates:

CREATE TABLE "users" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT
);

Boolean Handling

SQLite stores booleans as integers:

const User = model("users", {
  isActive: boolean().default(true),
});

Generates:

CREATE TABLE "users" (
  "isActive" INTEGER NOT NULL DEFAULT 1
);

Values: 1 for true, 0 for false.

Enum Handling

SQLite doesn’t have native enums, so VibORM uses TEXT columns with CHECK constraints for validation:

const User = model("users", {
  status: enumScalar(["active", "inactive", "pending"]),
});

Generates:

CREATE TABLE "users" (
  "status" TEXT CHECK("status" IN ('active', 'inactive', 'pending')) NOT NULL
);

Enum Operations

Adding enum values: Requires table recreation to update the CHECK constraint.

Removing enum values: Requires table recreation to update the CHECK constraint. You must also handle existing data with the removed values.

Dropping an enum: Converts the column to plain TEXT (removes CHECK constraint) via table recreation.

Array Handling

Arrays are stored as JSON:

const User = model("users", {
  tags: string().array(),
  scores: int().array(),
});

Generates:

CREATE TABLE "users" (
  "tags" JSON NOT NULL,
  "scores" JSON NOT NULL
);

Values are JSON-encoded: ["tag1", "tag2"] or [100, 200, 300].

Supported Direct Operations

These operations work directly in SQLite:

Table Recreation

For operations SQLite doesn’t support natively, VibORM uses table recreation:

Operations Requiring Recreation

• Alter column (type, nullability, default changes)
• Add/drop foreign key
• Add/drop primary key
• Alter/drop enum (updating CHECK constraints)

These operations rewrite the whole table (create a new table, copy the data across, drop the old one, rename, recreate indexes) — slow on large tables. Renamed columns are mapped to their new names during the copy, new columns get their defaults, and dropped columns are excluded.

DDL Examples

Create Table with Enum

CREATE TABLE "users" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "email" TEXT NOT NULL,
  "role" TEXT CHECK("role" IN ('admin', 'user', 'guest')) NOT NULL,
  "tags" JSON,
  "created_at" TEXT DEFAULT (datetime('now'))
);

Add Column

ALTER TABLE "users" ADD COLUMN "bio" TEXT;

Rename Column

ALTER TABLE "users" RENAME COLUMN "name" TO "full_name";

Drop Column

ALTER TABLE "users" DROP COLUMN "bio";

Limitations

Querying JSON Arrays

Querying JSON arrays requires SQLite’s JSON functions:

-- Check if array contains value
SELECT users.*
FROM users, json_each(users.tags)
WHERE json_each.value = 'admin';

LibSQL / Turso

The SQLite driver also works with LibSQL (Turso). The same limitations and features apply.

Next Steps