my-library

Database

Three tables on Neon Postgres, two connection strings, and a client that refuses to connect until someone actually asks it to.

The tables

Table	Holds
`users`	One row. A UUID, a username, timestamps. There is no sign-up; rows are minted by script.
`api_tokens`	SHA-256 hashes of bearer tokens, keyed by hash, with a revoked flag. The plaintext is printed once at mint time and never stored.
`books`	The library. 3,367 rows, every one owned by a user id.

db/schema.ts (books)

id                     bigint identity, primary key
userId                 text → users.id            the tenant scope
title, author          text, not null             the only required facts
isbn                   text
description            text                       OpenLibrary work description
openlibraryId          text                       e.g. OL27482W — the stable key
communityRating        real                       OpenLibrary average, 0–5
communityRatingsCount  integer                    how many people rated it
thumbnailUrl           text                       …-M.jpg, for the shelf
coverUrl               text                       …-L.jpg, for one book
publisher              text
publishedYear          integer
pageCount              integer
createdAt, updatedAt   timestamptz, not null

indexes  (userId, id)  (userId, updatedAt)  (userId, openlibraryId)

Every index leads with userId. That is not scale — it is one shelf — it is so that a query which forgets to scope cannot accidentally be the fast one. The third index exists for the import, which dedupes on OpenLibrary work id before inserting.

Two columns that are not there

A personal rating. The only score stored is OpenLibrary’s community average, and the column names say so — communityRating, not rating. The distinction was worth encoding: a number that means “strangers liked this” should not be mistakable for “I liked this”.

A reading status. A status want_to_read | reading | read column was designed and built, then cut before any data existed. Removing it early cost one migration regeneration; leaving it would have meant an unused, always-default column in every API response and all three clients.

Two connection strings

Variable	Host	Used by
`DATABASE_URL`	pooled — contains `-pooler`	Every runtime query, from route handlers and Server Components alike.
`DIRECT_URL`	direct — no -pooler	drizzle-kit only: migrations and studio.

prepare: false is not optional

The pooled endpoint is PgBouncer in transaction mode, which cannot hold prepared statements across a pooled connection. Leaving the driver default on produces errors that look like random query failures under concurrency. DDL goes over the direct string because schema changes through a transaction pooler are a bad idea for the same reason.

The client is a lazy proxy

db/client.ts

export const db = new Proxy({} as Database, {
  get(_t, prop, receiver) {
    const real = resolveDb();          // first touch opens the connection
    const value = Reflect.get(real as object, prop, receiver);
    return typeof value === "function" ? value.bind(real) : value;
  },
}) as Database;

Three problems solved by one indirection:

Importing is free. A module can import db without opening a socket. On a serverless platform that matters — plenty of invocations never touch the database.
Tests can substitute. setDatabase() injects pglite before first use, so route handlers run against real SQL with no server. See Testing.
Warm invocations reuse. The client is cached on globalThis in development so hot reload doesn’t leak connections.

Pool size is max: 1 with a 20s idle timeout. Each serverless invocation is single-threaded and short-lived, so a pool larger than one just holds Neon connections open for nothing.

Migrations

npm run db:generate   # diff schema.ts → SQL in db/migrations
npm run db:migrate    # apply over DIRECT_URL
npm run db:studio     # browse

Migrations are committed and applied by hand. Deploying does not run them: a Vercel build that migrates is a build that can half-migrate a production database on a bad push. The rule is migrate first, then deploy — and because the app and its schema ship together, the window where they disagree is a deliberate, supervised one.

What’s actually in there

	Rows	Share
Books	3,367	—
Distinct authors	2,111	—
With a cover	3,018	90%
With a community rating	1,715	51%
With a description	1,662	49%

Those gaps are OpenLibrary’s, not the import’s — plenty of works simply have no description or rating on file. The UI treats every one of these as optional, which is why the shelf has a spine-like placeholder and the detail page omits sections rather than showing empty ones.