Database queries

Use ffdb-client when you need typed reads and writes that stay aligned with your generated Database schema. This page shows what to use, when to use it, and why each path exists.

Why this layer exists

FFDB gives you two query paths because applications usually need both:

Name
Typed query builder (`db`)
Description
Best default for most CRUD and filter flows. You get autocomplete and compile-time checks against your schema.
Name
Raw SQL helper (`sql`)
Description
Useful when a query is easier to write as SQL (for example complex search or aggregation).

The goal is predictable app code: use typed builder first, drop to SQL only where it is clearer.

Core client interface (querying surface)

type FFDBClient<Database> = {
  db: Kysely<Database>
  sql: {
    execute: <TRow>(input: { sql: string; values?: unknown[]; bypassCache?: boolean }) => Promise<{ rows: TRow[] }>
    query: <TRow>(input: { sql: string; values?: unknown[]; bypassCache?: boolean }) => Promise<TRow[]>
    first: <TRow>(input: { sql: string; values?: unknown[]; bypassCache?: boolean }) => Promise<TRow | undefined>
  }
}

End-to-end typed flow

This is the most common app path: initialize once, read rows, write rows, then use the result in UI.

import { createClient } from 'ffdb-client'
import type { Database } from './ffdb.types'

const { db } = await createClient<Database>({
  config: {
    apiUrl: import.meta.env.VITE_FFDB_API_URL,
  },
})

const users = await db
  .selectFrom('user')
  .select(['id', 'email', 'name'])
  .where('isActive', '=', 1)
  .orderBy('name asc')
  .execute()

await db
  .insertInto('user')
  .values({
    id: crypto.randomUUID(),
    email: '[email protected]',
    name: 'Alice',
    isActive: 1,
  })
  .execute()

await db
  .updateTable('user')
  .set({ name: 'Alice Cooper' })
  .where('email', '=', '[email protected]')
  .execute()

Raw SQL flow

Use this when SQL is the clearest expression of your query.

type UserSearchRow = {
  id: string
  name: string
  email: string
}

const rows = await client.sql.query<UserSearchRow>({
  sql: `
    SELECT id, name, email
    FROM user
    WHERE name LIKE ?
    ORDER BY name
    LIMIT 20
  `,
  values: [`%${term}%`],
})

Why use parameter values? They keep dynamic inputs separated from SQL text and make query logic safer and easier to maintain.

React query usage

For UI reads, prefer useQuery so data loading participates in reactive state updates.

import { useQuery, useRawQuery } from 'ffdb-client/react'

function UsersTable() {
  const { data, isLoading, error } = useQuery((db) =>
    db.selectFrom('user').select(['id', 'email', 'name']).execute(),
  )

  if (isLoading) return <p>Loading users...</p>
  if (error) return <p>Failed: {error.message}</p>

  return <pre>{JSON.stringify(data, null, 2)}</pre>
}

function UserPicker({ term }: { term: string }) {
  const { data } = useRawQuery<{ id: string; name: string }>(
    {
      sql: 'SELECT id, name FROM user WHERE name LIKE ? ORDER BY name LIMIT 20',
      values: [`%${term}%`],
    },
    { deps: [term], enabled: term.length > 1 },
  )

  return <pre>{JSON.stringify(data, null, 2)}</pre>
}

Caching context

Query hooks use a lightweight in-memory cache to reduce duplicate work during repeated renders. This improves responsiveness, but it is runtime-local and should not be treated as persistent storage.

For persistence across app restarts or offline-first behavior, use the offline and storage adapter features covered in later docs.

Practical decision guide

Use db for normal app CRUD and domain logic.
Use sql when query-builder composition becomes harder to read than SQL.
Use useQuery and useRawQuery for UI-driven reads with loading/error states.
Keep schema types current with ffdb-cli generate so query typing remains trustworthy.