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
This video illustrates how to use DB Browser for SQLite to build a database for the College Enrollment System:
SQLite with Node.js Worksheets: 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 Connecting Forms to the SQL database.
Download DB Browser for SQLite