Databases for Web Apps
Most web apps persist data in a database. Choose based on the shape of your data and your access patterns (see DBMS).
SQL (PostgreSQL) with Node
npm install pg
const { Pool } = require("pg");
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
async function createNote(userId, text) {
const { rows } = await pool.query(
"INSERT INTO notes (user_id, text) VALUES ($1, $2) RETURNING *",
[userId, text], // parameters: never string-concatenate input
);
return rows[0];
}
async function listNotes(userId, limit = 20) {
const { rows } = await pool.query(
"SELECT id, text, created_at FROM notes WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2",
[userId, limit],
);
return rows;
}
CAUTION
Always use parameterised queries. Concatenating user input into SQL causes SQL injection.
Schema and migrations
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE notes (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
text TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_notes_user_created ON notes (user_id, created_at DESC);
Keep schema changes in versioned migration files (Prisma Migrate, Knex, Flyway) rather than editing production by hand.
MongoDB with Mongoose
const mongoose = require("mongoose");
const noteSchema = new mongoose.Schema({
userId: { type: mongoose.Types.ObjectId, index: true },
text: { type: String, required: true, maxlength: 500 },
createdAt: { type: Date, default: Date.now },
});
const Note = mongoose.model("Note", noteSchema);
await Note.create({ userId, text: "Hello" });
const notes = await Note.find({ userId }).sort({ createdAt: -1 }).limit(20);
SQL or NoSQL?
| Use SQL when | Use a document store when |
|---|---|
| Relationships and joins matter | Data is hierarchical and read together |
| You need transactions | Schema changes often |
| Reporting and analytics | Horizontal scale with simple key lookups |
Common pitfalls
| Problem | Fix |
|---|---|
| N+1 queries (a query per row in a loop) | Use a join or IN (...) batch query |
| Missing indexes on filter/sort columns | Add indexes; check with EXPLAIN |
Unbounded SELECT * | Paginate; select only needed columns |
| Storing passwords in plain text | Hash with bcrypt or argon2 |
| Connection per request | Use a connection pool |
Loading Comments...