Databases Introduction in Node

Introduction

Persistent apps store data in databases—PostgreSQL, MySQL, MongoDB, Redis, and others. Node does not include a database engine; you install a driver or ORM and connect over the network or local socket. This chapter explains concepts and a minimal PostgreSQL-style flow without binding you to one vendor.

Prerequisites

SQL vs Document Stores (Brief)

StyleExamplesShape
Relational (SQL)PostgreSQL, MySQL, SQLiteTables, rows, JOINs
DocumentMongoDBJSON-like documents
Key-value / cacheRedisStrings, hashes, TTL

Choose based on data relationships, team skills, and ops constraints.

Connection String in Env

plaintext
DATABASE_URL=postgres://user:pass@localhost:5432/myapp

Never commit real credentials—use .env locally and secrets in production.

Raw SQL with pg (Conceptual)

bash
npm install pg
javascript
// db.mjs — PostgreSQL example
import pg from "pg";
 
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});
 
export async function findUserByEmail(email) {
  const result = await pool.query(
    "SELECT id, email, name FROM users WHERE email = $1",
    [email]
  );
  return result.rows[0] ?? null;
}
 
// Usage
const user = await findUserByEmail("ada@example.com");
console.log(user);

$1 placeholders prevent SQL injection—never interpolate user strings into SQL.

ORM Example Sketch (Prisma-style)

ORMs map tables to JavaScript objects and generate migrations:

javascript
// Pseudocode — actual API depends on library
// const user = await prisma.user.create({ data: { email, name } });

Good for rapid development; learn SQL fundamentals anyway.

MongoDB Sketch

javascript
// Conceptual — mongodb driver
// const doc = await collection.findOne({ email });
// await collection.insertOne({ email, name, createdAt: new Date() });

Flexible schema; enforce shape in application code or JSON Schema.

Redis for Cache

javascript
// Cache expensive query result 60 seconds
// await redis.set(`user:${id}`, JSON.stringify(user), "EX", 60);
// const cached = await redis.get(`user:${id}`);

Not a replacement for primary storage—cache and session store use cases.

Transactions (SQL)

javascript
// Transfer points between accounts atomically
const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [
    10,
    fromId,
  ]);
  await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [
    10,
    toId,
  ]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();
}

Mini Example: In-Memory Repo (Tests)

javascript
// Replace with real DB in production
const users = new Map();
 
export function saveUser(user) {
  users.set(user.email, user);
}
 
export function getUser(email) {
  return users.get(email) ?? null;
}
 
saveUser({ email: "a@b.com", name: "Ada" });
console.log(getUser("a@b.com"));

Swap implementation behind the same functions when adding PostgreSQL.

FAQ

SQLite for learning?

Yes—single file, zero server—great for local tutorials and tests.

Connection pooling?

Reuse pools (pg.Pool)—opening a connection per request is slow.

Migrations?

Version schema with SQL migration files or ORM tools—never edit production tables by hand without a plan.

What comes next?

Course wrap-up, then async/await and message queues.