AI SaaS Template Docs
AI SaaS Template Docs
IntroductionQuick StartArchitecture
Vercel DeploymentDocker Deployment
ConfigurationProject StructureDatabase GuideAPI Guide
AuthenticationFile ManagementPayment & Billing
Theme Customization
Developer Guide

Database Guide

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

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

  1. Naming Conventions

    • Use snake_case for column names
    • Use descriptive names for tables and columns
    • Add proper foreign key constraints
  2. Type Safety

    • Use TypeScript types for all columns
    • Define proper default values
    • Use references for foreign keys
  3. 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: '[email protected]',
    };

    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: '[email protected]',
    };

    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 AI SaaS Template using Drizzle ORM. Follow these patterns and practices to maintain a robust and scalable database layer.

Project Structure

Previous Page

API Guide

Next Page

On this page

Database ArchitectureTechnology StackDatabase StructureSchema DefinitionCore SchemaSchema Best PracticesDatabase ConnectionConnection SetupConfigurationRepository PatternBase RepositoryFile Repository ImplementationPayment RepositoryDatabase OperationsQuery PatternsTransactionsMigrationsCreating MigrationsMigration ExampleError HandlingDatabase ErrorsTestingDatabase TestingBest Practices1. Repository Pattern2. Query Optimization3. Schema Design4. Error Handling5. Testing