|$ curl https://forge-ai.dev/api/markdown?path=docs/databases/nosql-vs-sql
$cat docs/nosql-vs-sql.md
updated Recently·30 min read·published
NoSQL vs SQL
Introduction
SQL (relational) and NoSQL (non-relational) databases make different trade-offs between consistency, flexibility, scalability, and query power. The right choice depends on your data model, access patterns, and scale requirements — not ideology.
Head-to-Head Comparison
| Aspect | SQL (PostgreSQL, MySQL) | NoSQL (MongoDB, DynamoDB) |
|---|---|---|
| Data Model | Tables with fixed schema | Documents, key-value, graph, column |
| Schema | Fixed (migrations required) | Flexible (schemaless or dynamic) |
| Query Language | SQL (standardized) | Proprietary APIs per database |
| Joins | Native, optimized | $lookup (weak) or application-level |
| ACID | Full ACID transactions | Varies (MongoDB 4.0+ has multi-doc ACID) |
| Scaling | Vertical (scale up) | Horizontal (scale out) |
| Best For | Complex queries, relationships | Flexible schemas, high throughput |
Document vs Relational Modeling
modeling-comparison.js
JavaScript
| 1 | // MongoDB: Document model — embed related data |
| 2 | db.orders.insertOne({ |
| 3 | _id: ObjectId("..."), |
| 4 | customer: { |
| 5 | name: "Alice", |
| 6 | email: "alice@example.com", |
| 7 | address: { city: "Portland", state: "OR" } |
| 8 | }, |
| 9 | items: [ |
| 10 | { product: "Widget", qty: 2, price: 9.99 }, |
| 11 | { product: "Gadget", qty: 1, price: 24.99 } |
| 12 | ], |
| 13 | total: 44.97, |
| 14 | status: "shipped", |
| 15 | createdAt: new Date() |
| 16 | }); |
| 17 | |
| 18 | // PostgreSQL: Relational model — normalize into tables |
| 19 | // customers (id, name, email, city, state) |
| 20 | // orders (id, customer_id, total, status, created_at) |
| 21 | // order_items (id, order_id, product_id, qty, price) |
| 22 | // products (id, name, price) |
| 23 | |
| 24 | // Query comparison |
| 25 | // MongoDB: single collection scan |
| 26 | db.orders.find({ "customer.email": "alice@example.com" }); |
| 27 | |
| 28 | // PostgreSQL: requires JOIN |
| 29 | SELECT o.*, c.name, c.email |
| 30 | FROM orders o |
| 31 | JOIN customers c ON c.id = o.customer_id |
| 32 | WHERE c.email = 'alice@example.com'; |
ℹ
info
Use embedding (NoSQL style) when data is read together and rarely updated independently. Use normalization (SQL style) when data is shared across entities or updated frequently.
CAP Theorem
The CAP theorem states that a distributed system can guarantee at most two of three properties: Consistency (all reads return the latest write), Availability (every request gets a response), and Partition Tolerance (system works despite network failures). Since network partitions are inevitable, the real choice is between CP and AP.
| Category | Databases | Trade-off |
|---|---|---|
| CP | PostgreSQL, MongoDB, Redis Cluster, HBase | Consistent but may reject requests during partitions |
| AP | Cassandra, DynamoDB, CouchDB, Riak | Always available but may serve stale data |
| CA | Single-node PostgreSQL | No partition tolerance (not distributed) |
When to Use Each
Use SQL When
◆Data has clear relationships (users, orders, products)
◆You need complex queries with JOINs and aggregations
◆ACID transactions are critical (financial, healthcare)
◆Data integrity is more important than flexibility
◆Team knows SQL well
Use NoSQL When
◆Data structure varies widely (logs, IoT, user-generated content)
◆You need massive horizontal scale (millions of writes/sec)
◆Schema changes frequently (rapid prototyping)
◆Low-latency reads/writes at any scale
◆Data is naturally hierarchical or document-shaped
Polyglot Persistence
Most production systems use multiple databases, each chosen for a specific workload. This is polyglot persistence — using the right tool for each job.
polyglot.ts
TypeScript
| 1 | // Common polyglot architecture |
| 2 | // PostgreSQL: core business data (users, orders, payments) |
| 3 | // Redis: session cache, rate limiting, real-time counters |
| 4 | // Elasticsearch: full-text search, log analytics |
| 5 | // MongoDB: user-generated content, flexible schemas |
| 6 | // S3: file storage, backups |
| 7 | |
| 8 | // Example: application using multiple databases |
| 9 | import { Pool } from "pg"; |
| 10 | import Redis from "ioredis"; |
| 11 | import { MongoClient } from "mongodb"; |
| 12 | |
| 13 | const pg = new Pool({ connectionString: process.env.DATABASE_URL }); |
| 14 | const redis = new Redis(process.env.REDIS_URL); |
| 15 | const mongo = new MongoClient(process.env.MONGODB_URL); |
| 16 | |
| 17 | // Cache-aside pattern with PostgreSQL + Redis |
| 18 | async function getUser(id: string) { |
| 19 | // Check cache first |
| 20 | const cached = await redis.get(`user:${id}`); |
| 21 | if (cached) return JSON.parse(cached); |
| 22 | |
| 23 | // Fall back to database |
| 24 | const { rows } = await pg.query("SELECT * FROM users WHERE id = $1", [id]); |
| 25 | const user = rows[0]; |
| 26 | |
| 27 | // Populate cache with 5-minute TTL |
| 28 | if (user) await redis.setex(`user:${id}`, 300, JSON.stringify(user)); |
| 29 | return user; |
| 30 | } |
✓
best practice
Polyglot persistence adds operational complexity. Start with one database (usually PostgreSQL) and add others only when a specific workload clearly benefits. The operational cost of running multiple databases is often underestimated.
$Blueprint — Engineering Documentation·Section ID: DB-NOSQL-01·Revision: 1.0
Community
Get help on Slack, Discord or VIP
Stuck on a guide? Join the community and ask.