Hono + Drizzle ORM + SQLite REST API (Vitest): working versions (tested 2026-10-15)

{
  "name": "hono-drizzle-sqlite-api",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "vitest run --reporter=default --reporter=json --outputFile=test-report.json"
  },
  "dependencies": {
    "hono": "4.13.13",
    "drizzle-orm": "0.45.4",
    "better-sqlite3": "13.0.3",
    "zod": "4.6.7",
    "@hono/zod-validator": "0.9.1"
  },
  "devDependencies": {
    "@types/node": "24.19.2",
    "vitest": "5.0.4",
    "@types/better-sqlite3": "9.6.0"
  }
}

import { Hono } from "hono";
import { zValidator } from "@hono/zod-validator";
import { z } from "zod";
import Database from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import { sqliteTable, integer, text } from "drizzle-orm/sqlite-core";
import { eq } from "drizzle-orm";

export const tasks = sqliteTable("tasks", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  done: integer("done", { mode: "boolean" }).notNull().default(false),
});

export function createApp(file = ":memory:") {
  const sqlite = new Database(file);
  sqlite.exec("create table if not exists tasks (id integer primary key autoincrement, title text not null, done integer not null default 0)");
  const db = drizzle(sqlite);
  const app = new Hono();
  const Id = z.object({ id: z.coerce.number().int().positive() });

  app.get("/tasks", (c) => c.json(db.select().from(tasks).all()));
  app.post("/tasks", zValidator("json", z.object({ title: z.string().min(1) })), (c) =>
    c.json(db.insert(tasks).values(c.req.valid("json")).returning().get(), 201));
  app.patch("/tasks/:id", zValidator("param", Id), zValidator("json", z.object({ title: z.string().min(1).optional(), done: z.boolean().optional() })), (c) => {
    const row = db.update(tasks).set(c.req.valid("json")).where(eq(tasks.id, c.req.valid("param").id)).returning().get();
    return row ? c.json(row) : c.json({ error: "not_found" }, 404);
  });
  app.delete("/tasks/:id", zValidator("param", Id), (c) => {
    const r = db.delete(tasks).where(eq(tasks.id, c.req.valid("param").id)).run();
    return r.changes ? c.body(null, 204) : c.json({ error: "not_found" }, 404);
  });
  return app;
}

import { describe, it, expect, beforeEach } from "vitest";
import { createApp } from "../src/app";

let app: ReturnType<typeof createApp>;
const json = (method: string, body: unknown) => ({ method, headers: { "content-type": "application/json" }, body: JSON.stringify(body) });
beforeEach(() => { app = createApp(); });

describe("tasks api", () => {
  it("creates, updates, lists and deletes", async () => {
    const created = await (await app.request("/tasks", json("POST", { title: "write recipe" }))).json();
    expect(created).toEqual({ id: 1, title: "write recipe", done: false });
    const updated = await (await app.request("/tasks/1", json("PATCH", { done: true }))).json();
    expect(updated.done).toBe(true);
    expect(await (await app.request("/tasks")).json()).toHaveLength(1);
    expect((await app.request("/tasks/1", { method: "DELETE" })).status).toBe(204);
  });

  it("validates body and params", async () => {
    expect((await app.request("/tasks", json("POST", { title: "" }))).status).toBe(400);
    expect((await app.request("/tasks/abc", json("PATCH", { done: true }))).status).toBe(400);
  });

  it("404s on missing rows", async () => {
    expect((await app.request("/tasks/99", json("PATCH", { done: true }))).status).toBe(404);
    expect((await app.request("/tasks/99", { method: "DELETE" })).status).toBe(404);
  });
});