Share
Explore

Lab Workbook: Setting Up a Node.js Express Server with SQLite and Form Handling

#JS_Front_End

Learning outcomes:

Setup a NODE.js Express Server to present a FORM from which the user selects a product category from a dropdown list and in response, a sqlite database query returns a list of items available for sale in that category. These items are rendered into html and returned in the response to the user.

Prerequisites:

Introduction

In this lab workbook, you will learn how to set up a Node.js Express server to present a form with a dropdown list of product categories.
Upon selecting a category, a SQLite database query will return a list of items available for sale in that category.
The server will render these items into HTML and send the response back to the user.

Prerequisites

Basic knowledge of JavaScript, HTML, CSS. Basic knowledge of SQL and relational databases.
Node.js and npm installed on your machine
Note: In this Lab, you will use DBBrowser for SQLite to create the SQLite Database that you code will interact with:

Lab Outline

Setting up the project
Creating the database
Building the Express server
Creating the form with a dropdown list
Handling form submissions and querying the database
Rendering the results in HTML
Testing and wrapping up

1. Setting up the project

Create a new directory for the project:
mkdir node-express-sqlite-form
cd node-express-sqlite-form
Initialize the project with npm:
npm init -y
Install the required dependencies:
Observe here that we are using a templating library called ejs
npm install express sqlite3 ejs

2. Creating the database

Create a new file named database.js in the project directory:

touch database.js
In database.js, set up a SQLite database and create a table with sample data:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
db.run('CREATE TABLE products (id INTEGER PRIMARY KEY, category TEXT, name TEXT, price REAL)');

const sampleData = [
['Electronics', 'Smartphone', 599.99],
['Electronics', 'Tablet', 399.99],
['Clothing', 'T-shirt', 19.99],
['Clothing', 'Jeans', 49.99],
['Books', 'Thriller Novel', 9.99]
];

const stmt = db.prepare('INSERT INTO products (category, name, price) VALUES (?, ?, ?)');
sampleData.forEach((row) => stmt.run(row));
stmt.finalize();
});

module.exports = db;

3. Building the Express server

Create a new file named server.js in the project directory:
touch server.js
In server.js, set up the Express server:
const express = require('express');
const db = require('./database');
const app = express();
const port = 3000;

app.set('view engine', 'ejs');
app.use(express.urlencoded({ extended: false }));

// Routes will be added here

app.listen(port, () => {
console.log(`Server listening on port ${port}`);
});

4. Creating the form with a dropdown list

Create a new directory named views and a new file named index.ejs inside it:
mkdir views
touch views/index.ejs
In index.ejs, create the form with a dropdown list of product categories:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Select a Product Category</title>
</head>
<body>
<h1>Select a Product Category</h1>
<form action="/products" method="post">
<label for="category">Category:</label>
<select name="category" id="category">
<option value="Electronics">Electronics</option>
<option value="Clothing">Clothing</option>
<option value="Books">Books</option>
</select>
<button type="submit">Submit</button>
</form>
</body>
</html>
In server.js, add a route to render the form:
app.get('/', (req, res) => {
res.render('index');
});

5. Handling form submissions and querying the database

In server.js, add a route to handle form submissions and query the database:
app.post('/products', (req, res) => {
const { category } = req.body;
const query = 'SELECT * FROM products WHERE category = ?';
db.all(query, [category], (err, rows) => {
if (err) {
console.error(err);
return res.status(500).send('An error occurred while fetching data from the database');
}
res.render('products', { products: rows });
});
});

6. Rendering the results in HTML

Create a new file named products.ejs inside the views directory:
touch views/products.ejs
In products.ejs, display the list of products:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Products</title>
</head>
<body>
<h1>Products in <%= products[0].category %></h1>
<ul>
<% products.forEach((product) => { %>
<li><%= product.name %> - $<%= product.price.toFixed(2) %></li>
<% }) %>
</ul>
<a href="/">Back to Category Selection</a>
</body>
</html>

7. Testing and wrapping up

Start the server by running the following command:
node server.js
Open your browser and navigate to http://localhost:3000. Select a product category from the dropdown list and submit the form. You should see a list of products in the selected category.
Congratulations! You have successfully set up a Node.js Express server with SQLite and form handling.
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.