Share
Explore

S23 CSD 3103 S3 TUE S2 THU Fullstack JavaScript Lecture and Lab Workbooks

Class Plan: Day 02 May 18




Make a directory focused
Using DB Browser for SQLite to setup the SQL Database for use by our Node.js Code
Download SQLite:

This video illustrates how to use DB Browser for SQLite to build a database for the College Enrollment System:

EJS The Templating Engine:


Setting up the Web Server:


Student Lab Book: Connecting SQLite Database to Node.js Code

Introduction

In this lab book, you will learn how to connect an SQLite database to a Node.js application.
SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It is an excellent choice for small-scale applications, such as web applications, embedded systems, and mobile apps.

We will cover the following steps:

Setting up the environment
Installing SQLite and required packages
Creating a database and table
Connecting to the database
Performing CRUD operations (Create, Read, Update, Delete)

1. Setting up the Environment

Before starting, make sure you have Node.js installed on your computer. If not, download and install it from .
Create a new folder for your project and navigate to it in the terminal:
bash
Copy code
mkdir sqlite-nodejs-lab
cd sqlite-nodejs-lab
Initialize a new Node.js project by running the following command:
bash
Copy code
npm init -y
This command will create a package.json file in your project folder.

2. Installing SQLite and Required Packages

In this step, we will install the sqlite3 package, which is a Node.js module for working with SQLite databases. Run the following command to install the package:
bash
Copy code
npm install sqlite3

3. Creating a Database and Table

Create a new file named database.js in your project folder and add the following code to create a new SQLite database and table:
javascript
Copy code
const sqlite3 = require('sqlite3').verbose();

const db = new sqlite3.Database(':memory:', (err) => {
if (err) {
return console.error(err.message);
}
console.log('Connected to the in-memory SQLite database.');
});

db.serialize(() => {
db.run('CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL);', (err) => {
if (err) {
console.error(err.message);
}
console.log('Students table created.');
});
});

module.exports = db;
This code creates an in-memory SQLite database and a students table with three columns: id, name, and age.

4. Connecting to the Database

Now, let's create a new file named app.js in your project folder and add the following code to connect to the SQLite database:
javascript
Copy code
const db = require('./database');

db.serialize(() => {
// Your CRUD operations will go here
});

db.close((err) => {
if (err) {
return console.error(err.message);
}
console.log('Closed the database connection.');
});
This code imports the database.js file and establishes a connection to the SQLite database.

5. Performing CRUD Operations

In this section, we will perform basic CRUD operations on the students table:

5.1. Create (Insert)

Add the following code to the app.js file to insert a new student into the students table:
javascript
Copy code
db.run('INSERT INTO students (name, age) VALUES (?, ?)', ['John Doe', 25], function (err) {
if (err) {
return console.error(err.message);
}
console.log(`Inserted student with id ${this.lastID}`);
});

5.2.Read (Select)

Add the following code to the app.js file to retrieve all students from the students table:
javascript
Copy code
db.all('SELECT * FROM students', [], (err, rows) => {
if (err) {
return console.error(err.message);
}
rows.forEach((row) => {
console.log(row.id, row.name, row.age);
});
});
This code uses the db.all() method to retrieve all rows from the students table and logs them to the console.

5.3. Update

Add the following code to the app.js file to update a student's age in the students table:
javascript
Copy code
db.run('UPDATE students SET age = ? WHERE name = ?', [30, 'John Doe'], function (err) {
if (err) {
return console.error(err.message);
}
console.log(`Updated ${this.changes} row(s).`);
});
This code uses the db.run() method with an SQL UPDATE statement to update the age of the student named "John Doe" to 30.

5.4. Delete

Add the following code to the app.js file to delete a student from the students table:
javascript
Copy code
db.run('DELETE FROM students WHERE name = ?', ['John Doe'], function (err) {
if (err) {
return console.error(err.message);
}
console.log(`Deleted ${this.changes} row(s).`);
});
This code uses the db.run() method with an SQL DELETE statement to delete the student named "John Doe" from the students table.

Conclusion

Congratulations! You have successfully connected an SQLite database to a Node.js application and performed basic CRUD operations on it. SQLite is an excellent choice for small-scale applications, and Node.js provides a convenient way to work with it. Keep exploring and building more complex applications with these technologies!

Class Plan: Day 01 May 10

Hour 1
Course Introduction
Review Time Table and Course Outline
Set up our Tooling: Visual Studio code

Hour 2: Exercises

Class Plan: Day 02 May 16

Building the Full Stack Web Applications

Building the Database with SQLite and DBBrowser for SQLite
Access the Database with Node.js SQLITE
Building the Web Server with Express.js
Adding in EJS
Connecting Forms to the SQL database.


Download DB Browser for SQLite
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.