Databases Complete Guide: SQL, NoSQL & When to Use Each

┌─────────────────────────────────────────────────────────────┐
│                   Database Selection                         │
│                                                              │
│  Need ACID transactions?              → PostgreSQL           │
│  Need caching / session storage?      → Redis               │
│  Need document storage (flexible)?    → MongoDB             │
│  Need serverless / infinite scale?    → DynamoDB            │
│  Need analytics / OLAP?              → ClickHouse           │
│  Need time-series data?              → TimescaleDB / InfluxDB│
│  Need a graph?                        → Neo4j               │
│  Need full-text search?              → Elasticsearch        │
│  Need embedded / single-file?         → SQLite              │
└─────────────────────────────────────────────────────────────┘

-- Create a table with constraints
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    role        TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ    -- soft delete
);

-- Index for common lookups
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- Query with JSONB
SELECT * FROM users WHERE metadata @> '{"plan": "pro"}';

-- CTE + Window Function (top 3 customers per region)
WITH ranked AS (
    SELECT 
        customer_id, region, total_spent,
        RANK() OVER (PARTITION BY region ORDER BY total_spent DESC) AS rk
    FROM orders
)
SELECT * FROM ranked WHERE rk <= 3;

-- Upsert (insert or update)
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();

// Node.js — pg library
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

# Python — psycopg2
import psycopg2
conn = psycopg2.connect(os.environ["DATABASE_URL"])
cur = conn.cursor()
cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))

// .NET — EF Core
services.AddDbContext<AppDbContext>(opt =>
    opt.UseNpgsql(builder.Configuration.GetConnectionString("Default")));

-- MySQL-specific: AUTO_INCREMENT (vs BIGSERIAL in Postgres)
CREATE TABLE products (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    price       DECIMAL(10,2) NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- FULLTEXT search in MySQL
ALTER TABLE articles ADD FULLTEXT INDEX idx_content(title, body);
SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('terraform' IN NATURAL LANGUAGE MODE);

# Python — sqlite3 (built in, no install needed)
import sqlite3

conn = sqlite3.connect("myapp.db")
conn.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
conn.execute("INSERT INTO notes (body) VALUES (?)", ("Hello SQLite",))
conn.commit()

rows = conn.execute("SELECT * FROM notes").fetchall()

// A MongoDB document (looks like JSON)
{
  "_id": ObjectId("665abc123"),
  "name": "Alice Johnson",
  "email": "alice@example.com",
  "address": {
    "street": "123 Main St",
    "city": "Austin",
    "zip": "78701"
  },
  "orders": [
    { "id": "ORD-1", "total": 149.99, "status": "completed" },
    { "id": "ORD-2", "total": 89.00,  "status": "pending" }
  ],
  "tags": ["premium", "early-adopter"],
  "createdAt": ISODate("2026-04-17T09:00:00Z")
}

// MongoDB Node.js driver
const { MongoClient } = require("mongodb");
const client = new MongoClient(process.env.MONGO_URI);
const db = client.db("myapp");
const users = db.collection("users");

// Insert
await users.insertOne({ name: "Alice", email: "alice@example.com", role: "user" });

// Find with filter
const user = await users.findOne({ email: "alice@example.com" });

// Update
await users.updateOne(
  { _id: user._id },
  { $set: { role: "admin" }, $push: { tags: "promoted" } }
);

// Aggregation pipeline — like SQL GROUP BY + JOIN
const stats = await orders.aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$customerId", total: { $sum: "$amount" }, count: { $sum: 1 } } },
  { $sort: { total: -1 } },
  { $limit: 10 }
]).toArray();

# String — simple key-value
SET session:user:123 "eyJhbGci..."   # Store JWT session
EXPIRE session:user:123 3600         # Expire in 1 hour
GET session:user:123

# Hash — object fields
HSET user:123 name "Alice" email "alice@ex.com" role "admin"
HGET user:123 name
HGETALL user:123

# List — ordered sequence
LPUSH queue:emails "email-job-456"
RPOP queue:emails          # Process from right (FIFO)

# Set — unique members
SADD active_users "user:123" "user:456"
SISMEMBER active_users "user:123"   # → 1 (true)

# Sorted Set — leaderboard
ZADD leaderboard 1500 "alice" 2300 "bob" 1900 "carol"
ZREVRANGE leaderboard 0 2 WITHSCORES  # Top 3

# Pub/Sub — real-time messaging
SUBSCRIBE notifications:user:123
PUBLISH notifications:user:123 '{"type":"message","body":"Hello"}'

# Streams — append-only log (like Kafka, lightweight)
XADD events * type "purchase" amount "49.99"
XREAD COUNT 10 STREAMS events 0

// .NET — IDistributedCache with StackExchange.Redis
services.AddStackExchangeRedisCache(opt =>
    opt.Configuration = builder.Configuration["Redis:ConnectionString"]);

public class ProductService(IDistributedCache cache)
{
    public async Task<Product?> GetAsync(int id)
    {
        var key = $"product:{id}";
        var cached = await cache.GetStringAsync(key);
        
        if (cached is not null)
            return JsonSerializer.Deserialize<Product>(cached);

        var product = await db.Products.FindAsync(id);
        if (product is null) return null;

        await cache.SetStringAsync(key, JsonSerializer.Serialize(product),
            new DistributedCacheEntryOptions { AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10) });

        return product;
    }
}

Table: appointments

PK (Partition Key)  |  SK (Sort Key)        |  Attributes
────────────────────|───────────────────────|────────────────────────
CLINIC#CLN-1        |  APPT#2026-04-17#001  |  status, patientId, doctorId
CLINIC#CLN-1        |  APPT#2026-04-17#002  |  status, patientId, doctorId
PATIENT#PAT-901     |  APPT#2026-04-17#001  |  status, clinicId (duplicated data → no JOIN needed)
DOCTOR#DOC-42       |  APPT#2026-04-17#001  |  status, patientId (same)

// DynamoDB SDK v3 (Node.js)
const { DynamoDBClient, GetItemCommand, QueryCommand, PutItemCommand } = require("@aws-sdk/client-dynamodb");
const { marshall, unmarshall } = require("@aws-sdk/util-dynamodb");

const client = new DynamoDBClient({ region: "us-east-1" });

// Get one appointment
const { Item } = await client.send(new GetItemCommand({
  TableName: "appointments",
  Key: marshall({ pk: "CLINIC#CLN-1", sk: "APPT#2026-04-17#001" })
}));

// Query all appointments for a clinic on a specific date
const { Items } = await client.send(new QueryCommand({
  TableName: "appointments",
  KeyConditionExpression: "pk = :pk AND begins_with(sk, :prefix)",
  ExpressionAttributeValues: marshall({
    ":pk": "CLINIC#CLN-1",
    ":prefix": "APPT#2026-04-17"
  })
}));

-- Cassandra Query Language (CQL) — looks like SQL but isn't
CREATE TABLE sensor_readings (
    device_id  UUID,
    recorded_at TIMESTAMP,
    temperature FLOAT,
    humidity    FLOAT,
    PRIMARY KEY (device_id, recorded_at)
) WITH CLUSTERING ORDER BY (recorded_at DESC);

-- Efficient query (uses partition key)
SELECT * FROM sensor_readings WHERE device_id = ? AND recorded_at > ?;

-- NOT supported in Cassandra (no secondary index without separate table)
-- SELECT * FROM sensor_readings WHERE temperature > 25;

Row store (PostgreSQL):
  Row 1: [user_id=1, name="Alice", revenue=149.99, country="US"]
  Row 2: [user_id=2, name="Bob",   revenue=89.00,  country="UK"]
  → Good for: fetching full rows (individual user lookups)

Column store (ClickHouse):
  user_id column:  [1, 2, 3, 4, ...]
  revenue column:  [149.99, 89.00, 200.00, 45.00, ...]
  → Good for: aggregating one column across billions of rows
  SELECT SUM(revenue) reads ONLY the revenue column — tiny I/O

-- ClickHouse: aggregate 1 billion events in seconds
SELECT 
    toDate(event_time) AS day,
    country,
    COUNT() AS page_views,
    uniq(user_id) AS unique_users,
    AVG(session_duration_seconds) AS avg_session
FROM page_events
WHERE event_time >= '2026-01-01'
GROUP BY day, country
ORDER BY day DESC, page_views DESC
LIMIT 100;

// Index a document
PUT /products/_doc/1
{
  "name": "MacBook Pro 14-inch",
  "description": "Apple laptop with M4 chip for developers",
  "category": "laptops",
  "price": 1999.00,
  "tags": ["apple", "laptop", "developer"]
}

// Full-text search with fuzzy matching
GET /products/_search
{
  "query": {
    "multi_match": {
      "query": "macbok pro developer",
      "fields": ["name^3", "description"],
      "fuzziness": "AUTO"
    }
  }
}

-- Create a hypertable (partitioned by time automatically)
CREATE TABLE sensor_metrics (
    time        TIMESTAMPTZ NOT NULL,
    device_id   TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION
);
SELECT create_hypertable('sensor_metrics', 'time');

-- TimescaleDB time functions
SELECT 
    time_bucket('1 hour', time) AS hour,
    device_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp
FROM sensor_metrics
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY hour, device_id
ORDER BY hour DESC;

-- Compress chunks older than 7 days
SELECT add_compression_policy('sensor_metrics', INTERVAL '7 days');

// Cypher query language
// Create nodes and relationships
CREATE (alice:Person {name: "Alice"})
CREATE (bob:Person {name: "Bob"})
CREATE (alice)-[:FOLLOWS]->(bob)
CREATE (bob)-[:FOLLOWS]->(alice)

// Find mutual friends (3 JOINs in SQL → 1 graph pattern)
MATCH (me:Person {name: "Alice"})-[:FOLLOWS]->(friend)-[:FOLLOWS]->(mutual)
WHERE NOT (me)-[:FOLLOWS]->(mutual) AND me <> mutual
RETURN mutual.name, COUNT(*) AS mutual_friends
ORDER BY mutual_friends DESC
LIMIT 10

// Shortest path between two users
MATCH path = shortestPath((alice:Person {name:"Alice"})-[:FOLLOWS*]-(target:Person {name:"Carol"}))
RETURN length(path), [n IN nodes(path) | n.name]

-- InfluxDB SQL (v3)
SELECT date_bin('5 minutes', time) AS bucket,
       avg(cpu_usage) AS avg_cpu,
       max(memory_gb) AS peak_mem
FROM system_metrics
WHERE time >= now() - interval '1 hour'
  AND host = 'prod-api-1'
GROUP BY bucket
ORDER BY bucket

┌─────────────────────────────────────────────────────────┐
│                 Production System                        │
│                                                          │
│  PostgreSQL ──── primary app data (users, orders)        │
│  Redis ────────── cache + sessions + rate limiting        │
│  Elasticsearch ── full-text product search               │
│  ClickHouse ───── analytics dashboard queries            │
│  TimescaleDB ──── application metrics + alerts           │
└─────────────────────────────────────────────────────────┘

# PostgreSQL + Redis + MongoDB with Docker Compose
# docker-compose.yml
version: "3.9"
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: dev
      POSTGRES_PASSWORD: dev
      POSTGRES_DB: appdb
    ports: ["5432:5432"]
    volumes: ["postgres_data:/var/lib/postgresql/data"]

  redis:
    image: redis:7-alpine
    ports: ["6379:6379"]

  mongodb:
    image: mongo:7
    environment:
      MONGO_INITDB_ROOT_USERNAME: dev
      MONGO_INITDB_ROOT_PASSWORD: dev
    ports: ["27017:27017"]
    volumes: ["mongo_data:/data/db"]

volumes:
  postgres_data:
  mongo_data:

docker-compose up -d

# Connect
psql postgresql://dev:dev@localhost:5432/appdb
redis-cli -h localhost
mongosh mongodb://dev:dev@localhost:27017

Starting a new app? → PostgreSQL
Need a cache? → Redis (always, even if your app is small)
Product catalogue, CMS? → MongoDB or PostgreSQL JSONB
AWS serverless? → DynamoDB
Analytics / BI? → ClickHouse or BigQuery
Search (product, docs)? → Elasticsearch or Postgres FTS
IoT / metrics? → TimescaleDB or InfluxDB
Social graph? → Neo4j
Single-user / mobile? → SQLite