Database

Complete guide for database operations in Laju framework.

Overview

Laju provides two database services:

Service	Use Case	Performance
DB (Kysely)	Complex queries, migrations	Standard
SQLite (Native)	Simple reads, performance-critical	2-4x faster

Both use BetterSQLite3 with WAL mode enabled by default.

DB Service (Kysely)

Kysely is a type-safe SQL query builder for TypeScript. It provides unparalleled autocompletion and compile-time type safety for complex queries.

Basic Queries

import DB from "app/services/DB";

// SELECT all
const users = await DB.selectFrom("users").selectAll().execute();

// SELECT with WHERE
const user = await DB.selectFrom("users")
  .selectAll()
  .where("email", "=", email)
  .executeTakeFirst();

const activeUsers = await DB.selectFrom("users")
  .selectAll()
  .where("is_active", "=", true)
  .execute();

// SELECT with multiple conditions
const results = await DB.selectFrom("posts")
  .selectAll()
  .where("status", "=", "published")
  .where("views", ">", 1000)
  .orderBy("created_at", "desc")
  .limit(10)
  .execute();

INSERT

// Basic insert
await DB.insertInto("posts")
  .values({
    title: "Hello World",
    content: "Post content",
    created_at: Date.now(),
    updated_at: Date.now()
  })
  .execute();

// Insert and get result
const result = await DB.insertInto("users")
  .values({
    name: "John Doe",
    email: "john@example.com"
  })
  .executeTakeFirst();
console.log(result.insertId); // Inserted row ID

// Batch insert
await DB.insertInto("logs").values([
  { message: "Log 1", created_at: Date.now() },
  { message: "Log 2", created_at: Date.now() },
  { message: "Log 3", created_at: Date.now() }
]).execute();

UPDATE

await DB.updateTable("users")
  .set({
    name: "New Name",
    updated_at: Date.now()
  })
  .where("id", "=", userId)
  .execute();

// Update multiple rows
await DB.updateTable("posts")
  .set({ status: "archived" })
  .where("status", "=", "draft")
  .execute();

DELETE

await DB.deleteFrom("sessions")
  .where("id", "=", sessionId)
  .execute();

// Delete with multiple conditions
await DB.deleteFrom("tokens")
  .where("expires_at", "<", new Date().toISOString())
  .execute();

JOIN

const posts = await DB.selectFrom("posts")
  .innerJoin("users", "posts.user_id", "users.id")
  .select(["posts.id", "posts.title", "users.name as author"])
  .execute();

// Left join
const users = await DB.selectFrom("users")
  .leftJoin("profiles", "users.id", "profiles.user_id")
  .select(["users.id", "users.name", "profiles.bio"])
  .execute();

WHERE Variations

// OR WHERE
const results = await DB.selectFrom("users")
  .selectAll()
  .where((eb) => eb.or([
    eb("role", "=", "admin"),
    eb("role", "=", "moderator")
  ]))
  .execute();

// WHERE IN
const results = await DB.selectFrom("posts")
  .selectAll()
  .where("category_id", "in", [1, 2, 3])
  .execute();

// WHERE NOT
const results = await DB.selectFrom("users")
  .selectAll()
  .where("status", "!=", "banned")
  .execute();

// WHERE NULL
const results = await DB.selectFrom("users")
  .selectAll()
  .where("deleted_at", "is", null)
  .execute();

// LIKE search
const results = await DB.selectFrom("users")
  .selectAll()
  .where("name", "like", "%john%")
  .execute();

Aggregates

// COUNT
const result = await DB.selectFrom("users")
  .select((eb) => eb.fn.countAll().as("count"))
  .executeTakeFirst();
console.log(result?.count);

// SUM
const result = await DB.selectFrom("orders")
  .select((eb) => eb.fn.sum("amount").as("total"))
  .executeTakeFirst();

// AVG
const result = await DB.selectFrom("products")
  .select((eb) => eb.fn.avg("price").as("average"))
  .executeTakeFirst();

// MIN/MAX
const minResult = await DB.selectFrom("products")
  .select((eb) => eb.fn.min("price").as("lowest"))
  .executeTakeFirst();

const maxResult = await DB.selectFrom("products")
  .select((eb) => eb.fn.max("price").as("highest"))
  .executeTakeFirst();

Transactions

await DB.transaction().execute(async (trx) => {
  const userResult = await trx.insertInto("users")
    .values({ name: "John" })
    .executeTakeFirst();
  
  const userId = userResult.insertId?.toString();
  
  await trx.insertInto("profiles")
    .values({ user_id: userId, bio: "Hello" })
    .execute();
    
  await trx.insertInto("settings")
    .values({ user_id: userId, theme: "dark" })
    .execute();
});

Raw Queries

import { sql } from "kysely";

const results = await sql<{
  id: string;
  name: string;
}>`SELECT * FROM users WHERE email = ${email}`.execute(DB);

// Raw in select
const users = await DB.selectFrom("users")
  .select([
    "id",
    sql<number>`COUNT(*) OVER()`.as("total_count")
  ])
  .execute();

Multiple Connections

const stagingDB = DB.connection("staging");
const users = await stagingDB.selectFrom("users").selectAll().execute();

SQLite Service (Native)

Direct better-sqlite3 access for maximum performance.

When to Use

Use Native SQLite	Use Kysely
Simple reads (2-4x faster)	Complex query building
Performance-critical paths	Type-safe queries
Bulk operations	Developer productivity
Direct SQL control	Complex joins and subqueries

Basic Methods

import SQLite from "app/services/SQLite";

// Get single row
const user = SQLite.get(
  'SELECT * FROM users WHERE email = ?',
  ['user@example.com']
);

// Get all rows
const posts = SQLite.all(
  'SELECT * FROM posts ORDER BY created_at DESC'
);

// Execute (INSERT, UPDATE, DELETE)
const result = SQLite.run(
  'INSERT INTO posts (title, content) VALUES (?, ?)',
  ['Title', 'Content']
);
console.log('Inserted ID:', result.lastInsertRowid);
console.log('Changes:', result.changes);

Prepared Statements

// Reuse statement for bulk operations
const insert = SQLite.prepare('INSERT INTO logs (message) VALUES (?)');
for (const msg of messages) {
  insert.run(msg);
}

Transactions

const insertMany = SQLite.transaction((items) => {
  const insert = SQLite.prepare('INSERT INTO items (name) VALUES (?)');
  for (const item of items) {
    insert.run(item.name);
  }
});
insertMany(items);

Migrations

Laju uses Kysely's built-in migration system.

Create Migration

Create a new file in the migrations/ folder with the naming convention:

migrations/YYYYMMDDhhmmss_description.ts

Example:

// migrations/20240101000000_create_posts_table.ts
import { Kysely, sql } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable("posts")
    .addColumn("id", "serial", (col) => col.primaryKey())
    .addColumn("title", "varchar", (col) => col.notNull())
    .addColumn("content", "text")
    .addColumn("user_id", "integer", (col) =>
      col.references("users.id").onDelete("cascade")
    )
    .addColumn("is_published", "boolean", (col) => col.defaultTo(false))
    .addColumn("created_at", "bigint")
    .addColumn("updated_at", "bigint")
    .execute();
    
  // Add indexes
  await db.schema
    .createIndex("posts_user_id_idx")
    .on("posts")
    .column("user_id")
    .execute();
    
  await db.schema
    .createIndex("posts_is_published_created_at_idx")
    .on("posts")
    .columns(["is_published", "created_at"])
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("posts").execute();
}

Run Migrations

# Run all pending migrations
npm run migrate

# Database refresh (interactive)
npm run refresh

Rollback Migrations

# Rollback 1 migration (default)
npm run migrate:down

# Rollback N migrations
npm run migrate:down 3

# Rollback to specific migration 
npm run migrate:down 20230514062913_sessions
npm run migrate:down 20230514062913_sessions.ts

Database Refresh

The db:refresh command allows you to selectively refresh a specific database by deleting its SQLite file and re-running migrations.

# Interactive mode - prompts you to select a database
npm run refresh

# Direct selection - refresh specific database
npm run refresh 1    # Development
npm run refresh 2    # Production
npm run refresh 3    # Test

Performance Tips

1. Use Native SQLite for Simple Reads

// Standard (Kysely)
const user = await DB.selectFrom("users")
  .selectAll()
  .where("id", "=", id)
  .executeTakeFirst();

// Fast (Native) - 2-4x faster
const user = SQLite.get("SELECT * FROM users WHERE id = ?", [id]);

2. Add Indexes

// In migration
await db.schema
  .createIndex("users_email_idx")
  .on("users")
  .column("email")
  .execute();

// Composite index
await db.schema
  .createIndex("posts_user_id_created_at_idx")
  .on("posts")
  .columns(["user_id", "created_at"])
  .execute();

// Unique index
await db.schema
  .createIndex("users_email_unique_idx")
  .on("users")
  .column("email")
  .unique()
  .execute();

3. Use Transactions for Bulk Operations

// Slow - individual inserts
for (const item of items) {
  await DB.insertInto("items").values(item).execute();
}

// Fast - batch insert
await DB.insertInto("items").values(items).execute();

// Or use transaction
await DB.transaction().execute(async (trx) => {
  for (const item of items) {
    await trx.insertInto("items").values(item).execute();
  }
});

4. Use executeTakeFirst() for Single Records

// Correct
const user = await DB.selectFrom("users")
  .selectAll()
  .where("id", "=", id)
  .executeTakeFirst();

// Avoid - returns array
const [user] = await DB.selectFrom("users")
  .selectAll()
  .where("id", "=", id)
  .limit(1)
  .execute();

5. Select Only Needed Columns

// Good - select specific columns
const users = await DB.selectFrom("users")
  .select(["id", "name", "email"])
  .execute();

// Acceptable when you need all columns
const users = await DB.selectFrom("users")
  .selectAll()
  .execute();

Next Steps

• Authentication - Add user authentication
• Storage - Handle file uploads