Development

In Article 17, you learned the theory of RAG. Now we build a complete document Q&A system. Upload PDF documents, ask questions in natural language, and get answers with citations pointing back to the source. This is Article 22 in the Claude AI — From Zero to Power User series. You should know RAG and Vision before this article. Architecture Upload PDF → Parse → Chunk → Embed → Store in Vector DB ↓ Question → Embed → Search → Top Chunks → Claude → Cited Answer The system has two pipelines: ...

Quick reference for everything in this series. Bookmark this page. PostgreSQL Docker Quick Start docker run -d \ --name postgres \ -p 5432:5432 \ -e POSTGRES_USER=myuser \ -e POSTGRES_PASSWORD=mypassword \ -e POSTGRES_DB=mydb \ postgres:17 # Connect psql postgresql://myuser:mypassword@localhost:5432/mydb DDL CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); ALTER TABLE users ADD COLUMN phone TEXT; ALTER TABLE users DROP COLUMN phone; ALTER TABLE users RENAME COLUMN name TO full_name; DROP TABLE users; DROP TABLE IF EXISTS users; -- Copy table structure CREATE TABLE users_backup AS SELECT * FROM users WHERE false; DML -- Insert INSERT INTO users (email, name) VALUES ('alex@example.com', 'Alex'); INSERT INTO users (email, name) VALUES ('sam@example.com', 'Sam') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; -- Select SELECT * FROM users WHERE email LIKE '%@example.com' ORDER BY name LIMIT 10 OFFSET 20; SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id; -- Update UPDATE users SET name = 'Alex J' WHERE id = 1 RETURNING *; -- Delete DELETE FROM users WHERE created_at < NOW() - INTERVAL '1 year' RETURNING id; Useful Queries -- Table sizes SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; -- Slow queries (requires pg_stat_statements) SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; -- Lock monitoring SELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event IS NOT NULL; -- Index usage SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan; -- Vacuum and analyze VACUUM ANALYZE users; Connection Strings # Standard postgresql://user:password@host:5432/database # With SSL postgresql://user:password@host:5432/database?sslmode=require # Connection pool (PgBouncer) postgresql://user:password@pgbouncer-host:6432/database MongoDB Docker Quick Start docker run -d \ --name mongodb \ -p 27017:27017 \ -e MONGO_INITDB_ROOT_USERNAME=admin \ -e MONGO_INITDB_ROOT_PASSWORD=password \ mongodb/mongodb-community-server:8.0 # Connect mongosh "mongodb://admin:password@localhost:27017" CRUD // Insert db.users.insertOne({ name: "Alex", email: "alex@example.com" }) db.users.insertMany([{ name: "Sam" }, { name: "Jordan" }]) // Find db.users.find({ age: { $gte: 18 } }).sort({ name: 1 }).limit(10) db.users.findOne({ email: "alex@example.com" }) db.users.countDocuments({ active: true }) // Update db.users.updateOne({ _id: id }, { $set: { name: "Alex J" } }) db.users.updateMany({ active: false }, { $set: { archived: true } }) db.users.findOneAndUpdate({ _id: id }, { $inc: { score: 10 } }, { returnDocument: "after" }) // Delete db.users.deleteOne({ _id: id }) db.users.deleteMany({ createdAt: { $lt: cutoffDate } }) // Upsert db.users.updateOne({ email: "new@example.com" }, { $set: { name: "New" } }, { upsert: true }) Query Operators $eq, $ne, $gt, $gte, $lt, $lte // comparison $in, $nin // in/not in array $and, $or, $nor, $not // logical $exists, $type // element $regex // string match $where // JavaScript expression (slow) $elemMatch // match array element Connection Strings # Standard mongodb://user:password@host:27017/database?authSource=admin # Replica set mongodb://user:pass@host1:27017,host2:27017,host3:27017/database?replicaSet=rs0 # Atlas mongodb+srv://user:password@cluster.mongodb.net/database Redis Docker Quick Start docker run -d \ --name redis \ -p 6379:6379 \ redis:8 # Connect redis-cli Commands by Data Type # String SET key value EX 300 # with TTL in seconds GET key INCR counter MSET k1 v1 k2 v2 MGET k1 k2 # Hash HSET user:1 name "Alex" email "alex@example.com" HGET user:1 name HGETALL user:1 HINCRBY user:1 score 10 # List LPUSH list val # push left RPUSH list val # push right LRANGE list 0 -1 # get all LPOP list / RPOP list # pop # Set SADD myset val SMEMBERS myset SISMEMBER myset val SINTER s1 s2 / SUNION s1 s2 # Sorted Set ZADD leaderboard 1500 "alex" ZRANGE leaderboard 0 -1 WITHSCORES ZREVRANGE leaderboard 0 9 # top 10 ZINCRBY leaderboard 100 "alex" # Key management TTL key # seconds remaining EXPIRE key 3600 # set TTL PERSIST key # remove TTL DEL key [key ...] EXISTS key KEYS pattern # NEVER in production — use SCAN SCAN 0 MATCH "user:*" COUNT 100 Connection Strings # Standard redis://localhost:6379 # With password redis://:password@localhost:6379 # With database selection redis://localhost:6379/1 # TLS rediss://user:password@host:6380 SQLite Quick Start (Python) import sqlite3 conn = sqlite3.connect("myapp.db") conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA foreign_keys=ON") conn.execute(""" CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL ) """) conn.commit() Quick Start (Node.js) import Database from "better-sqlite3"; const db = new Database("myapp.db"); db.pragma("journal_mode = WAL"); db.pragma("foreign_keys = ON"); db.exec(`CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL )`); const insert = db.prepare("INSERT INTO users (email, name) VALUES (?, ?) RETURNING *"); const user = insert.get("alex@example.com", "Alex"); Choosing a Database Use Case Best Choice Web app with relational data PostgreSQL Flexible/nested documents MongoDB Caching, sessions, rate limiting Redis Mobile app, desktop app, CLI SQLite Time-series metrics TimescaleDB (PostgreSQL extension) Full-text search at scale Elasticsearch or PostgreSQL FTS Edge/serverless Turso (SQLite), PlanetScale, Neon Complete Series # Article 1 SQL vs NoSQL — When to Use What 2 PostgreSQL Setup and Basics 3 PostgreSQL — Advanced Queries 4 PostgreSQL Indexing and Performance 5 PostgreSQL JSON and Full-Text Search 6 PostgreSQL Transactions and Concurrency 7 PostgreSQL Migrations and Schema Design 8 PostgreSQL Replication and High Availability 9 MongoDB Setup and CRUD 10 MongoDB Data Modeling 11 MongoDB Aggregation Pipeline 12 MongoDB Indexing and Performance 13 Redis Setup and Data Types 14 Redis Caching Patterns 15 Redis Pub/Sub and Streams 16 Redis Best Practices and Production 17 SQLite — When and How to Use It 18 Database Design Patterns 19 ORMs vs Raw SQL — Prisma, SQLAlchemy, GORM 20 Database Cheat Sheet 2026 (this article)

An ORM (Object-Relational Mapper) lets you work with your database using your programming language instead of SQL. It reduces boilerplate and prevents SQL injection. But ORMs also add abstraction — and abstraction can hide performance problems. When to Use an ORM vs Raw SQL Use an ORM when: You do standard CRUD (create, read, update, delete) You want type safety and IDE autocomplete You want schema migrations integrated with your code You are building fast and the query complexity is low Use raw SQL when: ...

Good schema design is not about theory. It is about patterns that solve real problems — audit trails, multi-tenancy, concurrent updates, and data history. These patterns work with any relational database. Soft Deletes Hard deleting rows permanently removes data. Soft deletes mark rows as deleted without removing them — useful for audit trails, undo functionality, and regulatory compliance. ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ; -- Soft delete UPDATE users SET deleted_at = NOW() WHERE id = 42; -- Query active users only SELECT * FROM users WHERE deleted_at IS NULL; -- Include deleted users SELECT * FROM users; -- Restore UPDATE users SET deleted_at = NULL WHERE id = 42; Create a partial index for performance on active records: ...

SQLite is the most deployed database in the world. It is built into every iPhone, Android device, and browser. You can use it as a production database for many real applications. What Makes SQLite Different SQLite is serverless and embedded. There is no separate database process. The entire database lives in a single file on disk. No installation, no configuration, no network The database is just a .db file you can copy, back up, or email Reads are fast — no network round trip Writes are serialized — only one writer at a time This makes SQLite perfect for: desktop apps, mobile apps, edge computing, testing, local development, CLIs, and read-heavy web apps with low write frequency. ...

Redis is easy to start with and hard to run well at scale. This tutorial covers the practices that keep Redis fast, reliable, and secure in production. Key Naming Conventions Consistent key names make debugging and management much easier. Use colons as separators: app:entity:identifier:field Examples: user:1001:profile session:abc123 cache:product:5:details rate:api:user:1001 lock:order:processing:42 Rules: Use lowercase Prefer colons over dots or hyphens (Redis treats colons as namespace separators in RedisInsight) Keep keys short — they add up in memory Include a TTL on anything that should expire Memory Optimization Redis stores everything in RAM. Know what you are using: ...

Redis has two messaging systems: Pub/Sub for fire-and-forget real-time events, and Streams for persistent, replayable event logs with consumer groups. Pub/Sub Pub/Sub is simple: publishers send messages to channels, subscribers receive them. Messages are not stored — if no subscriber is listening, the message is lost. # Terminal 1: Subscribe to a channel SUBSCRIBE notifications:user:1 # Terminal 2: Publish PUBLISH notifications:user:1 '{"type":"message","text":"Hello!"}' # Terminal 1 receives: "Hello!" Node.js Pub/Sub ioredis requires separate client connections for publishing and subscribing: ...

A database query takes 50ms. The same query through Redis takes 0.1ms. Caching removes load from your database and makes your application faster. Why Cache? Reduce database load — fewer queries hit the database Lower latency — in-memory reads are 100-500x faster than disk reads Absorb traffic spikes — cache handles burst traffic without scaling the database Caching adds complexity. Only cache what you need to. Cache-Aside (Lazy Loading) The most common pattern. Read from cache first; fall back to database on miss. ...

Redis is an in-memory database. It is fast — reads and writes take microseconds. It is used for caching, sessions, rate limiting, pub/sub, queues, and leaderboards. Redis 8.0 (released May 2025) includes JSON, vector search, probabilistic data structures, and time series natively — no extensions needed. Setup with Docker docker run -d \ --name redis \ -p 6379:6379 \ redis:8 \ redis-server --save 60 1 --loglevel warning Connect with redis-cli: redis-cli # or with Docker: docker exec -it redis redis-cli Strings The most basic type. Strings can hold text, numbers, or binary data (up to 512 MB). ...

MongoDB reads every document in a collection if there is no index — a collection scan. At 10 million documents, that is slow. Indexes fix this. Single-Field Index // Create an index on the email field db.users.createIndex({ email: 1 }) // 1 = ascending, -1 = descending // The query now uses the index db.users.find({ email: "alex@example.com" }) // Unique index — enforces uniqueness db.users.createIndex({ email: 1 }, { unique: true }) Compound Index Index multiple fields together when you filter or sort on more than one: ...