@syxl/bun-sqlite

A lightweight, type-safe, Prisma-like ORM for Bun's native SQLite driver.

Zero external dependencies. Class-based schemas with decorators. Fully inferred return types. Built-in migrations CLI.

Why @syxl/bun-sqlite?

| Feature | This ORM | Raw bun:sqlite | Prisma | |---|---|---|---| | Type-safe queries | ✅ | ❌ | ✅ | | Zero dependencies | ✅ | ✅ | ❌ | | Class methods on results | ✅ | ✅ (manual) | ❌ | | Auto table sync | ✅ | ❌ | ❌ | | Migration CLI | ✅ | ❌ | ✅ | | Prisma-like API | ✅ | ❌ | ✅ | | Native Bun SQLite | ✅ | ✅ | ❌ |

Table of Contents

• Installation
• Quick Start
• Configuration
• Defining Schemas
  • @Table
  • @PrimaryKey
  • @Column
  • Type shorthand decorators
  • @Unique, @Index, @Default
  • Class methods & getters
• Database Initialization
• Creating Models
• Query API
  • findMany
  • findFirst
  • findUnique
  • create
  • createMany
  • update
  • updateMany
  • upsert
  • delete
  • deleteMany
  • count
  • raw
• Where Clause Operators
• CLI — Migrations
  • migrate
  • push
  • reset
  • status
• TypeScript Tips
• Full Example

Installation

# Install the package
bun add @syxl/bun-sqlite

# Or for a local/workspace project
bun install

Requirements: Bun v1.0+ · TypeScript 5.x

Quick Start

1. Enable decorators in tsconfig.json:

{
  "compilerOptions": {
    "experimentalDecorators": true
  }
}

2. Create a config file in your project root:

// bun-sqlite.config.ts
import type { BunSqliteConfig } from "@syxl/bun-sqlite";

export default {
  database: "app.db",
  strict: true,
  wal: true,
  foreignKeys: true,
  migrationsDir: "migrations",
  schemaDir: "src/schemas",
} satisfies BunSqliteConfig;

3. Define a schema:

// src/schemas/movie.ts
import { Table, Column, PrimaryKey } from "@syxl/bun-sqlite";

@Table("movies")
export class Movie {
  @PrimaryKey()
  id!: number;

  @Column()
  title!: string;

  @Column({ type: "INTEGER" })
  year!: number;

  @Column({ nullable: true })
  director?: string;

  // Getters and methods work on query results!
  get isMarvel() {
    return this.title.includes("Marvel");
  }

  get decade() {
    return `${Math.floor(this.year / 10) * 10}s`;
  }
}

4. Initialize and query:

// src/index.ts
import { initDatabaseSync, createModel } from "@syxl/bun-sqlite";
import { Movie } from "./schemas/movie";

// Tables are auto-created on init
initDatabaseSync();

const Movies = createModel(Movie);

const movie = Movies.create({ title: "Iron Man", year: 2008 });
console.log(movie.isMarvel); // true ✅

const recent = Movies.findMany({
  where: { year: { gte: 2020 } },
  orderBy: { year: "desc" },
});

Configuration

Create bun-sqlite.config.ts in your project root. All options are optional and have sensible defaults.

// bun-sqlite.config.ts
import type { BunSqliteConfig } from "@syxl/bun-sqlite";

export default {
  /** SQLite file path. Use ":memory:" for in-memory DB. Default: "sqlite.db" */
  database: "app.db",

  /** Require all named parameters to be bound exactly (no typo silencing). Default: true */
  strict: true,

  /** Open database in read-only mode. Default: false */
  readonly: false,

  /** Enable WAL journal mode (better concurrent reads). Default: true */
  wal: true,

  /** Enforce FOREIGN KEY constraints. Default: true */
  foreignKeys: true,

  /** Use BigInt for SQLite integers > Number.MAX_SAFE_INTEGER. Default: false */
  safeIntegers: false,

  /** Directory where migration .sql files are stored. Default: "migrations" */
  migrationsDir: "migrations",

  /** Directory the CLI scans for schema files. Default: "src/schemas" */
  schemaDir: "src/schemas",
} satisfies BunSqliteConfig;

Defining Schemas

Schemas are plain TypeScript classes decorated with metadata. The ORM uses Bun's native query.as(Class) to hydrate results — so getters, methods, and computed properties all work on every returned record.

@Table

Marks a class as a database table.

// Explicit table name
@Table("movies")
class Movie { ... }

// Auto-named: PascalCase → plural snake_case
// BlogPost → "blog_posts", User → "users"
@Table()
class BlogPost { ... }

@PrimaryKey

Marks a property as INTEGER PRIMARY KEY AUTOINCREMENT. Always use this for your id column.

@Table("users")
class User {
  @PrimaryKey()
  id!: number;
}

To disable auto-increment (e.g. for UUID primary keys stored as TEXT):

@PrimaryKey({ autoIncrement: false })
id!: string;

@Column

Marks a property as a database column. The SQLite type is inferred from common naming conventions, or you can specify it explicitly.

@Table("products")
class Product {
  @PrimaryKey()
  id!: number;

  // TEXT column (inferred)
  @Column()
  name!: string;

  // Explicit type
  @Column({ type: "INTEGER" })
  stock!: number;

  @Column({ type: "REAL" })
  price!: number;

  // Nullable column (allows NULL)
  @Column({ nullable: true })
  description?: string;

  // Unique constraint
  @Column({ unique: true })
  sku!: string;

  // With a default value
  @Column({ type: "INTEGER", default: 0 })
  views!: number;

  // Custom SQL column name (overrides snake_case conversion)
  @Column({ name: "created_at", default: "CURRENT_TIMESTAMP" })
  createdAt!: string;

  // Create an index on this column
  @Column({ index: true })
  category!: string;
}

Column options

| Option | Type | Description | |---|---|---| | type | "TEXT" \| "INTEGER" \| "REAL" \| "BLOB" | SQLite storage type | | nullable | boolean | Allow NULL. Default: false | | unique | boolean | Add UNIQUE constraint. Default: false | | default | string \| number \| boolean | SQL DEFAULT value/expression | | index | boolean | Create an index. Default: false | | name | string | Override the SQL column name |

SQLite type mapping

| TypeScript | SQLite | |---|---| | string | TEXT | | number | INTEGER (or REAL if specified) | | boolean | INTEGER (0 / 1) | | Uint8Array | BLOB | | bigint | INTEGER |

Type shorthand decorators

For convenience, you can use shorthand decorators instead of @Column({ type: "..." }):

import { Table, PrimaryKey, Text, Int, Real, Blob } from "@syxl/bun-sqlite";

@Table("posts")
class Post {
  @PrimaryKey()
  id!: number;

  @Text()
  title!: string;

  @Text({ nullable: true })
  body?: string;

  @Int({ default: 0 })
  views!: number;

  @Real({ nullable: true })
  rating?: number;

  @Blob({ nullable: true })
  thumbnail?: Uint8Array;
}

@Unique, @Index, @Default

These can be stacked with @Column for a declarative style:

import { Table, Column, PrimaryKey, Unique, Index, Default } from "@syxl/bun-sqlite";

@Table("users")
class User {
  @PrimaryKey()
  id!: number;

  @Column()
  @Unique()         // Adds UNIQUE constraint
  email!: string;

  @Column()
  @Index()          // Creates an index
  username!: string;

  @Column({ type: "INTEGER" })
  @Default(1)       // DEFAULT 1
  active!: number;

  @Column()
  @Default("CURRENT_TIMESTAMP")  // DEFAULT CURRENT_TIMESTAMP
  createdAt!: string;
}

Class methods & getters

This is the key advantage over other ORMs. Because Bun maps query results via Object.create(ClassName.prototype), the class prototype chain is preserved — so getters and methods work automatically on every result:

@Table("movies")
class Movie {
  @PrimaryKey() id!: number;
  @Column()     title!: string;
  @Column({ type: "INTEGER" }) year!: number;
  @Column({ nullable: true }) director?: string;

  // Computed getter — works on all results (findMany, findFirst, create, etc.)
  get isMarvel(): boolean {
    return this.title.includes("Marvel");
  }

  get decade(): string {
    return `${Math.floor(this.year / 10) * 10}s`;
  }

  get displayName(): string {
    return this.director ? `${this.title} (${this.director})` : this.title;
  }

  // Methods work too
  isSameDecade(other: Movie): boolean {
    return this.decade === other.decade;
  }
}

const movie = Movies.findFirst({ where: { title: "Iron Man" } })!;
console.log(movie.isMarvel);   // true
console.log(movie.decade);     // "2000s"
console.log(movie.displayName); // "Iron Man (Jon Favreau)"

Relations and Foreign Keys

@syxl/bun-sqlite supports fully-typed schema relations, cascading foreign keys, and nested eager loading (avoiding N+1 queries automatically).

Defining Relations

Define relations using @ForeignKey for the database constraint, and the @HasMany, @BelongsTo, or @HasOne decorators to describe the relationship.

import { Table, Column, PrimaryKey, ForeignKey, HasMany, BelongsTo, HasOne } from "@syxl/bun-sqlite";

@Table("users")
class User {
  @PrimaryKey() id!: number;
  @Column() name!: string;

  // A User can have many Posts
  @HasMany(() => Post, "userId")
  posts!: Post[];

  // A User can have one Profile
  @HasOne(() => Profile, "userId")
  profile!: Profile | null;
}

@Table("posts")
class Post {
  @PrimaryKey() id!: number;
  @Column() title!: string;

  // The actual foreign key column in the database
  @Column({ type: "INTEGER" })
  @ForeignKey(() => User, { onDelete: "CASCADE" })
  userId!: number;

  // The inverse relation back to User
  @BelongsTo(() => User, "userId")
  user!: User | null;
}

@Table("profiles")
class Profile {
  @PrimaryKey() id!: number;
  @Column() bio!: string;

  @Column({ type: "INTEGER", unique: true })
  @ForeignKey(() => User, { onDelete: "CASCADE" })
  userId!: number;

  @BelongsTo(() => User, "userId")
  user!: User | null;
}

Eager Loading (include)

When querying, use the include option to automatically fetch related records. The ORM batches the lookups in a single IN (...) query behind the scenes, effectively eliminating N+1 queries.

// Fetch all users and include their posts
const users = Users.findMany({
  include: { posts: true },
});
console.log(users[0].posts[0].title); // Fully typed!

// Fetch a single post and include its author (User)
const post = Posts.findUnique({
  where: { id: 1 },
  include: { user: true },
});
console.log(post?.user?.name);

// Nested eager loading
const postWithEverything = Posts.findFirst({
  include: {
    user: {
      profile: true, // You can nest inclusions infinitely
    },
  },
});
console.log(postWithEverything?.user?.profile?.bio);

Database Initialization

Call initDatabaseSync() or initDatabase() once at app startup, before any model queries. This:

1. Connects to the SQLite file
2. Applies pragmas (WAL, foreign keys, strict mode)
3. Auto-creates any registered tables that don't exist yet

import { initDatabaseSync, closeDatabase } from "@syxl/bun-sqlite";

// Synchronous (recommended for most use cases)
initDatabaseSync();

// Or with inline config overrides:
initDatabaseSync({
  database: ":memory:",  // In-memory for testing
  wal: false,
});

// Async version (if you prefer)
await initDatabase();

// Close the connection on shutdown
process.on("exit", () => closeDatabase());

In-memory database for testing

import { initDatabaseSync, closeDatabase } from "@syxl/bun-sqlite";
import { beforeEach, afterEach } from "bun:test";

beforeEach(() => {
  initDatabaseSync({ database: ":memory:", wal: false });
});

// afterEach(() => {
//   closeDatabase();
// });

Multiple Databases / Custom Registries

Currently, @syxl/bun-sqlite uses a global SchemaRegistry for decorators. If you need to connect to entirely separate SQLite databases with different schemas in the same process, be aware that all decorated classes are registered to the same global registry. It is generally recommended to keep schemas unified per process or utilize separate processes.

Creating Models

createModel(SchemaClass) returns a Model<T> instance with all query methods. Create it once and reuse.

import { createModel } from "@syxl/bun-sqlite";
import { Movie } from "./schemas/movie";
import { User } from "./schemas/user";

// Create once at module level — these are cheap, stateless wrappers
export const Movies = createModel(Movie);
export const Users = createModel(User);

Query API

findMany

Fetch multiple records. Returns T[].

// All records
const all = Movies.findMany();

// With filtering
const recent = Movies.findMany({
  where: { year: { gte: 2020 } },
});

// With ordering
const sorted = Movies.findMany({
  orderBy: { year: "desc" },
});

// Multiple order criteria
const multiSorted = Movies.findMany({
  orderBy: [{ year: "desc" }, { title: "asc" }],
});

// With pagination
const page2 = Movies.findMany({
  orderBy: { id: "asc" },
  limit: 10,
  offset: 10,
});

// Select specific fields — return type narrows to Pick<Movie, "title" | "year">
const titles = Movies.findMany({
  select: ["title", "year"],
});
// titles[0].title  ✅
// titles[0].year   ✅
// titles[0].id     ❌ TypeScript error

// Distinct results
const years = Movies.findMany({
  select: ["year"],
  distinct: true,
  orderBy: { year: "asc" },
});

findFirst

Fetch the first matching record or null.

const movie = Movies.findFirst({
  where: { title: "Iron Man" },
});

if (movie) {
  console.log(movie.isMarvel); // getter works!
}

// With ordering
const oldest = Movies.findFirst({
  orderBy: { year: "asc" },
});

findUnique

Fetch exactly one record by a unique field. Returns T | null.

const movie = Movies.findUnique({
  where: { id: 1 },
});

const user = Users.findUnique({
  where: { email: "[email protected]" },
});

create

Insert a new record and return the fully hydrated instance (with getters working).

const movie = Movies.create({
  title: "Iron Man",
  year: 2008,
  director: "Jon Favreau",
});

console.log(movie.id);       // auto-generated, e.g. 1
console.log(movie.isMarvel); // true — getter works!
console.log(movie.decade);   // "2000s"

Fields with @Default values and nullable fields are optional in the input.

createMany

Insert multiple records in a single transaction.

const movies = Movies.createMany([
  { title: "Iron Man", year: 2008 },
  { title: "Thor", year: 2011 },
  { title: "The Avengers", year: 2012 },
]);

console.log(movies.length); // 3
console.log(movies[0].isMarvel); // true

update

Update the first matching record and return it.

const updated = Movies.update({
  where: { id: 1 },
  data: { director: "Jon Favreau" },
});

// Returns null if no record matched
if (updated) {
  console.log(updated.director);
}

updateMany

Update all matching records. Returns { count: number }.

const result = Movies.updateMany({
  where: { year: { lt: 2000 } },
  data: { director: "Unknown" },
});

console.log(`Updated ${result.count} records`);

Update all records (omit where):

Movies.updateMany({ data: { director: "Unset" } });

upsert

Create a record if it doesn't exist, or update it if it does.

const movie = Movies.upsert({
  // Used to find the existing record
  where: { title: "Iron Man" },

  // Used if no record was found
  create: { title: "Iron Man", year: 2008 },

  // Used if a record was found
  update: { director: "Jon Favreau" },
});

delete

Delete the first matching record and return it.

const deleted = Movies.delete({
  where: { id: 1 },
});

// Returns null if nothing was deleted
if (deleted) {
  console.log(`Deleted: ${deleted.title}`);
}

deleteMany

Delete all matching records. Returns { count: number }.

const result = Movies.deleteMany({
  where: { year: { lt: 2000 } },
});

console.log(`Deleted ${result.count} records`);

// Delete all records
Movies.deleteMany();

count

Count records matching a condition.

const total = Movies.count();

const marvelCount = Movies.count({
  where: { title: { contains: "Marvel" } },
});

const recentCount = Movies.count({
  where: { year: { gte: 2020 } },
});

raw

Execute a raw SQL query and map results to the model class (preserving getters).

// Returns T[] with getters working
const topRated = Movies.raw(
  "SELECT * FROM movies WHERE year > ? ORDER BY year DESC LIMIT ?",
  [2010, 5]
);

// Returns T | null
const first = Movies.rawFirst(
  "SELECT * FROM movies WHERE title LIKE ?",
  ["%Man%"]
);

Warning: Always use parameterized queries (?) instead of string interpolation to prevent SQL injection vulnerabilities.

Where Clause Operators

The where option accepts a flexible filter object:

Movies.findMany({
  where: {
    // Direct equality
    year: 2012,

    // Operator objects
    year: { equals: 2012 },
    year: { not: 2012 },
    year: { in: [2008, 2011, 2012] },
    year: { notIn: [2008] },
    year: { gt: 2010 },
    year: { gte: 2010 },
    year: { lt: 2020 },
    year: { lte: 2020 },

    // String operators
    title: { contains: "Man" },      // LIKE '%Man%'
    title: { startsWith: "The" },    // LIKE 'The%'
    title: { endsWith: "Man" },      // LIKE '%Man'
    title: { like: "I_on%" },        // Raw LIKE pattern

    // Null checks
    director: null,                   // IS NULL
    director: { not: null },          // IS NOT NULL
  }
});

Logical operators (AND / OR / NOT)

// AND — all conditions must match
Movies.findMany({
  where: {
    AND: [
      { year: { gte: 2010 } },
      { title: { contains: "Man" } },
    ],
  },
});

// OR — at least one condition must match
Movies.findMany({
  where: {
    OR: [
      { year: 2008 },
      { year: 2012 },
    ],
  },
});

// NOT — invert a condition
Movies.findMany({
  where: {
    NOT: { year: 2008 },
  },
});

// Combine logical and field operators
Movies.findMany({
  where: {
    year: { gte: 2010 },
    OR: [
      { title: { contains: "Man" } },
      { director: "Joss Whedon" },
    ],
  },
});

CLI — Migrations

The CLI manages the full lifecycle of database schema migrations.

Setup

Make sure your bun-sqlite.config.ts has the correct schemaDir (where your decorated schema classes live) and migrationsDir (where .sql files are stored).

migrate

Compares your current schema classes against the last saved snapshot and generates a new .sql migration file if there are differences.

bunx bun-sqlite migrate

⬡ Loading schemas from src/schemas...
⬡ Discovered 3 schema file(s), 3 table(s)
⬡ Generating migration...
✔ Migration created: 20260627123456_migration.sql

Run this whenever you:

• Add a new table (new decorated class)
• Add a new column (@Column) to an existing table
• Add a new index (@Index)

Note: The current migration generator detects new tables and new columns. Renaming or dropping columns requires writing a migration manually.

push

Applies all pending migration files to the database in order.

bunx bun-sqlite push

⬡ Applying pending migrations...
✔ Applied: 20260627120000_migration.sql
✔ Applied: 20260627123456_migration.sql
✔ 2 migration(s) applied.

Applied migrations are tracked in the _bun_sqlite_migrations table so they won't be run twice.

reset

⚠️ Destructive. Drops all tables and replays every migration from the beginning. Use for a clean slate in development.

bunx bun-sqlite reset

⚠ Resetting database: app.db
⚠ This will DROP all tables and replay all migrations!
✔ Replayed: 20260627120000_migration.sql
✔ Replayed: 20260627123456_migration.sql
✔ Database reset complete. 2 migration(s) replayed.

status

Shows the current migration state and all registered schemas.

bunx bun-sqlite status

  bun-sqlite migration status
  ─────────────────────────────
  Database:   app.db
  Migrations: migrations
  Total:      3
  Applied:    2
  Pending:    1

  Pending migrations:
  → 20260627130000_migration.sql

  Registered schemas (3):
  → Movie   → movies    (4 columns)
  → User    → users     (5 columns)
  → BlogPost → blog_posts (6 columns)

Typical development workflow

# 1. Define or update your schema classes
# 2. Generate migration
bunx bun-sqlite migrate

# 3. Review the generated .sql file in migrations/
# 4. Apply to your dev database
bunx bun-sqlite push

# 5. Start your app — tables are already synced
bun run src/index.ts

TypeScript Tips

Type-safe select narrowing

When you use select, TypeScript narrows the return type automatically:

// type: Movie[]
const all = Movies.findMany();

// type: Pick<Movie, "title" | "year">[]
const partial = Movies.findMany({
  select: ["title", "year"],
});

partial[0].title  // ✅ string
partial[0].year   // ✅ number
partial[0].id     // ❌ TypeScript error — not in select

CreateInput omits getters

The create method input type automatically excludes getter-only properties (like isMarvel, decade) and the id primary key. Only actual writable column fields need to be provided:

Movies.create({
  title: "Iron Man",
  year: 2008,
  // id: omitted — auto-generated
  // isMarvel: omitted — it's a getter, not a column
  // decade: omitted — it's a getter, not a column
});

Partial inputs

All fields in create are optional at the TypeScript level — the ORM only inserts the fields you provide, letting SQLite apply DEFAULT values for the rest. Non-nullable columns without defaults will throw a SQLite error at runtime if omitted.

Full Example

// bun-sqlite.config.ts
import type { BunSqliteConfig } from "@syxl/bun-sqlite";

export default {
  database: "cinema.db",
  strict: true,
  wal: true,
  foreignKeys: true,
  migrationsDir: "migrations",
  schemaDir: "src/schemas",
} satisfies BunSqliteConfig;

// src/schemas/movie.ts
import { Table, Column, PrimaryKey, Unique, Index, Default } from "@syxl/bun-sqlite";

@Table("movies")
export class Movie {
  @PrimaryKey()
  id!: number;

  @Column()
  @Unique()
  title!: string;

  @Column({ type: "INTEGER" })
  year!: number;

  @Column({ nullable: true })
  director?: string;

  @Column({ type: "INTEGER" })
  @Default(0)
  views!: number;

  @Column()
  @Index()
  genre!: string;

  @Column()
  @Default("CURRENT_TIMESTAMP")
  createdAt!: string;

  get isMarvel() { return this.title.includes("Marvel"); }
  get decade()   { return `${Math.floor(this.year / 10) * 10}s`; }
  get age()      { return new Date().getFullYear() - this.year; }
}

// src/app.ts
import { initDatabaseSync, createModel } from "@syxl/bun-sqlite";
import { Movie } from "./schemas/movie";

// Initialize — creates the table automatically
initDatabaseSync();

const Movies = createModel(Movie);

// Seed some data
Movies.createMany([
  { title: "Iron Man",      year: 2008, director: "Jon Favreau",   genre: "Action" },
  { title: "Thor",          year: 2011, director: "Kenneth Branagh", genre: "Action" },
  { title: "The Avengers",  year: 2012, director: "Joss Whedon",   genre: "Action" },
  { title: "Black Panther", year: 2018, director: "Ryan Coogler",  genre: "Action" },
  { title: "Dune",          year: 2021, director: "Denis Villeneuve", genre: "Sci-Fi" },
]);

// Find with filters
const mcu = Movies.findMany({
  where: { genre: "Action" },
  orderBy: { year: "asc" },
});

mcu.forEach((m) => {
  console.log(`${m.title} (${m.decade}) — ${m.age} years old`);
});
// Iron Man (2000s) — 18 years old
// Thor (2010s) — 15 years old
// ...

// Find one
const avengers = Movies.findFirst({
  where: { title: { contains: "Avengers" } },
});
console.log(avengers?.isMarvel); // true

// Count
console.log(Movies.count({ where: { genre: "Action" } })); // 4

// Upsert
const dune = Movies.upsert({
  where: { title: "Dune" },
  create: { title: "Dune", year: 2021, genre: "Sci-Fi" },
  update: { director: "Denis Villeneuve" },
});

// Update many
Movies.updateMany({
  where: { genre: "Action", year: { lt: 2015 } },
  data: { views: 1000 },
});

// Delete old records
const removed = Movies.deleteMany({
  where: { year: { lt: 2000 } },
});
console.log(`Removed ${removed.count} records`);

// Raw SQL
const topViewed = Movies.raw(
  "SELECT * FROM movies ORDER BY views DESC LIMIT 3"
);

License

MIT