What SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more databases actually do

SQL (Structured Query Language) databases organize data into tables with predefined columns. Every row follows the same schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. Ask AI for more. Relationships between tables are explicit through foreign keys, and you query data using SQL, a declarative language that's been around since the 1970s.

The defining characteristics of SQL databases:

Strict schema enforcement. You define columns and types upfront. The database rejects anything that doesn't fit. This catches bugs before they reach production.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total_cents INTEGER NOT NULL CHECK (total_cents > 0),
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
);

-- The database rejects this - total_cents can't be negative
INSERT INTO orders (user_id, total_cents)
VALUES (1, -500);
-- ERROR: new row violates check constraint "orders_total_cents_check"

ACIDWhat is acid?Four guarantees a database makes about transactions: changes are all-or-nothing, data stays valid, concurrent users don't interfere, and saved data survives crashes. Ask AI for more transactions. Atomicity, Consistency, Isolation, Durability. When you transfer money between accounts, either both the debit and credit succeed, or neither does. There is no in-between state where money vanishes.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If anything fails, both changes are rolled back

Complex queries with JOINs. SQL shines when you need to combine data from multiple tables in a single query. Finding all orders by a user, grouped by month, with product names attached? That's one SQL statement.

SELECT u.name, DATE_TRUNC('month', o.created_at) AS month,
       COUNT(*) AS order_count, SUM(o.total_cents) / 100.0 AS total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '1 year'
GROUP BY u.name, month
ORDER BY month DESC;

Popular SQL databases: PostgreSQL, MySQL, SQLite, SQL Server, Oracle.

What NoSQLWhat is nosql?A category of databases that store data without fixed table schemas, using documents, key-value pairs, or graphs instead of rows and columns. Ask AI for more databases actually do

NoSQL is not a single technology, it's a family of approaches that relax one or more SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more constraints to gain something else. The four main categories:

Document stores (MongoDB, CouchDB) store JSONWhat is json?A text format for exchanging data between systems. It uses key-value pairs and arrays, and every programming language can read and write it. Ask AI for more-like documents. Each document can have a different structure. Good for content management, catalogs, and user profiles with varying fields.

// MongoDB: each product can have different attributes
db.products.insertMany([
  {
    name: "Running Shoes",
    price: 89.99,
    sizes: [8, 9, 10, 11],
    material: "mesh",
    waterproof: false
  },
  {
    name: "Laptop Stand",
    price: 49.99,
    weight_kg: 1.2,
    adjustable: true,
    max_screen_size: 17
    // no sizes, no material - different shape, same collection
  }
]);

Key-value stores (Redis, DynamoDB) map keys to values. Extremely fast lookups when you know exactly what you're looking for. Think of them as a giant hash map.

// Redis: instant lookups by key
await redis.set("session:abc123", JSON.stringify({ userId: 42, role: "admin" }));
const session = JSON.parse(await redis.get("session:abc123"));

Wide-column stores (Cassandra, HBase) handle massive datasets distributed across many nodes. Built for write-heavy workloads at enormous scale.

Graph databases (Neo4j, Amazon Neptune) model relationships as first-class citizens. Perfect for social networks, recommendation engines, and fraud detection.

Feature comparison

This is the table you'll reference when making real decisions:

Decision framework based on access patterns

The key insight most developers miss: your access patterns should drive your database choice, not your data model. Two applications storing the same kind of data might need different databases because they read and write in different ways.

Ask these questions:

How do you read data? If you often need to joinWhat is join?A SQL operation that combines rows from two or more tables based on a shared column, letting you query related data in one request. Ask AI for more data across entities (orders + users + products in one query), SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more is the natural fit. If you always read a single self-contained document (a user profile with embedded preferences), a document store is faster.

How do you write data? If writes need to touch multiple tables atomically (financial transactions), you want ACIDWhat is acid?Four guarantees a database makes about transactions: changes are all-or-nothing, data stays valid, concurrent users don't interfere, and saved data survives crashes. Ask AI for more. If writes are mostly appending new records (event logs, sensor data), NoSQLWhat is nosql?A category of databases that store data without fixed table schemas, using documents, key-value pairs, or graphs instead of rows and columns. Ask AI for more can be simpler and faster.

How does the data grow? If you expect millions of rows in a well-structured table, SQL handles that fine with proper indexing. If you expect billions of events across many nodes, a wide-column store like Cassandra is purpose-built for that.

How much does the schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. Ask AI for more change? If your domain is stable (accounting, inventory), SQL's rigid schema prevents bugs. If you're iterating fast and the data shape changes weekly (startup MVP, A/B testing variants), document stores let you move without migrations.

// Decision in code form
function chooseDatabase(requirements: {
  needsJoins: boolean;
  needsACID: boolean;
  schemaStable: boolean;
  scaleHorizontally: boolean;
  readPattern: "by-key" | "by-query" | "by-relationship";
}): string {
  if (requirements.needsACID && requirements.needsJoins) {
    return "PostgreSQL";
  }
  if (requirements.readPattern === "by-key" && requirements.scaleHorizontally) {
    return "DynamoDB or Redis";
  }
  if (!requirements.schemaStable && requirements.readPattern === "by-query") {
    return "MongoDB";
  }
  return "PostgreSQL"; // safe default
}

The hybrid reality

In practice, most production systems use multiple databases. This is called polyglot persistence: you'll learn more about it in Lesson 4. Here's a typical example:

PostgreSQL  →  Users, orders, payments (relational, ACID)
Redis       →  Sessions, caching, rate limits (speed)
S3          →  Images, PDFs, uploads (blob storage)

The answer to "SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more or NoSQLWhat is nosql?A category of databases that store data without fixed table schemas, using documents, key-value pairs, or graphs instead of rows and columns. Ask AI for more?" is almost always "both, for different things." Start with PostgreSQL for your core data. Add specialized stores when you hit specific pain points, not before.

Common mistakes

Choosing MongoDB because "it's easier." Skipping schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. Ask AI for more design doesn't make your problem simpler, it just moves the complexity to your application code. You end up writing validation logic that a SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more database gives you for free.

Choosing SQL for everything. Storing sessionWhat is session?A server-side record that tracks a logged-in user. The browser holds only a session ID in a cookie, and the server looks up the full data on each request. Ask AI for more data in PostgreSQL when Redis would give you 100x faster lookups is leaving performance on the table.

Choosing based on what's trendy. A technology's popularity on Hacker News is not a valid selection criterion. Choose based on your access patterns, team expertise, and operational requirements.