Databases
SQL, NoSQL, NewSQL — how data is stored, queried, and why the choice matters for cost and scale.
Relational vs. Non-Relational: The Fundamental Choice
Nearly every application stores data. The choice between relational (SQL) and non-relational (NoSQL) databases shapes your architecture:
Relational (SQL)
Data organized into tables with strict schemas. ACID guarantees (Atomicity, Consistency, Isolation, Durability) ensure data integrity. SQL (Structured Query Language) is standardized across databases. Good for structured, interconnected data with complex queries.
Example use cases: E-commerce (orders, products, customers), financial systems, CRMs
Non-Relational (NoSQL)
Flexible schemas, data stored as documents, key-value pairs, wide columns, or graphs. Trade ACID for flexibility and horizontal scaling. Great for rapidly changing data, high volume writes, and semi-structured data.
Example use cases: User profiles, content management, real-time analytics, session storage
Relational SQL Databases
MySQL / MariaDB
Open-source, widely supported. Powers WordPress. Mature and reliable but not the fastest.
PostgreSQL
Open-source, feature-rich. Advanced data types, full-text search, JSON support. Increasingly the default choice for new projects.
SQLite
Embedded, file-based. Zero setup. Perfect for development, mobile apps, and edge computing.
MSSQL (SQL Server)
Microsoft's enterprise database. Excellent tooling, expensive licensing.
Oracle
Legacy enterprise standard. Expensive, declining adoption for new projects.
Non-Relational NoSQL Databases
MongoDB (Document)
JSON-like documents. Flexible schema. Easiest NoSQL to learn. Popular but debates about ACID support and performance.
Firestore (Document + Real-Time)
Google's managed database. Real-time syncing, excellent for mobile/web apps. Vendor lock-in and querying limitations.
Redis (Key-Value / Cache)
In-memory data store. Blazingly fast. Used for caching, sessions, real-time counters, pub/sub. Not for primary data storage (data loss on restart).
Cassandra (Wide-Column)
Distributed, handles massive scale. Used by Netflix, Twitter. Complex to operate.
Neo4j (Graph)
Stores relationships as first-class citizens. Perfect for social networks, recommendation engines, knowledge graphs.
Elasticsearch (Search)
Full-text search engine. Used for logs, analytics, search-heavy applications.
InfluxDB / TimescaleDB (Time-Series)
Optimized for metrics and time-stamped data. Used for monitoring, metrics, IoT data.
NewSQL: The Best of Both Worlds
NewSQL databases attempt to combine SQL's ACID guarantees with NoSQL's scalability:
- CockroachDB: Distributed SQL database. ACID guarantees at scale. Great for globally distributed applications.
- PlanetScale: MySQL-compatible, serverless. Branching and zero-downtime deploys.
- Neon: PostgreSQL-compatible, serverless. Auto-scaling, branching for development.
ORMs: Abstraction Layer Between Code and Database
ORMs (Object-Relational Mapping) let you write database queries in your programming language instead of raw SQL. Popular ORMs:
- Prisma (Node.js): Modern, type-safe, excellent developer experience.
- SQLAlchemy (Python): Mature, powerful, flexible.
- Eloquent (PHP/Laravel): Elegant syntax, Laravel-native.
- ActiveRecord (Ruby): Rails-native, convention-driven.
- Hibernate (Java): Enterprise standard.
Database Decision Matrix
| Database Type | Best For | Scaling | Complexity | Examples |
|---|---|---|---|---|
| Relational (PostgreSQL) | Structured data, complex queries | Vertical | Low | E-commerce, SaaS |
| Document (MongoDB) | Semi-structured, flexible schema | Horizontal | Medium | Content systems, mobile |
| Key-Value (Redis) | Caching, sessions, real-time | Horizontal | Low | Leaderboards, cache layers |
| Graph (Neo4j) | Relationships, networks | Vertical | High | Social networks, recommendations |
| Search (Elasticsearch) | Full-text search, logs | Horizontal | Medium | Analytics, logging |
| Time-Series (InfluxDB) | Metrics, monitoring | Horizontal | Medium | Metrics, IoT |