Talking to Databases

The todo API from the last lesson kept data in an array. That was useful for seeing HTTP clearly, but it had one fatal flaw: restart the server and the data disappears. A database is where a server keeps durable, shared data.

Node does not make databases mysterious. Your code sends a query, the database runs it, and your code receives rows back. The professional part is choosing the right database, validating inputs before they reach it, and never building SQL by gluing user text into strings.

SQL from Node

SQL is the language most relational databases understand. Tables hold rows, columns describe fields, and queries ask the database to read or change those rows.

A tiny todo table might start like this:

CREATE TABLE todos (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  done INTEGER NOT NULL DEFAULT 0
);

SQLite is a good local starting point because the database lives in one file. That makes it pleasant for learning, small tools, tests, prototypes, and local development. A Node script can create the table and insert rows with a SQLite driver package:

npm install better-sqlite3
node scripts/setup-sqlite.mjs

import Database from "better-sqlite3";
 
const db = new Database("dev.sqlite");
 
db.exec(`
  CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    done INTEGER NOT NULL DEFAULT 0
  );
`);
 
const insertTodo = db.prepare("INSERT INTO todos (title) VALUES (?)");
insertTodo.run("Move todos out of memory");
 
const rows = db.prepare("SELECT id, title, done FROM todos ORDER BY id").all();
 
console.table(rows);
db.close();

The important shape is not the package name. It is the rhythm: connect to a database, run SQL, pass values separately, receive rows, close or release resources when appropriate.

Parameters are the injection fix

SQL injection happens when untrusted input becomes part of the SQL program. This is the unsafe shape:

const title = request.body.title;
const sql = `INSERT INTO todos (title) VALUES ('${title}')`;
 
db.exec(sql);

That looks tempting because it resembles template literals you use everywhere else. Do not do this for SQL. The title is outside data from a request. If you paste it into the SQL string, you are letting outside data change the command itself.

The fix is a parameterized query: write the SQL with placeholders, then pass the values separately.

const insertTodo = db.prepare("INSERT INTO todos (title) VALUES (?)");
insertTodo.run(request.body.title);

The placeholder ? says "a value goes here." The driver sends the SQL shape and the value separately, so a title stays a title. It cannot turn into DROP TABLE, a fake condition, or another SQL fragment.

Placeholders are not always spelled the same way. SQLite drivers commonly use ?. Postgres drivers commonly use $1, $2, and so on. The rule is the same: SQL text in one place, values in another.

import pg from "pg";
 
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});
 
const result = await pool.query(
  "INSERT INTO todos (title) VALUES ($1) RETURNING id, title, done",
  [request.body.title],
);
 
console.log(result.rows[0]);
await pool.end();

Parameterized queries are not a nice extra. They are the normal way to put outside values into SQL. Validate first so your app accepts only meaningful data. Parameterize next so accepted values cannot rewrite the database command.

Validation still happens before the query

The API boundary from lesson 22.3 still matters. The database should not be the first part of your app to discover that a request body is null, an array, or a blank title.

function validateTodoInput(input) {
  if (typeof input !== "object" || input === null || Array.isArray(input)) {
    return { ok: false, error: "JSON body must be an object" };
  }
 
  if (typeof input.title !== "string" || input.title.trim() === "") {
    return { ok: false, error: "title is required" };
  }
 
  return { ok: true, title: input.title.trim() };
}
 
const inputs = [null, { title: "" }, { title: "  Save to the database  " }];
 
for (const input of inputs) {
  const result = validateTodoInput(input);
  console.log(result.ok ? result.title : result.error);
}

→ JSON body must be an object, then title is required, then Save to the database.

Once validation returns { ok: true, title }, the insert can use title as a parameter. That gives you two separate protections: validation protects your app's rules, and parameters protect the SQL command boundary.

From SQLite to Postgres

SQLite and Postgres are both relational databases, but they fit different jobs.

SQLite stores data in a local file. It is excellent when the app and the database live together: local development, tests, command-line tools, small internal utilities, and prototypes. Postgres runs as a database server. It is the usual shape for deployed web apps where multiple app instances, users, migrations, backups, permissions, and operations matter.

The path from SQLite to Postgres is smoother when you keep database code behind small functions instead of scattering SQL through every route. Your API route should not know every detail of connection setup.

export async function createTodo(db, title) {
  const result = await db.query(
    "INSERT INTO todos (title) VALUES ($1) RETURNING id, title, done",
    [title],
  );
 
  return result.rows[0];
}
 
export async function listTodos(db) {
  const result = await db.query("SELECT id, title, done FROM todos ORDER BY id");
  return result.rows;
}

Then a route can stay focused on HTTP:

import { createTodo, listTodos } from "../todos-repository.mjs";
 
app.get("/todos", async (req, res) => {
  res.json(await listTodos(req.db));
});
 
app.post("/todos", async (req, res) => {
  const valid = validateTodoInput(req.body);
 
  if (!valid.ok) {
    res.status(400).json({ error: valid.error });
    return;
  }
 
  const todo = await createTodo(req.db, valid.title);
  res.status(201).json(todo);
});

Real migrations need more than copy-pasting a file from one database to another. Data types differ, connection setup differs, placeholder syntax can differ, and deployed databases need credentials. But the core design transfers: tables, rows, SQL, validation, parameterized queries, and small repository functions.

ORMs and query builders are tools, not magic

Raw SQL is direct and worth learning. Many teams still add a layer above it.

Tools such as Prisma and Drizzle can model your schema in code, generate helpful types or clients, organize migrations, compose queries, and make common CRUD work less repetitive. A query builder can help construct SQL from functions while still producing parameterized queries. An ORM can map rows into application-shaped objects and manage relationships.

The tradeoff is that you are learning a second API on top of SQL. Generated clients need setup. Migrations become part of your workflow. Some queries are clearer in plain SQL than in an abstraction. Performance questions still require understanding what query actually reaches the database. And no ORM removes the need to validate request input or protect secrets.

Use this rule of thumb: learn enough SQL to know what should happen, then choose the lightest database layer that keeps your project readable and your team productive.

Next lesson, the database connection string becomes part of the bigger deployment story: secrets stay out of code and Git, configuration changes per environment, and your Node app needs a place to run.