Database Guide

This guide covers database development patterns, Drizzle ORM usage, and repository patterns in Better SaaS.

Database Architecture

Technology Stack

ORM: Drizzle ORM
Database: PostgreSQL (Neon)
Connection: HTTP-based connection for serverless
Migrations: Drizzle migrations
Schema: Type-safe schema definitions

Database Structure

src/server/db/
├── index.ts                 # Database connection
├── schema.ts               # Database schema definitions
├── types.ts                # Database types and errors
├── repositories/           # Repository pattern implementations
│   ├── base-repository.ts  # Base repository interface
│   ├── file-repository.ts  # File operations
│   ├── payment-repository.ts # Payment operations
│   └── index.ts           # Repository exports
└── services/              # Database services
    └── index.ts           # Service exports

Schema Definition

Core Schema

// src/server/db/schema.ts
import { boolean, integer, pgTable, text, timestamp } from 'drizzle-orm/pg-core';

export const user = pgTable('user', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified')
    .$defaultFn(() => false)
    .notNull(),
  image: text('image'),
  createdAt: timestamp('created_at')
    .$defaultFn(() => new Date())
    .notNull(),
  updatedAt: timestamp('updated_at')
    .$defaultFn(() => new Date())
    .notNull(),
  role: text('role'),
  banned: boolean('banned'),
  banReason: text('ban_reason'),
  banExpires: timestamp('ban_expires'),
});

export const file = pgTable('file', {
  id: text('id').primaryKey(),
  filename: text('filename').notNull(),
  originalName: text('original_name').notNull(),
  mimeType: text('mime_type').notNull(),
  size: integer('size').notNull(),
  width: integer('width'),
  height: integer('height'),
  r2Key: text('r2_key').notNull(),
  thumbnailKey: text('thumbnail_key'),
  uploadUserId: text('upload_user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at')
    .$defaultFn(() => new Date())
    .notNull(),
  updatedAt: timestamp('updated_at')
    .$defaultFn(() => new Date())
    .notNull(),
});

export const payment = pgTable('payment', {
  id: text('id').primaryKey(),
  priceId: text('price_id').notNull(),
  type: text('type').notNull(),
  interval: text('interval'),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  customerId: text('customer_id').notNull(),
  subscriptionId: text('subscription_id'),
  status: text('status').notNull(),
  periodStart: timestamp('period_start'),
  periodEnd: timestamp('period_end'),
  cancelAtPeriodEnd: boolean('cancel_at_period_end'),
  trialStart: timestamp('trial_start'),
  trialEnd: timestamp('trial_end'),
  createdAt: timestamp('created_at')
    .$defaultFn(() => new Date())
    .notNull(),
  updatedAt: timestamp('updated_at')
    .$defaultFn(() => new Date())
    .notNull(),
});

Schema Best Practices

Naming Conventions
- Use snake_case for column names
- Use descriptive names for tables and columns
- Add proper foreign key constraints
Type Safety
- Use TypeScript types for all columns
- Define proper default values
- Use references for foreign keys
Timestamps
- Always include createdAt and updatedAt
- Use consistent timestamp handling

Database Connection

Connection Setup

// src/server/db/index.ts
import { env } from '@/env';
import { drizzle } from 'drizzle-orm/neon-http';
import * as schema from './schema';

const db = drizzle(env.DATABASE_URL, { schema });

export default db;
export * from './repositories';

Configuration

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
import { env } from '@/env';

export default defineConfig({
  schema: './src/server/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: env.DATABASE_URL,
  },
});

Repository Pattern

Base Repository

// src/server/db/repositories/base-repository.ts
import { DatabaseError } from '../types';

export interface IBaseRepository<TSelect, TInsert> {
  findById(id: string): Promise<TSelect | undefined>;
  findAll(): Promise<TSelect[]>;
  create(data: TInsert): Promise<TSelect>;
  update(id: string, data: Partial<TInsert>): Promise<TSelect | undefined>;
  delete(id: string): Promise<boolean>;
  exists(id: string): Promise<boolean>;
  count(): Promise<number>;
}

export abstract class BaseRepository<TSelect, TInsert>
  implements IBaseRepository<TSelect, TInsert>
{
  abstract findById(id: string): Promise<TSelect | undefined>;
  abstract findAll(): Promise<TSelect[]>;
  abstract create(data: TInsert): Promise<TSelect>;
  abstract update(id: string, data: Partial<TInsert>): Promise<TSelect | undefined>;
  abstract delete(id: string): Promise<boolean>;
  abstract exists(id: string): Promise<boolean>;
  abstract count(): Promise<number>;

  protected handleError(error: unknown, operation: string, code: string): never {
    const message = error instanceof Error ? error.message : '未知错误';
    throw new DatabaseError(`${operation}失败: ${message}`, code);
  }
}

File Repository Implementation

// src/server/db/repositories/file-repository.ts
import { and, desc, eq, ilike, sql } from 'drizzle-orm';
import type { FileInfo } from '@/lib/file-service';
import db from '../index';
import { file, user } from '../schema';

export interface CreateFileData {
  id: string;
  filename: string;
  originalName: string;
  mimeType: string;
  size: number;
  width?: number;
  height?: number;
  r2Key: string;
  thumbnailKey?: string;
  uploadUserId: string;
}

export interface FileListOptions {
  page?: number;
  limit?: number;
  search?: string;
}

export class FileRepository {
  async create(data: CreateFileData): Promise<FileInfo> {
    const [created] = await db
      .insert(file)
      .values({
        ...data,
        createdAt: new Date(),
        updatedAt: new Date(),
      })
      .returning();

    if (!created) {
      throw new Error('Failed to create file record');
    }

    return this.toFileInfo(created);
  }

  async findById(id: string): Promise<FileInfo | null> {
    const [found] = await db.select().from(file).where(eq(file.id, id));
    return found ? this.toFileInfo(found) : null;
  }

  async findByUserId(userId: string, options: FileListOptions = {}): Promise<{
    files: FileInfo[];
    total: number;
  }> {
    const { page = 1, limit = 20, search = '' } = options;
    const offset = (page - 1) * limit;

    const conditions = [eq(file.uploadUserId, userId)];
    
    if (search) {
      conditions.push(ilike(file.originalName, `%${search}%`));
    }

    const whereClause = conditions.length > 1 ? and(...conditions) : conditions[0];

    // Get total count
    const countResult = await db
      .select({ count: sql`count(*)`.mapWith(Number) })
      .from(file)
      .where(whereClause);

    const total = countResult[0]?.count || 0;

    // Get files
    const files = await db
      .select()
      .from(file)
      .where(whereClause)
      .orderBy(desc(file.createdAt))
      .limit(limit)
      .offset(offset);

    return {
      files: files.map((f) => this.toFileInfo(f)),
      total,
    };
  }

  async findAll(options: FileListOptions = {}): Promise<{
    files: FileInfo[];
    total: number;
  }> {
    const { page = 1, limit = 20, search = '' } = options;
    const offset = (page - 1) * limit;

    let whereClause = undefined;
    if (search) {
      whereClause = ilike(file.originalName, `%${search}%`);
    }

    // Get total count
    const countResult = await db
      .select({ count: sql`count(*)`.mapWith(Number) })
      .from(file)
      .where(whereClause);

    const total = countResult[0]?.count || 0;

    // Get files with user info
    const files = await db
      .select({
        file: file,
        user: {
          email: user.email,
        },
      })
      .from(file)
      .leftJoin(user, eq(file.uploadUserId, user.id))
      .where(whereClause)
      .orderBy(desc(file.createdAt))
      .limit(limit)
      .offset(offset);

    return {
      files: files.map((result) => this.toFileInfoWithUser(result.file, result.user?.email)),
      total,
    };
  }

  async delete(id: string): Promise<boolean> {
    const [deleted] = await db
      .delete(file)
      .where(eq(file.id, id))
      .returning();

    return !!deleted;
  }

  async deleteByUserId(userId: string, fileId: string): Promise<boolean> {
    const [deleted] = await db
      .delete(file)
      .where(and(eq(file.id, fileId), eq(file.uploadUserId, userId)))
      .returning();

    return !!deleted;
  }

  private toFileInfo(record: any): FileInfo {
    return {
      id: record.id,
      filename: record.filename,
      originalName: record.originalName,
      mimeType: record.mimeType,
      size: record.size,
      width: record.width,
      height: record.height,
      url: `https://cdn.example.com/${record.r2Key}`,
      thumbnailUrl: record.thumbnailKey ? `https://cdn.example.com/${record.thumbnailKey}` : undefined,
      uploadUserId: record.uploadUserId,
      createdAt: record.createdAt,
      updatedAt: record.updatedAt,
    };
  }

  private toFileInfoWithUser(record: any, userEmail?: string): FileInfo {
    const fileInfo = this.toFileInfo(record);
    return {
      ...fileInfo,
      userEmail,
    };
  }
}

export const fileRepository = new FileRepository();

Payment Repository

// src/server/db/repositories/payment-repository.ts
import { eq, desc, and, inArray } from 'drizzle-orm';
import db from '@/server/db';
import { payment, paymentEvent } from '@/server/db/schema';
import type { PaymentRecord, PaymentStatus, PaymentType, PaymentInterval } from '@/payment/types';
import { v4 as uuidv4 } from 'uuid';

export interface CreatePaymentData {
  id?: string;
  priceId: string;
  type: PaymentType;
  interval?: PaymentInterval;
  userId: string;
  customerId: string;
  subscriptionId?: string;
  status: PaymentStatus;
  periodStart?: Date;
  periodEnd?: Date;
  cancelAtPeriodEnd?: boolean;
  trialStart?: Date;
  trialEnd?: Date;
}

export class PaymentRepository {
  async create(data: CreatePaymentData): Promise<PaymentRecord> {
    const paymentId = data.id || uuidv4();
    
    const [result] = await db
      .insert(payment)
      .values({
        id: paymentId,
        priceId: data.priceId,
        type: data.type,
        interval: data.interval || null,
        userId: data.userId,
        customerId: data.customerId,
        subscriptionId: data.subscriptionId || null,
        status: data.status,
        periodStart: data.periodStart || null,
        periodEnd: data.periodEnd || null,
        cancelAtPeriodEnd: data.cancelAtPeriodEnd || null,
        trialStart: data.trialStart || null,
        trialEnd: data.trialEnd || null,
      })
      .returning();

    return this.mapToPaymentRecord(result);
  }

  async findById(id: string): Promise<PaymentRecord | null> {
    const result = await db
      .select()
      .from(payment)
      .where(eq(payment.id, id))
      .limit(1);

    return result[0] ? this.mapToPaymentRecord(result[0]) : null;
  }

  async findByUserId(userId: string): Promise<PaymentRecord[]> {
    const results = await db
      .select()
      .from(payment)
      .where(eq(payment.userId, userId))
      .orderBy(desc(payment.createdAt));

    return results.map(this.mapToPaymentRecord);
  }

  async findBySubscriptionId(subscriptionId: string): Promise<PaymentRecord | null> {
    const result = await db
      .select()
      .from(payment)
      .where(eq(payment.subscriptionId, subscriptionId))
      .limit(1);

    return result[0] ? this.mapToPaymentRecord(result[0]) : null;
  }

  async findActiveSubscriptionByUserId(userId: string): Promise<PaymentRecord | null> {
    const result = await db
      .select()
      .from(payment)
      .where(
        and(
          eq(payment.userId, userId),
          eq(payment.type, 'subscription'),
          inArray(payment.status, ['active', 'trialing', 'past_due'])
        )
      )
      .orderBy(desc(payment.createdAt))
      .limit(1);

    return result[0] ? this.mapToPaymentRecord(result[0]) : null;
  }

  async update(id: string, data: Partial<CreatePaymentData>): Promise<PaymentRecord | null> {
    const [result] = await db
      .update(payment)
      .set({
        ...data,
        updatedAt: new Date(),
      })
      .where(eq(payment.id, id))
      .returning();

    return result ? this.mapToPaymentRecord(result) : null;
  }

  async delete(id: string): Promise<boolean> {
    const [result] = await db
      .delete(payment)
      .where(eq(payment.id, id))
      .returning();

    return !!result;
  }

  private mapToPaymentRecord(record: any): PaymentRecord {
    return {
      id: record.id,
      priceId: record.priceId,
      type: record.type as PaymentType,
      interval: record.interval as PaymentInterval,
      userId: record.userId,
      customerId: record.customerId,
      subscriptionId: record.subscriptionId,
      status: record.status as PaymentStatus,
      periodStart: record.periodStart,
      periodEnd: record.periodEnd,
      cancelAtPeriodEnd: record.cancelAtPeriodEnd,
      trialStart: record.trialStart,
      trialEnd: record.trialEnd,
      createdAt: record.createdAt,
      updatedAt: record.updatedAt,
    };
  }
}

export const paymentRepository = new PaymentRepository();

Database Operations

Query Patterns

// Basic queries
const users = await db.select().from(user);
const userById = await db.select().from(user).where(eq(user.id, userId));

// Joins
const filesWithUsers = await db
  .select({
    file: file,
    user: user,
  })
  .from(file)
  .leftJoin(user, eq(file.uploadUserId, user.id));

// Pagination
const paginatedFiles = await db
  .select()
  .from(file)
  .orderBy(desc(file.createdAt))
  .limit(20)
  .offset(0);

// Filtering
const searchResults = await db
  .select()
  .from(file)
  .where(ilike(file.originalName, `%${search}%`));

// Aggregations
const fileCount = await db
  .select({ count: sql`count(*)`.mapWith(Number) })
  .from(file);

Transactions

// Transaction example
await db.transaction(async (tx) => {
  const user = await tx.insert(userTable).values(userData).returning();
  const profile = await tx.insert(profileTable).values({
    userId: user[0].id,
    ...profileData,
  }).returning();
  
  return { user: user[0], profile: profile[0] };
});

Migrations

Creating Migrations

# Generate migration
pnpm drizzle-kit generate

# Push to database
pnpm drizzle-kit push

# View migrations
pnpm drizzle-kit up

Migration Example

-- drizzle/0000_dark_sentry.sql
CREATE TABLE IF NOT EXISTS "user" (
	"id" text PRIMARY KEY NOT NULL,
	"name" text NOT NULL,
	"email" text NOT NULL,
	"email_verified" boolean DEFAULT false NOT NULL,
	"image" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	"role" text,
	"banned" boolean,
	"ban_reason" text,
	"ban_expires" timestamp,
	CONSTRAINT "user_email_unique" UNIQUE("email")
);

Error Handling

Database Errors

// src/server/db/types.ts
export class DatabaseError extends Error {
  constructor(
    message: string,
    public code?: string
  ) {
    super(message);
    this.name = 'DatabaseError';
  }
}

// Usage in repositories
try {
  const result = await db.insert(table).values(data);
  return result;
} catch (error) {
  this.handleError(error, 'create', 'DB_CREATE_ERROR');
}

Testing

Database Testing

// tests/integration/database/user-operations.test.ts
import { describe, it, expect, beforeEach, afterEach } from '@jest/globals';
import db from '@/server/db';
import { user } from '@/server/db/schema';

describe('User Database Operations', () => {
  beforeEach(async () => {
    // Clean up test data
    await db.delete(user);
  });

  afterEach(async () => {
    // Clean up after tests
    await db.delete(user);
  });

  it('should create a user', async () => {
    const userData = {
      id: 'test-user-1',
      name: 'Test User',
      email: 'test@example.com',
    };

    const [created] = await db.insert(user).values(userData).returning();

    expect(created).toBeDefined();
    expect(created.email).toBe(userData.email);
  });

  it('should find user by email', async () => {
    const userData = {
      id: 'test-user-2',
      name: 'Test User 2',
      email: 'test2@example.com',
    };

    await db.insert(user).values(userData);

    const found = await db.select().from(user).where(eq(user.email, userData.email));

    expect(found).toHaveLength(1);
    expect(found[0].email).toBe(userData.email);
  });
});

Best Practices

1. Repository Pattern

Use repositories for data access logic
Keep business logic separate from data access
Implement proper error handling
Use TypeScript for type safety

2. Query Optimization

Use indexes for frequently queried columns
Implement pagination for large datasets
Use appropriate joins
Avoid N+1 queries

3. Schema Design

Use proper foreign key constraints
Implement soft deletes where appropriate
Use consistent naming conventions
Add proper indexes

4. Error Handling

Implement proper error types
Log database errors with context
Handle constraint violations gracefully
Use transactions for related operations

5. Testing

Write integration tests for database operations
Use test database for testing
Clean up test data properly
Test error scenarios

This guide provides a comprehensive foundation for database development in Better SaaS using Drizzle ORM. Follow these patterns and practices to maintain a robust and scalable database layer.

Database Guide

On this page