Database

Database

Complete guide to working with PostgreSQL, Drizzle ORM, and UUID-based data architecture in your SaaS application.

Table of Contents

UUID-First Architecture

Critical Setup Required: This application uses UUID primary keys throughout the database schema. You must enable the UUID extension before running any migrations:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
The entire application is built around UUID primary keys for enhanced security, scalability, and distributed system compatibility:
Security & Privacy:
  • No sequential IDs that reveal business metrics
  • Difficult to guess or enumerate resources
  • Better for public-facing APIs
Scalability:
  • Works across distributed databases
  • No ID conflicts in multi-server environments
  • Easier database merging and replication
Developer Experience:
  • Consistent ID format across all tables
  • Type-safe with TypeScript
  • No auto-increment race conditions

Database Stack

Modern, type-safe database infrastructure designed for scalability:

Core Technologies

Database Features:
  • PostgreSQL 15+ with full ACID compliance
  • UUID extension for primary key generation
  • JSON/JSONB support for flexible data
  • Full-text search capabilities
  • Advanced indexing strategies
Extensions Used:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";    -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pg_trgm";      -- Text similarity
CREATE EXTENSION IF NOT EXISTS "btree_gin";    -- Advanced indexing

Project Structure

src/
├── db/
│   ├── models/               # UUID-based schema definitions
│   │   ├── auth-model.ts     # Users, sessions, accounts
│   │   ├── user-model.ts     # User profiles, settings
│   │   ├── organization-model.ts  # Organizations, memberships
│   │   ├── project-model.ts  # Projects, tasks
│   │   ├── post-model.ts     # Blog posts, translations
│   │   └── subscription-model.ts  # Stripe subscriptions
│   ├── repositories/         # Type-safe data access layer
│   │   ├── user-repository.ts
│   │   ├── organization-repository.ts
│   │   ├── project-repository.ts
│   │   └── ...
│   ├── index.ts             # Database connection & schema export
│   └── migrate.ts           # Migration runner
├── drizzle.config.ts        # Drizzle configuration
└── drizzle/                 # Generated migrations
    └── migrations/
        ├── 0001_*.sql
        └── meta/

Schema Organization

All tables use UUID primary keys with logical grouping by domain: 
// src/db/models/auth-model.ts
export const users = pgTable('users', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 255 }),
  image: text('image'),
  emailVerified: boolean('email_verified').default(false),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

export const sessions = pgTable('sessions', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  userId: text('user_id').references(() => users.id).notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  token: text('token').unique().notNull(),
})

export const accounts = pgTable('accounts', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  userId: text('user_id').references(() => users.id).notNull(),
  provider: varchar('provider', { length: 50 }).notNull(),
  providerAccountId: varchar('provider_account_id', { length: 255 }).notNull(),
})

Common Operations

Essential database operations for daily development and production management:
Initial Setup:
# Enable UUID extension (run once per database)
psql $DATABASE_URL -c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"

# Install dependencies and setup
pnpm install
pnpm db:push        # Push schema to development database
pnpm db:seed        # Add demo data with UUIDs
Daily Development:
# Open database management interface
pnpm db:studio      # Launch Drizzle Studio at localhost:4983

# Test database connection
pnpm db:check       # Verify connectivity and permissions

# Quick development reset
pnpm db:reset-seed  # Drop all data and reseed (development only)
Development Workflow:
# 1. Make schema changes in src/db/models/
# 2. Push changes to dev database
pnpm db:push

# 3. Test changes in Drizzle Studio
pnpm db:studio

# 4. Generate production migration when ready
pnpm db:generate

Development Workflow

Step-by-step process for working with the UUID-based database:
1. Plan Your Changes:
// Example: Adding user preferences
export const userPreferences = pgTable('user_preferences', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  userId: text('user_id').references(() => users.id).notNull(),
  theme: themeEnum('theme').default('system'),
  language: varchar('language', { length: 10 }).default('en'),
  timezone: varchar('timezone', { length: 50 }).default('UTC'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})
2. Implement Repository Functions:
// src/db/repositories/user-repository.ts
export async function getUserPreferences(userId: string) {
  return await db.query.userPreferences.findFirst({
    where: eq(userPreferences.userId, userId),
  })
}

export async function updateUserPreferences(
  userId: string,
  prefs: Partial<InferInsertModel<typeof userPreferences>>
) {
  return await db
    .update(userPreferences)
    .set({ ...prefs, updatedAt: new Date() })
    .where(eq(userPreferences.userId, userId))
    .returning()
}
3. Test with Development Data:
# Push schema changes
pnpm db:push

# Open Drizzle Studio to verify
pnpm db:studio

# Test with real UUID data
pnpm db:seed

Quick Reference

Common patterns and operations for UUID-based database work: 
// User operations with UUIDs
export async function getUserById(userId: string) {
  return await db.query.users.findFirst({
    where: eq(users.id, userId),
    with: {
      organizations: { with: { members: true } },
      settings: true,
    },
  })
}

export async function createUser(userData: CreateUserType) {
  const [newUser] = await db.insert(users)
    .values({
      ...userData,
      id: crypto.randomUUID(), // Explicit UUID generation
    })
    .returning()
  return newUser
}

// Organization operations
export async function getOrganizationWithMembers(orgId: string) {
  return await db.query.organizations.findFirst({
    where: eq(organizations.id, orgId),
    with: {
      members: { with: { user: true } },
      projects: true,
    },
  })
}

export async function addMemberToOrganization(userId: string, orgId: string, role: MemberRole) {
  return await db.insert(members).values({
    id: crypto.randomUUID(),
    userId,
    organizationId: orgId,
    role,
  }).returning()
}

Next Steps

Explore the complete database documentation:
Learning Path: Start with Repository Pattern → Drizzle Queries → Migration Workflow → Production Best Practices

Detailed Guides

Quick Access

# Essential commands for daily work
pnpm db:studio      # Open database GUI
pnpm db:push        # Apply schema changes (dev)
pnpm db:seed        # Load demo data with UUIDs
pnpm db:generate    # Create production migrations
UUID Benefits: Enhanced security, better scalability, and consistent developer experience across the entire application stack.
    Database | ShipSaaS Documentation | ShipSaaS - Launch your SaaS with AI in days