Hack University
Course outline · 0% complete

0/29 lessons0%

Course overview →

Talking to SQL from Node

lesson 6-3 · ~13 min · 19/29

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 example below uses SQLite, a real SQL database small enough to embed anywhere (it likely runs inside your phone right now). 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.

One table, two rows, one query

Three SQL statements in sequence. The SELECT at the end prints matching rows as column values separated by |.

The column constraints carry real meaning: NOT NULL refuses a row with a missing name, UNIQUE refuses a second row with the same email, and INTEGER PRIMARY KEY fills id in automatically. Those are the same rules your repository enforced by hand in lesson 6-2, except now the database enforces them for every caller, not just the one code path that remembered to check.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
INSERT INTO users (name, email) VALUES ('Grace', 'grace@example.com');

SELECT id, name FROM users WHERE email = 'grace@example.com';

Output

2|Grace

Notes

  • Grace was the second insert, so her auto-assigned id is 2. Neither INSERT mentions id at all.
  • The WHERE clause matched on email, but the SELECT list asked for id and name, so those are the two values printed. What you filter on and what you return are independent choices.

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.

Why placeholders defeat injection

Placeholders work because the driver sends the query structure and the values separately, so a value can never be mistaken for SQL code.

The database receives the query shape, SELECT ... WHERE email = ?, and the value as two distinct things. The value is only ever treated as data, so '; DROP TABLE users or ' OR '1'='1 becomes a weird string to search for and match against, not code to run. Gluing strings erases that boundary, because by the time the database sees the query the value has already become part of the SQL text.

Three wrong explanations worth ruling out:

  • Placeholders do not encrypt anything. The query still travels as readable SQL, and encryption would not help, since an injected value would simply be decrypted along with the rest.
  • They do not strip quote characters. An email containing an apostrophe is stored intact, which is the point: escaping and stripping are lossy guesswork, while separating structure from data is exact.
  • They have nothing to do with how many rows come back. db.all with a placeholder can still return thousands.

Filtering and sorting in one query

A posts table with three rows, and one SELECT that returns the title and likes of every post by 'ada', highest likes first.

This is WHERE and ORDER BY doing separate jobs in the same statement. WHERE decides which rows come back, and ORDER BY decides what order they arrive in. Grace's post has the most likes of any row in the table and never appears, because filtering happens before sorting.

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author TEXT NOT NULL,
  title TEXT NOT NULL,
  likes INTEGER DEFAULT 0
);

INSERT INTO posts (author, title, likes) VALUES ('ada', 'Why engines matter', 12);
INSERT INTO posts (author, title, likes) VALUES ('grace', 'Compilers 101', 30);
INSERT INTO posts (author, title, likes) VALUES ('ada', 'Notes on machines', 5);

SELECT title, likes FROM posts WHERE author = 'ada' ORDER BY likes DESC;

Output

Why engines matter|12
Notes on machines|5

SQL strings use single quotes, so it is WHERE author = 'ada'. Double quotes mean something different in SQL, where they name a column or table, which is a confusing first error to hit coming from JavaScript.

ORDER BY likes DESC sorts highest first, and without DESC the default is ascending, which would put the 5-like post on top. Leaving the sort out entirely gives no guaranteed order at all, not even insertion order.

Doing the sort in SQL rather than in JavaScript matters at scale. The database can use an index and can return only the rows you want, instead of shipping every row to Node to be sorted in memory.

The likes INTEGER DEFAULT 0 column shows why defaults are worth declaring. An insert that omits likes gets 0 rather than NULL, and NULL would poison later arithmetic and sort in a position most people do not expect.

From Node this becomes db.all("SELECT title, likes FROM posts WHERE author = ? ORDER BY likes DESC", [author], cb). The ORDER BY column is fixed SQL text and only the value gets a placeholder, which is a limitation worth knowing, since a client-chosen sort column has to be validated against an allowed list rather than passed through.

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.

An update and a delete, then the proof

Post 1 earned a like and post 3 has to go. One UPDATE raises likes by one for id 1, one DELETE removes id 3, and the closing SELECT shows the table afterwards with exactly two rows.

The UPDATE reads SET likes = likes + 1 rather than SET likes = 13. Letting the database compute from the current value avoids a read-then-write race, where two requests both read 12 and both write 13, losing a like.

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  author TEXT NOT NULL,
  title TEXT NOT NULL,
  likes INTEGER DEFAULT 0
);

INSERT INTO posts (author, title, likes) VALUES ('ada', 'Why engines matter', 12);
INSERT INTO posts (author, title, likes) VALUES ('grace', 'Compilers 101', 30);
INSERT INTO posts (author, title, likes) VALUES ('ada', 'Notes on machines', 5);

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

SELECT id, title, likes FROM posts ORDER BY id;

Output

1|Why engines matter|13
2|Compilers 101|30

Both statements absolutely need their WHERE. UPDATE posts SET likes = likes + 1 with no WHERE likes every post in the table, and DELETE FROM posts empties it, and neither asks for confirmation.

That in-database increment is the same lesson as the file race from 6-2, solved properly. The read and the write happen inside one statement, so no other request can slip between them.

The closing SELECT is the habit worth copying. After any write, read the rows back and confirm the change is the one you meant, which catches a wrong WHERE while it is still one row instead of a whole table.

ORDER BY id on that final SELECT is what makes the output predictable. Without it the engine may return rows in any order it finds convenient, and relying on the order it happens to pick today is a test that breaks for no visible reason later.

Note that a Node driver reports how many rows a write touched, through this.changes with db.run. That number is what a DELETE handler uses to choose between 204 and 404, exactly as the repository's boolean did in lesson 6-2.