Databases HOME¶

You're gonna make a wrong choice first , we all did The question isn't whether you'll pick the wrong database for your first project - it's how long until you realize it and migrate without losing production data at 2AM on a Saturday when Burp took a coffee break and your pager just lit up like a Christmas tree

Relational databases store data in tables with rows and columns , enforcing schemas and relationships through foreign keys and constraints that keep your data from turning into a dumpster fire when some intern forgets to validate input on the signup form. NoSQL stores give you flexibility with documents (MongoDB) , key-value pairs (Redis) , column families , or graphs - trading strict consistency for horizontal scaling and developer velocity that sounds great until you're writing five aggregation stages to get a simple join that Postgres would handle in one query

Every database is a tradeoff wrapped in marketing hype

Relational vs NoSQL - the blood match¶

Relational databases (Postgres , MySQL , SQLite) enforce ACID guarantees: Atomicity (transactions either complete fully or roll back entirely) , Consistency (data follows all rules and constraints) , Isolation (concurrent transactions don't corrupt each other) , Durability (committed data survives crashes and power failures). When your finance system needs to deduct from Account A and credit Account B without ever losing a penny in between , you want ACID so bad you can taste it

NoSQL databases trade some or all ACID for scale and speed. MongoDB gives you documents with flexible schemas that make prototyping fast but can leave orphaned fields scattered across your data like landmines for future devs who didn't get the memo about schema validation. Redis keeps everything in RAM and gives you sub-millisecond reads that make it perfect for caching sessions and rate limiting but absolutely terrifying as a primary datastore because one crash without persistence configured and your entire user base gets logged out simultaneously

-- This is what you're avoiding with relational integrity
-- Without foreign keys , you get orphaned orders like this
SELECT * FROM orders WHERE user_id = 999999;
-- User 999999 was deleted , orders remain , nobody knows why

Picking the right DB for the job¶

Your stack isn't a fashion show - don't pick Mongo because it's trendy if your data is inherently relational with joins and transactions and structured relationships that scream "I belong in normalized tables". Vice versa: don't force Postgres to store JSON blobs with wildly inconsistent schemas when you're doing event sourcing at Twitter scale and every document has different fields based on event type and version

Postgres wins when: your data has clear relationships , you need ACID transactions , you want query flexibility through SQL , you need JSONB for hybrid relational+document workloads , you're building something that needs to stay correct more than it needs to go viral. Postgres handles most workloads up to fairly serious scale before you even need to think about sharding or read replicas because it's been optimized by two decades of people much smarter than us

MongoDB wins when: your schema is genuinely fluid and changes weekly during rapid prototyping , you're storing documents with deeply nested structures that would require 12 JOINs in SQL , you need horizontal scaling across commodity hardware with automatic sharding , you're doing massive write workloads where BASE (Basically Available , Soft state , Eventual consistency) is good enough and you can handle some data drift

Redis wins when: you need speed measured in microseconds not milliseconds , you're caching frequently accessed data to take load off your primary database , you need ephemeral data structures like rate limit counters and job queues that expire automatically , you're implementing real-time features like leaderboards and pub/sub messaging where every millisecond of latency loses users

// Node.js connection decision matrix
const db = dataNeedsJoins && transactionsMatter
  ? connectPostgres("postgresql://localhost:5432/mydb")
  : schemaChangesWeekly && scaleHorizontally
    ? connectMongo("mongodb://localhost:27017/mydb")
    : needsFastCache && ephemeralData
      ? connectRedis("redis://localhost:6379")
      : connectSQLite("./dev.db"); // dev - just use SQLite for prototyping

Security at the data layer¶

Most breaches happen because developers treat the database as an internal fortress with no guard at the gate when the real threat is the application layer routing malicious input straight into your queries without sanitization or parameterization. SQL injection remains the OWASP top 10 year after year because developers keep building queries with string concatenation like it's 1999 and they think "I'll just escape the input" - spoiler: escaping doesn't work when attackers bypass it with Unicode tricks and alternate encodings

Every database covered in this curriculum has specific injection patterns and attack surfaces that you need to understand before you deploy anything to production

The golden rules that never change: * Parameterize every query - no exceptions for "this one is internal only" because internal endpoints get compromised through SSRF more often than you think * Least privilege for database users - your application user should not be able to DROP TABLE or TRUNCATE or ALTER , it should only SELECT , INSERT , UPDATE , DELETE on the tables it actually needs for its specific operations * Encrypt data at rest and in transit - TLS for connections , encryption at rest for disks , and column-level encryption for PII so that when (not if) someone dumps your database , the credit card numbers aren't readable * Audit everything - who queried what , when , from which IP , using which role. When you're investigating a breach at 3AM , audit logs are the only thing between you and "we have no idea how the data left"

What's in this curriculum¶

PostgreSQL (db_01 through db_05): from installation and psql basics through CRUD mastery , indexing strategies that make queries scream , advanced features like window functions and CTEs that separate the pros from the script kiddies , and a full deep-dive on Postgres security including RLS , encryption , and SQL injection prevention

MongoDB (db_06 through db_09): document model fundamentals , CRUD with the aggregation pipeline that's more powerful than you think , indexing strategies critical for NoSQL performance , and NoSQL injection patterns that are different from SQLi but equally devastating when exploited

Redis (db_10 through db_12): in-memory data structures and their real-world use cases , caching patterns that won't leave you with stale data serving your users garbage , and security configs that prevent the FLUSHALL tragedy every Redis admin has nightmares about

SQLite & Prisma (db_13 through db_15): when to use the world's most deployed database engine , Prisma schema design and migrations , and advanced ORM patterns that keep your queries fast and your data safe

Reference (db_16 through db_17): migration strategies that don't nuke production , and a comprehensive comparison guide linking to official docs and recommended resources

prerequisites¶

This is a backend curriculum - you should know basic programming (JavaScript/Node.js for the examples) and have a terminal open that you're not afraid to type in. If you can run npm install without panicking at the warning messages, you're qualified

next → db_01_postgres_intro.md