← Goudbeek studio
DataBeginner~1 hour

Set up a database

You need somewhere to store the data your app creates — users, posts, orders — that survives restarts and can be queried reliably. Getting this right early saves you from painful migrations and data corruption once real users show up.

the approach

In 2026 you almost never install or run a database server yourself. You rent managed Postgres (Neon or Supabase), define your schema as code with a typed ORM (Drizzle), and evolve it with generated migration files checked into git. Postgres is the default answer — reach for anything else only with a concrete reason. The mental model that keeps you sane: your git repo is the source of truth for the schema, and the database is just where that schema currently lives. You never click around a dashboard to change structure; you change code, generate a migration, and apply it.

Step by step

  1. 1

    Rent managed Postgres, don't host it

    Create a project on Neon (great autoscaling + database branching) or Supabase (Postgres plus built-in auth and storage). Copy the *pooled* connection string into DATABASE_URL in .env.local. Don't self-host in a container — you'll spend your afternoon on backups and pooling instead of your app.

  2. 2

    Install a typed ORM

    Use Drizzle: `npm i drizzle-orm && npm i -D drizzle-kit` plus your driver (e.g. `@neondatabase/serverless` for Neon). Drizzle gives you type-safe queries that stay close to SQL. Prisma is a fine alternative if you prefer a higher-level API; pick one and move on.

  3. 3

    Define your schema in code

    Create db/schema.ts and model your real entities as tables. Use uuid primary keys, a not-null createdAt timestamp, and explicit constraints (unique email, not-null where it matters). Start with fewer tables than you think you need — you'll add columns via migrations.

  4. 4

    Wire up one shared client

    Create db/index.ts that builds a single drizzle instance from your connection string and exports it. Import that one `db` everywhere. In serverless (Vercel), use the serverless/HTTP driver so you don't exhaust connections spinning up a pool per request.

  5. 5

    Set up migrations from day one

    Add drizzle.config.ts, then run `drizzle-kit generate` to turn schema changes into SQL files, and `drizzle-kit migrate` to apply them. Commit the generated migration files. Use `drizzle-kit push` only for throwaway prototypes — never against a database with real data.

  6. 6

    Seed and test against a dev branch

    Write a small seed script for sample data, and point it at a separate dev database or a Neon/Supabase branch — not production. Verify a couple of inserts and selects actually round-trip before you build features on top.

Starter snippet

typescript
// db/schema.ts
import { pgTable, uuid, text, timestamp } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  name: text("name"),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});

// db/index.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";

const sql = neon(process.env.DATABASE_URL!); // pooled connection string
export const db = drizzle(sql, { schema });

// usage anywhere
// const [user] = await db.insert(schema.users)
//   .values({ email: "sam@example.com" }).returning();

// then: npx drizzle-kit generate && npx drizzle-kit migrate

✕ Watch out for

  • Using a direct (non-pooled) connection in a serverless app. Each request opens a new connection and you hit 'too many connections' under any real traffic. Use the pooled connection string — Neon's serverless driver, or Supabase's transaction pooler on port 6543.
  • Editing tables by hand in the dashboard. Your code and database drift apart, and the next `generate` produces a broken diff. Every structural change goes through a migration.
  • Running `drizzle-kit push` against production. It syncs schema without a reviewable migration and can silently drop columns and data. Use generate + migrate in anything that holds real data.
  • Using SQLite locally but Postgres in production. Subtle dialect differences (types, defaults, constraints) bite you at deploy time. Run the same engine everywhere — a Postgres dev branch costs nothing.
  • Committing DATABASE_URL to git. That string is a full credential. Keep it in .env.local (gitignored) and in your host's env settings.

✓ Pro tips

  • When you hand this to an AI coding agent, be specific about the stack: name the provider and exact driver ('Neon with drizzle-orm/neon-http', or 'Supabase transaction pooler'), say you want generated migrations not push, and paste your real entities. Then verify three things in its output: it created an actual migration file, it used the pooled connection string, and it didn't quietly swap Drizzle for Prisma. Ask it to run `drizzle-kit generate` and show you the SQL diff before applying.
  • Turn on database branching (Neon or Supabase) so each PR/preview deploy gets its own isolated copy of the data — you can test destructive migrations without fear.
  • Add indexes as you add queries, not before. At minimum, index every foreign key and any column you filter or sort on frequently; unique constraints on things like email double as data-integrity guards.
← PreviousAdd search to your appNext →Add realtime updates