Databases & SQLWhen AI scaffolds a project, it picks a database. This lesson gives you the criteria to evaluate that choice. Instead of blindly accepting what AI generated, you'll know when to keep it, when to swap it, and what red flags to watch for.
SQLite: the zero-config database
SQLite is unique, the entire database is a single file. No server process, no configuration, no setup. Just a file that you can copy, backup, or delete like any other.
When SQLite shines
Local development: Start coding immediately. No DockerWhat is docker?A tool that packages your application and all its dependencies into a portable container that runs identically on any machine. containers, no service to start.
Automated testing: Each test can use an in-memory database that disappears when the test ends.
Desktop and mobile apps: Your app bundles the database file. Users don't need to install anything extra.
IoT and embedded: Running on a Raspberry Pi with limited resources? SQLite handles it.
SQLite limitations
SQLite supports multiple readers but only one writer at a time. For a web application with concurrent users, this becomes a bottleneck.
// SQLite in Node.js
import sqlite3 from 'sqlite3';
const db = new sqlite3.Database('./myapp.db');
db.run(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE,
name TEXT
)
`);PostgreSQL: the professional choice
PostgreSQL (often called Postgres) is the database most professional developers reach for. It's been actively developed for over 30 years and strikes the perfect balance between features, reliability, and performance.
Why PostgreSQL?
Advanced features: Store and query 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., work with arrays, use full-text search, create custom data types. PostgreSQL keeps adding capabilities while staying stable.
Extensibility: The PostGIS extension adds geographic capabilities. TimescaleDB turns it into a time-series database. There's an extension for almost everything.
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. compliance: Your data is safe. Transactions work exactly as you expect.
Industry standard: When job postings say "SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. database," they usually mean PostgreSQL or something very similar.
Cloud ready: Every major cloud providerWhat is provider?A wrapper component that makes data available to all components nested inside it without passing props manually. (AWS, Google Cloud, Azure) offers managed PostgreSQL. You don't have to become a database administrator.
When to use PostgreSQL
✅ Production web applications
✅ Complex relational data
✅ Analytics and reporting needs
✅ Applications requiring geospatial features
✅ Startups planning to scale
MySQL and MariaDB
MySQL powered the early web. WordPress runs on it. Many shared hosting providers offer it by default. It's not as feature-rich as PostgreSQL, but it's battle-tested.
When MySQL makes sense
Legacy applications: If you're maintaining an existing PHP application, it's probably using MySQL.
Shared hosting: Cheap web hosts almost always offer MySQL.
WordPress ecosystem: The world's most popular CMS expects MySQL.
Team expertise: If your entire team knows MySQL and not PostgreSQL, that's a valid reason to stick with it.
PostgreSQL vs MySQL today
For new projects, PostgreSQL is generally the better choice. It has more features, better SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. standard compliance, and a more active open-source community. But MySQL won't block your success, millions of applications run on it happily.
MongoDB: the flexible alternative
MongoDB stores documents (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.-like objects) instead of tables. This flexibility is its superpower and its weakness.
When MongoDB fits
Rapid prototyping: Your schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. changes daily as you figure out what you're building. No migrations needed.
Product catalogs: One product has dimensions, another has technical specs, another has color options. Documents handle this naturally.
Content management: Blog posts with varying fields, pages with custom widgets, flexible content loves documents.
Time-series data: IoT sensors, application logs, high-volume data that doesn't need complex relationships.
When to avoid MongoDB
❌ Complex multi-document transactions (possible but awkward)
❌ Heavy 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.-like operations
❌ Complex reporting with many aggregations
❌ When data consistency is absolutely critical
Redis: more than a cache
Redis stores data in memory (RAM) instead of disk. This makes it incredibly fast, microseconds instead of milliseconds.
Common Redis uses
Caching: Store the results of slow database queries. Next request? Serve from memory in microseconds.
Sessions: When users log in, store their 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. in Redis. Check it on every request, it's fast enough.
Rate limitingWhat is rate limiting?Restricting how many requests a client can make within a time window. Prevents brute-force attacks and protects your API from being overwhelmed.: Count APIWhat is api?A set of rules that lets one program talk to another, usually over the internet, by sending requests and getting responses. requests per user. Block abusers before they hit your main database.
Real-time features: Leaderboards, live counters, chat presence, Redis pub/subWhat is pub/sub?A messaging pattern where senders publish events to a channel and any number of listeners receive them in real time. handles real-time messaging.
Task queues: Background jobs wait in Redis lists. Workers pick them up and process them.
Redis with PostgreSQL
The most common modern stack combines both:
// Check cache first
let user = await redis.get(`user:${userId}`);
if (!user) {
// Cache miss - query the database
user = await db.query('SELECT * FROM users WHERE id = CODE_BLOCK', [userId]);
// Store in cache for next time (expires in 1 hour)
await redis.setex(`user:${userId}`, 3600, JSON.stringify(user));
}This pattern, "cache aside" or "lazy loadingWhat is lazy loading?Deferring the loading of a resource like an image or component until the moment it's actually needed, speeding up the initial page load.", gives you the durability of PostgreSQL with the speed of Redis.
Decision matrix
| Your situation | Recommended database | Why |
|---|---|---|
| Learning/prototyping | SQLite | Zero setup, just a file |
| New web application | PostgreSQL | Best balance of features and reliability |
| E-commerce site | PostgreSQL + Redis | Transactions + performance |
| Blog or CMS | PostgreSQL or MongoDB | Depends on content complexity |
| Social network | PostgreSQL + Redis | Relationships + real-time |
| Mobile app backend | PostgreSQL | Standard API patterns |
| High-frequency trading | Specialized (not covered here) | Extreme requirements |
How to evaluate AI's database choice
When AI generates a backend, check the database decision against these guidelines:
If AI picked PostgreSQL, probably right
- Handles 90% of use cases without problems
- Scales from prototype to production
- Verify it included connection pooling and parameterized queries
If AI added Redis, check why
- Caching and sessions are valid reasons
- Rate limitingWhat is rate limiting?Restricting how many requests a client can make within a time window. Prevents brute-force attacks and protects your API from being overwhelmed. is a valid reason
- If it's using Redis as the primary data store, that's a red flag
If AI picked MongoDB, question it
- Is the data actually document-shaped, or does it have foreign keyWhat is foreign key?A column in one database table that references a row in another table, creating a link between the two. relationships?
- Did AI default to Mongoose out of habit rather than data analysis?
- Would PostgreSQL's JSONBWhat is jsonb?PostgreSQL's binary-encoded JSON column type that is indexable and supports efficient querying, unlike plain JSON. column handle the flexible parts without giving up SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables.?
Quick reference
| Database | Best For | Avoid When |
|---|---|---|
| SQLite | Dev, testing, mobile | Concurrent web apps |
| PostgreSQL | Production, complex data | You need extreme horizontal scale |
| MySQL | Legacy, shared hosting | You want modern SQL features |
| MongoDB | Flexible schemas, prototyping | Complex transactions needed |
| Redis | Cache, sessions, real-time | Permanent data storage |
// Typical modern stack: PostgreSQL + Redis
// PostgreSQL: Reliable data storage
const user = await pg.query(
'SELECT * FROM users WHERE email = $1',
[email]
);
// Redis: Speed layer
await redis.setex(
`session:${sessionId}`,
86400, // 24 hours
JSON.stringify({ userId: user.id })
);
// Check rate limit
const attempts = await redis.incr(`login_attempts:${ip}`);
if (attempts > 5) {
throw new Error('Too many attempts');
}