My backend conventions

Backend conventions (Nuxt / Nitro)

These are defaults for personal projects. Adjust per project when a project's needs differ.

Database

• Turso (libSQL) with Drizzle ORM. SQLite dialect: drizzle-orm/libsql + sqliteTable, not the Postgres or MySQL cores.
• Define schema in Drizzle, derive validation with drizzle-zod.
• Migrations with drizzle-kit.

Migrations must be idempotent and crash-resistant

Every migration must be safe to re-run and must complete even after a partial failure or a crash, so running it again always drives the schema to the target state no matter where a prior run stopped. Never write a migration that only works against one exact starting state.

• Use IF NOT EXISTS / IF EXISTS wherever the SQLite dialect supports it: CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, DROP TABLE IF EXISTS, DROP INDEX IF EXISTS.
• SQLite does not support IF [NOT] EXISTS on ALTER TABLE ADD COLUMN or DROP COLUMN, so guard those instead. Read PRAGMA table_info(<table>) first and skip the statement when the column already matches the target, or run through a runner that catches the benign duplicate column name and no such column errors and continues rather than aborting the whole migration.
• Make backfills re-runnable. Use INSERT ... WHERE NOT EXISTS or INSERT OR IGNORE so a second run does not duplicate rows.
• Apply through a runner that executes statement by statement and continues past statements that are already satisfied, so a crashed migration finishes cleanly on the next run rather than getting stuck halfway.

Validation

• Zod for all input validation. Validate at the server boundary (request body, params, query) before touching the database.

Server routes

• Nitro server routes under server/. Keep handlers thin: validate, call a small typed function, return. Push reusable logic into server/utils.

List endpoints

List endpoints paginate, sort, and search on the server, never on the client. The client sends the page, page size, sort column, sort direction, and search term as query params, and the endpoint returns only the rows for that page plus a total count. Client-side sorting or filtering only reorders the rows already loaded, so it silently breaks the moment the list spans more than one page. Do the work where the whole dataset lives.

• Accept page, pageSize, sort, order, and search as validated query params. Use z.coerce.number() for the numeric ones with sane defaults and a max page size.
• Whitelist the sortable columns with a Zod enum. Never sort by a raw column name taken from the query string.
• Return the page rows plus a total count so the client can render pagination without loading everything.
• Push the paging, sorting, and filtering against the full dataset. When a list is assembled in memory from more than one source, filter and sort the merged set before slicing the page, not after.

Auth

• Owner-managed auth with nuxt-auth-utils. No public signup and no third-party identity providers unless a project explicitly needs them.

Email

• Resend for transactional email.

Tooling

• Bun for scripts and seeding.