A Type-Safe Data Layer with Nuxt 4 + TypeScript + Drizzle ORM + SQLite + Zod

Most Nuxt backends end up with the same shape of bug. There's a table definition somewhere, a Zod schema somewhere else that validates the request body, and a TypeScript interface in a third file that the frontend imports. Three descriptions of one thing. Add a column and you have to remember all three, and nothing tells you when you forget — the app just quietly stops storing a field.

This guide wires four pieces together so that number goes down to one:

• Drizzle ORM for the schema, which is TypeScript, so the row types come out of it for free
• drizzle-zod to generate the request validators from those same table definitions
• h3's validated handlers so nothing enters the system unparsed
• Nitro tasks for the scheduled background work every real app eventually needs

Tags: Nuxt 4, Drizzle ORM, SQLite, Zod, Nitro, TypeScript

Time to read: 15 min

What you'll build: a read-later inbox. You POST a URL, it lands in an inbox, you move it through reading and archived, and a background task periodically visits the un-enriched entries and fills in their real page title. That last part is the interesting one — it's the piece that usually forces people to bolt on a queue, a second process, or a cron container.

Why this combination

Each piece is here for a reason that the others can't cover.

Drizzle is a query builder that happens to know SQL. You get typeof table.$inferSelect and $inferInsert straight off the table object, so there is no generated client to keep in sync and no build step between editing a column and seeing the type change. It's also small enough to run inside a Nitro bundle without thinking about it.

SQLite removes an entire category of setup. No container, no connection string, no "is the database up" branch in your dev instructions. For a side project with one writer it is not a compromise — it's a file with transactions.

drizzle-zod is the part that actually earns the title. It reads a Drizzle table and produces Zod schemas from it, which means your validators inherit the column's nullability, its length, and its enum values. Widen a column and the validator widens with it. This is the drift-killer.

Nitro tasks give you a cron and a job runner that live inside the app you already deploy. Same bundle, same imports, same database handle — a task can call useDb() exactly like a route handler does.

Prerequisites

• Node.js 20+
• A Nuxt 4 project, or five seconds to make one
• Comfort with TypeScript generics at the "I can read them" level

1) Scaffold and install

npx nuxi@latest init nuxt4-stash
cd nuxt4-stash
npm i drizzle-orm better-sqlite3 drizzle-zod zod
npm i -D drizzle-kit @types/better-sqlite3

At the time of writing that resolves to drizzle-orm@0.45, drizzle-zod@0.8, drizzle-kit@0.31 and zod@4. Pin them if you like — the v1 note below explains why.

Drizzle v1 is in RC, and it moves two of these imports. In v1, createInsertSchema and friends come from drizzle-orm/zod instead of the separate drizzle-zod package, and there's a new drizzle-orm/node-sqlite driver that uses Node's built-in node:sqlite with no native dependency at all. Neither exists in 0.45 — if you copy a snippet from the current Drizzle docs and the import path 404s, that's why. Everything below targets the stable line.

Then enable Nitro's task runner, which is behind an experimental flag and does nothing until you turn it on:

// nuxt.config.ts
export default defineNuxtConfig({
  compatibilityDate: '2025-07-15',

  runtimeConfig: {
    dbFile: process.env.NUXT_DB_FILE || './.data/stash.db',
  },

  nitro: {
    experimental: { tasks: true },
    scheduledTasks: {
      // Enrich anything still missing a real title, every 10 minutes.
      '*/10 * * * *': ['bookmarks:enrich'],
    },
  },
})

Add .data/ to .gitignore while you're there.

2) One schema, three consumers

Everything downstream reads from this file. Put it under server/ so it never gets pulled into the client bundle:

// server/database/schema.ts
import { sql } from 'drizzle-orm'
import { index, integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'

export const bookmarks = sqliteTable('bookmarks', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  url: text('url').notNull().unique(),
  title: text('title').notNull(),
  note: text('note'),
  status: text('status', { enum: ['inbox', 'reading', 'archived'] })
    .notNull()
    .default('inbox'),
  tags: text('tags', { mode: 'json' })
    .$type<string[]>()
    .notNull()
    .default(sql`'[]'`),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date()),
  fetchedAt: integer('fetched_at', { mode: 'timestamp' }),
}, t => [
  index('bookmarks_status_idx').on(t.status),
])

export type Bookmark = typeof bookmarks.$inferSelect
export type NewBookmark = typeof bookmarks.$inferInsert

Four details in there are worth slowing down for.

text(..., { enum: [...] }) is not just a comment. Drizzle narrows the column's TypeScript type to that union, and — as you'll see in the next section — drizzle-zod turns it into z.enum([...]) automatically. One list, and both the compiler and the runtime validator respect it.

mode: 'json' plus $type<string[]>() gives you arrays in a column. Drizzle stringifies on write and parses on read, so row.tags is a real string[]. But be clear about what $type is: a cast. It changes what TypeScript believes and checks nothing at runtime. If a bad value reaches the insert, SQLite will happily store it. That's precisely the hole the generated validator plugs.

mode: 'timestamp' stores Unix seconds. You get a Date back, but sub-second precision is gone on the round trip. Use timestamp_ms if you're storing anything you'll later sort or diff at millisecond resolution.

$defaultFn runs in JavaScript, not SQL. It fills the value on insert from your app process, which means createdAt is set even for rows created outside a route handler — the enrichment task included.

3) Validators derived from the table

Here's the part that makes the rest of the app hard to break. Instead of hand-writing a body schema, generate it:

// server/database/validators.ts
import { createInsertSchema, createSelectSchema, createUpdateSchema } from 'drizzle-zod'
import { z } from 'zod'
import { bookmarks } from './schema'

/** POST body: what a client is allowed to send. */
export const bookmarkInsertSchema = createInsertSchema(bookmarks, {
  url: z.url(),
  title: s => s.min(1).max(200),
  tags: z.array(z.string().min(1).max(24)).max(8),
}).pick({ url: true, title: true, note: true, tags: true })

/** PATCH body: every field optional, server-owned columns excluded. */
export const bookmarkPatchSchema = createUpdateSchema(bookmarks)
  .pick({ title: true, note: true, status: true, tags: true })

/** The response shape, if you want to assert on it in tests. */
export const bookmarkSchema = createSelectSchema(bookmarks)

export type BookmarkInput = z.infer<typeof bookmarkInsertSchema>
export type BookmarkPatch = z.infer<typeof bookmarkPatchSchema>

status never appears in the insert schema, so no client can create a row that starts life as archived. id, createdAt and fetchedAt are gone for the same reason. .pick() is the whole authorisation story for this endpoint and it's one line.

The refinement argument has two modes, and mixing them up is the classic drizzle-zod bug:

• A callback extends. title: s => s.min(1).max(200) takes the schema Drizzle generated and adds constraints to it. Nullability and optionality are applied afterwards, so a nullable column stays nullable.
• A bare schema replaces. url: z.url() throws away everything Drizzle inferred for that column, including its nullability. That's fine here — url is notNull() and z.url() is non-optional, so the two agree. On a nullable column it would silently make the field required.

Rule of thumb: reach for the callback unless you specifically want to override the shape, and when you do override, check the column's nullability by hand.

Note what you did not write. Nothing says status is one of three values, and nothing says title is a string — those came from the table. Add a fourth status to the enum and the validator accepts it on the next compile, with no second edit.

createUpdateSchema makes every field optional, which is exactly right for PATCH and has one sharp edge: {} is valid. Guard for it explicitly rather than issuing an UPDATE with no SET clause.

4) The connection

Nitro auto-imports everything in server/utils/, so a single file gives every route and task the same handle:

// server/utils/db.ts
import Database from 'better-sqlite3'
import { drizzle } from 'drizzle-orm/better-sqlite3'
import { mkdirSync } from 'node:fs'
import { dirname } from 'node:path'
import * as schema from '../database/schema'

type Db = ReturnType<typeof create>

function create() {
  const file = useRuntimeConfig().dbFile
  mkdirSync(dirname(file), { recursive: true })

  const sqlite = new Database(file)
  sqlite.pragma('journal_mode = WAL')
  sqlite.pragma('busy_timeout = 5000')
  sqlite.pragma('foreign_keys = ON')

  return drizzle(sqlite, { schema })
}

// Survives Nitro's dev-time module reloads. Without this you leak a
// file handle on every HMR pass and eventually hit SQLITE_BUSY.
const g = globalThis as typeof globalThis & { __stashDb?: Db }

export function useDb(): Db {
  return (g.__stashDb ??= create())
}

The globalThis cache is not superstition. Nitro re-evaluates server modules on change in dev, and a module-level new Database(...) opens a new connection each time while the old ones stay open on the same file. WAL tolerates a lot of that, but not forever.

busy_timeout is the other one people skip. SQLite allows exactly one writer; without a timeout, a concurrent write fails immediately instead of waiting the 5ms it needed. Setting it turns a class of spurious 500s into nothing at all.

5) Migrations

Drizzle Kit needs its own config, because it runs outside Nuxt and can't read runtimeConfig:

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: 'sqlite',
  schema: './server/database/schema.ts',
  out: './server/database/migrations',
  dbCredentials: { url: process.env.NUXT_DB_FILE || './.data/stash.db' },
})

npx drizzle-kit generate   # schema diff -> a numbered .sql file
npx drizzle-kit migrate    # apply pending files
npx drizzle-kit studio     # browse the data

drizzle-kit push also exists and skips the file entirely, applying the diff straight to the database. It's genuinely nice while you're still moving columns around every ten minutes. It is also unreviewable and unrepeatable, so switch to generate + migrate the moment the schema is worth keeping.

Committing the SQL files means you can apply them on boot instead of remembering to. That's the first task:

// server/tasks/db/migrate.ts
import { migrate } from 'drizzle-orm/better-sqlite3/migrator'

export default defineTask({
  meta: {
    name: 'db:migrate',
    description: 'Apply pending Drizzle migrations',
  },
  run() {
    migrate(useDb(), { migrationsFolder: './server/database/migrations' })
    return { result: 'migrated' as const }
  },
})

The file path gives the task its name: server/tasks/db/migrate.ts becomes db:migrate. Trigger it from a Nitro plugin so a fresh container is usable the moment it starts:

// server/plugins/migrate.ts
export default defineNitroPlugin(async () => {
  await runTask('db:migrate')
})

One caveat before you ship that: on a rolling deploy, several instances start at once and all of them run migrations against the same file. SQLite's single-writer lock means one wins and the rest wait, which is survivable for additive changes and not something to rely on for destructive ones. Nitro deduplicates concurrent calls of the same task within one process — it does not coordinate across processes.

6) Routes that validate at every edge

h3 ships validated variants of its body, query and param readers. They take any function that throws on bad input, which schema.parse already is:

// server/api/bookmarks/index.get.ts
import { and, desc, eq, like, or } from 'drizzle-orm'
import { z } from 'zod'
import { bookmarks } from '~~/server/database/schema'

const querySchema = z.object({
  status: z.enum(['inbox', 'reading', 'archived']).optional(),
  q: z.string().trim().min(1).max(80).optional(),
  limit: z.coerce.number().int().min(1).max(100).default(25),
  offset: z.coerce.number().int().min(0).default(0),
})

export default defineEventHandler(async (event) => {
  const { status, q, limit, offset } = await getValidatedQuery(event, querySchema.parse)
  const db = useDb()

  const filters = [
    status ? eq(bookmarks.status, status) : undefined,
    q
      ? or(like(bookmarks.title, `%${q}%`), like(bookmarks.url, `%${q}%`))
      : undefined,
  ].filter(Boolean)

  return db
    .select()
    .from(bookmarks)
    .where(filters.length ? and(...filters) : undefined)
    .orderBy(desc(bookmarks.createdAt))
    .limit(limit)
    .offset(offset)
})

z.coerce.number() is doing necessary work — query strings are strings, and limit=25 arrives as "25". Coercing at the boundary means limit is a number everywhere after this line.

The like filter uses a parameter, not string interpolation into SQL. Drizzle binds it, so a q full of quotes is data rather than syntax. The sql template tag behaves the same way; the only way to build an injectable query in Drizzle is to go out of your way with sql.raw().

The POST handler is where the generated schema pays off:

// server/api/bookmarks/index.post.ts
import { bookmarks } from '~~/server/database/schema'
import { bookmarkInsertSchema } from '~~/server/database/validators'

export default defineEventHandler(async (event) => {
  const input = await readValidatedBody(event, bookmarkInsertSchema.parse)

  try {
    const [created] = await useDb()
      .insert(bookmarks)
      .values(input)
      .returning()

    setResponseStatus(event, 201)
    return created
  }
  catch (error: any) {
    if (String(error?.code).includes('SQLITE_CONSTRAINT_UNIQUE')) {
      throw createError({ statusCode: 409, statusMessage: 'Already saved' })
    }
    throw error
  }
})

input is typed as exactly the four fields the schema picked, so passing it to .values() type-checks against NewBookmark with no cast. If you delete note from the table, this file stops compiling — which is the entire point of deriving one from the other.

The unique constraint on url is enforced by SQLite, not by a SELECT first. Checking in application code would be a race; catching the constraint error is the version that's actually correct under concurrency.

PATCH pulls the id out of the route, and validates that too:

// server/api/bookmarks/[id].patch.ts
import { eq } from 'drizzle-orm'
import { z } from 'zod'
import { bookmarks } from '~~/server/database/schema'
import { bookmarkPatchSchema } from '~~/server/database/validators'

const paramsSchema = z.object({ id: z.coerce.number().int().positive() })

export default defineEventHandler(async (event) => {
  const { id } = await getValidatedRouterParams(event, paramsSchema.parse)
  const patch = await readValidatedBody(event, bookmarkPatchSchema.parse)

  if (Object.keys(patch).length === 0)
    throw createError({ statusCode: 400, statusMessage: 'Empty patch' })

  const [updated] = await useDb()
    .update(bookmarks)
    .set(patch)
    .where(eq(bookmarks.id, id))
    .returning()

  if (!updated)
    throw createError({ statusCode: 404, statusMessage: 'Not found' })

  return updated
})

.returning() is how you avoid the read-after-write round trip, and it doubles as the existence check — no rows back means no row matched.

One thing to know about readValidatedBody before it surprises you in production. When the validator throws, h3 catches it and re-raises a 400 with statusMessage: 'Validation Error' and the original error attached as data. For a Zod failure that data is the full ZodError, issue paths and all, and it goes to the client. That's a lovely developer experience and a small information leak — those paths are your column names. If you'd rather not publish your schema, use safeParse and shape the response yourself:

const result = bookmarkInsertSchema.safeParse(await readBody(event))
if (!result.success) {
  throw createError({
    statusCode: 400,
    statusMessage: 'Invalid bookmark',
    data: { fields: result.error.issues.map(i => i.path.join('.')) },
  })
}
const input = result.data

7) Enrichment as a Nitro task

The app accepts a URL with a placeholder title. Something has to go and find the real one. In most tutorials this is where a queue library appears; here it's a file:

// server/tasks/bookmarks/enrich.ts
import { and, eq, isNull } from 'drizzle-orm'
import { bookmarks } from '~~/server/database/schema'

const TITLE_RE = /<title[^>]*>([\s\S]*?)<\/title>/i

async function fetchTitle(url: string): Promise<string | null> {
  const res = await fetch(url, {
    redirect: 'follow',
    signal: AbortSignal.timeout(8000),
    headers: { 'user-agent': 'stash-bot/1.0' },
  })
  if (!res.ok) return null

  const html = (await res.text()).slice(0, 100_000)
  const raw = TITLE_RE.exec(html)?.[1]
  return raw ? raw.replace(/\s+/g, ' ').trim().slice(0, 200) || null : null
}

export default defineTask({
  meta: {
    name: 'bookmarks:enrich',
    description: 'Fetch real page titles for un-enriched bookmarks',
  },

  async run({ payload }) {
    const db = useDb()
    const batch = Number(payload?.limit ?? 10)

    const pending = await db
      .select({ id: bookmarks.id, url: bookmarks.url })
      .from(bookmarks)
      .where(and(isNull(bookmarks.fetchedAt), eq(bookmarks.status, 'inbox')))
      .limit(batch)

    let updated = 0

    for (const row of pending) {
      const title = await fetchTitle(row.url).catch(() => null)

      // Stamp fetchedAt either way, so a dead link isn't retried forever.
      await db
        .update(bookmarks)
        .set({ fetchedAt: new Date(), ...(title ? { title } : {}) })
        .where(eq(bookmarks.id, row.id))

      if (title) updated++
    }

    return { result: { scanned: pending.length, updated } }
  },
})

fetchedAt is the queue. There's no table of jobs, no visibility timeout, no dead-letter handling — the work item is the row, and isNull(fetchedAt) is the query that finds unprocessed ones. Stamping the timestamp on failure as well as success is what keeps a permanently 404ing URL from being retried every ten minutes until the heat death of the universe. When you want retries with a budget, that's an attempts column and one more predicate.

Nitro's dev server exposes tasks over HTTP so you don't have to wait for the cron:

curl http://localhost:3000/_nitro/tasks                       # list tasks + schedule
curl -X POST http://localhost:3000/_nitro/tasks/bookmarks:enrich \
  -H 'content-type: application/json' -d '{"payload":{"limit":3}}'

Two properties of the task runner are worth internalising. Each task has at most one running instance — call bookmarks:enrich five times in parallel and it runs once, with all five callers receiving the same promise. That's free protection against an overlapping cron tick, and it's also why the batch limit matters: the task should finish comfortably inside its interval. Scheduling is in-process, handled by croner under the Node presets. It works on a long-lived server and does not work on a platform that freezes your process between requests — on Vercel or Netlify you need that platform's own scheduler to hit an endpoint that calls runTask. Cloudflare is the exception: the cloudflare_module preset maps scheduledTasks onto native Cron Triggers, provided the patterns in wrangler.toml match yours exactly.

If you expose a manual trigger over HTTP, authenticate it. runTask will happily run anything by name:

// server/api/admin/enrich.post.ts
export default defineEventHandler(async (event) => {
  const token = getRequestHeader(event, 'authorization')
  if (token !== `Bearer ${process.env.ADMIN_TOKEN}`)
    throw createError({ statusCode: 401 })

  const { result } = await runTask('bookmarks:enrich', { payload: { limit: 25 } })
  return result
})

8) The client, and the Date that isn't

Nitro generates types for everything under server/api/, so the frontend gets the response shape without importing anything:

<!-- app/pages/index.vue -->
<script setup lang="ts">
const status = ref<'inbox' | 'reading' | 'archived'>('inbox')

const { data: items, refresh } = await useFetch('/api/bookmarks', {
  query: { status },
})

const url = ref('')

async function add() {
  if (!url.value) return
  await $fetch('/api/bookmarks', {
    method: 'POST',
    body: { url: url.value, title: new URL(url.value).hostname },
  })
  url.value = ''
  await refresh()
}

async function move(id: number, next: 'reading' | 'archived') {
  await $fetch(`/api/bookmarks/${id}`, { method: 'PATCH', body: { status: next } })
  await refresh()
}
</script>

<template>
  <main>
    <form @submit.prevent="add">
      <input v-model="url" type="url" placeholder="https://…" required>
      <button type="submit">Save</button>
    </form>

    <nav>
      <button
        v-for="s in (['inbox', 'reading', 'archived'] as const)"
        :key="s"
        :aria-pressed="status === s"
        @click="status = s"
      >
        {{ s }}
      </button>
    </nav>

    <ul>
      <li v-for="item in items" :key="item.id">
        <a :href="item.url" rel="noopener">{{ item.title }}</a>
        <time :datetime="item.createdAt">
          {{ new Date(item.createdAt).toLocaleDateString() }}
        </time>
        <button v-if="item.status !== 'reading'" @click="move(item.id, 'reading')">
          Read next
        </button>
        <button @click="move(item.id, 'archived')">Archive</button>
      </li>
    </ul>
  </main>
</template>

query: { status } takes the ref itself, so flipping the filter re-runs the request. No watcher.

Now the detail that catches everyone. Hover item.createdAt in your editor: it's string, even though the handler returned a Date. That's correct, not a bug. Responses go through JSON.stringify, which turns a Date into an ISO string, and Nuxt's type helpers model that transformation rather than lying to you about it. The value in the browser really is a string.

So new Date(item.createdAt) at the point of use is the honest fix, and :datetime="item.createdAt" works unchanged because an ISO string is what that attribute wants. If you have a lot of these, convert once in a transform and let the types follow:

const { data: items } = await useFetch('/api/bookmarks', {
  query: { status },
  transform: rows => rows.map(r => ({ ...r, createdAt: new Date(r.createdAt) })),
})

The wider lesson: createSelectSchema describes rows as they exist in the database. It is not a description of your JSON response. When you want to validate what actually crosses the wire, bookmarkSchema.extend({ createdAt: z.iso.datetime() }) is the schema you mean.

9) Build and deploy notes

npx nuxt typecheck
npx nuxt build
NUXT_DB_FILE=/data/stash.db node .output/server/index.mjs

A few things that only show up outside dev:

better-sqlite3 is a native module. The node-server preset traces it into .output/server/node_modules with its compiled .node binary, so a plain Node deploy works. Serverless and edge presets do not — there's no place to put a binary or a file to write to. If you're heading to Workers or Lambda, swap the driver for libSQL or D1; the schema, the validators, the routes and the tasks are all unchanged, because the only file that names the driver is server/utils/db.ts.

Build on the platform you deploy to. The compiled binary is specific to the OS, architecture and Node ABI. Building on an Apple Silicon laptop and shipping the output to a Linux container is the single most common way this breaks. Build in the image.

The database file needs to outlive the container. Mount a volume and point NUXT_DB_FILE at it, and keep the WAL and shared-memory sidecar files (stash.db-wal, stash.db-shm) on the same volume — they're part of the database, not scratch.

One writer, one instance. SQLite over a network filesystem, or two containers sharing a volume, is where the horror stories come from. If you need to scale out, that's the moment to switch drivers — and again, it's one file.

Back up with the API, not with cp. Copying a database mid-write gives you a torn file. sqlite.backup('/backups/stash.db') is atomic, and it makes a very satisfying second task:

// server/tasks/db/backup.ts
export default defineTask({
  meta: { name: 'db:backup', description: 'Snapshot the SQLite file' },
  async run() {
    const stamp = new Date().toISOString().slice(0, 10)
    await useDb().$client.backup(`./.data/backups/stash-${stamp}.db`)
    return { result: 'ok' as const }
  },
})

$client is Drizzle's escape hatch to the underlying driver — better-sqlite3's own Database instance, with everything Drizzle doesn't wrap.

Where to take it

• Full-text search. SQLite's FTS5 is a CREATE VIRTUAL TABLE in a custom migration and a sql template in the query. Far less work than adding a search service, and like '%q%' stops scaling around the same point you'd notice.
• Relations. Add a collections table with references(() => collections.id), define relations(), and db.query.bookmarks.findMany({ with: { collection: true } }) returns a nested object with nested types.
• Soft deletes. A deletedAt column and a shared notDeleted filter, which is the same pattern fetchedAt already uses.
• Test the validators. They're plain Zod objects with no database attached, so bookmarkInsertSchema.safeParse(...) in Vitest covers your API contract in milliseconds.
• A retry budget. An attempts column, incremented in the enrichment task, with lt(bookmarks.attempts, 3) in the predicate. Ten lines to a genuinely robust job runner.

Wrapping up

The whole data layer here is one schema file, one validator file, one connection file, and handlers that are mostly a single query each. Nothing is generated into a folder you have to regenerate, and there is no place where the database's idea of a bookmark and the API's idea of a bookmark can quietly diverge — because the second is computed from the first.

Three things to carry into your own build. Derive validators from tables rather than writing them twice, and .pick() the fields a client is allowed to touch instead of trusting it. Remember that a callback refinement extends a generated schema while a bare schema replaces it, nullability included. And treat the Date-to-string gap between handler and browser as real, because your types already do.

Sources

• Drizzle ORM — SQLite
• Drizzle ORM — Zod integration (documents the v1 drizzle-orm/zod import path)
• Drizzle Kit — migrations overview
• Nitro — Tasks
• Nuxt — Data fetching
• Zod
• better-sqlite3