数据库
- 作者仓库星标 0
- 作者仓库 skills-registry
- 领域
- 设计与多媒体
- 作者声明的智能体
- 未找到明确声明;不据此推断已兼容或已测试
- 静态检查
- 88 / 100 · 启发式扫描,不代表运行安全
- 运行环境证据
- 未执行;当前没有隔离运行测试证据
- 作者 / 版本 / 许可证
- @tomevault-io · 未声明许可证
- 上下文用量评级
- 低消耗
- 接入复杂程度
- 即装即用
- 是否需要外部接口密钥
- 不需要
- 兼容的系统
- macOS · Linux · Windows
- 运行环境要求
- 未检出明确要求
- 检测到的文件与系统行为
-
- 只读
- 允许写入 / 修改
- 网络行为
- 仅限本地
- 安装命令数
- 无(仅作为资料)
需要注意: 未声明工具权限范围;实际权限由运行环境决定。
# 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 -->
章节与文件索引
按说明文件中的顺序排列。
何时使用该技能
命名约定
数据类型指南
何时添加索引
何时不建立索引
文件/命令 -> users · order_items · created_at · first_name · <singular_table>_id · user_id · order_id · idx_<table>_<columns>
内容校验值 -> 5f108edd9c86
当前原文的自动摘译,尚未人工审校。
何时使用该技能
设计数据库模式 编写或优化 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 行) 基数非常低的列(布尔值) 写入负载较重且读取很少的表