new-route

Step 1 — Schema (src/db/schema/<PLURAL>.ts)

Create the file. Follow this structure exactly — do not hand-write Zod schemas for DB columns, always derive them with drizzle-zod:

import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

export const <CAMEL_PLURAL> = pgTable('<PLURAL>', {
  id:        uuid('id').primaryKey().defaultRandom(),
  // Add resource-specific columns here
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

export const Select<PASCAL>Schema = createSelectSchema(<CAMEL_PLURAL>);
export const Insert<PASCAL>Schema = createInsertSchema(<CAMEL_PLURAL>).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});
export const Update<PASCAL>Schema = Insert<PASCAL>Schema.partial();

export type <PASCAL>    = typeof <CAMEL_PLURAL>.$inferSelect;
export type New<PASCAL> = typeof <CAMEL_PLURAL>.$inferInsert;

If the user hasn't specified what columns the resource needs, ask before creating the file. A schema with only id/createdAt/updatedAt is almost never right.

Then add to src/db/schema/index.ts:

export * from './<PLURAL>.js';

Step 2 — Migration

make db-generate   # creates a new file in migrations/
make migrate       # applies it

If make migrate fails, read the error before doing anything else — it usually means the Postgres container isn't running (make dev starts it) or a column constraint is wrong.

Step 3 — Service (src/services/<PLURAL>.ts)

All DB queries live here, never in route handlers. Follow the users.ts pattern exactly:

import { eq } from 'drizzle-orm';
import type { Logger } from 'pino';
import type { z } from 'zod';
import { db } from '@/db/index';
import { <CAMEL_PLURAL>, type Update<PASCAL>Schema } from '@/db/schema/index';
import type { <PASCAL>, New<PASCAL> } from '@/db/schema/index';

export async function findAll<PASCAL>s(logger: Logger): Promise<<PASCAL>[]> {
  logger.debug('fetching all <PLURAL>');
  return db.select().from(<CAMEL_PLURAL>);
}

export async function find<PASCAL>ById(id: string, logger: Logger): Promise<<PASCAL> | null> {
  logger.debug({ id }, 'fetching <SINGULAR> by id');
  const [row] = await db.select().from(<CAMEL_PLURAL>).where(eq(<CAMEL_PLURAL>.id, id));
  return row ?? null;
}

export async function create<PASCAL>(
  data: Omit<New<PASCAL>, 'id' | 'createdAt' | 'updatedAt'>,
  logger: Logger
): Promise<<PASCAL>> {
  logger.debug('creating <SINGULAR>');
  const [row] = await db.insert(<CAMEL_PLURAL>).values(data).returning();
  if (!row) throw new Error('Insert returned no rows');
  return row;
}

export async function update<PASCAL>(
  id: string,
  data: z.infer<typeof Update<PASCAL>Schema>,
  logger: Logger
): Promise<<PASCAL> | null> {
  logger.debug({ id }, 'updating <SINGULAR>');
  // Strip undefined values so drizzle doesn't receive explicit undefined fields.
  const patch = Object.fromEntries(
    Object.entries(data).filter(([, v]) => v !== undefined)
  ) as z.infer<typeof Update<PASCAL>Schema>;

  const [row] = await db
    .update(<CAMEL_PLURAL>)
    .set(patch)
    .where(eq(<CAMEL_PLURAL>.id, id))
    .returning();
  return row ?? null;
}

export async function delete<PASCAL>(id: string, logger: Logger): Promise<boolean> {
  logger.debug({ id }, 'deleting <SINGULAR>');
  const [deleted] = await db
    .delete(<CAMEL_PLURAL>)
    .where(eq(<CAMEL_PLURAL>.id, id))
    .returning({ id: <CAMEL_PLURAL>.id });
  return deleted !== undefined;
}

Step 4 — Route (src/routes/<PLURAL>.ts)

Use createRouter() — never new OpenAPIHono<AppEnv>() directly. Path params use OpenAPI style /{id}, not Express style /:id.

import { createRoute } from '@hono/zod-openapi';
import { z } from 'zod';
import {
  Insert<PASCAL>Schema,
  Select<PASCAL>Schema,
  Update<PASCAL>Schema,
} from '@/db/schema/index';
import { createRouter, jsonBody, jsonContent } from '@/lib/router';
import { ErrorSchema } from '@/lib/schemas';
import {
  create<PASCAL>,
  delete<PASCAL>,
  findAll<PASCAL>s,
  find<PASCAL>ById,
  update<PASCAL>,
} from '@/services/<PLURAL>';

const router = createRouter();

// z.string().uuid() is used here (vs z.uuid()) for compatibility with @hono/zod-openapi's
// path-param coercion, which expects a ZodString schema.
const IdParamSchema = z.object({ id: z.string().uuid() });

// GET /<PLURAL>
router.openapi(
  createRoute({
    method: 'get',
    path: '/',
    tags: ['<PASCAL>s'],
    summary: 'List <PLURAL>',
    responses: {
      200: jsonContent(z.array(Select<PASCAL>Schema), 'List of <PLURAL>'),
    },
  }),
  async (c) => {
    const result = await findAll<PASCAL>s(c.var.logger);
    return c.json(result, 200);
  }
);

// GET /<PLURAL>/:id
router.openapi(
  createRoute({
    method: 'get',
    path: '/{id}',
    tags: ['<PASCAL>s'],
    summary: 'Get a <SINGULAR>',
    request: { params: IdParamSchema },
    responses: {
      200: jsonContent(Select<PASCAL>Schema, 'The <SINGULAR>'),
      404: jsonContent(ErrorSchema, '<PASCAL> not found'),
    },
  }),
  async (c) => {
    const { id } = c.req.valid('param');
    const row = await find<PASCAL>ById(id, c.var.logger);
    if (!row) return c.json({ error: { message: '<PASCAL> not found' } }, 404);
    return c.json(row, 200);
  }
);

// POST /<PLURAL>
router.openapi(
  createRoute({
    method: 'post',
    path: '/',
    tags: ['<PASCAL>s'],
    summary: 'Create a <SINGULAR>',
    request: {
      body: jsonBody(Insert<PASCAL>Schema),
    },
    responses: {
      201: jsonContent(Select<PASCAL>Schema, 'Created <SINGULAR>'),
    },
  }),
  async (c) => {
    const body = c.req.valid('json');
    const row = await create<PASCAL>(body, c.var.logger);
    return c.json(row, 201);
  }
);

// PATCH /<PLURAL>/:id
router.openapi(
  createRoute({
    method: 'patch',
    path: '/{id}',
    tags: ['<PASCAL>s'],
    summary: 'Update a <SINGULAR>',
    request: {
      params: IdParamSchema,
      body: jsonBody(Update<PASCAL>Schema),
    },
    responses: {
      200: jsonContent(Select<PASCAL>Schema, 'Updated <SINGULAR>'),
      404: jsonContent(ErrorSchema, '<PASCAL> not found'),
    },
  }),
  async (c) => {
    const { id } = c.req.valid('param');
    const body = c.req.valid('json');
    const row = await update<PASCAL>(id, body, c.var.logger);
    if (!row) return c.json({ error: { message: '<PASCAL> not found' } }, 404);
    return c.json(row, 200);
  }
);

// DELETE /<PLURAL>/:id
router.openapi(
  createRoute({
    method: 'delete',
    path: '/{id}',
    tags: ['<PASCAL>s'],
    summary: 'Delete a <SINGULAR>',
    request: { params: IdParamSchema },
    responses: {
      204: { description: '<PASCAL> deleted' },
      404: jsonContent(ErrorSchema, '<PASCAL> not found'),
    },
  }),
  async (c) => {
    const { id } = c.req.valid('param');
    const deleted = await delete<PASCAL>(id, c.var.logger);
    if (!deleted) return c.json({ error: { message: '<PASCAL> not found' } }, 404);
    return c.body(null, 204);
  }
);

export default router;

Step 5 — Mount in src/app.ts

Add the import alongside the existing route imports and mount it with app.route():

import <CAMEL_PLURAL> from './routes/<PLURAL>';
// ...
app.route('/<PLURAL>', <CAMEL_PLURAL>);

Place the app.route() call in the block with the other routes — keep them alphabetically sorted.

Step 6 — Unit test (tests/unit/<PLURAL>.test.ts)

Mock the service module so these tests never touch the DB. Use the users.test.ts as the structural template — the key patterns are:

• vi.mock('../../src/services/<PLURAL>.js', () => ({...})) at the top
• Dynamic import after the mock: const { find<PASCAL>ById, update<PASCAL>, delete<PASCAL> } = await import('../../src/services/<PLURAL>.js');
• beforeEach(() => vi.clearAllMocks())
• Test HTTP shape, status codes, and 422 validation for bad inputs
• Use a hardcoded valid UUID for EXISTING_ID and the nil UUID 00000000-0000-0000-0000-000000000000 for MISSING_ID
• Include an OpenAPI spec test: verify /openapi.json has /<PLURAL> and /<PLURAL>/{id} paths

The mock return value for create<PASCAL> must include all schema columns (including id, createdAt, updatedAt) with plausible values.

Step 7 — Integration test (tests/integration/<PLURAL>.test.ts)

import { afterAll, beforeEach, describe, expect, it } from 'vitest';
import app from '../../src/app.js';
import { closeTestDb, truncateAll } from './db.js';

afterAll(closeTestDb);
beforeEach(truncateAll);

// Test the full CRUD lifecycle against a real Postgres container.
// Pattern: POST to create → use returned id for subsequent requests.

Cover: create → 201, list → contains created item, get by id → 200, update → field changed, delete → 204 then 404.

Step 8 — Verify

make ci

This runs lint + typecheck + all tests. Fix any errors before considering the task done. Common issues:

• Forgot .js extension on imports (required for ESM)
• delete is a reserved keyword in JS — name the service function delete<PASCAL> but be aware TypeScript will still accept it as a named export; if you get issues, alias it on import: import { delete<PASCAL> as remove<PASCAL> } from ...
• Migration not applied → make migrate again
• Type errors on Update<PASCAL>Schema → confirm you're importing the type, not the value, when only the type is needed

Non-obvious conventions

• No hand-written Zod schemas for DB columns. createSelectSchema / createInsertSchema from drizzle-zod is the only source of truth.
• Error shape is always { error: { message: "..." } }. Never a bare string.
• Use c.var.logger for any logging inside route handlers — it carries the requestId automatically.
• c.body(null, 204) for DELETE success, not c.json(null, 204).
• /{id} not /:id in OpenAPI path strings.
• z.string().uuid() for path param schemas, not z.uuid() — the @hono/zod-openapi param coercion expects a ZodString.