/using-relational-databases

name: using-relational-databases
description: Relational database implementation across Python, Rust, Go, and TypeScript. Use when building CRUD applications, transactional systems, or structured data storage. Covers PostgreSQL (primary), MySQL, SQLite, ORMs (SQLAlchemy, Prisma, SeaORM, GORM), query builders (Drizzle, sqlc, SQLx), migrations, connection pooling, and serverless databases (Neon, PlanetScale, Turso).

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.

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)

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)

Quick Reference: ORM vs Query Builder

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:**

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.

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.

Rust: SQLx (Compile-Time Checked)

**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:**

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.

Go: sqlc (Type-Safe Code Generation)

**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:**

-- 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.

Connection Pool