Database Design

数据库设计技能

为采用 Supabase 和 PostgreSQL 的 Next.js 应用设计高效、可扩展数据库架构的综合指南。从表设计和关系到行级安全策略和查询优化，这项技能涵盖构建稳健数据模型所需的一切。

设计规范化架构，实现安全的 RLS 策略，借助恰当的索引优化查询性能，并构建能够随应用扩展的可维护数据模型。

核心工作流

工作流 1：根据需求设计架构

目的： 将业务需求转化为数据库架构

步骤：

1. 确定实体和关系
2. 定义主键和外键
3. 选择合适的数据类型
4. 添加约束和默认值
5. 创建索引以提升性能

实现：

-- Correct data types
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT NOT NULL,                    -- Use TEXT, not VARCHAR
  age INTEGER CHECK (age >= 0),
  balance DECIMAL(10, 2),                 -- For money
  is_active BOOLEAN DEFAULT true,
  metadata JSONB,                         -- Use JSONB for JSON
  created_at TIMESTAMPTZ DEFAULT NOW()    -- Use TIMESTAMPTZ
);

工作流 2：常见关系模式

目的： 实现恰当的数据库关系

一对多：

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

多对多：

CREATE TABLE students (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL
);

CREATE TABLE courses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL
);

CREATE TABLE enrollments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  student_id UUID NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id UUID NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(student_id, course_id)
);

CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);

自引用（树形结构）：

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_comments_parent ON comments(parent_id);

工作流 3：行级安全（RLS）

目的： 在数据库层面保护数据访问

实现：

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Users can only read their own data
CREATE POLICY "Users can read own posts"
ON posts FOR SELECT
USING (auth.uid() = user_id);

-- Users can insert their own data
CREATE POLICY "Users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Users can update their own data
CREATE POLICY "Users can update own posts"
ON posts FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Users can delete their own data
CREATE POLICY "Users can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = user_id);

-- Public read, authenticated write
CREATE POLICY "Public can read posts"
ON posts FOR SELECT
USING (true);

CREATE POLICY "Authenticated users can create posts"
ON posts FOR INSERT
WITH CHECK (auth.role() = 'authenticated');

-- Role-based access
CREATE POLICY "Admins have full access"
ON posts FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM users
    WHERE id = auth.uid() AND role = 'admin'
  )
);

工作流 4：用于提升性能的索引

目的： 优化查询性能

实现：

-- Single Column Index
CREATE INDEX idx_users_email ON users(email);

-- Composite Index
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);

-- Partial Index
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Full-Text Search Index
CREATE INDEX idx_posts_search ON posts
USING gin(to_tsvector('english', title || ' ' || content));

-- Query with full-text search
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || content)
@@ plainto_tsquery('search term');

工作流 5：高级模式

目的： 实现常见数据库模式

软删除：

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_not_deleted ON posts(id) WHERE deleted_at IS NULL;

CREATE POLICY "Users see non-deleted posts"
ON posts FOR SELECT
USING (deleted_at IS NULL);

审计追踪：

CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  table_name TEXT NOT NULL,
  record_id UUID NOT NULL,
  action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  old_data JSONB,
  new_data JSONB,
  user_id UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, user_id)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE NULL END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) ELSE NULL END,
    auth.uid()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER posts_audit
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

自动更新时间戳：

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

快速参考

任务	SQL 模式
创建表	CREATE TABLE name (columns)
添加列	ALTER TABLE name ADD COLUMN col TYPE
添加索引	CREATE INDEX name ON table(columns)
启用 RLS	ALTER TABLE name ENABLE ROW LEVEL SECURITY
创建策略	CREATE POLICY name ON table FOR action USING (condition)
创建函数	CREATE FUNCTION name() RETURNS type AS $$ ... $$
创建触发器	CREATE TRIGGER name AFTER/BEFORE action ON table

使用 Supabase 进行迁移

# Create Migration
npx supabase migration new create_posts_table

-- supabase/migrations/20240101000000_create_posts_table.sql

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own posts"
ON posts FOR SELECT
USING (auth.uid() = user_id);

# Apply Migration
npx supabase db push

使用 Supabase 查询

// Select all
const { data } = await supabase.from('posts').select('*')

// Select specific columns
const { data } = await supabase.from('posts').select('id, title')

// Filter
const { data } = await supabase
  .from('posts')
  .select('*')
  .eq('user_id', userId)

// Join tables
const { data } = await supabase
  .from('posts')
  .select(`
    *,
    users (
      id,
      name,
      email
    )
  `)

// Count
const { count } = await supabase
  .from('posts')
  .select('*', { count: 'exact', head: true })

// Pagination
const { data } = await supabase
  .from('posts')
  .select('*')
  .range(0, 9)

// Order
const { data } = await supabase
  .from('posts')
  .select('*')
  .order('created_at', { ascending: false })

最佳实践

• 使用 UUID： 更适合分布式系统，也更安全
• 添加时间戳： 始终包含 created_at 和 updated_at
• 启用 RLS： 默认在所有表上启用
• 为外键创建索引： 为所有外键创建索引
• 使用 TIMESTAMPTZ： 为支持时区，使用 TIMESTAMPTZ 而非 TIMESTAMP
• 使用 TEXT： 在 PostgreSQL 中使用 TEXT 而非 VARCHAR
• 使用 JSONB： 使用 JSONB 而非 JSON，以获得更好的性能
• 先规范化： 仅为提升性能而反规范化
• 仅使用迁移： 绝不要在生产环境中直接修改架构
• 测试 RLS 策略： 在不同用户上下文中验证
• 使用事务： 用于相关操作
• 记录架构： 维护 ERD 图

约束

-- Primary Key
id UUID PRIMARY KEY DEFAULT uuid_generate_v4()

-- Foreign Key
user_id UUID REFERENCES users(id) ON DELETE CASCADE

-- Unique
email TEXT UNIQUE NOT NULL

-- Check
price DECIMAL CHECK (price > 0)
status TEXT CHECK (status IN ('active', 'inactive'))

-- Not Null
name TEXT NOT NULL

依赖项

# Supabase CLI for migrations
npm install -D supabase

# Generate types from schema
npx supabase gen types typescript --local > types/database.ts

错误处理

• 外键违规： 在应用中妥善处理
• 唯一性约束： 提供用户友好的错误消息
• 检查约束： 插入前在应用中验证
• RLS 错误： 出于安全考虑，返回 404 而非 403
• 连接数限制： 使用连接池

性能提示

• 使用连接池（PgBouncer）
• 为常见查询添加覆盖索引
• 使用 LIMIT 和游标分页
• 避免在生产环境中使用 SELECT *
• 使用预处理语句
• 使用 pg_stat_statements 监控慢查询
• 定期清理表
• 对大型数据集考虑表分区

何时使用此技能

在以下情况下调用此技能：

• 设计新的数据库表
• 设置关系
• 创建 RLS 策略
• 优化查询性能
• 实现软删除
• 设置审计追踪
• 编写迁移
• 调试数据库问题
• 选择数据类型
• 创建索引