Database

Kyrin includes a built-in database module with a clean, type-safe API. Supports SQLite, MySQL, and PostgreSQL.

Getting Started

Choose your database:

typescript

import { Database } from "kyrin";

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

// MySQL
const mysql = new Database({
  type: "mysql",
  host: "localhost",
  database: "myapp",
  username: "root",
  password: "pass",
});

// PostgreSQL
const postgres = new Database({
  type: "postgres",
  host: "localhost",
  database: "myapp",
  username: "postgres",
  password: "pass",
});

Or use a connection string:

typescript

import { database } from "kyrin";

const sqlite = database("sqlite:./app.db");
const mysql = database("mysql://user:pass@localhost:3306/myapp");
const postgres = database("postgres://user:pass@localhost:5432/myapp");

SQLite

Configuration

typescript

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

Option	Type	Default	Description
`filename`	string	`:memory:`	Path to database file
`readonly`	boolean	`false`	Read-only mode
`create`	boolean	`true`	Create file if not exists
`wal`	boolean	`true`	Enable WAL mode

Connection String

typescript

database("sqlite:./app.db")
database("sqlite::memory:")

Query Example

typescript

const users = db.sql`SELECT * FROM users`.all();
const user = db.sql`SELECT * FROM users WHERE id = ${id}`.first();
db.sql`INSERT INTO users (name) VALUES (${name})`.run();

MySQL

Configuration

typescript

const db = new Database({
  type: "mysql",
  host: "localhost",
  port: 3306,
  database: "myapp",
  username: "root",
  password: "pass",
  ssl: false,
});

Option	Type	Default	Description
`host`	string	`localhost`	Database host
`port`	number	`3306`	Database port
`database`	string	`mysql`	Database name
`username`	string	`root`	Database user
`password`	string	``	Database password
`ssl`	boolean	`false`	Enable SSL connection

Connection String

typescript

database("mysql://user:pass@localhost:3306/myapp")
database("mysql://user:pass@localhost:3306/myapp?ssl=true")

Query Example

MySQL queries are async:

typescript

const users = await db.sql`SELECT * FROM users`.all();
const user = await db.sql`SELECT * FROM users WHERE id = ${id}`.first();
await db.sql`INSERT INTO users (name) VALUES (${name})`.run();

PostgreSQL

Configuration

typescript

const db = new Database({
  type: "postgres",
  host: "localhost",
  port: 5432,
  database: "myapp",
  username: "postgres",
  password: "pass",
  ssl: false,
});

Option	Type	Default	Description
`host`	string	`localhost`	Database host
`port`	number	`5432`	Database port
`database`	string	`postgres`	Database name
`username`	string	`postgres`	Database user
`password`	string	``	Database password
`ssl`	boolean	`false`	Enable SSL connection

Connection String

typescript

database("postgres://user:pass@localhost:5432/myapp")
database("postgresql://user:pass@localhost:5432/myapp?ssl=true")

Query Example

PostgreSQL queries are async:

typescript

const users = await db.sql`SELECT * FROM users`.all();
const user = await db.sql`SELECT * FROM users WHERE id = ${id}`.first();
await db.sql`INSERT INTO users (name) VALUES (${name})`.run();

Query API

All databases share the same query API. Parameters are automatically escaped — no SQL injection.

sql Tagged Template

typescript

const users = db.sql`SELECT * FROM users`.all();
const user = db.sql`SELECT * FROM users WHERE id = ${id}`.first();
db.sql`INSERT INTO users (name, email) VALUES (${name}, ${email})`.run();
db.sql`UPDATE users SET name = ${name} WHERE id = ${id}`.run();
db.sql`DELETE FROM users WHERE id = ${id}`.run();

Query Methods

Method	Description	Returns
`.all()`	Get all matching rows	`T[]`
`.first()`	Get first row	`T
`.run()`	Execute DML	`{ changes, lastInsertRowid }`

Parameter Placeholders

Database	Placeholder
SQLite	`?`
MySQL	`?`
PostgreSQL	`$1`, `$2`, ...

typescript

// SQLite & MySQL
db.query("SELECT * FROM users WHERE age > ?", [18]);
db.queryOne("SELECT * FROM users WHERE id = ?", [1]);
db.run("INSERT INTO users (name) VALUES (?)", ["Alice"]);

// PostgreSQL
db.query("SELECT * FROM users WHERE age > $1", [18]);
db.queryOne("SELECT * FROM users WHERE id = $1", [1]);
db.run("INSERT INTO users (name) VALUES ($1)", ["Alice"]);

Traditional Methods

typescript

const users = db.query("SELECT * FROM users");
const user = db.queryOne("SELECT * FROM users WHERE id = ?", [1]);
const result = db.run("INSERT INTO users (name) VALUES (?)", ["Alice"]);
db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY)");

Type Safety

Add type parameters for better inference:

typescript

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

Transactions

Wrap multiple operations in a transaction:

typescript

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

// MySQL/PostgreSQL (async)
await db.transaction(async () => {
  await db.sql`INSERT INTO users (name) VALUES (${"Alice"})`.run();
  await db.sql`INSERT INTO users (name) VALUES (${"Bob"})`.run();
});

Auto rollback on error:

typescript

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

Prepared Statements

For repeated queries:

typescript

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

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

await findUser.finalize();

Schema Creation

Use exec() for DDL statements:

typescript

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
  )
`);

WAL Mode

SQLite specific — WAL (Write-Ahead Logging) is enabled by default for better performance:

typescript

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

Disable if needed:

typescript

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

Closing the Database

typescript

db.close();
// With async for MySQL/PostgreSQL:
await db.close();

Or handle with process events:

typescript

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

Complete Example

typescript

import { Kyrin, Database } from "kyrin";

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

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

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

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)
  )
`);

const app = new Kyrin();

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

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;
});

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 };
});

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 };
});

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

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

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 };
});

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 };
});

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

app.listen(3000);

Database ​

Getting Started ​

SQLite ​

Configuration ​

Connection String ​

Query Example ​

MySQL ​

Configuration ​

Connection String ​

Query Example ​

PostgreSQL ​

Configuration ​

Connection String ​

Query Example ​

Query API ​

sql Tagged Template ​

Query Methods ​

Parameter Placeholders ​

Traditional Methods ​

Type Safety ​

Transactions ​

Prepared Statements ​

Schema Creation ​

WAL Mode ​

Closing the Database ​

Complete Example ​

Database

Getting Started

SQLite

Configuration

Connection String

Query Example

MySQL

Configuration

Connection String

Query Example

PostgreSQL

Configuration

Connection String

Query Example

Query API

sql Tagged Template

Query Methods

Parameter Placeholders

Traditional Methods

Type Safety

Transactions

Prepared Statements

Schema Creation

WAL Mode

Closing the Database

Complete Example