Database Setup

Configure Postgres and Drizzle ORM — the backbone for auth, billing, storage, tasks, and app tables.

The database is the center of a modern SaaS. Every real feature — authentication, billing, credits, storage, usage tracking, even demo modules — flows through the database. In Chinese, we sometimes joke we are “CRUD engineers”: create, read, update, delete is the core daily work. As the schema grows, treating the database as the foundation keeps the rest of the app predictable.

This guide puts the database first: it explains how the schema maps to features, how we do typed CRUD with Drizzle, and then how to connect, migrate, and validate.

Why the database comes first

System of record: Auth, billing, files, and tasks are persisted here and joined in services and APIs.
Schema → Types → UI: Drizzle models in src/db/schema.ts generate safe types that propagate across models and routes.
CRUD as a product surface: Most screens are read/write lists and detail views over tables. Designing tables well makes UI easy to ship.

Key domains in this template (tables → features)

Auth: users, sessions, accounts, verifications power Better Auth.
Billing & credits: orders, credits track purchases and credit ledger entries.
Storage: files stores S3/R2 object metadata and lifecycle (uploading → active → deleted).
Usage & AI tasks: tasks records queued/running/completed work with credits_trans_no back to the ledger.
Content: posts for blog/docs content snapshots.
Growth & feedback: affiliates, feedbacks.
Demo features: reservation_services, reservations showcase scheduling/checkout patterns.

CRUD-first patterns in code

Models live in src/models/* and expose typed helpers:
- src/models/file.ts: insertFile, findFileByUuid, updateFileByUuid, listFilesByUser, softDeleteFile.
- src/models/task.ts: insertTask, findTaskByUuid, getTasksByUserUuid, updateTaskStatus.
Services and API routes call these helpers; keep DB access centralized rather than scattered queries.
When adding a feature: model tables in src/db/schema.ts → pnpm drizzle-kit generate → pnpm drizzle-kit migrate → add src/models/<domain>.ts → call from src/services/* and routes.

1. What is Drizzle ORM?

We use Drizzle ORM as the type-safe layer between TypeScript and Postgres. The schema in src/db/schema.ts is the single source of truth. From that file we can:

Generate migration SQL with drizzle-kit
Run migrations against Postgres
Share types across the app (e.g. users.$inferSelect)

You do not edit SQL directly; you edit the schema file and let Drizzle create and apply migrations for you.

2. Pick your database scenario

There is no one-size-fits-all setup. Choose the scenario that best matches your stack and update .env accordingly.

Scenario	Recommendation
Local development only	Run Postgres via Docker (e.g. `docker run ... postgres:16`) and point `DATABASE_URL` to `postgres://postgres:postgres@localhost:5432/postgres`.
Managed Postgres (Neon, Supabase, Railway, Render…)	Grab the pooled connection string provided by the service. Make sure the IP you run migrations from is allowed.
Different DB per environment	Keep `.env` for local development, store production secrets in your hosting provider, and override `DATABASE_URL` there.
Existing Postgres with other tables	Drizzle will only touch the tables declared in `src/db/schema.ts`. Ensure table names do not clash and consider namespacing with schemas if needed.

Tip: Drizzle cannot create the database itself. Create the empty database first (with your provider UI or psql) before running migrations.

3. Configure environment variables

Define the connection string and Better Auth secrets. We read from .env, .env.local, and .env.development by default.

.env

DATABASE_URL="postgresql://user:password@host:5432/db?sslmode=require"
BETTER_AUTH_SECRET="$(openssl rand -base64 32)"
BETTER_AUTH_URL="http://localhost:3000"

Restart pnpm dev after changing env files so the process picks up the new values.

4. Understand the schema files

src/db/schema.ts – Drizzle models for Better Auth (users, sessions, accounts, verifications) and app tables (orders, credits, files, tasks, posts, affiliates, feedbacks, reservation_services, reservations).
src/db/config.ts – The drizzle-kit configuration pointing at the schema and Postgres URL.
src/lib/auth.ts – Maps Better Auth fields to the Drizzle tables. Update this file if you rename columns in the users table.

When you need new columns or tables, update schema.ts and, if relevant, the field mapping inside auth.ts. Prefer adding indexes in schema.ts (see uniqueIndex/index patterns in existing tables) so reads stay fast as data grows.

5. Generate migrations after code changes

Whenever schema.ts changes, run:

pnpm drizzle-kit generate --config src/db/config.ts

Expected output (example):

Reading config file 'src/db/config.ts'
10 tables
...
[✓] Your SQL migration file ➜ src/db/migrations/0002_add_user_flag.sql 🚀

The command creates two files:

src/db/migrations/<timestamp>_*.sql – the SQL that will run
src/db/migrations/meta/_journal.json – drizzle’s migration ledger

Commit both files to version control.

6. Apply migrations to Postgres

Run the generated SQL against your database:

pnpm drizzle-kit migrate --config src/db/config.ts

Typical success output:

Applying migrations from src/db/migrations
Migration 0002_add_user_flag.sql executed in 380 ms
All migrations applied!

If you see authentication errors, re-check DATABASE_URL. If the command hangs or times out, confirm that your IP is whitelisted (managed Postgres) or that the container is running (local Postgres).

7. Validate the result

Use any Postgres client to spot-check the new tables/columns. For example with psql:

\dt            -- list tables
SELECT * FROM sessions LIMIT 1;
SELECT email_verified FROM users LIMIT 1;

You should see the Better Auth tables plus the core app tables. As a quick CRUD smoke test, you can insert and fetch a row via a model helper (e.g., create a minimal files row through POST /api/storage/uploads, then SELECT * FROM files LIMIT 1;).

8. Try the auth flow

Once migrations are applied, restart pnpm dev, open /en/signup, and create a user. You should be redirected to the home page and a new record should appear in users and sessions.

9. Troubleshooting

Error: model "userss" not found – Ensure src/lib/auth.ts references the correct table name (users). Remove the usePlural option if you copied it from older snippets.
Column missing during sign-in – You changed schema.ts but forgot to run generate + migrate. Regenerate and apply the migration.
Cannot connect to database – Confirm the database exists, credentials are correct, SSL flags match, and the network allows inbound connections.
Need a clean slate – Drop the tables manually or use drizzle-kit’s sql to revert, then re-run generate and migrate.

Keep these steps in mind whenever you evolve the schema. Edit the code, generate migrations, apply them, and verify—you will avoid 500 errors in the auth flows.

Database Setup

On this page