Querying Data

Layeron Database has two query surfaces:

• Table API for typed CRUD, filters, counts, and pagination.
• Raw SQL for direct SQLite/D1-compatible statements.

Both surfaces produce DbQuery objects, so they can be returned from routes, executed directly, or grouped in batches and transactions.

Choose a Surface

Use the Table API when the route maps naturally to one table:

const posts = await database
  .table("posts")
  .where({ authorId })
  .orderBy("createdAt", "desc")
  .limit(20)
  .all()

Use raw SQL when the route needs SQL features directly:

const posts = await database.sql<Post>(
  `
    select posts.id, posts.title, users.name as authorName
    from posts
    join users on users.id = posts.authorId
    where posts.authorId = ?
    order by posts.createdAt desc
  `,
  [authorId],
).all()

Execution Helpers

database.sql(...), database.statement(...), and Table API builders return a query object. Execute it with the helper that matches the route:

Helper	Use for
.all()	A list of rows.
.first()	The first row or undefined.
.one()	Exactly one expected row.
.maybeOne()	Zero or one expected row.
.run()	Writes or statements where metadata matters.
.as(key)	Returning query results as { [key]: rows }.
.json(key?, init?)	Returning a Response with JSON.

const post = await database.table("posts").where({ id }).one()

const maybePost = await database
  .sql<Post>("select * from posts where id = ?", [id])
  .maybeOne()

app.get("/posts", () => {
  return database.table("posts").orderBy("createdAt", "desc").limit(20).json("posts")
})

Type-Safe Results

When you use schema, the Table API infers row, insert, and update types. Raw SQL can use an explicit generic:

type PostSummary = {
  id: string
  title: string
  commentCount: number
}

const { results } = await database.sql<PostSummary>(
  `
    select posts.id, posts.title, count(comments.id) as commentCount
    from posts
    left join comments on comments.postId = posts.id
    group by posts.id
  `,
).all()

Group Statements

Table API and raw SQL statements can be mixed in one batch or transaction:

await database.batch([
  database.table("posts").insert({
    id,
    title,
    authorId,
    createdAt: Date.now(),
  }),
  database.sql(
    "insert into audit_logs (id, action, targetId) values (?, ?, ?)",
    [auditId, "post.created", id],
  ),
])

Use transactions for atomic write groups:

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

D1 Plus Shard Routing

When capacity expands beyond one shard, Layeron routes queries using your declared sharding policy.

Simple point reads and writes should include the shard key in the SQL parameters or provide an explicit shard hint. Use a hint for complex SQL, updates, deletes, or statements where the key is not obvious from the statement shape:

await database.sql({
  statement: "update posts set title = ? where slug = ?",
  params: [title, slug],
  shard: {
    table: "posts",
    key: postId, // Shard key value
  },
}).run()

You can also attach the hint fluently:

await database
  .statement("update posts set title = ? where slug = ?", [title, slug])
  .shardBy({ table: "posts", key: postId })
  .run()

The table field must match a table listed in db({ sharding }). The key field should be the value from that table’s configured keyColumn.

For a one-shard database, shard hints are accepted but usually unnecessary. Keeping hints in write paths can make later expansion simpler because the route code already carries the logical partition key.

Next Steps

• Table API: Use typed CRUD helpers, filters, pagination, single-row helpers, counts, and transaction helpers.
• Raw SQL: Write SQL statements with params, request objects, statement(...), exec(...), and result helpers.
• Batch and transactions: Group statements for batch execution, transactions, shard hints, and per-shard groups.
• Realtime database: Publish table changes from database-backed workflows.
• API reference: Review SQL request shapes, query result contracts, shard hints, and runtime helpers.