Explore

Using SQLite in the Node.js Full Stack Web Application

What is SQLite?

It is not a database server. It is a File Format.

You access this file with program code. This file format is like a room devider with many structured spaces to organize your data.

⁠

How to make the College Enrollment System using DBBrowser for SQLite:

Output of this step is our CES file.

Instructor’s sqlite File for the College enrollment system is available here:

⁠

https://www.dropbox.com/scl/fi/8br0nxx9tx5lkrf6frqao/ces.db?rlkey=y8p8nm14rwujhu4340q01rrha&dl=0⁠

Let’s get started:

Let’s download a front end sql client for dbbrowser for sqlite.

https://sqlitebrowser.org/dl/⁠

⁠

What we will build will our SQLite database using DB Browser for SQLite:

The college enrollment system data model (excel sheet) available here:

https://www.dropbox.com/s/i5xbgs7pld6zx0t/ces%20data%20plan.xlsx?dl=0⁠

⁠

Now that you have seen how to front end a sqlite database with a client like dbbrowser for SQLite, let’s build our own SQL client with Node.js.

Start by studying the

NPMJS.com⁠

package for sqlite:

⁠

Now make the College Enrollment System with Node.js - or make an example which is specific to your course project:

Node.js SQLite: Student Lab Workbook

⁠

Introduction:

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.

In this lab, you'll learn how to integrate SQLite with Node.js to support your web applications.

Provide the backend of the Model View Controller.

By the end of this lab, you'll be able to set up, query, and manage an SQLite database using Node.js.

⁠

Prerequisites:

First steps: (1) Make a node.js Project.

Ensure you have Node.js installed. You'll also need the sqlite3 npm package. Install it using:

bashCopy code

npm install sqlite3

⁠

Lab Exercise 1: Setting Up an SQLite Database

Objective: Learn how to create and set up an SQLite database using Node.js.

Task: Create a new file named setupDatabase.js.

In this file, initialize an SQLite database and create a table named students.

const sqlite3 = require('sqlite3').verbose();

// Open a database handle

const db = new sqlite3.Database('./school.db');

db.serialize(() => {

// Create a new table

db.run("CREATE TABLE students (id INT, name TEXT)");

console.log("Table created successfully!");

});

db.close();

Run the setupDatabase.js file:

bashCopy code

node setupDatabase.js

Expected Output:

cssCopy code

Table created successfully!

Explanation: This exercise introduced the basics of setting up an SQLite database and creating a table. The serialize method ensures that database operations are executed sequentially.

⁠

Lab Exercise 2: Inserting and Retrieving Data

Objective: Learn how to insert data into the SQLite database and retrieve it.

Task: Create a new file named manageData.js.

In this file, insert some student data into the students table and then retrieve it.

const sqlite3 = require('sqlite3').verbose();

const db = new sqlite3.Database('./school.db');

// Insert data

db.serialize(() => {

const stmt = db.prepare("INSERT INTO students VALUES (?, ?)");

stmt.run(1, 'John Doe');

stmt.run(2, 'Jane Smith');

stmt.finalize();

// Retrieve data

db.each("SELECT id, name FROM students", (err, row) => {

console.log(`ID: ${row.id}, Name: ${row.name}`);

});

db.close();

Run the manageData.js file:

bashCopy code

node manageData.js

Expected Output:

yamlCopy code

ID: 1, Name: John Doe

ID: 2, Name: Jane Smith

Explanation: This exercise demonstrated how to insert data into an SQLite table and then retrieve it. Prepared statements (like stmt above) help prevent SQL injection attacks.

⁠

Lab Exercise 3: Updating and Deleting Data

Objective: Learn how to update and delete data in the SQLite database.

Task: Modify the manageData.js file to update a student's name and then delete a student record.

javascriptCopy code

// ... [previous code]

// Update data

db.serialize(() => {

db.run("UPDATE students SET name = ? WHERE id = ?", ['Johnathan Doe', 1]);

// Delete data

db.run("DELETE FROM students WHERE id = ?", [2]);

// Retrieve data to verify changes

db.each("SELECT id, name FROM students", (err, row) => {

console.log(`ID: ${row.id}, Name: ${row.name}`);

});

// ... [closing the database connection]

Run the manageData.js file again:

bashCopy code

node manageData.js

Expected Output:

yamlCopy code

ID: 1, Name: Johnathan Doe

Explanation: This exercise showed how to update and delete records in an SQLite table. Notice how Jane Smith's record is no longer present after the deletion.

⁠

Conclusion:

Well done on completing the lab exercises! You've now gained hands-on experience with SQLite in a Node.js environment. This foundational knowledge will be crucial as you develop web applications that require a lightweight, serverless, and self-contained database solution. Remember, always ensure your database operations are safe and free from vulnerabilities. Happy coding!

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.