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 connect…

)\n);\n```\n\n**Use foreign keys:**\n```sql\nCREATE TABLE posts (\n id SERIAL PRIMARY KEY,\n user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n title TEXT NOT NULL\n);\n```\n\n**Use appropriate data types:**\n- `SERIAL` or `BIGSERIAL` for auto-incrementing IDs\n- `TIMESTAMPTZ` (not `TIMESTAMP`) for timestamps (stores timezone)\n- `TEXT` for variable-length strings (no length limit)\n- `VARCHAR(n)` only when length constraint required\n- `JSONB` (not `JSON`) for JSON data (faster queries)\n\n### Security\n\n**Create application user with limited permissions:**\n```sql\nCREATE USER myapp_user WITH PASSWORD 'secure_password';\nGRANT CONNECT ON DATABASE mydb TO myapp_user;\nGRANT USAGE ON SCHEMA public TO myapp_user;\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_user;\nGRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myapp_user;\n```\n\n**Enable SSL:**\n```sql\n-- postgresql.conf\nssl = on\nssl_cert_file = '/path/to/server.crt'\nssl_key_file = '/path/to/server.key'\n```\n\n**Connection string with SSL:**\n```\npostgresql://user:pass@host:5432/db?sslmode=require\n```\n\n### Backups\n\n**pg_dump (logical backup):**\n```bash\npg_dump -U postgres -d mydb -F c -f mydb_backup.dump\n# Restore: pg_restore -U postgres -d mydb mydb_backup.dump\n```\n\n**pg_basebackup (physical backup):**\n```bash\npg_basebackup -U postgres -D /backup/mydb -F tar -z -P\n```\n\n**Continuous archiving (WAL archiving):**\n```sql\n-- postgresql.conf\nwal_level = replica\narchive_mode = on\narchive_command = 'cp %p /backup/archive/%f'\n```\n\n**Point-in-time recovery (PITR):**\nRestore base backup + replay WAL files to specific timestamp.\n\n## Common Patterns\n\n### Soft Deletes\n\n```sql\nCREATE TABLE users (\n id SERIAL PRIMARY KEY,\n email VARCHAR(255),\n deleted_at TIMESTAMPTZ -- NULL = not deleted\n);\n\n-- Create partial index (only active users)\nCREATE UNIQUE INDEX idx_users_email_active ON users(email)\nWHERE deleted_at IS NULL;\n\n-- Soft delete\nUPDATE users SET deleted_at = NOW() WHERE id = 123;\n\n-- Query active users\nSELECT * FROM users WHERE deleted_at IS NULL;\n```\n\n### Audit Logs\n\n```sql\nCREATE TABLE users_audit (\n id SERIAL PRIMARY KEY,\n user_id INTEGER,\n operation VARCHAR(10), -- INSERT, UPDATE, DELETE\n old_data JSONB,\n new_data JSONB,\n changed_by INTEGER,\n changed_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE FUNCTION users_audit_trigger() RETURNS TRIGGER AS $\nBEGIN\n IF (TG_OP = 'DELETE') THEN\n INSERT INTO users_audit (user_id, operation, old_data)\n VALUES (OLD.id, 'DELETE', row_to_json(OLD));\n ELSIF (TG_OP = 'UPDATE') THEN\n INSERT INTO users_audit (user_id, operation, old_data, new_data)\n VALUES (NEW.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW));\n ELSIF (TG_OP = 'INSERT') THEN\n INSERT INTO users_audit (user_id, operation, new_data)\n VALUES (NEW.id, 'INSERT', row_to_json(NEW));\n END IF;\n RETURN NEW;\nEND;\n$ LANGUAGE plpgsql;\n\nCREATE TRIGGER users_audit_trigger\nAFTER INSERT OR UPDATE OR DELETE ON users\nFOR EACH ROW EXECUTE FUNCTION users_audit_trigger();\n```\n\n### UUID Primary Keys\n\n```sql\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";\n\nCREATE TABLE users (\n id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),\n email VARCHAR(255) UNIQUE NOT NULL\n);\n```\n\n**Benefits:**\n- Globally unique (no conflicts across databases)\n- Secure (unpredictable)\n- Distributed-friendly\n\n**Drawbacks:**\n- Larger storage (16 bytes vs 4 bytes for INTEGER)\n- Slower index performance (random inserts)\n- Less human-readable\n\n**Recommendation:** Use UUID for distributed systems, SERIAL/BIGSERIAL for single-database apps.\n\n## Resources\n\n**Official Documentation:**\n- PostgreSQL Docs: https://www.postgresql.org/docs/\n- pgvector: https://github.com/pgvector/pgvector\n- PostGIS: https://postgis.net/\n- TimescaleDB: https://docs.timescale.com/\n\n**Performance Tools:**\n- PgHero: https://github.com/ankane/pghero (performance dashboard)\n- pg_stat_statements: Built-in query statistics extension\n- EXPLAIN visualizer: https://explain.dalibo.com/\n\n**Community:**\n- PostgreSQL Wiki: https://wiki.postgresql.org/\n- Planet PostgreSQL: https://planet.postgresql.org/ (blog aggregator)\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":16698,"content_sha256":"0f33ce882e15b95c653c6133c0048bbcd62c9d2fcaf98a6a7d0c507a1ec188de"},{"filename":"references/serverless-databases.md","content":"# Serverless Databases Guide\n\nSetup, branching workflows, and cost comparisons for Neon (PostgreSQL), PlanetScale (MySQL), and Turso (SQLite).\n\n\n## Table of Contents\n\n- [Comparison Overview](#comparison-overview)\n- [Neon PostgreSQL](#neon-postgresql)\n - [Key Features](#key-features)\n - [Setup](#setup)\n - [Connection String](#connection-string)\n - [Database Branching Workflow](#database-branching-workflow)\n - [Autoscaling Configuration](#autoscaling-configuration)\n - [Time-Travel Queries](#time-travel-queries)\n- [PlanetScale MySQL](#planetscale-mysql)\n - [Key Features](#key-features)\n - [Setup](#setup)\n - [Connection String](#connection-string)\n - [Non-Blocking Schema Changes](#non-blocking-schema-changes)\n - [Prisma Integration](#prisma-integration)\n - [Read Replicas](#read-replicas)\n- [Turso SQLite](#turso-sqlite)\n - [Key Features](#key-features)\n - [Setup](#setup)\n - [Connection](#connection)\n - [Embedded Replicas (Local-First)](#embedded-replicas-local-first)\n - [Edge Deployment](#edge-deployment)\n- [Cost Comparison](#cost-comparison)\n - [Neon (PostgreSQL)](#neon-postgresql)\n - [PlanetScale (MySQL)](#planetscale-mysql)\n - [Turso (SQLite)](#turso-sqlite)\n- [Decision Framework](#decision-framework)\n - [Choose Neon if:](#choose-neon-if)\n - [Choose PlanetScale if:](#choose-planetscale-if)\n - [Choose Turso if:](#choose-turso-if)\n- [Integration Examples](#integration-examples)\n - [Next.js + Neon (Vercel)](#nextjs-neon-vercel)\n - [Cloudflare Workers + Turso](#cloudflare-workers-turso)\n - [Prisma + PlanetScale](#prisma-planetscale)\n- [Best Practices](#best-practices)\n - [Neon](#neon)\n - [PlanetScale](#planetscale)\n - [Turso](#turso)\n- [Resources](#resources)\n\n## Comparison Overview\n\n| Feature | Neon (PostgreSQL) | PlanetScale (MySQL) | Turso (SQLite) |\n|---------|-------------------|---------------------|----------------|\n| **Database Type** | PostgreSQL | MySQL (Vitess) | SQLite (libSQL) |\n| **Scale-to-Zero** | ✓✓✓ (Compute) | ✗ | ✓✓ (Replicas) |\n| **Branching** | ✓✓✓ (Git-like) | ✓✓✓ (Non-blocking deploys) | ✓ (Embedded replicas) |\n| **Cold Start** | \u003c500ms | N/A | \u003c10ms |\n| **Autoscaling** | ✓✓✓ (0-8 CPU) | ✓ (Connection pooling) | ✓ (Replica distribution) |\n| **Read Replicas** | ✓ (Manual) | ✓✓✓ (Built-in) | ✓✓✓ (Edge replicas) |\n| **Write Latency** | ~10-50ms | ~10-50ms | ~5-20ms (edge) |\n| **Free Tier** | 0.5GB storage, 3 branches | 5GB storage, 1B reads | 8GB storage, 500 locations |\n| **Pricing** | $19/month (Launch) | $29/month (Scaler) | $29/month (Scaler) |\n\n---\n\n## Neon PostgreSQL\n\n**Best for:** Preview environments, development branching, scale-to-zero PostgreSQL\n\n### Key Features\n\n- **Instant database branches** - Create copy of production in \u003c1 second\n- **Scale-to-zero compute** - Automatically pause after inactivity\n- **Time-travel queries** - Query database state from any point in time\n- **Autoscaling** - Scale compute from 0.25 to 8 vCPU automatically\n- **Point-in-time restore** - Restore to any second in the last 30 days\n\n### Setup\n\n```bash\n# Install Neon CLI\nnpm install -g neonctl\n\n# Create project\nneonctl projects create --name myapp\n\n# Create branch\nneonctl branches create --name dev --parent main\n\n# Get connection string\nneonctl connection-string main\n```\n\n### Connection String\n\n```bash\n# Pooled connection (recommended for serverless)\npostgresql://user:[email protected]/mydb?sslmode=require\n\n# Direct connection (for migrations)\npostgresql://user:[email protected]/mydb?sslmode=require\n```\n\n### Database Branching Workflow\n\n**Feature development:**\n```bash\n# 1. Create branch from main\nneonctl branches create --name feature-auth\n\n# 2. Get branch connection string\nneonctl connection-string feature-auth\n\n# 3. Run migrations on branch\nDATABASE_URL=$(neonctl connection-string feature-auth) npm run prisma migrate dev\n\n# 4. Test feature with real data (copy of production)\n# 5. Delete branch when done\nneonctl branches delete feature-auth\n```\n\n**Preview deployments (Vercel integration):**\n```bash\n# Each PR gets a database branch automatically\n# vercel.json\n{\n \"build\": {\n \"env\": {\n \"DATABASE_URL\": \"@neon-preview-branch\"\n }\n }\n}\n```\n\n### Autoscaling Configuration\n\n```typescript\n// Neon automatically scales compute based on load\n// Configure via dashboard or API:\n// - Min: 0.25 vCPU (scale-to-zero)\n// - Max: 8 vCPU\n// - Autosuspend: 5 minutes of inactivity\n```\n\n### Time-Travel Queries\n\n```sql\n-- Query database state from 2 hours ago\nSELECT * FROM users AS OF SYSTEM TIME '2025-12-03 10:00:00';\n\n-- Point-in-time restore via dashboard or CLI\nneonctl branches restore main --timestamp '2025-12-03 10:00:00'\n```\n\n---\n\n## PlanetScale MySQL\n\n**Best for:** Non-blocking schema changes, MySQL compatibility, read-heavy workloads\n\n### Key Features\n\n- **Non-blocking schema changes** - Deploy schema changes with zero downtime\n- **Database branching** - Create development branches with production schema\n- **Built-in read replicas** - Automatic geographic distribution\n- **Online DDL** - ALTER TABLE without locking\n- **No foreign key constraints** - Application-enforced referential integrity\n\n### Setup\n\n```bash\n# Install PlanetScale CLI\nbrew install planetscale/tap/pscale\n\n# Authenticate\npscale auth login\n\n# Create database\npscale database create myapp --region us-east\n\n# Create branch\npscale branch create myapp dev\n```\n\n### Connection String\n\n```bash\n# Get connection string (includes SSL certificate)\npscale connect myapp main --port 3309\n\n# Connection string format\nmysql://username:[email protected]/myapp?ssl={\"rejectUnauthorized\":true}\n```\n\n### Non-Blocking Schema Changes\n\n**Deploy request workflow:**\n```bash\n# 1. Create development branch\npscale branch create myapp add-user-phone\n\n# 2. Connect to branch\npscale shell myapp add-user-phone\n\n# 3. Make schema changes\nmysql> ALTER TABLE users ADD COLUMN phone VARCHAR(20);\n\n# 4. Create deploy request\npscale deploy-request create myapp add-user-phone\n\n# 5. Review diff\npscale deploy-request diff myapp 1\n\n# 6. Deploy (zero downtime)\npscale deploy-request deploy myapp 1\n\n# 7. Auto-merge to main after deploy\n```\n\n### Prisma Integration\n\n```prisma\n// schema.prisma\ndatasource db {\n provider = \"mysql\"\n url = env(\"DATABASE_URL\")\n relationMode = \"prisma\" // Required: No FK constraints\n}\n\nmodel User {\n id Int @id @default(autoincrement())\n email String @unique\n posts Post[]\n}\n\nmodel Post {\n id Int @id @default(autoincrement())\n userId Int\n user User @relation(fields: [userId], references: [id])\n\n @@index([userId]) // Manual index (replaces FK)\n}\n```\n\n### Read Replicas\n\n```javascript\n// Automatically routed to nearest replica\nconst users = await prisma.user.findMany(); // Read from replica\n\n// Force primary for consistency\nconst user = await prisma.user.create({ data: { email: '[email protected]' } }); // Write to primary\n```\n\n---\n\n## Turso SQLite\n\n**Best for:** Edge applications, local-first apps, global low-latency reads\n\n### Key Features\n\n- **Edge replicas** - Deploy to 200+ locations globally\n- **Embedded replicas** - Local SQLite + cloud sync\n- **Sub-10ms reads** - Query from nearest edge location\n- **LibSQL** - SQLite fork with extensions\n- **Multi-region writes** - Eventually consistent replication\n\n### Setup\n\n```bash\n# Install Turso CLI\ncurl -sSfL https://get.tur.so/install.sh | bash\n\n# Create database\nturso db create myapp\n\n# Create replica in multiple regions\nturso db replicate myapp --region ams # Amsterdam\nturso db replicate myapp --region sin # Singapore\n\n# Get connection URL\nturso db show myapp --url\n```\n\n### Connection\n\n```typescript\nimport { createClient } from '@libsql/client';\n\nconst db = createClient({\n url: process.env.TURSO_URL,\n authToken: process.env.TURSO_AUTH_TOKEN,\n});\n\nconst users = await db.execute('SELECT * FROM users');\n```\n\n### Embedded Replicas (Local-First)\n\n```typescript\nimport { createClient } from '@libsql/client';\n\nconst db = createClient({\n url: 'file:local.db', // Local SQLite file\n syncUrl: process.env.TURSO_URL, // Turso cloud\n authToken: process.env.TURSO_AUTH_TOKEN,\n});\n\n// Read from local (instant)\nconst users = await db.execute('SELECT * FROM users');\n\n// Sync to cloud (background)\nawait db.sync();\n```\n\n### Edge Deployment\n\n```typescript\n// Cloudflare Worker + Turso\nexport default {\n async fetch(request, env) {\n const db = createClient({\n url: env.TURSO_URL,\n authToken: env.TURSO_AUTH_TOKEN,\n });\n\n const users = await db.execute('SELECT * FROM users');\n return Response.json(users.rows);\n },\n};\n```\n\n---\n\n## Cost Comparison\n\n### Neon (PostgreSQL)\n\n**Free Tier:**\n- 0.5 GB storage\n- 3 compute branches\n- Shared compute (0.25 vCPU)\n\n**Launch ($19/month):**\n- 10 GB storage\n- 10 branches\n- 2 vCPU autoscaling (0.25-2)\n- Point-in-time restore (7 days)\n\n**Scale ($69/month):**\n- 50 GB storage\n- Unlimited branches\n- 8 vCPU autoscaling (0.25-8)\n- Point-in-time restore (30 days)\n\n**Break-even:** 24/7 usage vs managed PostgreSQL (~$50/month)\n\n---\n\n### PlanetScale (MySQL)\n\n**Hobby (Free):**\n- 5 GB storage\n- 1 billion row reads/month\n- 10 million row writes/month\n- 1 production branch\n\n**Scaler ($29/month):**\n- 10 GB storage\n- 100 billion row reads/month\n- 50 million row writes/month\n- 2 production branches\n- Deploy requests\n\n**Business ($39/month per branch):**\n- Unlimited reads/writes\n- Multiple production branches\n- SSO/SAML\n\n**Break-even:** High read volume vs managed MySQL\n\n---\n\n### Turso (SQLite)\n\n**Starter (Free):**\n- 8 GB storage\n- 500 database locations (replicas)\n- 1 billion row reads/month\n\n**Scaler ($29/month):**\n- 50 GB storage\n- 1,000 locations\n- 100 billion row reads/month\n\n**Enterprise (Custom):**\n- Unlimited\n- SLA guarantees\n- Dedicated support\n\n**Break-even:** Edge use cases with global distribution\n\n---\n\n## Decision Framework\n\n### Choose Neon if:\n- Need PostgreSQL features (JSONB, arrays, extensions)\n- Want scale-to-zero compute\n- Database branching for preview environments is critical\n- Development team needs isolated database copies\n\n### Choose PlanetScale if:\n- MySQL compatibility required\n- Non-blocking schema changes are critical\n- High read volume with geographic distribution\n- Want automated read replicas\n\n### Choose Turso if:\n- Building edge applications (Cloudflare Workers, Deno Deploy)\n- Need sub-10ms global reads\n- Local-first architecture (offline-capable apps)\n- SQLite compatibility required\n\n---\n\n## Integration Examples\n\n### Next.js + Neon (Vercel)\n\n```bash\n# 1. Create Neon database\nneonctl projects create\n\n# 2. Add to Vercel\nvercel env add DATABASE_URL\n\n# 3. Automatic branching (preview deployments)\n# Each PR gets a Neon branch automatically\n```\n\n### Cloudflare Workers + Turso\n\n```typescript\nexport default {\n async fetch(request, env) {\n const db = createClient({\n url: env.TURSO_URL,\n authToken: env.TURSO_AUTH_TOKEN,\n });\n\n const result = await db.execute({\n sql: 'SELECT * FROM users WHERE id = ?',\n args: [1],\n });\n\n return Response.json(result.rows[0]);\n },\n};\n```\n\n### Prisma + PlanetScale\n\n```typescript\n// prisma/schema.prisma\ndatasource db {\n provider = \"mysql\"\n url = env(\"DATABASE_URL\")\n relationMode = \"prisma\"\n}\n\n// Deploy schema changes\npscale branch create myapp migration\npscale connect myapp migration --port 3309\nDATABASE_URL=\"mysql://127.0.0.1:3309/myapp\" npx prisma db push\npscale deploy-request create myapp migration\n```\n\n---\n\n## Best Practices\n\n### Neon\n\n1. Use **pooled connections** for serverless functions\n2. **Branch per feature** for isolated development\n3. Enable **autoscaling** for variable workloads\n4. Use **time-travel** for debugging production issues\n\n### PlanetScale\n\n1. Use **deploy requests** for all schema changes\n2. **Index all foreign key columns** (no FK constraints)\n3. Test schema changes on **development branches**\n4. Monitor **row read/write quotas** on dashboard\n\n### Turso\n\n1. Deploy **replicas near users** for low latency\n2. Use **embedded replicas** for offline-first apps\n3. **Sync periodically** in background (embedded mode)\n4. Monitor **replication lag** in multi-region setups\n\n---\n\n## Resources\n\n- Neon Docs: https://neon.tech/docs\n- PlanetScale Docs: https://planetscale.com/docs\n- Turso Docs: https://docs.turso.tech\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":12356,"content_sha256":"873c9cd33b1e2ae5e36e41dda43d8e8fb4d217a0b02bf7698323762115cf8db8"},{"filename":"references/sqlite-guide.md","content":"# SQLite Implementation Guide\n\nSQLite patterns, Turso edge deployment, and embedded database best practices.\n\n\n## Table of Contents\n\n- [When to Choose SQLite](#when-to-choose-sqlite)\n- [Installation](#installation)\n- [Basic Usage](#basic-usage)\n - [Python](#python)\n - [TypeScript (better-sqlite3)](#typescript-better-sqlite3)\n- [Turso (Edge SQLite)](#turso-edge-sqlite)\n - [Setup](#setup)\n - [TypeScript Integration](#typescript-integration)\n - [Drizzle ORM + Turso](#drizzle-orm-turso)\n- [Performance Optimization](#performance-optimization)\n - [Write-Ahead Logging (WAL)](#write-ahead-logging-wal)\n - [Indexing](#indexing)\n - [Connection Pooling (Not Needed)](#connection-pooling-not-needed)\n- [Full-Text Search](#full-text-search)\n- [Backup and Restore](#backup-and-restore)\n- [Multi-Database (Attach)](#multi-database-attach)\n- [Concurrency](#concurrency)\n- [Turso Embedded Replicas](#turso-embedded-replicas)\n- [Best Practices](#best-practices)\n- [Common Pitfalls](#common-pitfalls)\n- [Migration to PostgreSQL/MySQL](#migration-to-postgresqlmysql)\n- [Resources](#resources)\n\n## When to Choose SQLite\n\n- **Embedded applications** - Mobile apps, desktop software, edge devices\n- **Edge deployment** - Turso for global low-latency reads (\u003c10ms)\n- **Local-first apps** - Offline-capable PWAs, mobile apps\n- **Prototyping** - Zero-config development databases\n- **Small-medium datasets** - \u003c100GB with proper indexing\n\n**When PostgreSQL/MySQL is better:** Multi-user writes, complex transactions, >100GB data\n\n## Installation\n\n```bash\n# Included with Python\npython3 -c \"import sqlite3; print(sqlite3.sqlite_version)\"\n\n# TypeScript\nnpm install better-sqlite3\n\n# Rust\ncargo add rusqlite\n\n# Go (built-in)\nimport \"database/sql\"\nimport _ \"github.com/mattn/go-sqlite3\"\n```\n\n## Basic Usage\n\n### Python\n\n```python\nimport sqlite3\n\nconn = sqlite3.connect('app.db')\ncursor = conn.cursor()\n\n# Create table\ncursor.execute('''\n CREATE TABLE IF NOT EXISTS users (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n email TEXT UNIQUE NOT NULL,\n name TEXT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n )\n''')\n\n# Insert\ncursor.execute('INSERT INTO users (email, name) VALUES (?, ?)', ('[email protected]', 'John'))\nconn.commit()\n\n# Query\ncursor.execute('SELECT * FROM users WHERE email = ?', ('[email protected]',))\nuser = cursor.fetchone()\n```\n\n### TypeScript (better-sqlite3)\n\n```typescript\nimport Database from 'better-sqlite3';\n\nconst db = new Database('app.db');\n\n// Create table\ndb.exec(`\n CREATE TABLE IF NOT EXISTS users (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n email TEXT UNIQUE NOT NULL,\n name TEXT NOT NULL\n )\n`);\n\n// Prepared statement (faster for multiple inserts)\nconst insert = db.prepare('INSERT INTO users (email, name) VALUES (?, ?)');\ninsert.run('[email protected]', 'John');\n\n// Query\nconst user = db.prepare('SELECT * FROM users WHERE email = ?').get('[email protected]');\n```\n\n## Turso (Edge SQLite)\n\n**libSQL (SQLite fork) deployed globally with \u003c10ms reads.**\n\n### Setup\n\n```bash\n# Install Turso CLI\ncurl -sSfL https://get.tur.so/install.sh | bash\n\n# Create database\nturso db create myapp\n\n# Get connection URL\nturso db show myapp --url\n```\n\n### TypeScript Integration\n\n```typescript\nimport { createClient } from '@libsql/client';\n\nconst db = createClient({\n url: 'libsql://myapp-username.turso.io',\n authToken: process.env.TURSO_AUTH_TOKEN,\n});\n\n// Query\nconst result = await db.execute('SELECT * FROM users WHERE email = ?', ['[email protected]']);\nconsole.log(result.rows);\n\n// Transaction\nawait db.batch([\n { sql: 'INSERT INTO users (email, name) VALUES (?, ?)', args: ['[email protected]', 'Alice'] },\n { sql: 'INSERT INTO posts (user_id, title) VALUES (?, ?)', args: [1, 'Hello'] },\n]);\n```\n\n### Drizzle ORM + Turso\n\n```typescript\nimport { drizzle } from 'drizzle-orm/libsql';\nimport { createClient } from '@libsql/client';\n\nconst client = createClient({ url: process.env.TURSO_URL, authToken: process.env.TURSO_TOKEN });\nconst db = drizzle(client);\n\nconst users = await db.select().from(usersTable).where(eq(usersTable.email, '[email protected]'));\n```\n\n## Performance Optimization\n\n### Write-Ahead Logging (WAL)\n\n```sql\n-- Enable WAL mode (significantly faster writes)\nPRAGMA journal_mode = WAL;\nPRAGMA synchronous = NORMAL;\nPRAGMA cache_size = -64000; -- 64MB cache\nPRAGMA temp_store = MEMORY;\n```\n\n**Benefits:**\n- Concurrent reads during writes\n- Faster writes (3-5x)\n- Better crash recovery\n\n### Indexing\n\n```sql\nCREATE INDEX idx_users_email ON users(email);\nCREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);\n\n-- Analyze query plan\nEXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';\n```\n\n### Connection Pooling (Not Needed)\n\nSQLite uses file locking, not connection pooling. Use a single connection per application.\n\n## Full-Text Search\n\n```sql\n-- Create FTS5 virtual table\nCREATE VIRTUAL TABLE articles_fts USING fts5(title, content);\n\n-- Insert data\nINSERT INTO articles_fts (title, content) VALUES ('SQLite Guide', 'Full-text search in SQLite...');\n\n-- Search\nSELECT * FROM articles_fts WHERE articles_fts MATCH 'full-text' ORDER BY rank;\n```\n\n## Backup and Restore\n\n```bash\n# Backup\nsqlite3 app.db \".backup backup.db\"\n\n# Or with CLI\nsqlite3 app.db \"VACUUM INTO 'backup.db'\"\n\n# Restore\ncp backup.db app.db\n```\n\n## Multi-Database (Attach)\n\n```sql\nATTACH DATABASE 'analytics.db' AS analytics;\n\nSELECT u.name, COUNT(a.id) as events\nFROM users u\nJOIN analytics.events a ON u.id = a.user_id\nGROUP BY u.id;\n\nDETACH DATABASE analytics;\n```\n\n## Concurrency\n\n**SQLite concurrency model:**\n- Multiple readers OR one writer\n- WAL mode allows concurrent reads during writes\n- IMMEDIATE transaction for write intent\n\n```python\n# Proper transaction handling\nconn.execute('BEGIN IMMEDIATE')\ntry:\n conn.execute('UPDATE users SET name = ? WHERE id = ?', ('Alice', 1))\n conn.execute('INSERT INTO audit_log (action) VALUES (?)', ('update_user',))\n conn.commit()\nexcept Exception as e:\n conn.rollback()\n raise\n```\n\n## Turso Embedded Replicas\n\n**Local-first with cloud sync:**\n```typescript\nimport { createClient } from '@libsql/client';\n\nconst db = createClient({\n url: 'file:local.db', // Local SQLite file\n syncUrl: 'libsql://myapp.turso.io', // Turso cloud\n authToken: process.env.TURSO_TOKEN,\n});\n\n// Sync to cloud\nawait db.sync();\n```\n\n## Best Practices\n\n1. **Enable WAL mode** for better write performance\n2. **Use prepared statements** to prevent SQL injection\n3. **Single connection per app** (no pooling needed)\n4. **Index foreign keys** manually\n5. **Use INTEGER PRIMARY KEY** for auto-increment (alias for rowid)\n6. **VACUUM regularly** to reclaim space\n7. **Analyze after bulk inserts** to update query planner stats\n8. **Use transactions** for multiple writes\n9. **Limit database size** to \u003c100GB for optimal performance\n10. **Consider Turso** for edge deployment with replication\n\n## Common Pitfalls\n\n**INTEGER vs INT:**\n```sql\n-- ✅ INTEGER PRIMARY KEY (alias for rowid, auto-increment)\nCREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);\n\n-- ❌ INT PRIMARY KEY (not alias for rowid, manual management)\nCREATE TABLE users (id INT PRIMARY KEY, name TEXT);\n```\n\n**Type affinity (not strict types):**\n```sql\n-- SQLite allows any value in any column (dynamic typing)\nINSERT INTO users (id, name) VALUES ('abc', 123); -- Valid but wrong!\n\n-- Use CHECK constraints for validation\nCREATE TABLE users (\n id INTEGER PRIMARY KEY CHECK(typeof(id) = 'integer'),\n name TEXT CHECK(typeof(name) = 'text')\n);\n```\n\n## Migration to PostgreSQL/MySQL\n\n**Use pgLoader or custom scripts:**\n```bash\n# Export SQLite to CSV\nsqlite3 app.db \".mode csv\" \".once users.csv\" \"SELECT * FROM users\"\n\n# Import to PostgreSQL\npsql -d mydb -c \"\\COPY users FROM 'users.csv' CSV HEADER\"\n```\n\n## Resources\n\n- SQLite Docs: https://www.sqlite.org/docs.html\n- Turso Docs: https://docs.turso.tech/\n- better-sqlite3: https://github.com/WiseLibs/better-sqlite3\n- libSQL: https://github.com/tursodatabase/libsql\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7985,"content_sha256":"20b166d58e003c6a9f22fec5289d18100a4a4b318e5e5385ca006bb1af532e20"},{"filename":"scripts/generate_migration.py","content":"#!/usr/bin/env python3\n\"\"\"\nMigration Template Generator\n\nGenerates migration templates for common database operations.\nProvides safe patterns for adding/dropping columns, creating indexes, etc.\n\nUsage:\n python generate_migration.py add-column users email varchar\n python generate_migration.py add-index users email\n python generate_migration.py create-table posts\n\"\"\"\n\nimport argparse\nimport sys\nfrom datetime import datetime\nfrom pathlib import Path\n\n\nTEMPLATES = {\n \"add-column\": \"\"\"-- Migration: {name}\n-- Created: {timestamp}\n\n-- Phase 1: Add column (safe, allows NULL)\nALTER TABLE {table} ADD COLUMN {column} {type};\n\n-- Phase 2: Backfill data (if needed)\n-- UPDATE {table} SET {column} = ... WHERE {column} IS NULL;\n\n-- Phase 3: Add constraints (after backfill complete)\n-- ALTER TABLE {table} ALTER COLUMN {column} SET NOT NULL;\n-- ALTER TABLE {table} ADD CONSTRAINT check_{column} CHECK (...);\n\n-- Rollback\n-- ALTER TABLE {table} DROP COLUMN {column};\n\"\"\",\n\n \"drop-column\": \"\"\"-- Migration: {name}\n-- Created: {timestamp}\n-- WARNING: This is a multi-phase migration for zero-downtime deployment\n\n-- Phase 1: Make column nullable (deploy code that doesn't use this column)\nALTER TABLE {table} ALTER COLUMN {column} DROP NOT NULL;\n\n-- Phase 2: Remove constraints (deploy)\n-- ALTER TABLE {table} DROP CONSTRAINT constraint_name;\n\n-- Phase 3: Drop column (after confirming code doesn't use it)\n-- ALTER TABLE {table} DROP COLUMN {column};\n\n-- Rollback (Phase 1 only)\n-- ALTER TABLE {table} ALTER COLUMN {column} SET NOT NULL;\n\"\"\",\n\n \"add-index\": \"\"\"-- Migration: {name}\n-- Created: {timestamp}\n\n-- Create index concurrently (does not block writes)\nCREATE INDEX CONCURRENTLY IF NOT EXISTS idx_{table}_{column}\nON {table}({column});\n\n-- For composite index:\n-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_{table}_{column}_other\n-- ON {table}({column}, other_column);\n\n-- Rollback\n-- DROP INDEX CONCURRENTLY IF EXISTS idx_{table}_{column};\n\"\"\",\n\n \"create-table\": \"\"\"-- Migration: {name}\n-- Created: {timestamp}\n\nCREATE TABLE {table} (\n id SERIAL PRIMARY KEY,\n -- Add your columns here\n -- email VARCHAR(255) UNIQUE NOT NULL,\n -- name VARCHAR(255) NOT NULL,\n\n -- Timestamps (recommended)\n created_at TIMESTAMPTZ DEFAULT NOW(),\n updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- Create indexes\n-- CREATE INDEX idx_{table}_column ON {table}(column);\n\n-- Create trigger for updated_at (optional)\nCREATE TRIGGER update_{table}_updated_at\n BEFORE UPDATE ON {table}\n FOR EACH ROW\n EXECUTE FUNCTION update_updated_at_column();\n\n-- Rollback\n-- DROP TRIGGER IF EXISTS update_{table}_updated_at ON {table};\n-- DROP TABLE {table};\n\"\"\",\n\n \"add-foreign-key\": \"\"\"-- Migration: {name}\n-- Created: {timestamp}\n\n-- Add foreign key column\nALTER TABLE {table} ADD COLUMN {column} INTEGER;\n\n-- Create index on foreign key (important for performance)\nCREATE INDEX CONCURRENTLY idx_{table}_{column} ON {table}({column});\n\n-- Add foreign key constraint\nALTER TABLE {table}\nADD CONSTRAINT fk_{table}_{column}\nFOREIGN KEY ({column})\nREFERENCES {ref_table}(id)\nON DELETE CASCADE; -- Or: RESTRICT, SET NULL, SET DEFAULT\n\n-- Rollback\n-- ALTER TABLE {table} DROP CONSTRAINT fk_{table}_{column};\n-- DROP INDEX CONCURRENTLY idx_{table}_{column};\n-- ALTER TABLE {table} DROP COLUMN {column};\n\"\"\",\n\n \"rename-column\": \"\"\"-- Migration: {name}\n-- Created: {timestamp}\n-- WARNING: Multi-phase migration for zero-downtime\n\n-- Phase 1: Add new column\nALTER TABLE {table} ADD COLUMN {new_column} {type};\n\n-- Phase 2: Backfill data (deploy code writing to both columns)\nUPDATE {table} SET {new_column} = {column} WHERE {new_column} IS NULL;\n\n-- Phase 3: Make new column NOT NULL (after backfill)\n-- ALTER TABLE {table} ALTER COLUMN {new_column} SET NOT NULL;\n\n-- Phase 4: Drop old column (deploy code using only new column)\n-- ALTER TABLE {table} DROP COLUMN {column};\n\n-- Rollback (Phase 1-2)\n-- ALTER TABLE {table} DROP COLUMN {new_column};\n\"\"\",\n}\n\n\ndef generate_migration(operation: str, args: argparse.Namespace):\n \"\"\"Generate migration file for the specified operation.\"\"\"\n if operation not in TEMPLATES:\n print(f\"Error: Unknown operation '{operation}'\")\n print(f\"Available operations: {', '.join(TEMPLATES.keys())}\")\n sys.exit(1)\n\n template = TEMPLATES[operation]\n timestamp = datetime.now().strftime(\"%Y%m%d%H%M%S\")\n\n # Build context for template\n context = {\n \"name\": f\"{operation}_{args.table}\",\n \"timestamp\": datetime.now().strftime(\"%Y-%m-%d %H:%M:%S\"),\n \"table\": args.table,\n }\n\n if args.column:\n context[\"column\"] = args.column\n if args.type:\n context[\"type\"] = args.type\n if args.new_column:\n context[\"new_column\"] = args.new_column\n if args.ref_table:\n context[\"ref_table\"] = args.ref_table\n\n # Fill template\n try:\n migration = template.format(**context)\n except KeyError as e:\n print(f\"Error: Missing required argument: {e}\")\n sys.exit(1)\n\n # Output\n if args.output:\n filename = f\"{timestamp}_{operation}_{args.table}.sql\"\n filepath = Path(args.output) / filename\n filepath.parent.mkdir(parents=True, exist_ok=True)\n filepath.write_text(migration)\n print(f\"✅ Generated migration: {filepath}\")\n else:\n print(migration)\n\n\ndef main():\n parser = argparse.ArgumentParser(\n description=\"Generate database migration templates\",\n formatter_class=argparse.RawDescriptionHelpFormatter,\n epilog=\"\"\"\nExamples:\n # Add column\n python generate_migration.py add-column users email --type \"VARCHAR(255)\"\n\n # Add index\n python generate_migration.py add-index users email\n\n # Create table\n python generate_migration.py create-table posts\n\n # Add foreign key\n python generate_migration.py add-foreign-key posts author_id --ref-table users\n\n # Rename column (multi-phase)\n python generate_migration.py rename-column users email --new-column user_email --type \"VARCHAR(255)\"\n\n # Save to file\n python generate_migration.py add-column users email --type \"VARCHAR(255)\" -o migrations/\n \"\"\"\n )\n\n parser.add_argument(\n \"operation\",\n choices=list(TEMPLATES.keys()),\n help=\"Migration operation type\"\n )\n parser.add_argument(\n \"table\",\n help=\"Table name\"\n )\n parser.add_argument(\n \"column\",\n nargs=\"?\",\n help=\"Column name (if applicable)\"\n )\n parser.add_argument(\n \"--type\",\n help=\"Column type (e.g., VARCHAR(255), INTEGER)\"\n )\n parser.add_argument(\n \"--new-column\",\n help=\"New column name (for rename-column)\"\n )\n parser.add_argument(\n \"--ref-table\",\n help=\"Referenced table (for add-foreign-key)\"\n )\n parser.add_argument(\n \"-o\", \"--output\",\n help=\"Output directory for migration file\"\n )\n\n args = parser.parse_args()\n generate_migration(args.operation, args)\n\n\nif __name__ == \"__main__\":\n main()\n","content_type":"text/x-python; charset=utf-8","language":"python","size":6990,"content_sha256":"2a814282291d026ed806cfba131bba5f8bc88c58cb655b7c86bb63bb2bf50a3f"},{"filename":"scripts/validate_schema.py","content":"#!/usr/bin/env python3\n\"\"\"\nDatabase Schema Validator\n\nValidates database schema structure, constraints, and indexes.\nChecks for common issues like missing indexes, lack of constraints, and naming conventions.\n\nUsage:\n python validate_schema.py postgresql://user:pass@localhost/db\n python validate_schema.py postgresql://user:pass@localhost/db --table users\n\"\"\"\n\nimport argparse\nimport sys\nfrom typing import List, Dict, Any\nfrom urllib.parse import urlparse\n\ntry:\n import sqlalchemy\n from sqlalchemy import create_engine, inspect, MetaData\nexcept ImportError:\n print(\"Error: SQLAlchemy not installed. Run: pip install sqlalchemy\")\n sys.exit(1)\n\n\nclass SchemaValidator:\n def __init__(self, database_url: str):\n self.database_url = database_url\n self.engine = create_engine(database_url)\n self.inspector = inspect(self.engine)\n self.issues = []\n self.warnings = []\n\n def validate_all(self, table_name: str = None) -> bool:\n \"\"\"Run all validation checks.\"\"\"\n print(f\"Validating database schema: {self._get_db_name()}\\n\")\n\n tables = [table_name] if table_name else self.inspector.get_table_names()\n\n if not tables:\n print(\"Error: No tables found in database\")\n return False\n\n for table in tables:\n print(f\"Checking table: {table}\")\n self._validate_table(table)\n\n self._print_results()\n return len(self.issues) == 0\n\n def _validate_table(self, table_name: str):\n \"\"\"Validate a single table.\"\"\"\n # Check primary key\n pk = self.inspector.get_pk_constraint(table_name)\n if not pk or not pk.get('constrained_columns'):\n self.issues.append(f\" ❌ {table_name}: No primary key defined\")\n else:\n print(f\" ✓ Primary key: {pk['constrained_columns']}\")\n\n # Check for timestamps\n columns = {col['name']: col for col in self.inspector.get_columns(table_name)}\n has_created_at = any('created' in name.lower() for name in columns.keys())\n has_updated_at = any('updated' in name.lower() for name in columns.keys())\n\n if not has_created_at:\n self.warnings.append(f\" ⚠️ {table_name}: No created_at timestamp column\")\n if not has_updated_at:\n self.warnings.append(f\" ⚠️ {table_name}: No updated_at timestamp column\")\n\n # Check foreign keys\n fks = self.inspector.get_foreign_keys(table_name)\n for fk in fks:\n fk_cols = fk['constrained_columns']\n ref_table = fk['referred_table']\n ref_cols = fk['referred_columns']\n print(f\" ✓ Foreign key: {fk_cols} → {ref_table}({ref_cols})\")\n\n # Check if foreign key has index\n indexes = self.inspector.get_indexes(table_name)\n fk_indexed = any(\n set(fk_cols).issubset(set(idx['column_names']))\n for idx in indexes\n )\n if not fk_indexed:\n self.warnings.append(\n f\" ⚠️ {table_name}: Foreign key {fk_cols} not indexed (slow joins)\"\n )\n\n # Check unique constraints\n unique_constraints = self.inspector.get_unique_constraints(table_name)\n for uc in unique_constraints:\n print(f\" ✓ Unique constraint: {uc['column_names']}\")\n\n # Check indexes\n indexes = self.inspector.get_indexes(table_name)\n if not indexes:\n self.warnings.append(f\" ⚠️ {table_name}: No indexes defined\")\n else:\n for idx in indexes:\n print(f\" ✓ Index: {idx['name']} on {idx['column_names']}\")\n\n # Check nullable constraints\n for col_name, col_info in columns.items():\n if col_name.endswith('_id') and col_info.get('nullable', True):\n self.warnings.append(\n f\" ⚠️ {table_name}.{col_name}: Foreign key should be NOT NULL\"\n )\n\n # Check naming conventions\n self._check_naming_conventions(table_name, columns)\n\n def _check_naming_conventions(self, table_name: str, columns: Dict):\n \"\"\"Check if table and column names follow conventions.\"\"\"\n # Table name should be plural lowercase\n if table_name != table_name.lower():\n self.warnings.append(\n f\" ⚠️ {table_name}: Table name should be lowercase\"\n )\n\n # Column names should be snake_case\n for col_name in columns.keys():\n if col_name != col_name.lower():\n self.warnings.append(\n f\" ⚠️ {table_name}.{col_name}: Column name should be lowercase\"\n )\n if ' ' in col_name or '-' in col_name:\n self.issues.append(\n f\" ❌ {table_name}.{col_name}: Column name contains invalid characters\"\n )\n\n def _print_results(self):\n \"\"\"Print validation results.\"\"\"\n print(\"\\n\" + \"=\" * 60)\n print(\"VALIDATION RESULTS\")\n print(\"=\" * 60)\n\n if self.issues:\n print(f\"\\n❌ CRITICAL ISSUES ({len(self.issues)}):\")\n for issue in self.issues:\n print(issue)\n\n if self.warnings:\n print(f\"\\n⚠️ WARNINGS ({len(self.warnings)}):\")\n for warning in self.warnings:\n print(warning)\n\n if not self.issues and not self.warnings:\n print(\"\\n✅ All checks passed! Schema looks good.\")\n elif not self.issues:\n print(f\"\\n✅ No critical issues found (but {len(self.warnings)} warnings)\")\n else:\n print(f\"\\n❌ Found {len(self.issues)} critical issues\")\n\n def _get_db_name(self) -> str:\n \"\"\"Extract database name from URL.\"\"\"\n parsed = urlparse(self.database_url)\n return parsed.path.lstrip('/')\n\n\ndef main():\n parser = argparse.ArgumentParser(\n description=\"Validate database schema structure and constraints\"\n )\n parser.add_argument(\n \"database_url\",\n help=\"Database connection URL (e.g., postgresql://user:pass@localhost/db)\"\n )\n parser.add_argument(\n \"--table\",\n help=\"Validate specific table only\",\n default=None\n )\n\n args = parser.parse_args()\n\n try:\n validator = SchemaValidator(args.database_url)\n success = validator.validate_all(args.table)\n sys.exit(0 if success else 1)\n except Exception as e:\n print(f\"Error: {e}\")\n sys.exit(1)\n\n\nif __name__ == \"__main__\":\n main()\n","content_type":"text/x-python; charset=utf-8","language":"python","size":6571,"content_sha256":"28b4cc041e3771222bd9915c8e8d98b55ab8bd72efc528d6d21dc3e9d629747b"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Relational Databases","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Purpose","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"When to Use This Skill","type":"text"}]},{"type":"paragraph","content":[{"text":"Trigger this skill when:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Building user authentication, content management, e-commerce applications","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Implementing CRUD operations (Create, Read, Update, Delete)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Designing data models with relationships (users → posts, orders → items)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Migrating schemas safely in production","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Setting up connection pooling for performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Evaluating serverless database options (Neon, PlanetScale, Turso)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Integrating with frontend skills (forms, tables, dashboards, search-filter)","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Skip this skill for:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Time-series data at scale (use time-series databases)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Real-time analytics (use columnar databases)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Document-heavy workloads (use document databases)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Key-value caching (use Redis, Memcached)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Reference: Database Selection","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"Database Selection Decision Tree\n═══════════════════════════════════════════════════════════\n\nPRIMARY CONCERN?\n├─ MAXIMUM FLEXIBILITY & EXTENSIONS (JSON, arrays, vector search)\n│ └─ PostgreSQL\n│ ├─ Serverless → Neon (scale-to-zero, database branching)\n│ └─ Traditional → Self-hosted, AWS RDS, Google Cloud SQL\n│\n├─ EMBEDDED / EDGE DEPLOYMENT (local-first, global latency)\n│ └─ SQLite or Turso\n│ ├─ Global distribution → Turso (libSQL, edge replicas)\n│ └─ Local-only → SQLite (embedded, zero-config)\n│\n├─ LEGACY SYSTEM / MYSQL REQUIRED\n│ └─ MySQL\n│ ├─ Serverless → PlanetScale (non-blocking migrations)\n│ └─ Traditional → Self-hosted, AWS RDS, Google Cloud SQL\n│\n└─ RAPID PROTOTYPING\n ├─ Python → SQLModel (FastAPI) or SQLAlchemy 2.0\n ├─ TypeScript → Prisma (best DX) or Drizzle (performance)\n ├─ Rust → SQLx (compile-time checks)\n └─ Go → sqlc (type-safe code generation)","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Reference: ORM vs Query Builder","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"ORM vs Query Builder Selection\n═══════════════════════════════════════════════════════════\n\nTEAM PRIORITIES?\n├─ DEVELOPMENT SPEED / DEVELOPER EXPERIENCE\n│ └─ ORM (abstracts SQL, handles relations automatically)\n│ ├─ Python → SQLAlchemy 2.0, SQLModel\n│ ├─ TypeScript → Prisma (migrations, type generation)\n│ ├─ Rust → SeaORM (Active Record + Data Mapper)\n│ └─ Go → GORM, Ent\n│\n├─ PERFORMANCE / QUERY CONTROL\n│ └─ Query Builder (SQL-like, zero abstraction overhead)\n│ ├─ Python → SQLAlchemy Core, asyncpg\n│ ├─ TypeScript → Drizzle, Kysely\n│ ├─ Rust → SQLx (compile-time query validation!)\n│ └─ Go → sqlc (generates types from SQL)\n│\n├─ TYPE SAFETY / COMPILE-TIME GUARANTEES\n│ ├─ Rust → SQLx (queries checked at build time)\n│ ├─ Go → sqlc (generates types from SQL)\n│ ├─ TypeScript → Prisma or Drizzle\n│ └─ Python → SQLModel (Pydantic integration)\n│\n└─ COMPLEX QUERIES / JOINS\n ├─ SQL-first → Query builders or raw SQL\n └─ ORM-friendly → SeaORM, SQLAlchemy ORM","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Multi-Language Implementation","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Python: SQLAlchemy 2.0 + SQLModel","type":"text"}]},{"type":"paragraph","content":[{"text":"Recommended Libraries:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQLAlchemy 2.0","type":"text","marks":[{"type":"strong"}]},{"text":" (","type":"text"},{"text":"/websites/sqlalchemy_en_21","type":"text","marks":[{"type":"code_inline"}]},{"text":") - ORM + Core, 7,090 snippets","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQLModel","type":"text","marks":[{"type":"strong"}]},{"text":" - FastAPI integration, Pydantic validation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"asyncpg","type":"text","marks":[{"type":"strong"}]},{"text":" - High-performance async PostgreSQL driver","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"When to Use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Production applications requiring flexibility","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"FastAPI/Starlette backends","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Async/await workflows","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Quick Pattern:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"from sqlmodel import SQLModel, Field, Session\nclass User(SQLModel, table=True):\n id: int | None = Field(default=None, primary_key=True)\n email: str = Field(unique=True, index=True)","type":"text"}]},{"type":"paragraph","content":[{"text":"See:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"references/orms-python.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for complete SQLAlchemy/SQLModel patterns, async workflows, and connection pooling.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"TypeScript: Prisma vs Drizzle","type":"text"}]},{"type":"paragraph","content":[{"text":"Recommended Libraries:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Prisma 6.x","type":"text","marks":[{"type":"strong"}]},{"text":" (","type":"text"},{"text":"/prisma/prisma","type":"text","marks":[{"type":"code_inline"}]},{"text":", score: 96.4, 4,281 doc snippets) - Best DX, migrations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Drizzle ORM","type":"text","marks":[{"type":"strong"}]},{"text":" (","type":"text"},{"text":"/drizzle-team/drizzle-orm-docs","type":"text","marks":[{"type":"code_inline"}]},{"text":", score: 95.4, 4,037 snippets) - Performance, SQL-like","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Quick Comparison:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Prisma","type":"text","marks":[{"type":"strong"}]},{"text":": Best DX, auto-generated types, migrations included","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Drizzle","type":"text","marks":[{"type":"strong"}]},{"text":": Best performance, SQL-like syntax, zero overhead","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"See:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"references/orms-typescript.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for Prisma vs Drizzle detailed comparison, Kysely, TypeORM patterns.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Rust: SQLx (Compile-Time Checked)","type":"text"}]},{"type":"paragraph","content":[{"text":"Recommended Libraries:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQLx 0.8","type":"text","marks":[{"type":"strong"}]},{"text":" - Compile-time query validation, async","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SeaORM 1.x","type":"text","marks":[{"type":"strong"}]},{"text":" - Full ORM with Active Record pattern","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Diesel 2.3","type":"text","marks":[{"type":"strong"}]},{"text":" - Mature, stable (sync/async)","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Quick Pattern:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"rust"},"content":[{"text":"use sqlx::FromRow;\n#[derive(FromRow)]\nstruct User { id: i32, email: String, name: String }\n// Compile-time checked queries (verified at build time!)\nlet user = sqlx::query_as::\u003c_, User>(\"SELECT * FROM users WHERE email = $1\")\n .bind(\"[email protected]\").fetch_one(&pool).await?;","type":"text"}]},{"type":"paragraph","content":[{"text":"See:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"references/orms-rust.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for SQLx macros, SeaORM, Diesel patterns, and compile-time guarantees.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Go: sqlc (Type-Safe Code Generation)","type":"text"}]},{"type":"paragraph","content":[{"text":"Recommended Libraries:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"sqlc","type":"text","marks":[{"type":"strong"}]},{"text":" - Generates Go code from SQL queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"GORM v2","type":"text","marks":[{"type":"strong"}]},{"text":" - Full ORM with associations, hooks","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ent","type":"text","marks":[{"type":"strong"}]},{"text":" - Graph-based ORM, schema as code","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"pgx","type":"text","marks":[{"type":"strong"}]},{"text":" - High-performance PostgreSQL driver","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Quick Pattern:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- queries.sql: SQL annotations generate type-safe Go code\n-- name: CreateUser :one\nINSERT INTO users (email, name) VALUES ($1, $2) RETURNING *;","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"go"},"content":[{"text":"user, err := queries.CreateUser(ctx, db.CreateUserParams{Email: \"[email protected]\"})","type":"text"}]},{"type":"paragraph","content":[{"text":"See:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"references/orms-go.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for sqlc setup, GORM, Ent, and pgx patterns.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Connection Pooling","type":"text"}]},{"type":"paragraph","content":[{"text":"Recommended Pool Sizes:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Web API (single instance): 10-20 connections","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Serverless (per function): 1-2 connections + pgBouncer","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Background workers: 5-10 connections","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"See:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"references/connection-pooling.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for configuration examples, sizing formulas, and monitoring strategies.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Migrations","type":"text"}]},{"type":"paragraph","content":[{"text":"Critical Principles:","type":"text","marks":[{"type":"strong"}]}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use multi-phase deployment for column drops (never drop directly in production)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"CREATE INDEX CONCURRENTLY","type":"text","marks":[{"type":"code_inline"}]},{"text":" (PostgreSQL) to avoid blocking writes","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Test migrations in staging with production-like data volume","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Tools:","type":"text","marks":[{"type":"strong"}]},{"text":" Alembic (Python), Prisma Migrate (TypeScript), SQLx migrations (Rust), golang-migrate (Go)","type":"text"}]},{"type":"paragraph","content":[{"text":"See:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"references/migrations-guide.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for safe migration patterns, multi-phase deployments, and rollback strategies.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Serverless Databases","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Database","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Type","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Key Feature","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Best For","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Neon","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Database branching, scale-to-zero","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Development workflows, preview environments","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"PlanetScale","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"MySQL (Vitess)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Non-blocking schema changes","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"MySQL apps, zero-downtime migrations","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Turso","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"SQLite (libSQL)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Edge deployment, low latency","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Edge functions, global distribution","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"See:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"references/serverless-databases.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" for setup examples, branching workflows, and cost comparisons.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Frontend Integration","type":"text"}]},{"type":"paragraph","content":[{"text":"Common Integration Patterns:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Forms skill","type":"text","marks":[{"type":"strong"}]},{"text":": Form submission → API validation → Database CRUD (INSERT/UPDATE)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Tables skill","type":"text","marks":[{"type":"strong"}]},{"text":": Paginated queries → API → Table display with sorting/filtering","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Dashboards skill","type":"text","marks":[{"type":"strong"}]},{"text":": Aggregation queries (COUNT, SUM) → API → KPI cards","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Search-filter skill","type":"text","marks":[{"type":"strong"}]},{"text":": Full-text search (PostgreSQL tsvector) → Ranked results","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"See working examples in:","type":"text","marks":[{"type":"strong"}]},{"text":" ","type":"text"},{"text":"examples/python-sqlalchemy/","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"examples/typescript-drizzle/","type":"text","marks":[{"type":"code_inline"}]},{"text":", ","type":"text"},{"text":"examples/rust-sqlx/","type":"text","marks":[{"type":"code_inline"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Bundled Resources","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Reference Documentation","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/postgresql-guide.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - PostgreSQL features (pgvector, PostGIS, TimescaleDB)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/mysql-guide.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - MySQL-specific patterns, PlanetScale integration","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/sqlite-guide.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - SQLite patterns, Turso edge deployment","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/orms-python.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - SQLAlchemy 2.0, SQLModel, asyncpg","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/orms-typescript.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Prisma, Drizzle, Kysely comparisons","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/orms-rust.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - SQLx, SeaORM, Diesel","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/orms-go.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - GORM, sqlc, Ent, pgx","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/migrations-guide.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Safe schema evolution patterns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/connection-pooling.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Pool sizing and monitoring","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"references/serverless-databases.md","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Neon, PlanetScale, Turso deployment","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Working Examples","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"examples/python-sqlalchemy/","type":"text","marks":[{"type":"code_inline"}]},{"text":" - SQLAlchemy 2.0 + FastAPI with pooling, migrations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"examples/typescript-prisma/","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Prisma + Next.js with schema, migrations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"examples/typescript-drizzle/","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Drizzle + Hono with type-safe queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"examples/rust-sqlx/","type":"text","marks":[{"type":"code_inline"}]},{"text":" - SQLx + Axum with compile-time checks","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"examples/go-sqlc/","type":"text","marks":[{"type":"code_inline"}]},{"text":" - sqlc + Gin with generated type-safe code","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Utility Scripts","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"scripts/validate_schema.py","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Validate database schema structure, constraints","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"scripts/generate_migration.py","type":"text","marks":[{"type":"code_inline"}]},{"text":" - Generate migration templates for common operations","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Best Practices","type":"text"}]},{"type":"paragraph","content":[{"text":"Security:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Always use parameterized queries (prevents SQL injection)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Hash passwords with Argon2/bcrypt","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use environment variables for connection strings","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Enable SSL/TLS in production","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Performance:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use connection pooling (10-20 for web APIs)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Create indexes on filtered/sorted columns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Implement pagination for large result sets","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"EXPLAIN ANALYZE","type":"text","marks":[{"type":"code_inline"}]},{"text":" for slow queries","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Reliability:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Test migrations in staging first","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use transactions for multi-statement operations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Monitor connection pool exhaustion","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Set up and test database backups","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Development:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Version control schema and migrations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Use database branching (Neon) for features","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Write integration tests against real databases","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}}]},"metadata":{"date":"2026-06-05","name":"using-relational-databases","author":"@skillopedia","source":{"stars":368,"repo_name":"ai-design-components","origin_url":"https://github.com/ancoleman/ai-design-components/blob/HEAD/skills/using-relational-databases/SKILL.md","repo_owner":"ancoleman","body_sha256":"27bd660dd4648ea5dfab11ba0597e70043158402d91d4c03d80d3a21c9590725","cluster_key":"ef1fb5a95b6f1d3b1a301db3693c72751f1015fb61214d95859d7cca56c390c8","clean_bundle":{"format":"clean-skill-bundle-v1","source":"ancoleman/ai-design-components/skills/using-relational-databases/SKILL.md","attachments":[{"id":"ef5769cb-b453-510b-b43c-2d19dbea0022","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/ef5769cb-b453-510b-b43c-2d19dbea0022/attachment.md","path":"examples/python-sqlalchemy/README.md","size":2901,"sha256":"708bb0b837316704eac49bc94f6fde2ca54ba9bafd80d795900e9d48e0711e38","contentType":"text/markdown; charset=utf-8"},{"id":"0ec2e9cc-b5d5-575d-b8bd-89737c3dfac0","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/0ec2e9cc-b5d5-575d-b8bd-89737c3dfac0/attachment.py","path":"examples/python-sqlalchemy/main.py","size":4850,"sha256":"7eb16e23b57943f9eba4b5125b1052acceec0597db287b4bd18ee7583d11f2a1","contentType":"text/x-python; charset=utf-8"},{"id":"997b51d0-a81a-5576-93a5-71a85d51a101","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/997b51d0-a81a-5576-93a5-71a85d51a101/attachment.txt","path":"examples/python-sqlalchemy/requirements.txt","size":170,"sha256":"7b7addac6d95b077ba5884ffd4fb9fab4eaa77360c15607f8f6b89162d6157e3","contentType":"text/plain; charset=utf-8"},{"id":"077ea279-707c-577d-8a8a-e0bbb5b2d074","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/077ea279-707c-577d-8a8a-e0bbb5b2d074/attachment.yaml","path":"outputs.yaml","size":12805,"sha256":"6eaf2d7dc9fea7b13f7292afd5de1d46d585c3cc035d19ec8f66f6145789cb64","contentType":"application/yaml; charset=utf-8"},{"id":"f2bd73d9-7c9c-53be-8f72-205b9b0a6041","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/f2bd73d9-7c9c-53be-8f72-205b9b0a6041/attachment.md","path":"references/connection-pooling.md","size":9658,"sha256":"dc622fe97f0853bf67eab81e45d9d161c0fa18cf1c51c3f74e3c10dca24f66fc","contentType":"text/markdown; charset=utf-8"},{"id":"f3778df2-e2c6-5043-a479-5193bd293c86","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/f3778df2-e2c6-5043-a479-5193bd293c86/attachment.md","path":"references/migrations-guide.md","size":9846,"sha256":"838da9d13fdf0e79a2a0a8726b49de536c3d62cbd1ae1ee67e38907661a517c8","contentType":"text/markdown; charset=utf-8"},{"id":"af9bbb8e-3e84-5f7b-aa39-b8f1d21f6fbe","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/af9bbb8e-3e84-5f7b-aa39-b8f1d21f6fbe/attachment.md","path":"references/mysql-guide.md","size":12226,"sha256":"47af6a677baafbac4ec222d0b439b9d2ed2e14201776d7a8aa7933192468d5fb","contentType":"text/markdown; charset=utf-8"},{"id":"c08c17a6-22c9-5830-8736-0ecc1a679d84","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/c08c17a6-22c9-5830-8736-0ecc1a679d84/attachment.md","path":"references/orms-go.md","size":12032,"sha256":"66f2f03586c4a2954c64a9fc1f75003345fb1b0ed42172e3b48ff963d6b41d99","contentType":"text/markdown; charset=utf-8"},{"id":"459e4c91-a2f2-5fd7-97a5-866921ea099d","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/459e4c91-a2f2-5fd7-97a5-866921ea099d/attachment.md","path":"references/orms-python.md","size":15890,"sha256":"df56877cbd660011bf35b6fc4ed6048425085bf3d4cbdecb653b8163047fe60f","contentType":"text/markdown; charset=utf-8"},{"id":"c117d7af-ada0-5e8d-a3ff-b5550bc28b51","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/c117d7af-ada0-5e8d-a3ff-b5550bc28b51/attachment.md","path":"references/orms-rust.md","size":13208,"sha256":"fb6a953dc116a8e9b2bd28927f643479ed33b82cc3e2f556b44da6dfec21a1b7","contentType":"text/markdown; charset=utf-8"},{"id":"2f2642dd-87d4-5ed3-9f83-019ee94e58c5","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/2f2642dd-87d4-5ed3-9f83-019ee94e58c5/attachment.md","path":"references/orms-typescript.md","size":14401,"sha256":"03934a2b04761479b43d31b12e5d55de7c2f200b8942a48ff06408cbb5a5d42f","contentType":"text/markdown; charset=utf-8"},{"id":"43abe7bd-9094-530b-8fc5-68de5ecdcf35","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/43abe7bd-9094-530b-8fc5-68de5ecdcf35/attachment.md","path":"references/postgresql-guide.md","size":16698,"sha256":"0f33ce882e15b95c653c6133c0048bbcd62c9d2fcaf98a6a7d0c507a1ec188de","contentType":"text/markdown; charset=utf-8"},{"id":"37d8d45d-eb48-5490-86bd-3bb2911b09a1","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/37d8d45d-eb48-5490-86bd-3bb2911b09a1/attachment.md","path":"references/serverless-databases.md","size":12356,"sha256":"873c9cd33b1e2ae5e36e41dda43d8e8fb4d217a0b02bf7698323762115cf8db8","contentType":"text/markdown; charset=utf-8"},{"id":"06a09831-1360-55d2-bb33-5b574ef6bffd","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/06a09831-1360-55d2-bb33-5b574ef6bffd/attachment.md","path":"references/sqlite-guide.md","size":7985,"sha256":"20b166d58e003c6a9f22fec5289d18100a4a4b318e5e5385ca006bb1af532e20","contentType":"text/markdown; charset=utf-8"},{"id":"ba6fe1f8-8b9d-5fe7-8427-8f3a5ccd1cd3","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/ba6fe1f8-8b9d-5fe7-8427-8f3a5ccd1cd3/attachment.py","path":"scripts/generate_migration.py","size":6990,"sha256":"2a814282291d026ed806cfba131bba5f8bc88c58cb655b7c86bb63bb2bf50a3f","contentType":"text/x-python; charset=utf-8"},{"id":"3fa486d5-c794-5136-8e86-d7dea408d1e5","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/3fa486d5-c794-5136-8e86-d7dea408d1e5/attachment.py","path":"scripts/validate_schema.py","size":6571,"sha256":"28b4cc041e3771222bd9915c8e8d98b55ab8bd72efc528d6d21dc3e9d629747b","contentType":"text/x-python; charset=utf-8"}],"bundle_sha256":"9f6641f39c4cd30cd6c528a979871329e0dc984ae80b310b76a6a3f8390eed50","attachment_count":16,"text_attachments":16,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"skills/using-relational-databases/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"web-development","category_label":"Web"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"web-development","import_tag":"clean-skills-v1","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)."}},"renderedAt":1782979503470}

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 connect…