Databases: Store Data Permanently

Before You Study (5 mins)

Lesson focus: Lesson 13 made you the API provider — and the leaderboard worked, until you restarted the server and watched every score disappear. Variables in code are ephemeral; they live in memory and die with the process. Databases are the opposite: data written today survives every restart, every deploy, every server move. Today we add SQLite — a battle-tested relational database that lives in a single file with no installation — to your backend. Your API doesn't change shape; the storage layer slots in underneath.

What you should have ready:

• Lesson 13's backend/ with the in-memory leaderboard API working
• About 60 minutes
• A small irrational fear of databases that you'll lose by lunchtime
• A directory for the database file (./data/) — we'll create it together
• (Optional) A SQLite GUI: DB Browser for SQLite or VS Code's "SQLite" extension

The Concept

A database is software designed for one job: store data and retrieve it efficiently, even when there's a lot of it, even when many things are reading and writing at once, even when the power blinks. Every other property a database has — query language, indexes, transactions, replication — exists to serve that one job better.

There are two big families of databases, and you'll meet both in your career:

Today we use SQLite, the simplest possible relational database. It's not a server — there's no sqlite-server process running. It's just a library you import, plus a single file on disk where the data lives. Both Android and iOS use SQLite for their built-in databases. Firefox and Chrome use SQLite for browser history. Most desktop apps that need persistent local storage use SQLite. It is the most-deployed database engine in the world.

The relational model has three key abstractions:

• Tables — like spreadsheet sheets. A scores table has columns and rows.
• Columns — the fields each row has. id, name, score, created_at.
• Rows — individual records. One row = one score entry.

Every column has a type: INTEGER, TEXT, REAL (decimal number), BLOB (binary data), or NULL. Type discipline is one of the things that makes relational databases reliable; the database refuses to store a string in a column declared as INTEGER.

The language you use to talk to a relational database is SQL (Structured Query Language). It's been the dominant query language since the 1970s and isn't going anywhere. Five statements cover ~95% of what you'll do:

CREATE TABLE scores (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT    NOT NULL,
  score      INTEGER NOT NULL,
  created_at TEXT    NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO scores (name, score) VALUES ('Divyanshu', 1500);

SELECT * FROM scores WHERE score > 1000 ORDER BY score DESC LIMIT 10;

UPDATE scores SET score = 2000 WHERE id = 1;

DELETE FROM scores WHERE id = 1;

Two more concepts that pay back forever:

• Primary key — a column declared as PRIMARY KEY uniquely identifies each row. With AUTOINCREMENT, SQLite assigns 1, 2, 3, ... automatically.
• Indexes — special data structures the database maintains so queries on specific columns stay fast as the table grows. CREATE INDEX idx_score ON scores(score DESC) makes "top 10 scores" stay fast even with a million rows.

The reason relational databases dominated for 50 years is ACID — four properties that guarantee data integrity:

• Atomic — a transaction either fully succeeds or fully fails; no partial writes
• Consistent — the database moves from one valid state to another; constraints are never violated
• Isolated — concurrent transactions don't see each other's intermediate state
• Durable — once committed, data survives a crash or power loss

For a leaderboard you can probably ignore ACID. For a banking system, ACID is the entire reason the system exists.

The library we'll use today is better-sqlite3 — a fast, synchronous Node.js binding for SQLite that's easier to learn than async drivers and good enough for projects under tens of thousands of writes per second. For very high throughput or web-scale apps, you'd graduate to PostgreSQL with an async driver, but the SQL you learn here transfers verbatim.

Quick Concepts

What We Will Build

By the end of this lesson, you will have done these specific things:

1. Installed better-sqlite3 — npm install better-sqlite3 in your backend project. This is a native module; it might compile briefly during install. That's normal.
2. Created a data/ directory for the database file. Added data/ to .gitignore so the file doesn't end up in source control.
3. Initialized the database in a new file backend/db.js:
   javascript

   Copy

   import Database from 'better-sqlite3'
   const db = new Database('./data/scores.db')
   db.pragma('journal_mode = WAL')          // better concurrency
   
   db.exec(`
     CREATE TABLE IF NOT EXISTS scores (
       id         INTEGER PRIMARY KEY AUTOINCREMENT,
       name       TEXT    NOT NULL,
       score      INTEGER NOT NULL CHECK (score >= 0),
       created_at TEXT    NOT NULL DEFAULT (datetime('now'))
     );
     CREATE INDEX IF NOT EXISTS idx_score ON scores(score DESC);
   `)
   
   export default db
   

4. Ran the server once to create ./data/scores.db. Verified the file exists with ls -la data/.
5. Replaced the in-memory array in server.js with prepared SQL statements:
   javascript

   Copy

   import db from './db.js'
   
   const insertScore = db.prepare('INSERT INTO scores (name, score) VALUES (?, ?)')
   const listScores  = db.prepare('SELECT * FROM scores ORDER BY score DESC LIMIT ?')
   const findScore   = db.prepare('SELECT * FROM scores WHERE id = ?')
   const removeScore = db.prepare('DELETE FROM scores WHERE id = ?')
   
   app.get('/api/scores', (req, res) => {
     const limit = Math.min(Number(req.query.limit) || 10, 100)
     const rows = listScores.all(limit)
     res.json({ scores: rows, count: rows.length })
   })
   
   app.post('/api/scores', (req, res) => {
     const { name, score } = req.body
     // ... validation from Lesson 13 ...
     const result = insertScore.run(name.trim(), score)
     const created = findScore.get(result.lastInsertRowid)
     res.status(201).json(created)
   })
   
   app.delete('/api/scores/:id', (req, res) => {
     const result = removeScore.run(Number(req.params.id))
     if (result.changes === 0) return res.status(404).json({ error: 'Not found' })
     res.status(204).send()
   })
   

6. Tested with curl — added a few scores, restarted the server, fetched again. The data survived the restart. Felt the difference.
7. Inspected scores.db with the SQLite CLI:
   bash

   Copy

   sqlite3 ./data/scores.db
   sqlite> .tables                  -- shows all tables
   sqlite> .schema scores            -- shows the CREATE TABLE statement
   sqlite> SELECT * FROM scores;
   sqlite> .quit
   

8. (Bonus) Wrote a query that finds the median score (or the score at position 5 if you have 10 entries). The query language is more capable than it looks.

The "felt the difference" in step 6 is the goal. Once you've experienced data surviving a restart, every project from here onward can have real persistence — and you'll start designing systems differently.

Think About

Before studying, consider:

1. SQL was designed in 1974 by Donald Chamberlin and Raymond Boyce at IBM. It is older than most reading this lesson. Most other 1974 technologies have been replaced. Why has SQL survived? (Hint: a small, declarative vocabulary that maps cleanly to a deeply useful operation — set theory on rows.)
2. What happens to your scores.db file if your laptop's hard drive fails? (It's gone.) What strategies do real applications use to make sure data survives hardware failure? (Backups, replication, cloud-managed databases. Ideas for Module D and beyond.)

By the End

After this lesson, you'll:

• ✅ Have better-sqlite3 installed and a scores.db file on disk
• ✅ Have a db.js module that initializes the schema with IF NOT EXISTS
• ✅ Have replaced the in-memory array in your API with real SQL queries
• ✅ Be able to write the five core SQL statements (CREATE, INSERT, SELECT, UPDATE, DELETE)
• ✅ Have inspected the database from the sqlite3 CLI
• ✅ Have felt the difference — data surviving a server restart
• ✅ Be ready for Lesson 15 — the closing milestone for Module C

Memory is short. Disks are long. 💾

What We Learned

• A database is software for persistent, queryable storage. Variables in code disappear when the process ends; databases don't.
• Two big families dominate: relational (SQL — SQLite, Postgres, MySQL) and document/key-value (Mongo, Redis, DynamoDB). Today's lesson is relational.
• SQLite is a library, not a server. No background process, no connection string, no DBA. The data lives in a single .db file you can copy, version, ship, or back up like any other file.
• The relational model has three abstractions: tables (named collections of rows), columns (typed fields), rows (records). Tables relate to each other through foreign keys (a future lesson).
• SQL is small. Five statements (CREATE TABLE, INSERT, SELECT, UPDATE, DELETE) cover ~95% of daily database work.
• Indexes are the secret to speed. Without an index, "find the top 10 scores" requires scanning every row; with CREATE INDEX idx_score ON scores(score DESC), it's a constant-time operation regardless of table size.
• Prepared statements are how you write SQL safely from code. db.prepare('INSERT ... VALUES (?, ?)') then .run(name, score) — the ? placeholders prevent SQL injection by ensuring the values are escaped, not concatenated as strings.

Commands We Used

# Install the SQLite library for Node
npm install better-sqlite3

# Create a data directory (keep it out of source control)
mkdir -p data
echo "data/" >> .gitignore

# Run your server — db.js auto-creates the schema on first run
npm run dev

# Inspect the database from the SQLite CLI (macOS / Linux ship with sqlite3)
sqlite3 ./data/scores.db

The five SQL statements you'll use 95% of the time:

-- 1. CREATE TABLE — define the shape of your data
CREATE TABLE IF NOT EXISTS scores (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT    NOT NULL,
  score      INTEGER NOT NULL CHECK (score >= 0),
  created_at TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- 2. INSERT — add a new row
INSERT INTO scores (name, score) VALUES ('Divyanshu', 1500);

-- 3. SELECT — read rows (this is where you spend most of your time)
SELECT * FROM scores;
SELECT name, score FROM scores WHERE score > 1000;
SELECT * FROM scores ORDER BY score DESC LIMIT 10;
SELECT COUNT(*) FROM scores;
SELECT name, MAX(score) FROM scores GROUP BY name;

-- 4. UPDATE — modify existing rows
UPDATE scores SET score = 2000 WHERE id = 1;

-- 5. DELETE — remove rows
DELETE FROM scores WHERE id = 1;
DELETE FROM scores WHERE created_at < datetime('now', '-30 days');

The db.js module that initializes everything:

import Database from 'better-sqlite3'

const db = new Database('./data/scores.db')

// WAL mode = Write-Ahead Logging — better concurrent reads while writing.
// Recommended default for any non-trivial use of SQLite.
db.pragma('journal_mode = WAL')

// IF NOT EXISTS makes this idempotent — safe to run on every server start.
db.exec(`
  CREATE TABLE IF NOT EXISTS scores (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL,
    score      INTEGER NOT NULL CHECK (score >= 0),
    created_at TEXT    NOT NULL DEFAULT (datetime('now'))
  );
  CREATE INDEX IF NOT EXISTS idx_score ON scores(score DESC);
`)

export default db

Useful SQLite CLI commands:

sqlite> .tables                              -- list all tables
sqlite> .schema scores                        -- show the CREATE TABLE for one table
sqlite> .mode column                          -- prettier output
sqlite> .headers on                           -- show column names in SELECT output
sqlite> SELECT * FROM scores LIMIT 5;
sqlite> .quit

Why It Matters

Databases are the part of every application that survives. Code can be rewritten; servers can be redeployed; design systems can be refactored. The data — your users, their work, their history — is what gives the system its value, and the database is where it lives. Senior engineers spend disproportionate care on database design, schema migrations, and query performance because the database is the asset; everything else is the wrapping.

The deeper insight: schema is a contract. The shape of your scores table — its columns, types, constraints — declares what valid data looks like. The database refuses anything that violates the contract. This is a different discipline from code-level validation: the database is the last line of defense against bad data, and unlike code-level checks, you cannot accidentally bypass it. A score < 0 cannot enter the table because the CHECK (score >= 0) constraint refuses it, regardless of what your application code did.

What this lesson glossed over but you'll meet:

• Foreign keys and joins — relating tables to each other (users ← orders ← order_items). The relational model's superpower; we'll cover it in a future lesson.
• Migrations — schemas evolve over time. Adding a column to a table that already has data requires a migration tool. node-sqlite-migrations, Knex, or hand-rolled SQL migrations are common.
• Transactions — bundling multiple SQL statements so they either all succeed or all fail. db.transaction(() => { ... })() in better-sqlite3. Critical for any operation involving more than one row.
• PostgreSQL — SQLite's spiritual descendant for serious server-side work. Same SQL, much higher concurrency, runs as a separate process. The graduation path when SQLite stops scaling.
• ORMs — Prisma, Drizzle, Sequelize. Code-level abstractions that generate SQL for you. Useful for productivity once you understand the underlying SQL — not a replacement for understanding it.

The companion article for full data-format context is What is JSON and Why Does Every Application Use It. JSON is the wire format between systems; SQL is the storage format inside one system. Both matter; learn the boundary between them.

Checklist

• I installed better-sqlite3 and created db.js
• My scores.db file exists in the data/ directory and is gitignored
• My API now uses prepared SQL statements instead of an in-memory array
• I added a score, restarted the server, and the score was still there
• I opened sqlite3 ./data/scores.db and ran SELECT * FROM scores; from the CLI
• I can write the 5 core SQL statements (CREATE TABLE / INSERT / SELECT / UPDATE / DELETE) from memory
• I added an INDEX on the score column for fast leaderboard queries
• I understand why ? placeholders are used in prepared statements (SQL injection prevention)

Quick Quiz

Q1: What happens to data in a JavaScript variable when you restart the server?

• A) It is automatically backed up
• B) It is lost — variables live in process memory only ✓
• C) It is moved to disk

Q2: Which SQL command adds a new row?

• A) ADD
• B) INSERT ✓
• C) PUT

Q3: What does an INDEX do?

• A) Numbers your rows
• B) Maintains a data structure that makes queries on specific columns fast as the table grows ✓
• C) Encrypts the column

Q4: Why use ? placeholders in prepared statements?

• A) They make the code look prettier
• B) They prevent SQL injection by escaping values rather than concatenating strings ✓
• C) They are faster to type

Q5: What's special about SQLite compared to PostgreSQL or MySQL?

• A) SQLite is a library that uses a single file — no server process to run ✓
• B) SQLite is faster on web-scale workloads
• C) SQLite supports more data types

Bonus Challenge

Add a country column to your scores table. The challenge: the table already has rows. You can't just edit the schema — you need a migration:

-- Step 1: add the column with a default value (so existing rows don't break)
ALTER TABLE scores ADD COLUMN country TEXT NOT NULL DEFAULT 'IN';

-- Step 2: from your API, accept country as input on POST /api/scores
-- Step 3: GET /api/scores can now filter by country: ?country=IN

Wire the new column through your API. Two new outcomes:

1. You experience the small but important pain of evolving a schema without losing data — the discipline of migrations.
2. Your leaderboard becomes a tiny bit more interesting because it can now show "top scores from India," "top scores from anywhere."

Next Lesson

Lesson 15: MILESTONE — Online Leaderboard Works

You'll learn: Module C closes with the same kind of verification ritual as Module B. We compose Frontend + API + Database + Cloudflare Tunnel into one running system, send a score from a phone, watch it appear on a leaderboard a friend can also see, deploy the updated container, and write the second MILESTONE.md of the course.

Disks remember what memory forgets. 🔐