Database Setup

Learn how to work with db-mate, pg, and Supabase in this project.

Overview

Our project uses:

postgres.js - PostgreSQL client (used via @uwdsc/db; Supabase Transaction Pooler–compatible)
db-migrate - Database migration tool; migrations in packages/server/db
Supabase - PostgreSQL database, authentication, and storage
PostgreSQL - Relational database

Architecture

┌─────────────────────────────────────────┐
│     Application Layer                   │
│  (Next.js API Routes & Services)        │
└───────────┬─────────────────────────────┘
            │
┌───────────▼─────────────────────────────┐
│     @uwdsc/db (postgres.js)              │
│   Repositories extend BaseRepository     │
└───────────┬─────────────────────────────┘
            │
┌───────────▼─────────────────────────────┐
│     Supabase PostgreSQL                  │
│   (Database & Auth)                      │
└──────────────────────────────────────────┘

Setup

1. Environment Variables

Environment variables (including DATABASE_URL, SUPABASE_*, etc.) are synced via pull-secrets. From the repo root, run:

pnpm pull-secrets

This creates or updates .env.local in each app (web, admin) and loads the values needed for the database and Supabase. You do not need to create or edit these files manually. See Getting Started for Infisical/pull-secrets setup.

2. Database Migrations

Migrations are in packages/server/db. Run from repo root:

pnpm migrate — apply migrations
pnpm migrate:down — rollback last
pnpm migrate:create <name> — create new migration

Example migration file (in packages/server/db/src/migrations/):

-- packages/server/db: migration up file
 
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  bio TEXT,
  avatar VARCHAR(500),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);
 
CREATE INDEX idx_users_email ON users(email);

Down migration (matching .down.sql in packages/server/db):

DROP TABLE IF EXISTS users;

db-migrate Workflow

Migrations are in packages/server/db. Use root scripts (they load env and run db-migrate):

Create Migration

pnpm migrate:create add_user_bio

This creates migration files in packages/server/db.

Apply Migrations

pnpm migrate

Rollback / Reset / Status

pnpm migrate:down    # Rollback last migration
pnpm migrate:reset   # Reset (drops and reapplies) — warning: deletes data
pnpm migrate:check   # Check migration status

Using postgres.js via @uwdsc/db

Repositories extend BaseRepository from @uwdsc/db/baseRepository and use the protected this.sql tagged template (postgres.js). Do not use a global pool in app code; use the repository layer.

Repository pattern

// packages/server/core/src/repositories/UserRepository.ts
import { BaseRepository } from "@uwdsc/db/baseRepository";
 
export class UserRepository extends BaseRepository {
  async findById(id: string) {
    const rows = await this.sql`SELECT * FROM users WHERE id = ${id}`;
    return rows[0] ?? null;
  }
 
  async findAll() {
    return this.sql`SELECT * FROM users ORDER BY created_at DESC`;
  }
 
  async create(data: { email: string; name: string }) {
    const rows = await this.sql`
      INSERT INTO users (email, name) VALUES (${data.email}, ${data.name})
      RETURNING *
    `;
    return rows[0];
  }
 
  async update(id: string, data: { name?: string; bio?: string }) {
    // Use this.sql with dynamic fragments; see existing repos in core for patterns
    const rows = await this.sql`UPDATE users SET ... WHERE id = ${id} RETURNING *`;
    return rows[0];
  }
 
  async delete(id: string) {
    const rows = await this.sql`DELETE FROM users WHERE id = ${id} RETURNING *`;
    return rows[0];
  }
}

Relations

// In a repository — include related data with this.sql
async findUserWithProfile(userId: string) {
  const rows = await this.sql`
    SELECT u.*, p.bio as profile_bio
    FROM users u
    LEFT JOIN profiles p ON p.user_id = u.id
    WHERE u.id = ${userId}
  `;
  return rows[0] ?? null;
}
 
// Nested writes: use this.sql in a transaction (see postgres.js docs)
async createUserWithProfile(data: { email: string; name: string; bio: string }) {
  // Use this.sql with BEGIN/COMMIT or postgres.js transaction helpers
  try {
 
    // Create user and profile in one transaction (pseudo-code; use this.sql)
    const user = await this.sql`INSERT INTO users ... RETURNING *`;
    await this.sql`INSERT INTO profiles ...`;
    return user[0];
  } catch (error) {
    throw error;
  }
}

Filtering

// In a repository — use this.sql with tagged templates
async findActiveUsers() {
  return this.sql`
    SELECT * FROM users
    WHERE status = ${"active"} AND created_at >= ${new Date("2024-01-01")}
  `;
}
 
async search(query: string) {
  const searchTerm = `%${query}%`;
  return this.sql`
    SELECT * FROM users WHERE name ILIKE ${searchTerm} OR email ILIKE ${searchTerm}
  `;
}

Pagination

async findWithPagination(page: number, limit: number) {
  const offset = (page - 1) * limit;
  const [users, countResult] = await Promise.all([
    this.sql`SELECT * FROM users ORDER BY created_at DESC LIMIT ${limit} OFFSET ${offset}`,
    this.sql`SELECT COUNT(*) as total FROM users`,
  ]);
  const total = Number(countResult[0]?.total ?? 0);
  return { users, total, page, pages: Math.ceil(total / limit) };
}

Transactions

Use postgres.js transaction API within a repository (see postgres.js docs). Pattern: get a connection, run BEGIN, queries, COMMIT/ROLLBACK, then release.

Aggregations

async getStats() {
  const rows = await this.sql`
    SELECT COUNT(*) as total_users, AVG(age) as avg_age,
           MIN(created_at) as first_user, MAX(created_at) as latest_user
    FROM users
  `;
  return rows[0];
}
 
async groupByStatus() {
  return this.sql`SELECT status, COUNT(*) as count FROM users GROUP BY status`;
}

Supabase Integration

Supabase is used for auth and storage. Apps create a Supabase client via @uwdsc/db (createSupabaseServerClient, etc.) and use AuthService / ResumeService from @uwdsc/core (created per-request in lib/services.ts). See API Architecture and the code in packages/server/core and apps/web/lib/services.ts.

Data Seeding

Seed scripts

Create a seed script that uses the database connection from @uwdsc/db (or run SQL via db-migrate/psql). Ensure you have run pnpm pull-secrets so DATABASE_URL and other env vars are available. Run the seed with tsx or from an app script.

Best Practices

✅ Do

Use parameterized queries to prevent SQL injection
Use transactions for multi-step operations
Handle errors appropriately
Use TypeScript types for query results
Index frequently queried columns
Use SELECT to limit returned fields
Close database connections properly
Use connection pooling

❌ Don’t

Use string concatenation for SQL queries (SQL injection risk)
Store large files in database
Skip migrations in production
Expose database errors to clients
Create N+1 queries (use JOINs or batch queries)
Forget to release connection pool clients

Common Patterns

Repository Pattern

import { BaseRepository } from "@uwdsc/db/baseRepository";
 
export class UserRepository extends BaseRepository {
  async findByEmail(email: string) {
    const rows = await this.sql`SELECT * FROM users WHERE email = ${email}`;
    return rows[0] ?? null;
  }
 
  async existsByEmail(email: string): Promise<boolean> {
    const rows = await this.sql`SELECT 1 FROM users WHERE email = ${email} LIMIT 1`;
    return rows.length > 0;
  }
}

Soft Deletes

-- Migration
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

async softDelete(id: string) {
  const rows = await this.sql`
    UPDATE users SET deleted_at = NOW() WHERE id = ${id} RETURNING *
  `;
  return rows[0];
}
 
async findActive() {
  return this.sql`SELECT * FROM users WHERE deleted_at IS NULL`;
}

Optimistic Locking

-- Migration
ALTER TABLE users ADD COLUMN version INTEGER DEFAULT 0;

async updateWithVersion(id: string, currentVersion: number, data: { name: string }) {
  const rows = await this.sql`
    UPDATE users SET name = ${data.name}, version = version + 1
    WHERE id = ${id} AND version = ${currentVersion} RETURNING *
  `;
  return rows[0];
}

Troubleshooting

Connection Issues

Check environment variables and database connectivity:

import { sql } from "@uwdsc/db/connection";
 
async function testConnection() {
  try {
    await sql`SELECT 1`;
    console.log("Database connected");
  } catch (error) {
    console.error("Database connection failed:", error);
  }
}

Migration Issues

# Check migration status
pnpm migrate:check
 
# Rollback and re-apply (from root)
pnpm migrate:down
pnpm migrate

Query Performance

Use EXPLAIN ANALYZE to debug slow queries:

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

Resources

Next Steps

Creating API Endpoints - Build backend features
Development Tips - Improve workflow

Creating API Endpoints Development Tips