Raw SQL

Raw SQL is a first-class Database surface. Use it for joins, aggregates, reporting queries, SQLite functions, custom indexes, and D1-compatible statements.

Query Rows

type Post = {
  id: string
  title: string
  body: string
}

const { results } = await database.sql<Post>(
  "select id, title, body from posts where authorId = ? order by createdAt desc",
  [authorId],
).all()

Parameterize Dynamic Values

Pass dynamic values through the params array:

await database.sql(
  "update posts set title = ? where id = ?",
  [title, id],
).run()

This keeps SQL compatible with D1 prepared statements.

SQL Request Object

Use an object when you need a shard hint with the statement:

await database.sql({
  statement: "delete from posts where authorId = ? and id = ?",
  params: [authorId, id],
  shard: {
    table: "posts",
    key: authorId,
  },
}).run()

statement(...)

database.statement(...) is an alias for the same query builder:

await database
  .statement("update posts set title = ? where id = ?", [title, id])
  .run()

Use whichever reads better in the calling code.

exec(...)

Use exec(...) for direct SQL execution when you already have a complete SQL string:

await database.exec("pragma optimize;")

Most application routes should prefer sql(...) with params.

Result Helpers

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

Write statements return metadata:

const result = await database.sql(
  "update posts set status = ? where id = ?",
  ["published", id],
).run()

const changedRows = result.meta.changes

Route Responses

Return JSON directly from a query:

app.get("/posts", () => {
  return database
    .sql<Post>("select id, title from posts order by createdAt desc limit ?", [20])
    .json("posts")
})

.as("posts") returns a promise-like object shaped as { posts: rows }.