Database: Postgres and migrations

// NEVER: the value becomes part of the SQL text.
await db.$queryRawUnsafe(`SELECT * FROM app_my_app_bookings WHERE note = '${q}'`);
await db.$queryRaw(Prisma.raw('… ' + q));
const sql = 'SELECT … WHERE id = ' + id;

import {Hono} from 'hono';

import {problem, requireAuth, withDb, withViewer} from '../../../platform/guards';

type Booking = {id: number; starts_at: Date; status: string; note: string | null};

export const routes = new Hono().get('/bookings/:id', withViewer, requireAuth, withDb, async c => {
  const id = Number(c.req.param('id'));
  if (!Number.isSafeInteger(id) || id < 1) return c.json(problem('not_found', 'No such booking.'), 404);

  // The owner is IN the query: another person's id finds nothing, so it answers 404.
  const [booking] = await c.var.db.$queryRaw<Booking[]>`
    SELECT id, starts_at, status, note
    FROM app_my_app_bookings
    WHERE id = ${id} AND user_id = ${c.var.viewer.id!}
    LIMIT 1`;
  if (!booking) return c.json(problem('not_found', 'No such booking.'), 404);
  return c.json({booking});
});

// Insert and get the new id back in the same round trip.
const [created] = await db.$queryRaw<{id: number}[]>`
  INSERT INTO app_my_app_bookings (user_id, service_id, starts_at, note)
  VALUES (${me}, ${serviceId}, ${startsAt}, ${note})
  RETURNING id`;

// $executeRaw answers how many rows changed. A conditional update is the whole check:
// someone else's booking, or one that is already cancelled, changes nothing.
const changed = await db.$executeRaw`
  UPDATE app_my_app_bookings
  SET status = 'cancelled', updated_at = now()
  WHERE id = ${id} AND user_id = ${me} AND status = 'booked'`;
if (changed === 0) return c.json(problem('not_found', 'No such booking.'), 404);

import {Prisma} from '../../../generated/prisma/client';

// Prisma.join turns the list into bound parameters: IN ($1, $2, $3).
// It throws on an empty list, and a request may send at most 100 ids.
if (ids.length === 0 || ids.length > 100) {
  return c.json(problem('invalid_ids', 'Send 1 to 100 ids.'), 422);
}
const services = await db.$queryRaw<{id: number; title: string}[]>`
  SELECT id, title
  FROM app_my_app_services
  WHERE id IN (${Prisma.join(ids)})
  LIMIT 100`;

type Row = {service_id: number; title: string; bookings: bigint; next_start: Date | null};

// One query instead of "load the services, then count each one's bookings".
const rows = await db.$queryRaw<Row[]>`
  SELECT s.id AS service_id, s.title,
         count(b.id) AS bookings,
         min(b.starts_at) FILTER (WHERE b.starts_at > now()) AS next_start
  FROM app_my_app_services s
  LEFT JOIN app_my_app_bookings b ON b.service_id = s.id AND b.status = 'booked'
  GROUP BY s.id, s.title
  ORDER BY s.title
  LIMIT 50`;

// count() comes back as a BigInt, and JSON.stringify throws on a BigInt.
return c.json({services: rows.map(r => ({...r, bookings: Number(r.bookings)}))});

// Either convert in code…
const total = Number(row.bookings);          // safe below 2^53
const id = row.id.toString();                // a BIGSERIAL id, sent as a string
// …or cast in SQL, so it comes back as a number.
SELECT count(*)::int AS bookings FROM …

import {Prisma} from '../../../generated/prisma/client';

const PAGE = 20;
const before = Number(c.req.query('before') ?? '');
const cursor = Number.isSafeInteger(before) && before > 0 ? before : null;

// Index (user_id, id) covers this: no rows are read and thrown away, however deep the page.
const rows = await db.$queryRaw<{id: number; starts_at: Date; status: string}[]>`
  SELECT id, starts_at, status
  FROM app_my_app_bookings
  WHERE user_id = ${me} ${cursor ? Prisma.sql`AND id < ${cursor}` : Prisma.empty}
  ORDER BY id DESC
  LIMIT ${PAGE + 1}`;

const more = rows.length > PAGE;
return c.json({items: rows.slice(0, PAGE), nextBefore: more ? rows[PAGE - 1].id : null});

// A column or sort order can't be a parameter: pick it from a fixed map, never from input.
const SORT = {
  newest: Prisma.sql`created_at DESC, id DESC`,
  title: Prisma.sql`title ASC, id ASC`,
} as const;
const order = SORT[input.sort as keyof typeof SORT] ?? SORT.newest;
await db.$queryRaw`SELECT id, title FROM app_my_app_services ORDER BY ${order} LIMIT 50`;

BEGIN;

CREATE TABLE IF NOT EXISTS app_my_app_services (
  id          SERIAL PRIMARY KEY,
  title       VARCHAR(120) NOT NULL,
  minutes     INTEGER NOT NULL CHECK (minutes BETWEEN 5 AND 480),
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS app_my_app_bookings (
  id          SERIAL PRIMARY KEY,
  service_id  INTEGER NOT NULL REFERENCES app_my_app_services (id) ON DELETE RESTRICT,
  user_id     INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  starts_at   TIMESTAMPTZ NOT NULL,
  status      VARCHAR(16) NOT NULL DEFAULT 'booked' CHECK (status IN ('booked', 'cancelled')),
  note        VARCHAR(500),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One index per query shape: equality columns first, then the sort column.
CREATE INDEX IF NOT EXISTS app_my_app_bookings_user_idx
  ON app_my_app_bookings (user_id, id);
CREATE INDEX IF NOT EXISTS app_my_app_bookings_service_idx
  ON app_my_app_bookings (service_id, starts_at);

COMMIT;

-- Version 1.1, migration 20261201090000_app_my_app_customer_note: EXPAND
BEGIN;
ALTER TABLE app_my_app_bookings ADD COLUMN IF NOT EXISTS customer_note VARCHAR(500);
COMMIT;

-- Version 1.1, migration 20261201090100_app_my_app_customer_note_backfill: BACKFILL, one batch
UPDATE app_my_app_bookings SET customer_note = note
WHERE id IN (
  SELECT id FROM app_my_app_bookings
  WHERE customer_note IS NULL AND note IS NOT NULL
  LIMIT 5000
);

-- Version 1.3, a later migration once no version reads "note": CONTRACT
BEGIN;
ALTER TABLE app_my_app_bookings DROP COLUMN IF EXISTS note;
COMMIT;

# A throwaway Postgres (delete it and start again whenever you like)
docker run -d --name app-db -p 5432:5432 -e POSTGRES_PASSWORD=dev postgres:16

# .env in the template folder
DATABASE_URL=postgresql://postgres:dev@localhost:5432/postgres

npx prisma migrate deploy      # the site's migrations, then yours, in name order
npx prisma migrate deploy      # again: "No pending migrations to apply"
npm run dev

# Start from an empty database
docker rm -f app-db

# Integration tests run against a real Postgres and EMPTY its tables first.
TEST_DATABASE_URL=postgresql://postgres:dev@localhost:5432/postgres npm test