Talking to SQL from Node

Tables, rows, and SQL

A relational database stores data in tables: named grids where each row is a record and each column has a name and type. You talk to it in SQL, a small language of statements:

• CREATE TABLE defines a table and its columns.
• INSERT INTO adds rows.
• SELECT ... FROM ... WHERE ... asks questions.

The editor below runs SQLite, a real SQL database small enough to embed anywhere (it likely runs inside your phone right now). Run the example: it builds a users table, inserts two rows, and queries one back. INTEGER PRIMARY KEY makes id auto-fill with 1, 2, 3..., the same job your repository's nextId did in lesson 6-2.

From Node, with callbacks

Node talks to SQLite through a driver package such as sqlite3. The API is the error-first callback style from lesson 2-3:

const sqlite3 = require("sqlite3");
const db = new sqlite3.Database("app.db");

db.all("SELECT id, name FROM users WHERE email = ?", [email], (err, rows) => {
  if (err) return console.error(err);
  console.log(rows); // [{ id: 2, name: "Grace" }]
});

The ? is a placeholder: the driver inserts email safely. NEVER build SQL by gluing strings:

// NEVER DO THIS
db.all("SELECT * FROM users WHERE email = '" + email + "'");

If a user submits the email ' OR '1'='1, that glued query returns every user in the table. This attack, SQL injection, has breached thousands of real companies. Placeholders make it impossible, so they are not optional.

Changing and deleting rows

Your API's PUT and DELETE endpoints map onto two more SQL statements:

UPDATE posts SET likes = likes + 1 WHERE id = 1;
DELETE FROM posts WHERE id = 3;

Both act on every row the WHERE clause matches, and that is the sharp edge: UPDATE posts SET likes = 0 with no WHERE resets the entire table, and a bare DELETE FROM posts empties it, instantly and silently. Forgotten-WHERE accidents are common enough in industry that many teams require a SELECT with the same WHERE first, to preview exactly which rows are about to change. Build that habit now: write the WHERE before the SET.

From Node these run through the same placeholder mechanism as queries: db.run("UPDATE posts SET likes = ? WHERE id = ?", [13, 1], callback), keeping SQL injection impossible on writes too.