Getting started

This guide walks you through adding a SQL database to a Layeron backend application. You will declare a database, register it, read and write rows from route handlers, group writes, and understand where capacity and migrations fit.

Define the Database

Import db from @layeron/modules. Declare the database with a stable logical name and an initial schema, then register it with app.use(...).

import { backend } from "@layeron/core"
import { db, table, text, integer, rawSql } from "@layeron/modules"

const app = backend()

const database = db({
  name: "main",
  schema: {
    posts: table({
      id: text().primaryKey(),
      title: text().notNull(),
      body: text().notNull(),
      authorId: text().notNull().index(),
      status: text().notNull().default("draft"),
      createdAt: integer().notNull().default(rawSql("(unixepoch())")),
    }),
    audit_logs: table({
      id: text().primaryKey(),
      action: text().notNull(),
      targetId: text().notNull().index(),
      createdAt: integer().notNull().default(rawSql("(unixepoch())")),
    }),
  },
})

app.use(database)

The schema generates the first SQL migration and gives database.table("posts") typed rows. The database identity follows Layeron’s platform namespace/name model.

Read Rows With the Table API

Use database.table(name) for common CRUD routes:

app.get("/posts", async () => {
  const { results: posts } = await database
    .table("posts")
    .select(["id", "title", "body", "createdAt"])
    .orderBy("createdAt", "desc")
    .limit(20)
    .all()

  return Response.json({ posts })
})

Single-row helpers make route intent explicit:

app.get("/posts/:id", async (request) => {
  const pathSegments = new URL(request.url).pathname.split("/")
  const id = pathSegments[2]
  const post = await database.table("posts").where({ id }).maybeOne()

  if (!post) {
    return Response.json({ error: "Post not found" }, { status: 404 })
  }

  return Response.json({ post })
})

Write Rows

app.post("/posts", async (request) => {
  const input = await request.json() as {
    title: string
    body: string
    authorId: string
  }

  const post = {
    id: crypto.randomUUID(),
    title: input.title,
    body: input.body,
    authorId: input.authorId,
  }

  await database.table("posts").insert(post).run()

  return Response.json({ post }, { status: 201 })
})

Keep Raw SQL Available

Use raw SQL for joins, custom SQLite expressions, reporting queries, and direct D1-compatible statements:

app.get("/authors/:authorId/post-count", async (request) => {
  const pathSegments = new URL(request.url).pathname.split("/")
  const authorId = pathSegments[2]
  const row = await database.sql<{ count: number }>(
    "select count(*) as count from posts where authorId = ?",
    [authorId],
  ).one()

  return Response.json(row)
})

Dynamic values belong in the params array. This keeps the statement parameterized and D1-compatible.

Group Related Writes

Use a transaction when several statements belong to one logical write:

app.post("/posts/:id/publish", async (request) => {
  const pathSegments = new URL(request.url).pathname.split("/")
  const id = pathSegments[2]
  const auditId = crypto.randomUUID()

  await database.transaction((tx) => [
    tx.table("posts").update({ status: "published" }).where({ id }),
    tx.sql(
      "insert into audit_logs (id, action, targetId) values (?, ?, ?)",
      [auditId, "post.published", id],
    ),
  ])

  return Response.json({ ok: true })
})

Transactions and batches accept Table API statements and raw SQL statements together.

Choose Capacity

Omitting capacity starts with one D1 shard. You can spell that out with fixed when you want the decision to be visible:

const database = db({
  name: "main",
  capacity: {
    mode: "fixed",
  },
  sql: `
    create table if not exists posts (
      id text primary key,
      title text not null,
      body text not null,
      createdAt text not null
    );
  `,
})

When you need multiple D1 shards, use manual capacity and declare the sharded tables. See Capacity and sharding for the full model.

Evolve the Schema

For custom migration history, supply named SQL migrations:

const database = db({
  name: "main",
  sql: [
    {
      name: "001_create_posts",
      sql: `
        create table if not exists posts (
          id text primary key,
          title text not null,
          body text not null,
          createdAt text not null
        );
      `,
    },
    {
      name: "002_add_status",
      sql: "alter table posts add column status text not null default 'draft';",
    },
  ],
})

Layeron tracks which migrations have already been applied to your logical database. Applied migrations are immutable; add a new migration object for each schema change.

Next Steps

• Core concepts: Understand database instances, schema, raw SQL, migrations, capacity, and sharding policy.
• Schema: Declare typed tables, columns, indexes, insert types, and generated SQL.
• Table API: Use typed CRUD helpers, filters, pagination, counts, and transaction helpers.
• Raw SQL: Run direct SQLite/D1-compatible statements with params and result helpers.
• Batch and transactions: Group statements and handle per-shard execution.
• Migrations: Manage schema-generated SQL, versioned migrations, and safety checks.