⇦ Back

WDX-180

Web Development X

Public Routes & Basic CRUD

Building a Real Database-Driven Application

Yesterday we designed the blueprint.

Today we start pouring concrete.

Up until now, our products have lived inside JavaScript arrays:

  const products = [
    { id: 1, name: 'Keyboard' },
    { id: 2, name: 'Mouse' }
  ];

This works until:

Today we solve that problem.

Learning Objectives

By the end of this lesson, students will be able to:

What We Are Building Today

Our application currently looks like this:

  flowchart LR

  A[Browser]
  B[Express]
  C[JavaScript Array]

  A --> B
  B --> C

By the end of today:

  flowchart LR

  A[Browser]
  B[Express]
  C[SQLite Database]

  A --> B
  B --> C

We’re replacing fake data with real persistence.

Part 1 — What Is SQLite?

SQLite is a relational database engine.

Unlike:

SQLite stores everything in a single file.

Example:

  products.sqlite

That’s the entire database.

No server.

No installation.

No configuration.

No sacrifices to the DevOps gods.

Why SQLite?

For learning and small applications, SQLite is fantastic.

Advantages:

Examples:

Part 2 — Creating Our Database Structure

Make sure you have created the following files:

  ├── db/
      ├── db.js
      ├── setup.js
      └── products.sqlite

At this point, it might be a good idea to install the following VSCode extension for viewing your SQLite database directly through your code editor:

SQLite Viewer

Project Structure

  express-crud/

  ├── db/
  │   ├── db.js
  │   ├── setup.js
  │   └── products.sqlite
  ├── routes/
  ├── views/
  ├── public/
  └── index.js

Database Design

Since we are going to be designing our Database, here are two really interesting videos that walk you through designing a system.

You can learn a lot from this process, such as thinking about the system from a high level and breaking it up in different modules and deciding on the Database entities (tables) and Schema (columns and types).

Enjoy and gain some insights before moving on with creating our products Database.

Design Instagram: 30’ Design Calendar Application: 25’

Part 3 — Database Schema Design

Our Products table:

  CREATE TABLE products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT,
      price REAL NOT NULL,
      created DATETIME DEFAULT CURRENT_TIMESTAMP
  );

Understanding the Schema

Column Type Purpose
id INTEGER Unique ID
name TEXT Product name
description TEXT Product details
price REAL Product price
created DATETIME Creation timestamp

Database Diagram

  erDiagram

  PRODUCTS {
      INTEGER id PK
      TEXT name
      TEXT description
      REAL price
      DATETIME created
  }

Part 4 — Creating the Database

db/setup.js:

  const sqlite = require('node:sqlite');
  const path = require('node:path');

  const dbPath = path.join(__dirname, 'products.sqlite');

  const db = new sqlite.DatabaseSync(dbPath);

  db.exec(`
  CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT,
      price REAL NOT NULL,
      created DATETIME DEFAULT CURRENT_TIMESTAMP
  );
  `);

  console.log('Database created successfully.');

Running Setup

  node db/setup.js

After running:

  products.sqlite

appears inside your db folder.

Congratulations.

You now own a database.

Alternatively, you can provide a nice package.json script for your colleagues. Add the following script in your package.json:

  "scripts": {
    "db:seed": "node db/setup.js"
  }

You can now run npm run db:seed to initialize and seed the database.

TIP: You can use the SQLite Viewer extension to open the products.sqlite file and view the structure of the database

Part 5 — Seeding Sample Data

A database without data is just an expensive empty notebook.

Let’s add products.

db/setup.js:

Extend it:

  db.exec(`
  INSERT INTO products (name, description, price)
  VALUES
  ('Mechanical Keyboard', 'RGB Gaming Keyboard', 89.99),

  ('Wireless Mouse', 'Bluetooth Mouse', 24.99),

  ('27 Inch Monitor', '4K IPS Display', 349.99);
  `);

Verify the Data

Install VSCode SQLite Viewer.

Open:

  products.sqlite

You should see:

id name
1 Mechanical Keyboard
2 Wireless Mouse
3 27 Inch Monitor

Part 6 — Creating a Database Connection Module

Never connect directly inside routes.

Bad:

  router.get('/', () => {

    const db = ...

  });

Very quickly this becomes chaos.

db/db.js

  const sqlite = require('node:sqlite');
  const path = require('node:path');

  const dbPath = path.join(__dirname, 'products.sqlite');

  const db = new sqlite.DatabaseSync(dbPath);

  module.exports = db;

Why Create a Shared Connection?

Benefits:

Professional software engineers avoid duplication whenever possible.

Part 7 — Creating Product Routes

routes/products.js:

  const express = require('express');
  const db = require('../db/db');

  const router = express.Router();

Querying All Products

  router.get('/', (req, res) => {

      const stmt = db.prepare(`
          SELECT *
          FROM products
          ORDER BY id DESC
      `);

      const products = stmt.all();

      res.render('products/list', {
          title: 'Products',
          products
      });

  });

What Happens Here?

  flowchart LR

  A[Browser]

  B[Route]

  C[SQL Query]

  D[(SQLite)]

  E[EJS]

  A --> B
  B --> C
  C --> D
  D --> C
  C --> B
  B --> E
  E --> A

Part 8 — Registering the Router

index.js:

  const productsRouter = require('./routes/products');

  app.use('/products', productsRouter);

Now:

  http://localhost:3000/products

loads products from SQLite.

Not JavaScript arrays.

Not hardcoded values.

Real database records.

Part 9 — Rendering Products

views/products/list.ejs:

  <h2>Products</h2>

  <table>

    <thead>
      <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Price</th>
      </tr>
    </thead>

    <tbody>

      <% products.forEach(product => { %>

        <tr>
          <td><%= product.id %></td>
          <td><%= product.name %></td>
          <td>$<%= product.price %></td>
        </tr>

      <% }) %>

    </tbody>

  </table>

Result

Instead of:

  const products = [...]

we now have:

  SELECT *
  FROM products

Powerful difference.

The data survives application restarts.

Part 10 — Understanding SQL SELECT

Most CRUD applications spend 90% of their time reading data.

Understanding SELECT is critical.

Select Everything

  SELECT *
  FROM products;

Select Specific Columns

  SELECT
      id,
      name
  FROM products;

Sort Results

  SELECT *
  FROM products
  ORDER BY price DESC;

Filter Results

  SELECT *
  FROM products
  WHERE price > 100;

Limit Results

  SELECT *
  FROM products
  LIMIT 10;

Part 11 — Adding Sorting

Let’s allow sorting.

Example:

  /products?sort=name

Route

  router.get('/', (req, res) => {

      const sort = req.query.sort || 'id';

      const allowed = [
          'id',
          'name',
          'price'
      ];

      const column = allowed.includes(sort)
          ? sort
          : 'id';

      const stmt = db.prepare(`
          SELECT *
          FROM products
          ORDER BY ${column}
      `);

      const products = stmt.all();

      res.render('products/list', {
          title: "Products",
          products
      });

  });

Why Validation Matters

Never trust user input.

Bad:

  ORDER BY ${req.query.sort}

Could lead to SQL injection.

Always whitelist allowed values.

Part 12 — Adding Product Count

Example:

  const countStmt = db.prepare(`
      SELECT COUNT(*) AS total
      FROM products
  `);

  const result = countStmt.get();

Result:

  {
    total: 3
  }

Pass the result to the res.render() method as total property:

  res.render('products/list', {
    // ...
    total: result.total,
  });

Display (in list.ejs):

  <p>Total Products: <%= total %></p>

Part 13 — Route Planning for Tomorrow

Today:

  GET /products

Tomorrow:

  GET /products/:id

Future:

  GET /products/create
  POST /products/create

  GET /products/edit/:id
  POST /products/edit/:id

  POST /products/delete/:id

We are gradually building toward full CRUD.

Common Beginner Mistakes

1. Running setup.js Multiple Times

This:

  INSERT ...

runs every time.

You may accidentally create duplicates.

2. Creating Database Connections Everywhere

Bad:

  new Database()
  new Database()
  new Database()

Use one shared connection.

3. Forgetting to Create Views

Error:

  Failed to lookup view

Usually means:

  views/products/list.ejs

does not exist.

4. Using User Input in SQL

Never:

  ORDER BY ${req.query.column}

without validation.

Assignment

Exercise 1

Create:

  products.sqlite

and the Products table.

Exercise 2

Seed at least:

  10 products

with realistic data.

Exercise 3

Create:

  GET /products

that reads all products from SQLite.

Exercise 4

Render the results in an HTML table.

Exercise 5

Add sorting by:

  id
  name
  price

using query parameters.

Bonus Challenge

Create:

  GET /products/expensive

that only shows products costing:

  100+

using:

  WHERE price >= 100

Extra Resources

Since we’ve touched relational databases (the SQL kind), it’s probably a good time now to see some common mistakes beginners make when designing a new database.

Watch the following video to learn more about how to think and what to avoid when designing a SQL database for your project.

7 Database Design Mistakes to Avoid (With Solutions)

Key Takeaways

Today you learned:

⚠️ A large part of the content of this module was created using Generative AI (ChatGPT). The synthetic (AI-generated) content was reviewed and curated by Kostas Minaidis.

Project maintained by in-tech-gration Hosted on GitHub Pages — Theme by mattgraham