Web Programming
Lecture 11

Data persistence in web applications

Josue Obregon

jobregon@seoultech.ac.kr

Seoul National University of Science and Technology
Information Technology Management
Lecture slides index

May 9, 2026

Agenda

The persistence problem
Relational databases and SQL essentials
Foreign keys and JOINs
SQLite with Node.js
Parameter binding for path and query parameters
From in-memory to database-backed models
Practical: persisting the Flights API

Course structure

Roadmaps: Frontend, backend and fullstack

The persistence problem

Persistence is the property of data that survives beyond the lifetime of the process that created it
In-memory data:
- Stored in variables and arrays inside the running application
- Disappears as soon as the server stops or restarts
Persistent data:
- Stored in a non-volatile system (e.g., a database file)
- Remains intact across restarts and accessible from new sessions
Web applications require persistence to be useful:
- Accounts, content, transactions, history must survive between requests

Example: airlines and flights

An airline application manages flight schedules
Each flight has structured fields:
- origin, destination, duration
Users may want to search flights by origin, destination, or duration
This data must persist between sessions and across server restarts

origin	destination	duration
New York	London	415
Shanghai	Paris	760
Istanbul	Tokyo	700
New York	Paris	435
Moscow	Paris	245
Lima	New York	455

Why a database, not an array

Limitations of storing data in arrays:
- Filtering requires manual iteration for every new query
- No protection against concurrent modifications
- No enforcement of data types or required fields
- Data is lost when the server restarts
A database provides:
- A query language to filter, combine, and aggregate data
- Concurrent access by multiple clients
- Constraints and data type enforcement
- Durable storage on disk
A database is a separate, durable system that the backend communicates with through queries

What is a database

An electronically stored, systematic collection of data
Managed by a database management system (DBMS): the software that stores, retrieves, and updates the data
Examples of DBMSs:
- Oracle
- Microsoft SQL Server (powerful) and Microsoft Access (simple)
- PostgreSQL (powerful/complex free open-source database system)
- SQLite (transportable, lightweight free open-source database system)
- MySQL and MariaDB (simple free open-source database system)
- MongoDB and Apache Cassandra (popular NoSQL databases)
Most relational databases share the same query language: SQL

Structured Query Language (SQL)

Domain-specific language for relational databases
Declarative: describes what data is needed, not how to retrieve it
The statements used in this course:
- CREATE TABLE — define the structure of a table
- INSERT INTO — add new rows
- SELECT — retrieve rows
- UPDATE — modify existing rows
- DELETE — remove rows
These five statements cover the operations of a typical web backend

SQLite data types

INTEGER: signed integer, used for numeric ids and counts
REAL: floating-point number
TEXT: text strings (UTF-8 by default)

CREATE TABLE

Defines a new table, its columns, and their constraints

CREATE TABLE flights (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    origin TEXT NOT NULL,
    destination TEXT NOT NULL,
    duration INTEGER NOT NULL
);

Common constraints:
- PRIMARY KEY: uniquely identifies each row in the table
- AUTOINCREMENT: automatically generates the next id value
- NOT NULL: requires the column to have a value on insert

INSERT INTO

Adds a new row to a table

INSERT INTO flights (origin, destination, duration)
VALUES ("New York", "London", 415);

The list of columns specifies which fields to set
Columns omitted are filled with NULL or their AUTOINCREMENT / DEFAULT value
Values must match the column order in the column list

SELECT statement

Retrieves data from a table; result returned as a set of rows

SELECT * FROM flights;

SELECT specific columns

SELECT origin, destination FROM flights;

SELECT with WHERE clause

The WHERE clause filters rows based on column values
Common operators: =, >, >=, <, <=, <> (not equal)

SELECT * FROM flights WHERE id = 3;

WHERE on text columns

SELECT * FROM flights WHERE origin = "New York";

Combining conditions: AND, OR

AND requires all conditions to be true; OR requires at least one

SELECT * FROM flights WHERE duration > 500 AND destination = "Paris";

UPDATE

Modifies existing rows that match the WHERE clause

UPDATE flights
SET duration = 430
WHERE origin = "New York" AND destination = "London";

Without a WHERE clause, all rows would be updated

DELETE

Removes rows that match the WHERE clause

DELETE FROM flights WHERE destination = "Tokyo";

Warning

Without a WHERE clause, the statement deletes every row in the table.

Foreign keys

A foreign key is a column that references the primary key of another table
Establishes a relationship between two tables and enforces consistency
Allows data to be split across tables and combined when needed

id	origin	destination	duration
1	New York	London	415
2	Shanghai	Paris	760
3	Istanbul	Tokyo	700

Adding passenger information

Each flight may have multiple passengers
Passengers are stored in a separate table with a foreign key to flights

id	first	last	flight_id
1	Harry	Potter	1
2	Ron	Weasley	1
3	Hermione	Granger	2
4	Draco	Malfoy	4
5	Luna	Lovegood	6
6	Ginny	Weasley	6

flight_id references flights.id — the flight a passenger belongs to

Combining tables with JOIN

Foreign keys allow data from related tables to be combined in a single query
The JOIN clause matches rows in both tables based on the relationship

SELECT passengers.first, flights.origin, flights.destination
FROM passengers
JOIN flights ON passengers.flight_id = flights.id;

first	origin	destination
Harry	New York	London
Ron	New York	London
Hermione	Shanghai	Paris

Why JOINs matter

Without JOIN: the client would need two separate API calls and combine the results manually
With JOIN: the backend returns combined data in a single response
Reflects how relational data is meant to be used:
- Each table holds one kind of entity
- Relationships are reconstructed at query time, not duplicated in the data
Foundation for endpoints that expose related resources together

Types of JOINs

INNER JOIN — rows that match in both tables
LEFT OUTER JOIN — all rows from the left table, matched where possible
RIGHT OUTER JOIN — all rows from the right table
FULL OUTER JOIN — all rows from both tables

For this course we use INNER JOIN. SQLite does not currently support RIGHT and FULL OUTER JOINs.

SQLite with Node.js

sqlite3 is the official SQLite driver for Node.js, callback-based
sqlite is a thin promise-based wrapper built on top of sqlite3
Using sqlite allows queries to be written with async/await, matching the controller pattern from the previous lecture

Setup

Install both packages:

npm install sqlite sqlite3

Import them in the project:

const sqlite3 = require("sqlite3");
const sqlite = require("sqlite");

Opening a database connection

const sqlite3 = require("sqlite3");
const sqlite = require("sqlite");

const DB_NAME = "airline.db";

async function getDBConnection(dbFileName) {
  const db = await sqlite.open({
    filename: dbFileName,
    driver: sqlite3.Database
  });
  return db;
}

sqlite.open(...) returns a Promise that resolves to the database object
If the file does not exist, SQLite creates it automatically
The same db object is reused for all queries during the application’s lifetime

Querying the database

The db object exposes methods to execute SQL queries:

Method	Use
`db.all(sql, params)`	Returns all matching rows as an array of objects
`db.get(sql, params)`	Returns the first matching row as an object
`db.run(sql, params)`	Executes a statement (INSERT / UPDATE / DELETE) without returning rows

All methods return Promises and must be awaited inside async functions

Initializing the database on startup

A db.js module opens the connection, creates tables if missing, and seeds initial data once

async function initDb() {
  const db = await getDBConnection(DB_NAME);

  await db.exec(`
    CREATE TABLE IF NOT EXISTS flights (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      origin TEXT NOT NULL,
      destination TEXT NOT NULL,
      duration INTEGER NOT NULL
    );
  `);

  const row = await db.get("SELECT COUNT(*) AS count FROM flights");
  if (row.count === 0) {
    await db.run(
      "INSERT INTO flights (origin, destination, duration) VALUES (?, ?, ?)",
      ["New York", "London", 415]
    );
  }

  return db;
}

Reading rows

db.all returns the result as an array of plain JavaScript objects
Column names become object properties

async function listFlights(db) {
  const sql = "SELECT * FROM flights;";
  const flights = await db.all(sql);

  flights.forEach(flight => {
    console.log(`Flight ${flight.id}: ${flight.origin} → ${flight.destination}`);
  });

  return flights;
}

Inserting rows

db.run executes a statement that does not return rows
The result object exposes lastID (the autoincremented id) and changes (the number of rows affected)

async function createFlight(flight) {
  const result = await db.run(
    "INSERT INTO flights (origin, destination, duration) VALUES (?, ?, ?)",
    [flight.origin, flight.destination, flight.duration]
  );
  return { id: result.lastID, ...flight };
}

Deleting rows

Also uses db.run; result.changes indicates whether any row was removed
Useful to detect if the requested resource existed

async function removeFlight(id) {
  const result = await db.run("DELETE FROM flights WHERE id = ?", [id]);
  return result.changes > 0;
}

Path parameters and SQL queries

Path parameters from Express map directly to single-row queries in SQL
A request like GET /flights/3 becomes a SELECT ... WHERE id = ? query

// controller
async function getById(req, res) {
  const id = Number(req.params.id);
  const flight = await model.findById(id);
  if (!flight) return res.status(404).json({ error: "Flight not found" });
  res.status(200).json(flight);
}

// model
async function findById(id) {
  return db.get("SELECT * FROM flights WHERE id = ?", [id]);
}

Query parameters and SQL queries

Query parameters drive filtering of result sets
A request like GET /flights?origin=Seoul becomes SELECT ... WHERE origin = ?

// controller
async function getAll(req, res) {
  const flights = await model.findByFilters(req.query);
  res.status(200).json(flights);
}

// model
async function findByFilters({ origin }) {
  if (origin) {
    return db.all("SELECT * FROM flights WHERE origin = ?", [origin]);
  }
  return db.all("SELECT * FROM flights");
}

Parameter binding

The ? placeholders in the SQL string are filled in by values from the array
The database driver handles escaping and quoting automatically
Two consequences:
- Correctness — special characters in user input do not break the SQL
- Safety — user input cannot alter the structure of the query

// safe — user input is bound as a value
db.all("SELECT * FROM flights WHERE origin = ?", [userInput]);

Important

Never build SQL by concatenating strings with user input. Always use placeholders. The reason this matters will be examined next week.

Optional filters with dynamic WHERE

When multiple query parameters are optional, the SQL must adapt
Build the WHERE clause and the parameter list together

async function findByFilters({ origin, destination }) {
  const conditions = [];
  const params = [];

  if (origin) {
    conditions.push("origin = ?");
    params.push(origin);
  }
  if (destination) {
    conditions.push("destination = ?");
    params.push(destination);
  }

  let sql = "SELECT * FROM flights";
  if (conditions.length > 0) {
    sql += " WHERE " + conditions.join(" AND ");
  }

  return db.all(sql, params);
}

From in-memory to database-backed model

The structure of the application does not change:
- Routes still map URLs to controllers
- Controllers still handle HTTP-level decisions
- Models still expose data access functions
What changes:
- Model functions now query a database instead of an array
- Model functions become asynchronous and return Promises
- Controllers must await model calls

Async model functions

Comparison of in-memory vs database implementation

// before — in-memory model
function findAll() {
  return flights;
}

// after — database-backed model
async function findAll() {
  return db.all("SELECT * FROM flights");
}

Controller code

// controller — must now await the model
async function getAll(req, res) {
  const flights = await model.findAll();
  res.status(200).json(flights);
}

Error handling at the model layer

Database queries can fail (locked file, malformed SQL, constraint violations)
A simple try/catch in the controller is enough for this course

async function getById(req, res) {
  try {
    const flight = await model.findById(Number(req.params.id));
    if (!flight) return res.status(404).json({ error: "Flight not found" });
    res.status(200).json(flight);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Database error" });
  }
}

From in-memory to persistent

Objective:
- Replace the in-memory model from the previous lecture with a database-backed model
Same controller, same routes; only the model and db.js are added
New capabilities:
- Data survives server restarts
- Filtering and lookups happen at the database level
- Related data (passengers per flight) can be combined in a single query

Practical: starting point

The Flights API at the end of the previous lecture:
- Routes, controllers, and an in-memory model
- Endpoints: GET /flights, GET /flights/:id, POST /flights, DELETE /flights/:id
- Swagger UI available at /api-docs
Limitations:
- All data lost on server restart
- No support for related entities (passengers)
- No filtering at the data layer

Refactoring plan

Before

flights-api/
  app.js
  routes/
  controllers/
  models/
    flightsModel.js   (array)

After

flights-api/
  app.js
  db.js
  flights.db
  routes/
  controllers/
  models/
    flightsModel.js   (SQL)
    passengersModel.js
  public/
    index.html
    flight.html
    add.html
    manage.html

Adding `db.js`

Single module that opens the connection, creates the tables on first run, seeds data, and exports a shared db instance

// db.js
const sqlite3 = require("sqlite3");
const sqlite = require("sqlite");

let db;

async function initDb() {
  db = await sqlite.open({ filename: "./flights.db", driver: sqlite3.Database });
  await db.exec(`
    CREATE TABLE IF NOT EXISTS flights (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      origin TEXT NOT NULL,
      destination TEXT NOT NULL,
      duration INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS passengers (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      first TEXT NOT NULL,
      last TEXT NOT NULL,
      flight_id INTEGER NOT NULL,
      FOREIGN KEY (flight_id) REFERENCES flights(id)
    );
  `);
  // seeding omitted for brevity
}

function getDb() { return db; }

module.exports = { initDb, getDb };

Refactoring the model

Model functions become async and use the shared db connection

// models/flightsModel.js
const { getDb } = require("../db");

async function findAll() {
  return getDb().all("SELECT * FROM flights");
}

async function findById(id) {
  return getDb().get("SELECT * FROM flights WHERE id = ?", [id]);
}

async function create({ origin, destination, duration }) {
  const result = await getDb().run(
    "INSERT INTO flights (origin, destination, duration) VALUES (?, ?, ?)",
    [origin, destination, duration]
  );
  return { id: result.lastID, origin, destination, duration };
}

module.exports = { findAll, findById, create };

Adapting the controller

Controllers become async and await model calls
The HTTP-level logic is unchanged

// controllers/flightsController.js
async function getAll(req, res) {
  const flights = await model.findAll();
  res.status(200).json(flights);
}

async function getById(req, res) {
  const id = Number(req.params.id);
  const flight = await model.findById(id);
  if (!flight) return res.status(404).json({ error: "Flight not found" });
  res.status(200).json(flight);
}

Filtering with query parameters

New endpoint behavior: GET /flights?origin=Seoul&destination=Tokyo

// model
async function findByFilters({ origin, destination }) {
  const conditions = [];
  const params = [];
  if (origin)      { conditions.push("origin = ?");      params.push(origin); }
  if (destination) { conditions.push("destination = ?"); params.push(destination); }
  let sql = "SELECT * FROM flights";
  if (conditions.length) sql += " WHERE " + conditions.join(" AND ");
  return getDb().all(sql, params);
}

// controller
async function getAll(req, res) {
  const flights = await model.findByFilters(req.query);
  res.status(200).json(flights);
}

Deleting rows in the model

db.run returns result.changes, the number of rows affected
Use it to distinguish “deleted successfully” from “id did not exist”

// models/flightsModel.js
async function remove(id) {
  const result = await getDb().run(
    "DELETE FROM flights WHERE id = ?",
    [id]
  );
  return result.changes > 0;
}

The controller uses the boolean result to decide between 204 and 404

Adding the passengers JOIN endpoint

New endpoint: GET /flights/:id/passengers — list passengers of a specific flight

// models/passengersModel.js
const { getDb } = require("../db");

async function findByFlightId(flightId) {
  const sql = `
    SELECT passengers.id, passengers.first, passengers.last,
           flights.origin, flights.destination
    FROM passengers
    JOIN flights ON passengers.flight_id = flights.id
    WHERE flights.id = ?
  `;
  return getDb().all(sql, [flightId]);
}

module.exports = { findByFlightId };

Serving static files

A backend can serve plain files (HTML, CSS, JS, images) directly to the browser, in addition to JSON endpoints
Express provides built-in middleware for this:

app.use(express.static("public"));

Behavior:
- Any file inside the public/ folder becomes accessible by its name
- public/index.html → served at /
- public/styles.css → served at /styles.css
- public/manage.html → served at /manage.html
The browser can then load these files with normal URLs and use fetch to call the API endpoints

The frontend pages

Four small HTML pages live in public/, each illustrating one concept from the lecture
/ — Search page
- Query parameters → GET /flights?origin=...&destination=...
/flight.html?id=N — Detail page
- Path parameters + JOIN → GET /flights/:id and GET /flights/:id/passengers
/add.html — Add page
- Request body + validation → POST /flights
/manage.html — Manage page
- List + delete → GET /flights and DELETE /flights/:id
All four pages share the same backend

Testing the application

Postman:
- GET /flights?origin=New York — filtered list
- GET /flights/1 — single flight
- GET /flights/1/passengers — passengers of flight 1
- POST /flights — create a new flight (data survives restart)
Browser:
- Open the search form, submit different filter combinations
- Observe how the URL changes and how the backend responds
Restart the server and verify that data persists

Next week

Authentication, authorization, and security
Protecting endpoints with JWT, hashing passwords, and avoiding common vulnerabilities

Acknowledgements

Some contents of this lecture are partially adapted from:
- Harvard CS50’s Web Programming with Python and JavaScript, licensed under CC BY-NC-SA 4.0.
- Materials from University of Washington’s CSE 154 Web Programming (used with permission).
- The Odin Project (main website code under MIT license and curriculum licensed under a CC BY-NC-SA 4.0)
- The Fundamentals of Web Application Development (Web Edition) ©2025 Nicholas D. Freeman. All rights reserved. The content is provided for educational purposes only and is not an exhaustive treatment of the subjects.
- freeCodeCamp.org © 2025 freeCodeCamp.org. All rights reserved. The content is provided for educational purposes only and is not an exhaustive treatment of the subjects.
- Node.js for Beginners by Ulises Gascón. Some contents adapted under fair educational use for instructional purposes.

Back to title slide Back to lecture slides index

Web Programming Lecture 11

Agenda

Course structure

The persistence problem

Example: airlines and flights

Why a database, not an array

What is a database

Structured Query Language (SQL)

SQLite data types

CREATE TABLE

INSERT INTO

SELECT statement

SELECT specific columns

SELECT with WHERE clause

WHERE on text columns

Combining conditions: AND, OR

UPDATE

DELETE

Foreign keys

Adding passenger information

Combining tables with JOIN

Why JOINs matter

Types of JOINs

SQLite with Node.js

Setup

Opening a database connection

Querying the database

Initializing the database on startup

Reading rows

Inserting rows

Deleting rows

Path parameters and SQL queries

Query parameters and SQL queries

Parameter binding

Optional filters with dynamic WHERE

From in-memory to database-backed model

Async model functions

Error handling at the model layer

From in-memory to persistent

Practical: starting point

Refactoring plan

Adding db.js

Refactoring the model

Adapting the controller

Filtering with query parameters

Deleting rows in the model

Adding the passengers JOIN endpoint

Serving static files

The frontend pages

Testing the application

Next week

Acknowledgements

Web Programming
Lecture 11

Adding `db.js`