SQL Builder

Kyrin comes with an SQL Builder that works similar to knex.js for building chainable SQL queries, and Auto Migration for managing database schema.

Quick Start

import { Kyrin } from "kyrin";

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

// Define schema - tables will be created automatically
app.schema({
  users: {
    id: { type: "integer", primary: true },
    name: { type: "string" },
    email: { type: "string", notNull: true },
  },
});

// Use SQL Builder
app.get("/users", async () => {
  const users = await app.db().from("users").all();
  return { users };
});

app.listen(3000);

---

SQL Builder

Basic CRUD

const db = app.db();

// SELECT all
const users = await db.from("users").all();

// SELECT with columns
const names = await db.from("users").select("id", "name").all();

// SELECT first row
const user = await db.from("users").where("id", 1).first();

Where Conditions

// WHERE column = value
await db.from("users").where("id", 1).first();

// WHERE column with operator
await db.from("users").where("age", ">", 18).all();

// WHERE IN
await db.from("users").whereIn("status", ["active", "pending"]).all();

// WHERE NULL
await db.from("users").whereNull("deleted_at").all();

// WHERE LIKE
await db.from("users").whereLike("name", "%john%").all();

Insert, Update, Delete

// INSERT
await db.from("users").insert({ name: "John", email: "john@test.com" }).run();

// UPDATE
await db.from("users").where("id", 1).update({ name: "Jane" }).run();

// DELETE
await db.from("users").where("id", 1).delete().run();

Pagination

// LIMIT
await db.from("users").limit(10).all();

// OFFSET
await db.from("users").offset(10).all();

// Page (page number, per page)
await db.from("users").page(1, 10).all();

Order By

await db.from("users").orderBy("name").all();
await db.from("users").orderByDesc("created_at").all();

Joins

await db.from("users")
  .join("posts", "users.id", "posts.user_id")
  .all();

Utility Methods

// Count
const count = await db.from("users").count();

// Total (alias for count)
const total = await db.from("users").total();

// Exists
const exists = await db.from("users").where("id", 1).exists();

// Get SQL string
const sql = db.from("users").where("age", ">", 18).toSQL();

---

Auto Migration

Schema Definition

Use .schema() to define schema - tables will be created automatically

app.schema({
  users: {
    id: { type: "integer", primary: true },
    name: { type: "string" },
    email: { type: "string", notNull: true },
  },
  posts: {
    id: { type: "integer", primary: true },
    user_id: { type: "integer" },
    title: { type: "string", notNull: true },
    content: { type: "string", nullable: true },
  },
});

Field Definition Options

Option	Type	Description
type	"string" | "integer" | "real" | "bool" | "date"	SQL type
primary	boolean	Set as primary key (auto-detect if name is "id")
notNull	boolean	NOT NULL (default: true)
nullable	boolean	Allow NULL
default	any	Default value

Type Mapping

String Type	SQL Type
"string"	TEXT
"integer"	INTEGER
"real"	REAL
"bool"	INTEGER (0/1)
"date"	TEXT

Example

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

app.schema({
  users: {
    id: { type: "integer", primary: true },
    name: { type: "string" },
    email: { type: "string", notNull: true },
    age: { type: "integer", nullable: true },
  },
  posts: {
    id: { type: "integer", primary: true },
    user_id: { type: "integer" },
    title: { type: "string", notNull: true },
    content: { type: "string", nullable: true },
    published: { type: "bool", default: false },
  },
});

---

Database Configuration

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

// Or use connection string
const app2 = new Kryin({
  database: "sqlite:./data.db",
});

Supported Databases

• SQLite: sqlite:./data.db or sqlite::memory:
• PostgreSQL: postgres://user:pass@localhost:5432/dbname
• MySQL: mysql://user:pass@localhost:3306/dbname

---

API Reference

QueryBuilder Methods

Table

• from(table) - Set table name
• table(table) - Alias for from()

Select

• select(...columns) - Select columns
• selectAll() - Select all (*)

Where

• where(column, value) - Where equals
• where(column, operator, value) - Where with operator
• whereId(id) - Where id = value
• whereIn(column, values) - Where IN
• whereNull(column) - Where IS NULL
• whereNotNull(column) - Where IS NOT NULL
• whereLike(column, pattern) - Where LIKE
• whereBetween(column, range) - Where BETWEEN
• whereRaw(sql, ...params) - Raw WHERE
• orWhere(column, value) - OR where

Join

• join(table, on1, on2) - INNER JOIN
• leftJoin(table, on1, on2) - LEFT JOIN
• rightJoin(table, on1, on2) - RIGHT JOIN
• crossJoin(table) - CROSS JOIN

Order & Pagination

• orderBy(column, direction?) - ORDER BY
• orderByDesc(column) - ORDER BY DESC
• limit(n) - LIMIT
• offset(n) - OFFSET
• page(page, perPage) - Pagination

Group

• groupBy(...columns) - GROUP BY
• having(column, value) - HAVING

Insert/Update/Delete

• insert(data) - INSERT
• create(data) - INSERT (alias)
• update(data) - UPDATE
• delete() - DELETE
• softDelete(column?) - Soft delete

Execute

• all() - Get all rows
• first() - Get first row
• run() - Execute and return result
• count() - Count rows
• total() - Total (alias)
• exists() - Check if exists
• toSQL() - Get SQL string
• getParams() - Get parameters