Database

Kyrin includes a built-in database module with a clean, type-safe API. Currently supports SQLite via Bun's native driver.

Getting Started

import { Database } from "kyrin";

const db = new Database({
  type: "sqlite",
  filename: "./app.db", // or ":memory:" for in-memory
});

Or use a connection string:

import { database } from "kyrin";

const db = database("sqlite:./app.db");
const memoryDb = database("sqlite::memory:");

Configuration Options

SQLite config:

const db = new Database({
  type: "sqlite",
  filename: "./app.db", // Path to database file (default: ":memory:")
  readonly: false, // Read-only mode (default: false)
  create: true, // Create file if not exists (default: true)
  wal: true, // Enable WAL mode for performance (default: true)
});

The sql Tagged Template

The easiest way to query is using the sql tagged template:

// Select all
const users = db.sql`SELECT * FROM users`.all();

// Select with parameter
const user = db.sql`SELECT * FROM users WHERE id = ${id}`.first();

// Insert
db.sql`INSERT INTO users (name, email) VALUES (${name}, ${email})`.run();

// Update
db.sql`UPDATE users SET name = ${name} WHERE id = ${id}`.run();

// Delete
db.sql`DELETE FROM users WHERE id = ${id}`.run();

Parameters are automatically escaped — no SQL injection worries.

Query Methods

.all() — Get Multiple Rows

Returns an array of all matching rows:

const users = db.sql`SELECT * FROM users`.all();
// [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }]

const active = db.sql`SELECT * FROM users WHERE active = ${true}`.all();

.first() — Get Single Row

Returns the first matching row, or null:

const user = db.sql`SELECT * FROM users WHERE id = ${1}`.first();
// { id: 1, name: "Alice" } or null

.run() — Execute Statement

For INSERT, UPDATE, DELETE. Returns affected rows and last insert ID:

const result = db.sql`INSERT INTO users (name) VALUES (${"Alice"})`.run();
// { changes: 1, lastInsertRowid: 1 }

const updated = db.sql`UPDATE users SET active = ${true} WHERE id = ${1}`.run();
// { changes: 1, lastInsertRowid: 1 }

Type Safety

Add type parameters for better inference:

interface User {
  id: number;
  name: string;
  email: string;
}

const users = db.sql<User>`SELECT * FROM users`.all();
// users: User[]

const user = db.sql<User>`SELECT * FROM users WHERE id = ${id}`.first();
// user: User | null

Alternative Query Methods

If you prefer traditional method calls:

// query() — returns array
const users = db.query<User>("SELECT * FROM users WHERE age > ?", [18]);

// queryOne() — returns single row or null
const user = db.queryOne<User>("SELECT * FROM users WHERE id = ?", [1]);

// run() — for INSERT/UPDATE/DELETE
const result = db.run("INSERT INTO users (name) VALUES (?)", ["Alice"]);

// exec() — for DDL (no parameters)
db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");

Schema Creation

Use exec() for DDL statements:

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
`);

Transactions

Wrap multiple operations in a transaction:

db.transaction(() => {
  db.sql`INSERT INTO users (name) VALUES (${"Alice"})`.run();
  db.sql`INSERT INTO users (name) VALUES (${"Bob"})`.run();
  db.sql`INSERT INTO users (name) VALUES (${"Charlie"})`.run();
});

Transactions automatically rollback on error:

try {
  db.transaction(() => {
    db.sql`INSERT INTO users (name) VALUES (${"Alice"})`.run();
    throw new Error("Something went wrong");
    db.sql`INSERT INTO users (name) VALUES (${"Bob"})`.run(); // Never runs
  });
} catch (err) {
  // Alice was not inserted — transaction rolled back
}

Prepared Statements

For repeated queries, prepare once and reuse:

const findUser = db.prepare<User>("SELECT * FROM users WHERE id = ?");

const user1 = findUser.get(1);
const user2 = findUser.get(2);
const user3 = findUser.get(3);

// Clean up when done
findUser.finalize();

Prepared statement methods:

const stmt = db.prepare<User>("SELECT * FROM users WHERE active = ?");

stmt.all(true); // Get all matching rows
stmt.get(true); // Get first row
stmt.run(true); // Execute (for INSERT/UPDATE/DELETE)
stmt.finalize(); // Release resources

Closing the Database

Always close the database when shutting down:

// In your cleanup code
db.close();

Or handle it with process events:

process.on("SIGINT", () => {
  db.close();
  process.exit(0);
});

WAL Mode

By default, Kyrin enables SQLite's WAL (Write-Ahead Logging) mode for better performance. This allows concurrent reads while writing and improves write throughput.

Disable it if needed:

const db = new Database({
  type: "sqlite",
  filename: "./app.db",
  wal: false,
});

Future Support

PostgreSQL and MySQL support are planned:

// Coming soon
const pgDb = new Database({
  type: "postgres",
  host: "localhost",
  port: 5432,
  database: "myapp",
  username: "user",
  password: "pass",
});

const mysqlDb = database("mysql://user:pass@localhost:3306/myapp");

---

Complete Example

import { Kyrin, Database } from "kyrin";

// Database setup
const db = new Database({
  type: "sqlite",
  filename: "./app.db",
});

// Create tables
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
`);

// Types
interface User {
  id: number;
  name: string;
  email: string;
  created_at: string;
}

interface Post {
  id: number;
  user_id: number;
  title: string;
  content: string | null;
}

// App
const app = new Kyrin();

// GET /users
app.get("/users", () => {
  return db.sql<User>`SELECT * FROM users`.all();
});

// GET /users/:id
app.get("/users/:id", (c) => {
  const id = c.param("id");
  const user = db.sql<User>`SELECT * FROM users WHERE id = ${id}`.first();

  if (!user) {
    return c.notFound();
  }

  return user;
});

// POST /users
app.post("/users", async (c) => {
  const { name, email } = await c.body<{ name: string; email: string }>();

  const result = db.sql`
    INSERT INTO users (name, email) VALUES (${name}, ${email})
  `.run();

  c.set.status = 201;
  return { id: result.lastInsertRowid, name, email };
});

// PUT /users/:id
app.put("/users/:id", async (c) => {
  const id = c.param("id");
  const { name, email } = await c.body<{ name: string; email: string }>();

  const result = db.sql`
    UPDATE users SET name = ${name}, email = ${email} WHERE id = ${id}
  `.run();

  if (result.changes === 0) {
    return c.notFound();
  }

  return { id, name, email };
});

// DELETE /users/:id
app.delete("/users/:id", (c) => {
  const id = c.param("id");
  const result = db.sql`DELETE FROM users WHERE id = ${id}`.run();

  if (result.changes === 0) {
    return c.notFound();
  }

  return null; // 204 No Content
});

// GET /users/:id/posts
app.get("/users/:id/posts", (c) => {
  const userId = c.param("id");
  return db.sql<Post>`SELECT * FROM posts WHERE user_id = ${userId}`.all();
});

// POST /users/:id/posts
app.post("/users/:id/posts", async (c) => {
  const userId = c.param("id");
  const { title, content } = await c.body<{
    title: string;
    content?: string;
  }>();

  const result = db.sql`
    INSERT INTO posts (user_id, title, content) VALUES (${userId}, ${title}, ${
    content ?? null
  })
  `.run();

  c.set.status = 201;
  return { id: result.lastInsertRowid, user_id: userId, title, content };
});

// Transaction example
app.post("/batch-users", async (c) => {
  const { users } = await c.body<{
    users: { name: string; email: string }[];
  }>();

  const ids = db.transaction(() => {
    return users.map(({ name, email }) => {
      const result =
        db.sql`INSERT INTO users (name, email) VALUES (${name}, ${email})`.run();
      return result.lastInsertRowid;
    });
  });

  c.set.status = 201;
  return { created: ids };
});

// Cleanup on shutdown
process.on("SIGINT", () => {
  db.close();
  process.exit(0);
});

app.listen(3000);