Skip to content

DATABASE.md

Latest commit

 

History

History
360 lines (267 loc) · 8.42 KB

DATABASE.md

File metadata and controls

360 lines (267 loc) · 8.42 KB

Database Configuration Guide

This document explains the database setup using Next.js 15, DrizzleORM, and Supabase.

📁 Directory Structure

src/db/
├── index.ts           # Database connection and configuration
├── migrate.ts         # Migration runner script
├── seed.ts           # Database seeding script
├── utils.ts          # Database utility functions
├── migrations/       # Generated SQL migrations
├── schemas/          # Drizzle schema definitions
│   ├── index.ts
│   ├── user.ts
│   ├── team.ts
│   └── player.ts
├── queries/          # Reusable database queries
│   ├── teams.ts
│   └── players.ts
└── seeds/            # Seed data and scripts

🔧 Configuration

Environment Variables

Required environment variables (see .env.example):

# Supabase Database URL (use Transaction pooler for production)
DATABASE_URL=postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres

# Supabase API Configuration
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your_anon_key
SUPABASE_SERVICE_ROLE_KEY=your_service_role_key

Database Connection (src/db/index.ts)

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "@/db/schemas";

// Optimized for Supabase Transaction pooling mode
const connection = postgres(DATABASE_URL, {
  prepare: false,        // Required for Supabase
  max: 10,               // Connection pool size
  idle_timeout: 20,      // Idle timeout in seconds
  max_lifetime: 60 * 30, // Max connection lifetime (30 min)
});

export const db = drizzle(connection, { schema });

📊 Schema Design Best Practices

1. Use Timestamps with Date Mode

import { timestamp } from "drizzle-orm/pg-core";

// ✅ GOOD - Returns Date objects
createdAt: timestamp("created_at", { 
  mode: "date", 
  withTimezone: true 
}).notNull().defaultNow(),

// ✅ GOOD - Auto-updates on record update
updatedAt: timestamp("updated_at", { 
  mode: "date", 
  withTimezone: true 
}).notNull().defaultNow().$onUpdate(() => new Date()),

// ❌ BAD - Returns strings, harder to work with
createdAt: timestamp("created_at", { mode: "string" }).notNull().defaultNow(),

2. Define Relations for Type-Safe Joins

import { relations } from "drizzle-orm";

export const teamRelations = relations(team, ({ many }) => ({
  players: many(player),
}));

export const playerRelations = relations(player, ({ one }) => ({
  team: one(team, {
    fields: [player.teamId],
    references: [team.id],
  }),
}));

3. Export Type Inference

export type UserRow = typeof user.$inferSelect;
export type NewUserRow = typeof user.$inferInsert;

🔍 Query Patterns

Using React cache() for Request Deduplication

import { cache } from "react";

// ✅ Cached queries are deduplicated within the same request
export const getTeamById = cache(async (id: string) => {
  return await db.query.team.findFirst({
    where: eq(team.id, id),
    with: { players: true },
  });
});

Pagination Support

import { calculatePagination, type PaginationOptions } from "@/db/utils";

export async function getTeams(options?: PaginationOptions) {
  const total = await getTeamsCount();
  const { offset, limit, pagination } = calculatePagination(
    total,
    options?.page,
    options?.pageSize
  );

  const data = await db.query.team.findMany({
    limit,
    offset,
    orderBy: [desc(team.createdAt)],
  });

  return { data, pagination };
}

Search with ILIKE

import { ilike, or } from "drizzle-orm";

export async function searchTeams(query: string) {
  const searchTerm = `%${query}%`;
  
  return await db.query.team.findMany({
    where: or(
      ilike(team.name, searchTerm),
      ilike(team.country, searchTerm),
      ilike(team.league, searchTerm)
    ),
  });
}

🚀 Migration Workflow

1. Generate Migration

pnpm db:generate

This creates a SQL migration file in src/db/migrations/ based on schema changes.

2. Review Migration

Always review the generated SQL before applying:

-- Example: src/db/migrations/0001_add_player_stats.sql
CREATE TABLE IF NOT EXISTS "player_stats" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "player_id" uuid NOT NULL REFERENCES "players"("id"),
  "goals" integer DEFAULT 0,
  "assists" integer DEFAULT 0
);

3. Apply Migration

# Local development
pnpm db:migrate

# Production (use Supabase Dashboard or CI/CD)

4. Seed Database (Development Only)

pnpm db:seed

🛡️ Error Handling

Use the utility functions from src/db/utils.ts:

import { 
  isUniqueConstraintError, 
  getDatabaseErrorMessage 
} from "@/db/utils";

try {
  await db.insert(team).values(newTeam);
} catch (error) {
  if (isUniqueConstraintError(error)) {
    return { error: "Team name already exists" };
  }
  
  return { error: getDatabaseErrorMessage(error) };
}

🔐 Security Best Practices

1. Never Import Database Queries in Client Components

// ❌ BAD - Client component importing server-only queries
"use client";
import { getTeams } from "@/db/queries/teams"; // ERROR!

// ✅ GOOD - Use Server Actions or Server Components
// In Server Component:
export default async function TeamsPage() {
  const teams = await getTeams();
  return <TeamList teams={teams} />;
}

// Or in Server Action:
"use server";
import { getTeams } from "@/db/queries/teams";

export async function getTeamsAction() {
  return await getTeams();
}

2. Use Supabase Row Level Security (RLS)

While DrizzleORM handles application-level queries, always enable RLS in Supabase for defense-in-depth:

-- Enable RLS on tables
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;

-- Example policy
CREATE POLICY "Users can view all teams"
ON teams FOR SELECT
TO authenticated
USING (true);

3. Validate Input in Server Actions

"use server";
import { z } from "zod";

const TeamSchema = z.object({
  name: z.string().min(1).max(100),
  country: z.string().min(2).max(100),
});

export async function createTeam(formData: FormData) {
  const data = TeamSchema.parse({
    name: formData.get("name"),
    country: formData.get("country"),
  });
  
  return await db.insert(team).values(data);
}

🎯 Performance Tips

1. Use Connection Pooling

Always use Supabase's Transaction Mode Pooler for production:

postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres

2. Limit Returned Columns

// ✅ GOOD - Select only needed columns
const teams = await db
  .select({ id: team.id, name: team.name })
  .from(team);

// ❌ BAD - Returns all columns when you only need a few
const teams = await db.query.team.findMany();

3. Use Indexes for Frequently Queried Columns

import { index } from "drizzle-orm/pg-core";

const player = pgTable("players", {
  // ... fields
}, (table) => ({
  teamIdIdx: index("player_team_id_idx").on(table.teamId),
  emailIdx: index("player_email_idx").on(table.email),
}));

4. Batch Operations

// ✅ GOOD - Single batch insert
await db.insert(team).values([team1, team2, team3]);

// ❌ BAD - Multiple individual inserts
for (const t of teams) {
  await db.insert(team).values(t);
}

📚 Additional Resources

🆘 Common Issues

Issue: "prepared statement already exists"

Solution: Ensure prepare: false in connection config (already configured).

Issue: "Too many connections"

Solution:

  1. Use Supabase Transaction pooler URL
  2. Reduce max connections in config
  3. Ensure connections are properly closed in migration/seed scripts

Issue: "relation does not exist"

Solution:

  1. Run pnpm db:generate to generate migrations
  2. Run pnpm db:migrate to apply migrations
  3. Check if you're connected to the correct database

Last Updated: October 2025