template-react/docs/DATABASE.md

Database

One PostgreSQL database, app, exposed as the package appdb. Managed with Drizzle. There is no second database and no cache database.

src/databases/app_db/
├── drizzle.config.ts
├── package.json          exports ".", "./db", "./schema", "./*"
└── src/
    ├── db.ts             the connection
    ├── env.ts            which database this process may reach
    ├── index.ts          public surface: one `export * from './<feature>'` per line
    ├── schema.ts         what db:push creates — see below
    └── <feature>/
        ├── index.ts      what this feature exports, declared beside the code it describes
        ├── schema.ts     its tables
        └── queries.ts    everything that reads or writes them

A feature owns its own public surface

src/index.ts is one export * per feature and nothing else. What a feature exports is declared in its own index.ts.

Adding a query is then one file in one directory, rather than that file plus a hand-written list of every symbol in the package. Those lists drift: the platform's reached 297 lines with twelve features listed twice before it was replaced.

One directory per feature, holding both halves

Not parallel schema/ and queries/ trees. Those drift too — the platform ended up with four features named differently on each side, one with no query file and one with no schema file, and nothing structural said so.

A directory that is lopsided says so by its contents.

src/schema.ts is drizzle-kit's view, not the runtime's

drizzle.config.ts points at it, so commenting one line there removes a table from the DATABASE without removing a line of code. Every query imports its tables from ./schema inside its own feature directory, so the code still compiles.

That is what lets a fresh install create only the tables it needs, with optional ones commented out until something wants them.

Schema changes use push, not migrations

bun run db:push                      # -> app_dev
APP_ENV=production bun run db:push # -> app, deliberately

db:push diffs the schema code against the live database and alters it directly. Treat the schema code as the source of truth. db:gen writes files to migrations/, but nothing applies them — do not start reading those files as though they were the record.

The schema is the source of truth for what the database may CONTAIN

Not just for its shape. Where a column has a known set of legal values, say so with a check() rather than leaving it free text — otherwise a value the code stopped supporting sits there unnoticed, and nothing structural says it became illegal.

export const ORDER_STATUSES = ["pending", "paid", "shipped"] as const;

status: text("status").notNull().$type<(typeof ORDER_STATUSES)[number]>(),
// in the table's second argument:
check("orders_status_check", sql`${table.status} in ('pending', 'paid', 'shipped')`),

But text without a check where the set must stay open. A category, a provider name, anything a third party can add: an enum cannot contain a value nobody has compiled yet, and adding one must not be a schema change. The test is whether the set is closed by us or open to the world.

Which database a process reaches

Decided by APP_ENV and enforced in env.ts before a connection opens:

APP_ENV	Database
production	app (POSTGRES_URL, from .env.production.local)
development	app_dev (POSTGRES_URL_DEV, from .env)

Any other pairing throws. The check is on the database name, because host and credentials are usually shared and one word at the end of a long URL is all that separates them.

Things that bite, already handled here

The client is a per-process singleton on globalThis. bun --hot re-evaluates modules on every edit; without it each save builds a fresh pool and abandons the last, queries in flight during the swap never resolve or release their connection, and the pool eventually wedges — every route that reads hangs while stateless ones stay fast.

waitForDatabase() before startup work. postgres() is lazy, so nothing fails at import when the database is not up. Startup work that queries otherwise fires once, fails, and is swallowed by whatever .catch() it was written with.

Money is numeric, never a float, and it comes back as a string. Sums of binary floating point are wrong in ways nobody notices until an invoice.

Denormalise what is historical fact. A row pointing at configuration — a provider, a price list — should also record what it actually used, as text. The configuration can be renamed, repointed or deleted; what happened cannot.

Tests

Tests run against app_dev; the APP_ENV guard makes production unreachable from them.

Two rules learned expensively: clean up what you write, and restore what you disturb. A test that sets a "default" flag clears it on every other row, so deleting only your own rows afterwards is not enough — snapshot the previous value and put it back.