数据库

设计与多媒体 社区

技能解读

当前原文的自动摘译,尚未人工审校。

何时使用该技能

设计数据库模式 编写或优化 SQL 查询 创建数据库迁移 设置 ORM(Prisma、Drizzle、TypeORM、SQLAlchemy) 调试查询性能

命名约定

表:plural、snake_case — users、order_items 列:snake_case — created_at、first_name 主键:id(自动增量或 UUID) 外键:<singular_table>_id — user_id、order_id 索引: idx_<table>_<columns> — idx_users_email

数据类型指南

使用案例 · PostgreSQL · MySQL 主键 · UUID 或 BIGSERIAL · BIGINT AUTO_INCREMENT 短文本 · VARCHAR(n) · VARCHAR(n) 长文本 · TEXT · TEXT 货币 · NUMERIC(12,2) · DECIMAL(12,2)

何时添加索引

WHERE 子句中经常使用的列 JOIN 条件中的列 ORDER BY 中的列(如果尚未涵盖) 外键列

何时不建立索引

小表(< 1000 行) 基数非常低的列(布尔值) 写入负载较重且读取很少的表

流狐档案 运行与权限由流狐静态检查推断,不代表作者声明或实际测试结果。
领域
设计与多媒体
作者声明的智能体
未找到明确声明;不据此推断已兼容或已测试
静态检查
88 / 100 · 启发式扫描,不代表运行安全
运行环境证据
未执行;当前没有隔离运行测试证据
作者 / 版本 / 许可证
@tomevault-io · 未声明许可证
上下文用量评级
低消耗
接入复杂程度
即装即用
是否需要外部接口密钥
不需要
兼容的系统
macOS · Linux · Windows
运行环境要求
未检出明确要求
检测到的文件与系统行为
  • 只读
  • 允许写入 / 修改
网络行为
仅限本地
安装命令数
无(仅作为资料)

需要注意: 未声明工具权限范围;实际权限由运行环境决定。

作者说明文件(原文) SKILL.md

# Database & SQL

## When to use this skill
- Designing a database schema
- Writing or optimizing SQL queries
- Creating database migrations
- Setting up an ORM (Prisma, Drizzle, TypeORM, SQLAlchemy)
- Debugging query performance
- Adding indexes

## Schema design principles

### Naming conventions
- **Tables**: plural, snake_case — `users`, `order_items`
- **Columns**: snake_case — `created_at`, `first_name`
- **Primary keys**: `id` (auto-increment or UUID)
- **Foreign keys**: `<singular_table>_id` — `user_id`, `order_id`
- **Indexes**: `idx_<table>_<columns>` — `idx_users_email`
- **Booleans**: `is_` or `has_` prefix — `is_active`, `has_verified`

### Common patterns

```sql
-- Standard table template
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user',
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Junction table for many-to-many
CREATE TABLE user_roles (
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, role_id)
);

-- Soft delete pattern
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL;
```

### Data types guide

| Use case | PostgreSQL | MySQL |
|----------|-----------|-------|
| Primary key | `UUID` or `BIGSERIAL` | `BIGINT AUTO_INCREMENT` |
| Short text | `VARCHAR(n)` | `VARCHAR(n)` |
| Long text | `TEXT` | `TEXT` |
| Currency | `NUMERIC(12,2)` | `DECIMAL(12,2)` |
| Timestamps | `TIMESTAMPTZ` | `DATETIME` |
| JSON | `JSONB` | `JSON` |
| Booleans | `BOOLEAN` | `TINYINT(1)` |
| Enums | `VARCHAR` + CHECK | `ENUM(...)` |

## Migrations

### Prisma

```prisma
// schema.prisma
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

model Order {
  id        String      @id @default(uuid())
  userId    String      @map("user_id")
  user      User        @relation(fields: [userId], references: [id])
  status    OrderStatus @default(PENDING)
  total     Decimal     @db.Decimal(12, 2)
  createdAt DateTime    @default(now()) @map("created_at")

  @@index([userId])
  @@map("orders")
}

enum OrderStatus {
  PENDING
  CONFIRMED
  SHIPPED
  DELIVERED
  CANCELLED
}
```

```bash
# Generate and apply migration
npx prisma migrate dev --name add_orders_table
npx prisma generate
```

### Raw SQL migration

```sql
-- migrations/001_create_users.up.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_email ON users (email);

-- migrations/001_create_users.down.sql
DROP TABLE IF EXISTS users;
```

## Query patterns

### Pagination

```sql
-- Offset-based (simple, but slow for large offsets)
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

-- Cursor-based (performant for large datasets)
SELECT * FROM users
WHERE created_at < $1  -- cursor from previous page
ORDER BY created_at DESC
LIMIT 20;
```

### Aggregation

```sql
-- Order summary by status
SELECT
    status,
    COUNT(*) AS count,
    SUM(total) AS revenue,
    AVG(total) AS avg_order
FROM orders
WHERE created_at >= now() - INTERVAL '30 days'
GROUP BY status
ORDER BY revenue DESC;
```

### Common Table Expressions (CTEs)

```sql
-- Readable complex queries with CTEs
WITH monthly_revenue AS (
    SELECT
        date_trunc('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE status = 'DELIVERED'
    GROUP BY month
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    ROUND((revenue - prev_revenue) / prev_revenue * 100, 1) AS growth_pct
FROM growth
ORDER BY month DESC;
```

### Upsert

```sql
-- PostgreSQL
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = now();

-- MySQL
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);
```

## Indexing strategy

```sql
-- Single column (most common queries)
CREATE INDEX idx_users_email ON users (email);

-- Composite (multi-column WHERE clauses)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- Rule: put equality columns first, range columns last

-- Partial index (subset of rows)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'PENDING';

-- CONCURRENTLY (no table lock in production)
CREATE INDEX CONCURRENTLY idx_users_name ON users (name);
```

### When to add indexes
- Columns in WHERE clauses used frequently
- Columns in JOIN conditions
- Columns in ORDER BY (if not already covered)
- Foreign key columns

### When NOT to index
- Small tables (< 1000 rows)
- Columns with very low cardinality (booleans)
- Tables with heavy write load and few reads

## Performance debugging

```sql
-- Explain query plan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = $1 AND status = 'active';

-- Look for:
-- Seq Scan → missing index
-- Nested Loop → potential N+1
-- Sort → missing index for ORDER BY
-- High "actual time" → slow operation
```

## Checklist

- [ ] Tables follow naming conventions
- [ ] Primary keys and foreign keys defined
- [ ] Appropriate data types chosen
- [ ] NOT NULL constraints where appropriate
- [ ] Indexes on frequently queried columns
- [ ] Foreign keys have ON DELETE behavior
- [ ] Migrations are reversible (up + down)
- [ ] Queries use parameterized values (no interpolation)
- [ ] Large result sets are paginated
- [ ] Query performance checked with EXPLAIN ANALYZE

---
> Source: [asgarovf/locusai](https://github.com/asgarovf/locusai) — distributed by [TomeVault](https://tomevault.io).
<!-- tomevault:4.0:skill_md:2026-05-22 -->

讨论

基于 GitHub Discussions。登录 GitHub 即可参与讨论、点赞、订阅更新。