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("[e-mail]").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: "[e-mail]"})

See: references/orms-go.md for sqlc setup, GORM, Ent, and pgx patterns.

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.

Migrations

Critical Principles:

1. Use multi-phase deployment for column drops (never drop directly in production)
2. Use CREATE INDEX CONCURRENTLY (PostgreSQL) to avoid blocking writes
3. 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.

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.

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/

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

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

Utility Scripts

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

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