Gallery
Share
Explore

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

Class Plan: Day 02 May 18



⁠
The Evolution of Programming Paradigms: From Machine Language to Quantum Computing
coda.io
⁠
⁠

Make a directory focused
Using DB Browser for SQLite to setup the SQL Database for use by our Node.js Code
⁠
https://sqlitebrowser.org/
⁠
⁠
Download SQLite:
https://sqlite.org/index.html
⁠
⁠

This video illustrates how to use DB Browser for SQLite to build a database for the College Enrollment System:
⁠
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
⁠
⁠

SQLite with Node.js Worksheets:
student learning materials
Shared with Dropbox
www.dropbox.com
⁠
⁠

EJS The Templating Engine:

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

Setting up the Web Server:

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

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
the official Node.js website
⁠
.
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 sqlite :
https://www.sqlite.org/download.html
⁠

Download DB Browser for SQLite
https://sqlitebrowser.org/
⁠
⁠
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.