using-relational-databases_skill

Overview

This skill helps you select, design, and implement relational databases across Python, Rust, Go, and TypeScript. It covers engine choice (PostgreSQL, MySQL, SQLite), ORMs and query builders, migrations, connection pooling, and serverless database patterns. Use it to build reliable CRUD apps, transactional systems, and structured data storage with language-appropriate tooling.

How this skill works

The skill evaluates priorities—flexibility, latency, type safety, or developer experience—and recommends engines and libraries (e.g., PostgreSQL + SQLAlchemy, Prisma, SQLx, sqlc). It provides patterns for schema design, migrations, pooling, and serverless setups (Neon, PlanetScale, Turso), plus concrete code and deployment examples per language. It also includes safe migration practices, connection sizing guidance, and integration patterns for frontend forms, tables, and dashboards.

When to use it

• Building CRUD apps, authentication, CMS, or e‑commerce backends
• Designing relational data models with joins and constraints
• Implementing transactional workflows and multi-statement operations
• Setting up connection pooling or optimizing DB performance
• Evaluating serverless or edge databases for previews and global distribution

Best practices

• Prefer PostgreSQL for flexibility (JSON, arrays, extensions) and use serverless Neon for branching/development
• Use ORMs for developer speed (SQLAlchemy, Prisma, SeaORM) and query builders/sql-first tools for performance and type safety (SQLx, sqlc, Drizzle)
• Always use parameterized queries, hash passwords (Argon2/bcrypt), and store credentials in environment variables
• Test migrations in staging, use multi-phase deploys for destructive changes, and use CREATE INDEX CONCURRENTLY for Postgres
• Size pools: web API 10–20, background workers 5–10, serverless use pooled proxies (pgBouncer) or 1–2 connections per function
• Monitor slow queries with EXPLAIN ANALYZE and add indexes for common filters/sorts; implement pagination for large result sets

Example use cases

• FastAPI + SQLModel for a production web API with asyncpg pooling and Alembic migrations
• Next.js + Prisma for a SaaS product with auto-generated types and migration flows
• Edge function + Turso for low-latency, globally distributed SQLite use cases
• Axum + SQLx in Rust for a performance-sensitive service with compile-time query checks
• Gin + sqlc in Go for type-safe SQL-driven CRUD microservices