Database Tutorial #20: Database Cheat Sheet 2026
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)