Database Tutorial #20: Database Cheat Sheet 2026

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

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;

-- 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;

-- 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;

# 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

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"

// 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 })

$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

# 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

docker run -d \
  --name redis \
  -p 6379:6379 \
  redis:8

# Connect
redis-cli

# 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

# Standard
redis://localhost:6379

# With password
redis://:password@localhost:6379

# With database selection
redis://localhost:6379/1

# TLS
rediss://user:password@host:6380

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()

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");