PocketBase 数据库设计最佳实践

┌─────────────────────────────────────────────────────────┐
│              PocketBase 数据库设计原则                    │
├─────────────────────────────────────────────────────────┤
│  1. 简单优先     │  避免过度设计，从简单开始              │
│  2. 关系清晰     │  明确定义实体间的关系                  │
│  3. 查询优化     │  根据查询模式设计数据结构              │
│  4. 可扩展性     │  预留扩展空间，避免频繁重构            │
│  5. 数据完整性   │  通过规则和约束保证数据质量            │
└─────────────────────────────────────────────────────────┘

初期设计                          演进后
┌─────────────┐                  ┌─────────────┐
│   users     │                  │   users     │
│  - id       │                  │  - id       │
│  - name     │                  │  - name     │
│  - email    │      ──────>     │  - email    │
│             │                  │  - role     │
└─────────────┘                  │  - status   │
                                 └─────────────┘

// 不好的设计：混合多种数据
collection: users
├── id
├── name
├── email
├── posts[]          // 文章应该独立
├── orders[]         // 订单应该独立
└── settings{}       // 设置可以独立

// 好的设计：职责分离
collections:
├── users            // 用户基础信息
├── posts            // 文章
├── orders           // 订单
└── user_settings    // 用户设置

// 规范化设计
authors:
  - id
  - name
  - email
  - bio

posts:
  - id
  - title
  - content
  - author (relation: authors)

// 反规范化设计（适合读多写少）
posts:
  - id
  - title
  - content
  - author (relation: authors)
  - authorName (冗余，用于快速显示)
  - authorAvatar (冗余)

text vs editor vs json

┌─────────────────┬─────────────────┬─────────────────┐
│     text        │     editor      │      json       │
├─────────────────┼─────────────────┼─────────────────┤
│  标题、名称      │  文章内容       │  配置、元数据    │
│  简短描述        │  评论、回复     │  复杂嵌套结构    │
│  标签、分类      │  富文本         │  灵活数据        │
│  < 500 字符      │  > 500 字符     │  结构化数据      │
└─────────────────┴─────────────────┴─────────────────┘

// 好的设计
posts:
  - title (text)          // 标题
  - slug (text)           // URL 友好标识
  - content (editor)      // 内容
  - excerpt (text)        // 摘要
  - metadata (json)       // SEO 元数据

// metadata 内容示例
{
  "seo_title": "SEO 标题",
  "description": "SEO 描述",
  "keywords": ["keyword1", "keyword2"],
  "og_image": "https://..."
}

// 使用 number 的场景
products: -price(number) - // 价格：99.99
  stock(number) - // 库存：100
  weight(number); // 重量：1.5

// 状态类数据使用 select
orders: -status(select) - // pending, paid, shipped, delivered
  paymentStatus(select); // unpaid, paid, refunded

// 标准日期字段设计
posts: -publishDate(date) - // 发布日期
  created(date) - // 创建时间（系统）
  updated(date); // 更新时间（系统）

// 日期范围查询
pb.collection("posts").getList(1, 20, {
  filter: 'publishDate >= "2025-01-01" && publishDate <= "2025-12-31"',
});

// 用户配置
users:
  - id
  - name
  - email

user_profiles:
  - id
  - user (relation: users, one)  // 一对一
  - bio
  - avatar
  - location

// 查询示例
pb.collection('user_profiles').getFirstListItem(
  'user = "user_id_here"',
  { expand: 'user' }
);

// 作者和文章
authors:
  - id
  - name
  - email

posts:
  - id
  - title
  - author (relation: authors, many)  // 一对多

// 查询作者的所有文章
pb.collection('posts').getList(1, 50, {
  filter: 'author = "author_id_here"',
  expand: 'author'
});

// 查询文章及其作者
pb.collection('posts').getList(1, 20, {
  expand: 'author'
});

// 文章和标签
posts:
  - id
  - title
  - tags (relation: tags, many)  // 多对多

tags:
  - id
  - name
  - slug

// 通过 expand 获取关联
pb.collection('posts').getList(1, 20, {
  expand: 'tags'
});

// 查询包含特定标签的文章
pb.collection('posts').getList(1, 20, {
  filter: 'tags ~ "tag_id_here"',
  expand: 'tags'
});

// 多对多关系的中间表
posts:
  - id
  - title
  - content

categories:
  - id
  - name
  - slug

post_categories:  // 中间表
  - id
  - post (relation: posts)
  - category (relation: categories)
  - order (number)  // 排序

// 查询文章的所有分类
pb.collection('post_categories').getFullList({
  filter: 'post = "post_id_here"',
  expand: 'category'
});

// 分类层级结构
categories:
  - id
  - name
  - slug
  - parent (relation: categories, optional)  // 自引用
  - children (virtual, 通过 parent 反向查询)

// 查询子分类
pb.collection('categories').getFullList({
  filter: 'parent = "parent_id_here"'
});

// 评论嵌套
comments:
  - id
  - content
  - author (relation: users)
  - post (relation: posts)
  - parent (relation: comments, optional)  // 回复的评论

-- 自动创建的索引
-- 主键 id 自动索引
-- relation 字段自动创建索引

-- 手动创建索引（通过 SQL 或 PocketBase 迁移）
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_author ON posts(author);
CREATE INDEX idx_posts_created ON posts(created);

// 需要索引的场景

// 1. 频繁用于过滤的字段
posts:
  - status (需要索引)
  - author (relation，自动索引)
  - category (需要索引)

// 2. 排序字段
posts:
  - created (需要索引，用于排序)
  - publishedAt (需要索引)

// 3. 唯一性约束
users:
  - email (唯一索引)
  - username (唯一索引)

// 通过迁移创建唯一索引

// 多字段组合查询的索引

// 查询模式
pb.collection('posts').getList(1, 20, {
  filter: 'status = "published" && author = "author_id"',
  sort: '-created'
});

// 创建复合索引
CREATE INDEX idx_posts_status_author_created
ON posts(status, author, created DESC);

// 集合级别的访问控制
posts:

// Create 规则
@request.auth.id != ""  // 必须登录

// List 规则
status = "published" || @request.auth.id = author
// 公开文章可见，作者可见所有文章

// View 规则
status = "published" || @request.auth.id = author

// Update 规则
@request.auth.id = author || @request.auth.role = "admin"

// Delete 规则
@request.auth.id = author || @request.auth.role = "admin"

pb_migrations/
├── 1705123456_create_posts_collection.js
├── 1705123457_add_slug_field.js
├── 1705123458_create_index_on_status.js
└── 1705123459_migrate_author_data.js

// 1. 备份数据
// 在执行迁移前，始终备份 pb_data 目录

// 2. 测试迁移
// 在开发环境充分测试迁移脚本

// 3. 使用事务（如果可能）
// 确保迁移的原子性

// 4. 分批处理大量数据
migration((app) => {
  const BATCH_SIZE = 100;
  let offset = 0;

  while (true) {
    const records = dao.findRecordsByExpr(
      collection,
      `id != ''`,
      undefined,
      BATCH_SIZE,
      offset,
    );

    if (records.length === 0) break;

    // 处理这批数据
    for (const record of records) {
      // 处理逻辑
    }

    offset += BATCH_SIZE;
  }
});

// 不好的做法：获取过多数据
const allRecords = await pb.collection("posts").getFullList();

// 好的做法：分页查询
const page1 = await pb.collection("posts").getList(1, 20);
const page2 = await pb.collection("posts").getList(2, 20);

// 不好的做法：过度使用 expand
const records = await pb.collection("posts").getList(1, 20, {
  expand: "author,comments,comments.author,tags",
});

// 好的做法：按需 expand
const records = await pb.collection("posts").getList(1, 20, {
  expand: "author",
});

// 客户端缓存
import PocketBase from "pocketbase";

const pb = new PocketBase("https://api.example.com");

// 启用自动缓存
pb.autoCancellation(false);

// 自定义缓存
const cache = new Map();

async function getPosts(cacheKey = "posts") {
  // 检查缓存
  if (cache.has(cacheKey)) {
    const cached = cache.get(cacheKey);
    if (Date.now() - cached.timestamp < 60000) {
      return cached.data;
    }
  }

  // 获取新数据
  const data = await pb.collection("posts").getList(1, 20);

  // 更新缓存
  cache.set(cacheKey, {
    data,
    timestamp: Date.now(),
  });

  return data;
}

// 批量创建
async function batchCreate(items) {
  const results = [];

  for (const item of items) {
    try {
      const result = await pb.collection("posts").create(item);
      results.push(result);
    } catch (error) {
      console.error("创建失败:", error);
    }
  }

  return results;
}

// 批量更新
async function batchUpdate(items) {
  const results = [];

  for (const item of items) {
    try {
      const result = await pb.collection("posts").update(item.id, item);
      results.push(result);
    } catch (error) {
      console.error("更新失败:", error);
    }
  }

  return results;
}

// PocketBase 使用连接池管理数据库连接
// 无需手动管理

// 并发请求优化
async function fetchParallel() {
  const [posts, authors, tags] = await Promise.all([
    pb.collection("posts").getList(1, 20),
    pb.collection("authors").getFullList(),
    pb.collection("tags").getFullList(),
  ]);

  return { posts, authors, tags };
}

// 监控指标
const metrics = {
  // 数据库大小
  databaseSize: async () => {
    const stats = await pb.collection("_").getFirstListItem("");
    return stats;
  },

  // 查询性能
  queryTime: async () => {
    const start = Date.now();
    await pb.collection("posts").getList(1, 20);
    return Date.now() - start;
  },

  // 并发连接数
  activeConnections: () => {
    // 通过监控工具获取
  },
};

// 1. 启用数据加密
// 在生产环境设置加密密钥
POCKETBASE_ENCRYPTION_KEY = your - 32 - character - key;

// 2. 限制 API 访问
// 通过反向代理限制访问

// 3. 定期更新
// 保持 PocketBase 版本最新

// 4. 审计日志
// 记录敏感操作