Explore

S23 CSD 3103 S3 TUE S2 THU Fullstack JavaScript Lecture and Lab Workbooks
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:

bashCopy codemkdir sqlite-nodejs-labcd sqlite-nodejs-lab

Initialize a new Node.js project by running the following command:

bashCopy codenpm 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:

bashCopy codenpm 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:

javascriptCopy codeconst 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:

javascriptCopy codeconst 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:

javascriptCopy codedb.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:

javascriptCopy codedb.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:

javascriptCopy codedb.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:

javascriptCopy codedb.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