Databases

Nifra's core bundles no database layer. Like Hono or Elysia, it owns the HTTP boundary — routing, validation, the typed client — and your handlers, loaders, and actions are just functions. Import any database client or ORM and call it there. SQLite, Postgres, MySQL, MongoDB, Drizzle, Prisma, Kysely — all work, because none of them are Nifra's concern.

Scaffold it (recommended)

You don't have to wire Drizzle by hand. bun create Nifra takes a --db flag and generates a correct, production-grade data layer — schema, typed client, drizzle.config.ts, the db:generate/db:migrate scripts, and .env.example — plus a Database section in the app's AGENTS.md, so a human or a coding agent starts from the right setup instead of inventing one.

# Scaffold an app with the Drizzle layer already wired (schema, typed client, migrations, scripts, .env):
bun create nifra notes-api --db drizzle-libsql     # SQLite everywhere incl. the edge (or Turso)
bun create nifra notes-api --db drizzle-postgres   # Postgres (postgres.js) on Bun/Node/Deno
bun create nifra notes-api --db drizzle-sqlite     # Bun's built-in bun:sqlite, local file

drizzle-libsql is the default recommendation — it's the one SQLite client that runs on every runtime, including the edge (a local file in dev, Turso in prod).

Inject the client once, read it as c.db

The whole integration is one seam: open the DB client once, decorate it onto the server, and every handler reads it as a typed c.db. This is how the scaffold wires it — and it keeps the client out of each handler's imports.

import { server } from "@nifrajs/core"
import { desc } from "drizzle-orm"
import { db, notes } from "./db"   // your Drizzle client + schema (what the scaffold generates)

// decorate() hangs the client on the context ONCE — every handler then reads it as `c.db`, fully typed.
export const app = server()
  .decorate("db", db)
  .get("/notes", async (c) => c.db.select().from(notes).orderBy(desc(notes.createdAt)))

export type App = typeof app

SQLite

bun:sqlite is built into Bun (zero install); use better-sqlite3 on Node, or libSQL/Turso anywhere. Open the connection once and query from your routes:

import { server } from "@nifrajs/core"
import { Database } from "bun:sqlite"        // Node: better-sqlite3 · edge: Turso/libSQL

// Open the connection ONCE at module scope (the driver pools internally).
const db = new Database("app.db")
const byId = db.query<{ id: number; name: string }>("SELECT * FROM users WHERE id = ?")

export const app = server().get("/users/:id", (c) =>
  // Parameterized (?) — never interpolate user input into SQL.
  byId.get(Number(c.params.id)) ?? new Response("Not found", { status: 404 }),
)

Postgres with Drizzle (Recommended)

Drizzle gives you a **fully typed schema**, auto-generated SQL migrations, and a composable query builder. Define your schema once, run bunx drizzle-kit generate to create migrations, and Nifra's loaders use fully-typed queries:

// schema.ts — define your schema (source of truth)
import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core"
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull(),
  createdAt: timestamp("created_at").defaultNow(),
})

// drizzle.config.ts — drizzle-kit config
import { defineConfig } from "drizzle-kit"
export default defineConfig({
  schema: "./schema.ts",
  out: "./migrations",
  dialect: "postgresql",
})

// db.ts — initialize and migrate
import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
import { migrate } from "drizzle-orm/postgres-js/migrator"
const sql = postgres(process.env.DATABASE_URL!)
export const db = drizzle(sql, { schema: import("./schema") })
await migrate(db, { migrationsFolder: "./migrations" })  // on app start

// routes/users.ts — use your typed schema in a loader
import { type LoaderArgs } from "@nifrajs/web"
import { eq } from "drizzle-orm"
import { db } from "../db"
import { users } from "../db/schema"

export async function loader({ params }: LoaderArgs) {
  const [user] = await db.select().from(users).where(eq(users.id, Number(params.id)))
  return { user }
}
export default function UserPage({ data }: any) { return <h1>{data.user?.name}</h1> }

Workflow:

Edit schema.ts — change a column name, add a table, whatever
Run bunx drizzle-kit generate — Drizzle writes a new SQL migration to migrations/
Call migrate(db, { migrationsFolder }) on app start — migrations run automatically, once each
Your loaders use the typed schema; no manual table names or inference

Request-scoped multi-tenancy (Postgres RLS + Drizzle)

For multi-tenant data, enforce isolation at the database, not in every WHERE clause (one forgotten filter is a cross-tenant leak). Postgres Row-Level Security does it — but Drizzle reuses pooled connections and won't carry a per-request SET, so the trick is to run each tenant query inside a transaction that first sets a transaction-local GUC the policy reads. This scoped() + tenantDb() helper (≈30 lines) wires it up; tenantDb() throws when called unscoped, so a missing scope fails loudly instead of silently reading every tenant's rows.

// rls.ts — request-scoped Postgres RLS for Drizzle. Drizzle reuses pooled connections and won't carry
// a per-request setting, so each tenant query runs in a tx that first sets a GUC the RLS policy reads.
import { AsyncLocalStorage } from "node:async_hooks"
import { sql } from "drizzle-orm"
import { db, notes } from "./db"

const als = new AsyncLocalStorage<typeof db>()

// Bind every query inside fn() to userId. set_config(..., true) is LOCAL to the tx; ${userId} is a bound
// parameter (no SQL injection). One round-trip sets the GUC, then your queries run isolated.
export function scoped<T>(userId: string, fn: () => Promise<T>): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.user_id', ${userId}, true)`)
    return als.run(tx, fn)
  })
}

// Use this (not the raw db) inside scoped(). Throws if called unscoped — a missing scope is a loud error,
// never a silent cross-tenant read.
export function tenantDb(): typeof db {
  const tx = als.getStore()
  if (!tx) throw new Error("tenantDb() called outside scoped() — every tenant query must be scoped")
  return tx as typeof db
}

// route: bind to the session user — the DB then isolates every query in scope, even a buggy one.
app.get("/notes", (c) => scoped(c.session.userId, () => tenantDb().select().from(notes)))

Enable RLS and the policy once, in a migration:

-- migration: enable RLS + a policy that reads the tx-local GUC set above.
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON notes
  USING (user_id = current_setting('app.user_id', true)::uuid);

The policy is the source of truth; scoped() just supplies the identity. Pair it with a UUIDv7 user_id and an index on it. Connect c.session.userId from @nifrajs/better-auth.

Postgres (Raw Driver)

If you prefer parameterized queries over an ORM, use postgres or pg directly:

import { server } from "@nifrajs/core"
import { drizzle } from "drizzle-orm/postgres-js"   // or node-postgres / neon-http
import postgres from "postgres"
import { eq } from "drizzle-orm"
import { users } from "./schema"

const db = drizzle(postgres(process.env.DATABASE_URL!))

export const app = server().get("/users/:id", async (c) => {
  const [user] = await db.select().from(users).where(eq(users.id, Number(c.params.id)))
  return user ?? new Response("Not found", { status: 404 })
})

In a loader

Full-stack? The same query goes in a route loader, and its result is typed straight into your page during SSR. The loader is server-only, so the database client is tree-shaken out of the browser bundle.

// In a full-stack app the same query runs in a route loader — typed end-to-end to the page.
export async function loader({ params }: LoaderArgs<typeof app>) {
  const post = await db.query("SELECT * FROM posts WHERE slug = ?").get(params.slug)
  if (!post) throw new Response("Not found", { status: 404 })
  return { post }
}

Servers vs the edge

This is a runtime constraint, not a Nifra one — every edge framework shares it. On a long-running server you use native TCP drivers; on the edge (no raw sockets) you use HTTP / serverless drivers. The route code is identical either way.

Runtime	SQLite	Postgres / other
Bun	`bun:sqlite` (built-in)	`postgres`, `pg`, Drizzle, Prisma
Node	`better-sqlite3`	`postgres`, `pg`, Drizzle, Prisma
Deno	libSQL	`postgres`, Drizzle
Cloudflare / edge	Cloudflare D1, Turso/libSQL	Neon serverless, Postgres over Hyperdrive, Prisma Accelerate

On Cloudflare, bindings like D1 are typed through c.env — see Edge & bindings. For Workers MySQL/Postgres, Hyperdrive pools the connection so a native driver works over the binding.

Security: always parameterize (? / driver placeholders or an ORM) — never string-build SQL from request input. Nifra validates the request body/params at the boundary (with t or any Standard Schema), so malformed input is rejected before your query runs. See Security.

Complete runnable references — a typed CRUD API you can diff your own against: examples/db-postgres (Drizzle + Postgres, embedded PGlite — zero setup) and examples/db-sqlite (the same API on raw bun:sqlite, parameterized queries, boundary validation).