---
name: codebooks-database
description: "Use this skill whenever the user is adding data persistence, designing tables, writing queries, picking an ORM, or planning relationships. Apply the four-question framework before writing any schema."
---

# Database

Beginners jump to "create a users table" and figure out the rest later.
That works for the first table and falls apart on the second. This skill
teaches the planning step that prevents redoing it.

## When to use this
- The user is adding data persistence to their app (any form).
- The user is unsure what tables they need.
- The user has tables but is hitting "how do I link X to Y" questions.
- The user is choosing between SQL/NoSQL or different ORMs.

## Stack-aware

This skill works for two common database paths:
- **Stack A (typed ORM):** PostgreSQL on Supabase/Neon + Drizzle or Prisma. Best when you want migrations, type-safe queries, and a schema file as the source of truth.
- **Stack B (no ORM):** Supabase + raw `@supabase/supabase-js`. Best when Supabase is your whole backend (auth + DB + storage). You get types from `generate-types` and joins from the SDK. Many indie projects skip the ORM entirely — see "Skip the ORM with Supabase" below.

Both work in production. Stack A scales to bigger codebases; Stack B is faster to ship and less code to maintain.

## The opinionated stack (2026)

| Concern | Pick | Why |
|---|---|---|
| Database | **PostgreSQL** | Best general-purpose, scales from hobby to enterprise |
| Hosting | **Supabase** or **Neon** | Free tier, instant setup, branching |
| ORM | **Drizzle** | Lightweight, type-safe, SQL-like, no codegen step |
| Alt ORM | **Prisma** | More features, larger, if you prefer the schema DSL |
| Migrations | **Drizzle Kit** or **Prisma Migrate** | Version-controlled schema changes |

**Skip these unless you have a specific reason:**
- MongoDB / DynamoDB — schemaless flexibility you don't need yet
- PlanetScale / Vitess — overkill until you have millions of users
- Raw SQL clients — you'll regret skipping the ORM at the first refactor

## The four-question framework

Before writing any schema, answer these four in plain English:

### 1. What "things" does the app care about?
List every noun: User, Recipe, Comment, Tag, Order, Booking. Each distinct
noun is probably a table.

### 2. What does each thing need to remember?
For each noun, list the facts you store:

> Recipe needs: a title, a description, an image, ingredients, cooking time,
> a difficulty rating, who created it, when it was created.

Each fact becomes a column. Pick a type:
- Short text (titles, names) → `text` or `varchar`
- Long text (descriptions) → `text`
- Numbers (whole) → `integer` / `bigint`
- Numbers (decimals) → `numeric` (NEVER `float` for money)
- Yes/no → `boolean`
- Dates → `timestamptz` (always with timezone)
- IDs → `uuid` (safer than auto-increment for public APIs)
- Files → `text` (store the URL, not the file)
- JSON blob → `jsonb`
- Enums → `text` with a check constraint, OR a Postgres enum type

### 3. How do the things relate?
Draw arrows:
- One-to-many: One user has many recipes
- Many-to-many: Recipes have many tags, tags belong to many recipes
- One-to-one: One user has one profile

Each arrow becomes either:
- A foreign key column (one-to-many): `recipes.user_id → users.id`
- A join table (many-to-many): `recipe_tags(recipe_id, tag_id)`

### 4. What does the app need to look up?
Think about queries. "Show me all recipes by this user." "Show me public
recipes tagged 'vegetarian'." Plan indexes on:
- Every foreign key
- Every column you'll filter or sort by
- Every column you'll search

## Worked example: a recipe app

```sql
-- users
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text UNIQUE NOT NULL,
  display_name text,
  avatar_url text,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- recipes
CREATE TABLE recipes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title text NOT NULL,
  description text,
  image_url text,
  cooking_time_minutes integer,
  difficulty text CHECK (difficulty IN ('easy', 'medium', 'hard')),
  is_public boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX recipes_user_id_idx ON recipes(user_id);
CREATE INDEX recipes_is_public_idx ON recipes(is_public) WHERE is_public = true;

-- tags (many-to-many with recipes)
CREATE TABLE tags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text UNIQUE NOT NULL
);

CREATE TABLE recipe_tags (
  recipe_id uuid NOT NULL REFERENCES recipes(id) ON DELETE CASCADE,
  tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (recipe_id, tag_id)
);
CREATE INDEX recipe_tags_tag_id_idx ON recipe_tags(tag_id);
```

## The Drizzle prompt

```
I'm using Drizzle with PostgreSQL on Supabase. Create a schema.ts for these
tables: [paste schema]. Use uuid primary keys with default sql`gen_random_uuid()`.
Add createdAt with default sql`now()`. Define relations using Drizzle's
relations() helper. Add indexes on every foreign key. After the schema, give
me the drizzle-kit generate and migrate commands.
```

## The Supabase prompt

```
I'm using Supabase. Create a SQL migration for these tables: [paste schema].
Use uuid primary keys with default gen_random_uuid(). Add timestamps with
default now(). Add foreign key constraints with ON DELETE CASCADE where
appropriate. Add indexes on every foreign key. Enable Row Level Security on
every table. Write one example RLS policy per table — users can read public
recipes and only modify their own.
```

## Skip the ORM with Supabase [Stack B]

If your whole backend is Supabase (auth + DB + storage), you can skip
Drizzle/Prisma entirely and use `@supabase/supabase-js` directly. This is
what most indie projects do — fewer dependencies, less code to maintain,
and you get types from Supabase's type generator.

**Step 1 — Generate types from your live schema:**
```bash
npx supabase gen types typescript --project-id <your-project-id> > src/types/database.types.ts
```

**Step 2 — Create a typed client** in `src/lib/supabase.ts`:
```ts
import { createClient } from "@supabase/supabase-js";
import type { Database } from "@/types/database.types";

export const supabase = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
);
```

**Step 3 — Write targeted query helpers** instead of one giant client. One
function per use case keeps the call sites clean and the queries optimal:

```ts
// src/lib/products.ts
import { supabase } from "./supabase";

export async function getAllProducts() {
  const { data, error } = await supabase
    .from("products")
    .select("*")
    .order("name");
  if (error) throw error;
  return data;
}

export async function getProductBySlug(slug: string) {
  const { data, error } = await supabase
    .from("products")
    .select(`
      *,
      brand:brands(id, name, slug),
      reviews(id, rating, body, created_at, user:user_profiles(username))
    `)
    .eq("slug", slug)
    .single();
  if (error) throw error;
  return data;
}

export async function getProductsByCategory(category: string) {
  const { data, error } = await supabase
    .from("products")
    .select("id, name, slug, image_url, brand:brands(name)")
    .eq("category", category)
    .order("name");
  if (error) throw error;
  return data ?? [];
}
```

**The big wins of this pattern:**
- **Joins via the SDK**: `select('*, brand:brands(*)')` does an inner join
  and returns nested data — no SQL needed.
- **Targeted columns**: `select('id, name, slug')` is faster than `select('*')`
  and reduces payload size.
- **One file per resource**: `lib/products.ts`, `lib/brands.ts`,
  `lib/articles.ts` — each owns its queries.
- **Server-side everywhere**: import these into Server Components, Server
  Actions, and Route Handlers. They run on the server with your service-role
  key (or the anon key + RLS).

**For complex queries** that the SDK can't express, use a Postgres function
and call it via `.rpc()`:
```sql
-- migration: 20260407_top_rated_products.sql
CREATE FUNCTION top_rated_products(min_reviews int)
RETURNS SETOF products AS $$
  SELECT p.*
  FROM products p
  JOIN reviews r ON r.product_id = p.id
  GROUP BY p.id
  HAVING COUNT(r.id) >= min_reviews
  ORDER BY AVG(r.rating) DESC
  LIMIT 20;
$$ LANGUAGE sql STABLE;
```
```ts
const { data } = await supabase.rpc("top_rated_products", { min_reviews: 5 });
```

## Row Level Security (Supabase essential)

Without RLS, anyone with your anon key can read every row of every table.
**Always enable RLS** and write policies:

```sql
ALTER TABLE recipes ENABLE ROW LEVEL SECURITY;

-- Anyone can read public recipes
CREATE POLICY "public recipes are visible to all"
  ON recipes FOR SELECT
  USING (is_public = true);

-- Users can read their own recipes (even private ones)
CREATE POLICY "users can read their own recipes"
  ON recipes FOR SELECT
  USING (auth.uid() = user_id);

-- Users can only insert their own recipes
CREATE POLICY "users can insert their own recipes"
  ON recipes FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Users can only update their own recipes
CREATE POLICY "users can update their own recipes"
  ON recipes FOR UPDATE
  USING (auth.uid() = user_id);

-- Users can only delete their own recipes
CREATE POLICY "users can delete their own recipes"
  ON recipes FOR DELETE
  USING (auth.uid() = user_id);
```

## Migrations

Always commit migrations to git. Never edit production schema by hand.

**With Drizzle:**
```bash
npx drizzle-kit generate    # creates a migration file from schema changes
npx drizzle-kit migrate     # applies pending migrations
```

**With Prisma:**
```bash
npx prisma migrate dev --name add_recipes_table
npx prisma migrate deploy   # in production
```

## Common mistakes to avoid

- **Storing arrays as comma-separated strings.** Use a join table.
- **Mixing user data with admin data in one table.** Split them.
- **Forgetting cascade behavior.** What happens when a user is deleted? Their recipes — cascade or set null? Decide upfront.
- **Skipping RLS in Supabase.** Without it, every authenticated user can read everything.
- **Storing files in the database.** Use object storage; store the URL.
- **Using `float` for money.** Use `numeric(10, 2)` or store cents as integer.
- **No timestamps.** Always add `created_at` and `updated_at`.
- **Auto-increment integer IDs in public APIs.** Use UUIDs to avoid leaking row counts.
- **Forgetting indexes.** Every foreign key needs one. Every filter column needs one.
- **Editing prod schema by hand.** Always go through migrations.

## Going deeper
- Database Design: https://www.codebooks.ai/database-design
- Backend: https://www.codebooks.ai/backend


---

This skill is part of the **CodeBooks Vibe Coding Skills Library**.
Browse all skills, install guides, and the source chapters at
https://www.codebooks.ai/skills