using-relational-databases

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Relational Databases

关系型数据库

Purpose

用途

This skill guides relational database selection and implementation across multiple languages. Choose the optimal database engine, ORM/query builder, and deployment strategy for transactional systems, CRUD applications, and structured data storage.

本技能指导你在多语言环境中选择和实现关系型数据库。为事务系统、CRUD应用和结构化数据存储选择最优的数据库引擎、ORM/查询构建器及部署策略。

When to Use This Skill

适用场景

Trigger this skill when:

Building user authentication, content management, e-commerce applications
Implementing CRUD operations (Create, Read, Update, Delete)
Designing data models with relationships (users → posts, orders → items)
Migrating schemas safely in production
Setting up connection pooling for performance
Evaluating serverless database options (Neon, PlanetScale, Turso)
Integrating with frontend skills (forms, tables, dashboards, search-filter)

Skip this skill for:

Time-series data at scale (use time-series databases)
Real-time analytics (use columnar databases)
Document-heavy workloads (use document databases)
Key-value caching (use Redis, Memcached)

以下场景可使用本技能：

构建用户认证、内容管理、电商应用
实现CRUD操作（创建、读取、更新、删除）
设计带关联关系的数据模型（用户→帖子、订单→商品）
在生产环境中安全迁移数据库 schema
配置连接池以提升性能
评估无服务器数据库选项（Neon、PlanetScale、Turso）
与前端技能集成（表单、表格、仪表盘、搜索过滤）

以下场景无需使用本技能：

大规模时序数据（使用时序数据库）
实时分析（使用列式数据库）
文档密集型工作负载（使用文档数据库）
键值缓存（使用Redis、Memcached）

Quick Reference: Database Selection

快速参考：数据库选择

Database Selection Decision Tree
═══════════════════════════════════════════════════════════

PRIMARY CONCERN?
├─ MAXIMUM FLEXIBILITY & EXTENSIONS (JSON, arrays, vector search)
│  └─ PostgreSQL
│     ├─ Serverless → Neon (scale-to-zero, database branching)
│     └─ Traditional → Self-hosted, AWS RDS, Google Cloud SQL
│
├─ EMBEDDED / EDGE DEPLOYMENT (local-first, global latency)
│  └─ SQLite or Turso
│     ├─ Global distribution → Turso (libSQL, edge replicas)
│     └─ Local-only → SQLite (embedded, zero-config)
│
├─ LEGACY SYSTEM / MYSQL REQUIRED
│  └─ MySQL
│     ├─ Serverless → PlanetScale (non-blocking migrations)
│     └─ Traditional → Self-hosted, AWS RDS, Google Cloud SQL
│
└─ RAPID PROTOTYPING
   ├─ Python → SQLModel (FastAPI) or SQLAlchemy 2.0
   ├─ TypeScript → Prisma (best DX) or Drizzle (performance)
   ├─ Rust → SQLx (compile-time checks)
   └─ Go → sqlc (type-safe code generation)

Database Selection Decision Tree
═══════════════════════════════════════════════════════════

PRIMARY CONCERN?
├─ MAXIMUM FLEXIBILITY & EXTENSIONS (JSON, arrays, vector search)
│  └─ PostgreSQL
│     ├─ Serverless → Neon (scale-to-zero, database branching)
│     └─ Traditional → Self-hosted, AWS RDS, Google Cloud SQL
│
├─ EMBEDDED / EDGE DEPLOYMENT (local-first, global latency)
│  └─ SQLite or Turso
│     ├─ Global distribution → Turso (libSQL, edge replicas)
│     └─ Local-only → SQLite (embedded, zero-config)
│
├─ LEGACY SYSTEM / MYSQL REQUIRED
│  └─ MySQL
│     ├─ Serverless → PlanetScale (non-blocking migrations)
│     └─ Traditional → Self-hosted, AWS RDS, Google Cloud SQL
│
└─ RAPID PROTOTYPING
   ├─ Python → SQLModel (FastAPI) or SQLAlchemy 2.0
   ├─ TypeScript → Prisma (best DX) or Drizzle (performance)
   ├─ Rust → SQLx (compile-time checks)
   └─ Go → sqlc (type-safe code generation)

Quick Reference: ORM vs Query Builder

快速参考：ORM vs 查询构建器

ORM vs Query Builder Selection
═══════════════════════════════════════════════════════════

TEAM PRIORITIES?
├─ DEVELOPMENT SPEED / DEVELOPER EXPERIENCE
│  └─ ORM (abstracts SQL, handles relations automatically)
│     ├─ Python → SQLAlchemy 2.0, SQLModel
│     ├─ TypeScript → Prisma (migrations, type generation)
│     ├─ Rust → SeaORM (Active Record + Data Mapper)
│     └─ Go → GORM, Ent
│
├─ PERFORMANCE / QUERY CONTROL
│  └─ Query Builder (SQL-like, zero abstraction overhead)
│     ├─ Python → SQLAlchemy Core, asyncpg
│     ├─ TypeScript → Drizzle, Kysely
│     ├─ Rust → SQLx (compile-time query validation!)
│     └─ Go → sqlc (generates types from SQL)
│
├─ TYPE SAFETY / COMPILE-TIME GUARANTEES
│  ├─ Rust → SQLx (queries checked at build time)
│  ├─ Go → sqlc (generates types from SQL)
│  ├─ TypeScript → Prisma or Drizzle
│  └─ Python → SQLModel (Pydantic integration)
│
└─ COMPLEX QUERIES / JOINS
   ├─ SQL-first → Query builders or raw SQL
   └─ ORM-friendly → SeaORM, SQLAlchemy ORM

ORM vs Query Builder Selection
═══════════════════════════════════════════════════════════

TEAM PRIORITIES?
├─ DEVELOPMENT SPEED / DEVELOPER EXPERIENCE
│  └─ ORM (abstracts SQL, handles relations automatically)
│     ├─ Python → SQLAlchemy 2.0, SQLModel
│     ├─ TypeScript → Prisma (migrations, type generation)
│     ├─ Rust → SeaORM (Active Record + Data Mapper)
│     └─ Go → GORM, Ent
│
├─ PERFORMANCE / QUERY CONTROL
│  └─ Query Builder (SQL-like, zero abstraction overhead)
│     ├─ Python → SQLAlchemy Core, asyncpg
│     ├─ TypeScript → Drizzle, Kysely
│     ├─ Rust → SQLx (compile-time query validation!)
│     └─ Go → sqlc (generates types from SQL)
│
├─ TYPE SAFETY / COMPILE-TIME GUARANTEES
│  ├─ Rust → SQLx (queries checked at build time)
│  ├─ Go → sqlc (generates types from SQL)
│  ├─ TypeScript → Prisma or Drizzle
│  └─ Python → SQLModel (Pydantic integration)
│
└─ COMPLEX QUERIES / JOINS
   ├─ SQL-first → Query builders or raw SQL
   └─ ORM-friendly → SeaORM, SQLAlchemy ORM

Multi-Language Implementation

多语言实现

Python: SQLAlchemy 2.0 + SQLModel

Recommended Libraries:

SQLAlchemy 2.0 (
```
/websites/sqlalchemy_en_21
```
) - ORM + Core, 7,090 snippets
SQLModel - FastAPI integration, Pydantic validation
asyncpg - High-performance async PostgreSQL driver

When to Use:

Production applications requiring flexibility
FastAPI/Starlette backends
Async/await workflows

Quick Pattern:

python

from sqlmodel import SQLModel, Field, Session
class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    email: str = Field(unique=True, index=True)

See:

references/orms-python.md

for complete SQLAlchemy/SQLModel patterns, async workflows, and connection pooling.

推荐库：

SQLAlchemy 2.0 (
```
/websites/sqlalchemy_en_21
```
) - ORM + Core，包含7090个代码片段
SQLModel - FastAPI集成、Pydantic验证
asyncpg - 高性能异步PostgreSQL驱动

适用场景：

需要灵活性的生产应用
FastAPI/Starlette后端
Async/await工作流

快速示例：

python

from sqlmodel import SQLModel, Field, Session
class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    email: str = Field(unique=True, index=True)

参考：

references/orms-python.md

包含完整的SQLAlchemy/SQLModel模式、异步工作流及连接池配置。

TypeScript: Prisma vs Drizzle

Recommended Libraries:

Prisma 6.x (
```
/prisma/prisma
```
, score: 96.4, 4,281 doc snippets) - Best DX, migrations
Drizzle ORM (
```
/drizzle-team/drizzle-orm-docs
```
, score: 95.4, 4,037 snippets) - Performance, SQL-like

Quick Comparison:

Prisma: Best DX, auto-generated types, migrations included
Drizzle: Best performance, SQL-like syntax, zero overhead

See:

references/orms-typescript.md

for Prisma vs Drizzle detailed comparison, Kysely, TypeORM patterns.

推荐库：

Prisma 6.x (
```
/prisma/prisma
```
, 评分：96.4，4281个文档片段) - 最佳开发体验（DX）、内置迁移功能
Drizzle ORM (
```
/drizzle-team/drizzle-orm-docs
```
, 评分：95.4，4037个代码片段) - 高性能、类SQL语法

快速对比：

Prisma：最佳开发体验，自动生成类型，内置迁移
Drizzle：最佳性能，类SQL语法，零抽象开销

参考：

references/orms-typescript.md

包含Prisma与Drizzle的详细对比、Kysely、TypeORM模式。

Rust: SQLx (Compile-Time Checked)

Rust: SQLx（编译时校验）

Recommended Libraries:

SQLx 0.8 - Compile-time query validation, async
SeaORM 1.x - Full ORM with Active Record pattern
Diesel 2.3 - Mature, stable (sync/async)

Quick Pattern:

rust

use sqlx::FromRow;
#[derive(FromRow)]
struct User { id: i32, email: String, name: String }
// Compile-time checked queries (verified at build time!)
let user = sqlx::query_as::<_, User>("SELECT * FROM users WHERE email = $1")
    .bind("test@example.com").fetch_one(&pool).await?;

See:

references/orms-rust.md

for SQLx macros, SeaORM, Diesel patterns, and compile-time guarantees.

推荐库：

SQLx 0.8 - 编译时查询验证、异步支持
SeaORM 1.x - 完整ORM，支持Active Record模式
Diesel 2.3 - 成熟稳定（同步/异步）

快速示例：

rust

use sqlx::FromRow;
#[derive(FromRow)]
struct User { id: i32, email: String, name: String }
// 编译时校验查询（构建阶段即验证！）
let user = sqlx::query_as::<_, User>("SELECT * FROM users WHERE email = $1")
    .bind("test@example.com").fetch_one(&pool).await?;

参考：

references/orms-rust.md

包含SQLx宏、SeaORM、Diesel模式及编译时保障说明。

Go: sqlc (Type-Safe Code Generation)

Go: sqlc（类型安全代码生成）

Recommended Libraries:

sqlc - Generates Go code from SQL queries
GORM v2 - Full ORM with associations, hooks
Ent - Graph-based ORM, schema as code
pgx - High-performance PostgreSQL driver

Quick Pattern:

sql

-- queries.sql: SQL annotations generate type-safe Go code
-- name: CreateUser :one
INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *;

user, err := queries.CreateUser(ctx, db.CreateUserParams{Email: "test@example.com"})

See:

references/orms-go.md

for sqlc setup, GORM, Ent, and pgx patterns.

推荐库：

sqlc - 从SQL查询生成Go代码
GORM v2 - 完整ORM，支持关联关系、钩子
Ent - 基于图的ORM，Schema即代码
pgx - 高性能PostgreSQL驱动

快速示例：

sql

-- queries.sql: SQL注解生成类型安全的Go代码
-- name: CreateUser :one
INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *;

user, err := queries.CreateUser(ctx, db.CreateUserParams{Email: "test@example.com"})

参考：

references/orms-go.md

包含sqlc配置、GORM、Ent及pgx模式。

Connection Pooling

连接池

Recommended Pool Sizes:

Web API (single instance): 10-20 connections
Serverless (per function): 1-2 connections + pgBouncer
Background workers: 5-10 connections

See:

references/connection-pooling.md

for configuration examples, sizing formulas, and monitoring strategies.

推荐连接池大小：

Web API（单实例）：10-20个连接
无服务器（单函数）：1-2个连接 + pgBouncer
后台工作进程：5-10个连接

参考：

references/connection-pooling.md

包含配置示例、容量计算公式及监控策略。

Migrations

数据迁移

Critical Principles:

Use multi-phase deployment for column drops (never drop directly in production)
Use
```
CREATE INDEX CONCURRENTLY
```
(PostgreSQL) to avoid blocking writes
Test migrations in staging with production-like data volume

Tools: Alembic (Python), Prisma Migrate (TypeScript), SQLx migrations (Rust), golang-migrate (Go)

See:

references/migrations-guide.md

for safe migration patterns, multi-phase deployments, and rollback strategies.

核心原则：

列删除操作采用多阶段部署（切勿在生产环境直接删除）
使用
```
CREATE INDEX CONCURRENTLY
```
（PostgreSQL）避免阻塞写入
在预发布环境使用类生产数据量测试迁移

工具： Alembic（Python）、Prisma Migrate（TypeScript）、SQLx migrations（Rust）、golang-migrate（Go）

参考：

references/migrations-guide.md

包含安全迁移模式、多阶段部署及回滚策略。

Serverless Databases

无服务器数据库

Database	Type	Key Feature	Best For
Neon	PostgreSQL	Database branching, scale-to-zero	Development workflows, preview environments
PlanetScale	MySQL (Vitess)	Non-blocking schema changes	MySQL apps, zero-downtime migrations
Turso	SQLite (libSQL)	Edge deployment, low latency	Edge functions, global distribution

See:

references/serverless-databases.md

for setup examples, branching workflows, and cost comparisons.

数据库	类型	核心特性	适用场景
Neon	PostgreSQL	数据库分支、Scale-to-Zero	开发工作流、预览环境
PlanetScale	MySQL (Vitess)	无阻塞 schema 变更	MySQL 应用、零停机迁移
Turso	SQLite (libSQL)	边缘部署、低延迟	边缘函数、全球分布式部署

参考：

references/serverless-databases.md

包含配置示例、分支工作流及成本对比。

Frontend Integration

前端集成

Common Integration Patterns:

Forms skill: Form submission → API validation → Database CRUD (INSERT/UPDATE)
Tables skill: Paginated queries → API → Table display with sorting/filtering
Dashboards skill: Aggregation queries (COUNT, SUM) → API → KPI cards
Search-filter skill: Full-text search (PostgreSQL tsvector) → Ranked results

See working examples in:

examples/python-sqlalchemy/

examples/typescript-drizzle/

examples/rust-sqlx/

常见集成模式：

表单技能：表单提交 → API验证 → 数据库CRUD（插入/更新）
表格技能：分页查询 → API → 带排序/过滤的表格展示
仪表盘技能：聚合查询（COUNT、SUM）→ API → KPI卡片
搜索过滤技能：全文搜索（PostgreSQL tsvector）→ 排序结果

查看示例：

examples/python-sqlalchemy/

、

examples/typescript-drizzle/

、

examples/rust-sqlx/

Bundled Resources

配套资源

Reference Documentation

参考文档

```
references/postgresql-guide.md
```
- PostgreSQL features (pgvector, PostGIS, TimescaleDB)
```
references/mysql-guide.md
```
- MySQL-specific patterns, PlanetScale integration
```
references/sqlite-guide.md
```
- SQLite patterns, Turso edge deployment
```
references/orms-python.md
```
- SQLAlchemy 2.0, SQLModel, asyncpg
```
references/orms-typescript.md
```
- Prisma, Drizzle, Kysely comparisons
```
references/orms-rust.md
```
- SQLx, SeaORM, Diesel
```
references/orms-go.md
```
- GORM, sqlc, Ent, pgx
```
references/migrations-guide.md
```
- Safe schema evolution patterns
```
references/connection-pooling.md
```
- Pool sizing and monitoring
```
references/serverless-databases.md
```
- Neon, PlanetScale, Turso deployment

```
references/postgresql-guide.md
```
- PostgreSQL特性（pgvector、PostGIS、TimescaleDB）
```
references/mysql-guide.md
```
- MySQL专属模式、PlanetScale集成
```
references/sqlite-guide.md
```
- SQLite模式、Turso边缘部署
```
references/orms-python.md
```
- SQLAlchemy 2.0、SQLModel、asyncpg
```
references/orms-typescript.md
```
- Prisma、Drizzle、Kysely对比
```
references/orms-rust.md
```
- SQLx、SeaORM、Diesel
```
references/orms-go.md
```
- GORM、sqlc、Ent、pgx
```
references/migrations-guide.md
```
- 安全 schema 演进模式
```
references/connection-pooling.md
```
- 连接池容量配置与监控
```
references/serverless-databases.md
```
- Neon、PlanetScale、Turso部署

Working Examples

实战示例

```
examples/python-sqlalchemy/
```
- SQLAlchemy 2.0 + FastAPI with pooling, migrations
```
examples/typescript-prisma/
```
- Prisma + Next.js with schema, migrations
```
examples/typescript-drizzle/
```
- Drizzle + Hono with type-safe queries
```
examples/rust-sqlx/
```
- SQLx + Axum with compile-time checks
```
examples/go-sqlc/
```
- sqlc + Gin with generated type-safe code

```
examples/python-sqlalchemy/
```
- SQLAlchemy 2.0 + FastAPI，含连接池、迁移
```
examples/typescript-prisma/
```
- Prisma + Next.js，含schema、迁移
```
examples/typescript-drizzle/
```
- Drizzle + Hono，含类型安全查询
```
examples/rust-sqlx/
```
- SQLx + Axum，含编译时校验
```
examples/go-sqlc/
```
- sqlc + Gin，含生成的类型安全代码

Utility Scripts

实用脚本

```
scripts/validate_schema.py
```
- Validate database schema structure, constraints
```
scripts/generate_migration.py
```
- Generate migration templates for common operations

```
scripts/validate_schema.py
```
- 验证数据库schema结构、约束
```
scripts/generate_migration.py
```
- 为常见操作生成迁移模板

Best Practices

最佳实践

Security:

Always use parameterized queries (prevents SQL injection)
Hash passwords with Argon2/bcrypt
Use environment variables for connection strings
Enable SSL/TLS in production

Performance:

Use connection pooling (10-20 for web APIs)
Create indexes on filtered/sorted columns
Implement pagination for large result sets
Use
```
EXPLAIN ANALYZE
```
for slow queries

Reliability:

Test migrations in staging first
Use transactions for multi-statement operations
Monitor connection pool exhaustion
Set up and test database backups

Development:

Version control schema and migrations
Use database branching (Neon) for features
Write integration tests against real databases

安全：

始终使用参数化查询（防止SQL注入）
使用Argon2/bcrypt哈希密码
使用环境变量存储连接字符串
生产环境启用SSL/TLS

性能：

使用连接池（Web API建议10-20个连接）
为需过滤/排序的列创建索引
对大结果集实现分页
对慢查询使用
```
EXPLAIN ANALYZE
```
分析

可靠性：

先在预发布环境测试迁移
多语句操作使用事务
监控连接池耗尽情况
配置并测试数据库备份

开发：

对schema和迁移进行版本控制
使用数据库分支（Neon）开发功能
针对真实数据库编写集成测试