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:

⁠
Lecture note on Node.js NPM ejs with code examples
coda.io
⁠
⁠

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:
⁠
Creating the Database with SQLite Database Browser
Title: Constructing Your First SQL Database: College Enrollment System Tutorial using SQLite & DB Browser Description: 🔍 Learn SQL the Easy Way! Dive into this beginner-friendly tutorial and set up your very first SQL database using SQLite & DB Browser. Ideal for students and new learners. ⭐ What You'll Learn: Basics of SQL databases. Setting up SQLite & DB Browser for SQLite. Creating a simple college enrollment system from scratch. 📚 Perfect for: Students looking to get a grasp on databases. Beginners wanting a straightforward guide to SQL. Anyone wanting to learn about database management without the hassle. 🔑 Keywords: SQL for beginners SQLite tutorial DB Browser guide College Enrollment System Database setup Easy SQL guide Students SQL introduction 🚀 Boost your IT skills, understand database structures, and add a valuable skill to your resume. Whether you're studying IT, looking to shift careers, or just curious about databases, this tutorial is your gateway to understanding SQL databases effectively. 🔔 Don't forget to LIKE, SHARE, and SUBSCRIBE for more easy-to-follow tech guides! 💬 Have questions or got stuck? Drop a comment below, and our community or I will be happy to assist. 🔗 Resources: Download SQLite Get DB Browser for SQLite Join the journey to demystifying databases. Happy Learning! 🎓
youtu.be
⁠
⁠

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-formcd 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 viewstouch 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.