Databases

Store data permanently with SQL: create tables, insert, query and update, using Node's built-in SQLite.

Lesson 10 of 110% of the track complete

What you'll learn

What a database is, the four basic SQL commands, and how to run them from Node using the built-in node:sqlite module, including safe parameterised queries.

Why it matters

Data kept in a JavaScript array disappears when the server restarts. A database stores it on disk, lets many users share it, and can find things fast. Almost every real app uses one.

Explanation

A relational database stores data in tables made of rows and columns, like a spreadsheet with strict rules. You talk to it in SQL:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER): define a table.
INSERT INTO users (name, age) VALUES ('Asha', 30): add a row.
SELECT * FROM users WHERE age > 25 ORDER BY name: read rows.
UPDATE users SET age = 31 WHERE name = 'Asha': change rows.
DELETE FROM users WHERE id = 2: remove rows.

SQLite is a database in a single file, with no server to install. Node 22.5 and newer include it as node:sqlite. For other databases (PostgreSQL, MySQL) the SQL is the same, but you use a driver package and a connection string.

Never build SQL by gluing strings together with user input. That allows SQL injection, where an attacker's text becomes commands. Use parameters (? placeholders) instead, so values are always treated as data.

Example

Step-by-step walkthrough

:memory: makes a database that lives only while the program runs. Use a file name like app.db to keep data.
db.exec runs the CREATE TABLE statement.
db.prepare(sql) compiles a statement with ? placeholders. .run(...) fills them in and executes. All three inserts reuse the same statement.
.all() returns every matching row as an array of objects: 1 Asha 30, 2 Ravi 25, 3 Zoya 35.
The WHERE age > ? query with 26 returns the names Asha and Zoya.
We update Ravi's age and delete Zoya, so the final COUNT(*) is 2.

Try it yourself

Run it on your computer. Then change ":memory:" to "app.db", run twice, and notice the UNIQUE-free table gets duplicates unless you add CREATE TABLE IF NOT EXISTS.

db.mjsNode.js

Ctrl+Enter to run

Common mistakes

Building SQL with string concatenation, which allows SQL injection.
Forgetting WHERE on UPDATE or DELETE, which changes every row.
Using :memory: and expecting the data to survive a restart.
Selecting * everywhere. Ask for only the columns you need.

Quick quiz

Question 1 of 3

Which SQL command reads rows?

SELECTINSERTCREATE

Question 2 of 3

Why use ? placeholders?

They prevent SQL injection by treating values as dataThey make queries run fasterThey are required for SELECT

Question 3 of 3

What happens if you run DELETE FROM users with no WHERE?

All rows are deletedNothingOne row is deleted

Summary

Databases store data in tables; SQL has CREATE, INSERT, SELECT, UPDATE, DELETE.
Use node:sqlite with prepare, run, all and get.
Always use parameters, never string concatenation.

Next: logging users in securely.

JavaScript

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");   // a temporary database in memory

db.exec(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
  )
`);

const insert = db.prepare("INSERT INTO users (name, age) VALUES (?, ?)");
insert.run("Asha", 30);
insert.run("Ravi", 25);
insert.run("Zoya", 35);

const all = db.prepare("SELECT id, name, age FROM users ORDER BY id").all();
console.log(all.map((u) => `${u.id} ${u.name} ${u.age}`));

const older = db.prepare("SELECT name FROM users WHERE age > ? ORDER BY name").all(26);
console.log(older.map((u) => u.name));

db.prepare("UPDATE users SET age = ? WHERE name = ?").run(26, "Ravi");
db.prepare("DELETE FROM users WHERE name = ?").run("Zoya");

const row = db.prepare("SELECT COUNT(*) AS total FROM users").get();
console.log("total:", row.total);