Lab-6-1

(5% of the course mark)

SQLLite and Jest

• This lab is designed to provide hands-on experience with using SQLite as a lightweight database and Jest for testing. Students will interact with an SQLite database and then write tests using Jest to validate database operations. This lab will reinforce the fundamentals of database manipulation and testing, essential for building reliable and maintainable applications.

Lab objectives

• Set up and connect to an SQLite database in a Node.js environment.

• Create tables and manipulate data within SQLite using SQL queries.

• Write and run Jest test cases to validate database CRUD operations.

• Understand the importance of unit and integration testing for data integrity and reliability in applications.

• Use mock data and mock functions in Jest to isolate and test database logic.

SQLLite and Jest setup

1. Download and extract the file Lab-6-1.zip.

2. Open Visual Studio Code and open the folder of the directory where the folders and files were extracted.

3. Right click on SQLLite-Sample folder and click on New File… and name it: index.ts.

4. Copy the code below:

// Developer: Roderick Bernardo
// Purpose: Database operations using SQLLite

import * as dotenv from "dotenv";
dotenv.config();
import { SQLLiteDB } from "./lib/SQLLiteDB";

export async function main() {
  console.time("Benchmark");
  const sqlLiteDB: SQLLiteDB = new SQLLiteDB();
  const openResponse: any = await sqlLiteDB.open();
  console.log(openResponse);

  console.log("=========================");
  console.log("= Select all records... =");
  console.log("=========================");

  // Get all the users by using SQL SELECT statement
  const getMultiResponse: any = await sqlLiteDB.all("SELECT * FROM USERS");
  console.log(getMultiResponse);

  // Get a user by using SQL SELECT statement
  const getSingleResponse = await sqlLiteDB.all(
    "SELECT * FROM USERS WHERE USER_NAME = ?",
    ["bill.gates"]
  );

  console.log("========================");
  console.log("= Select one record... =");
  console.log("========================");
  console.log(getSingleResponse);

  // Add a row to the users table by using SQL INSERT statement
  const runInsertResponse = await sqlLiteDB.runInsert(
    "INSERT INTO USERS (USER_NAME, EMAIL) VALUES(?, ?)",
    ["steve.jobs", "steve.jobs@apple.com"],
    () => {}
  );

  console.log("========================");
  console.log("= Insert one record... =");
  console.log("========================");
  console.log(runInsertResponse);

  // Update a row to the users table by using SQL UPDATE statement
  const runUpdateResponse = await sqlLiteDB.runUpdateOrDelete(
    "UPDATE USERS SET USER_NAME = ?, EMAIL = ? WHERE USER_NAME = ?",
    ["steve.balmer", "steve.balmer@microsoft.com", "steve.jobs"],
    () => {}
  );

  console.log("====================");
  console.log("= Update record... =");
  console.log("====================");
  console.log(runUpdateResponse);

  // Delete a row to the users table by using SQL DELETE statement
  const runDeleteResponse = await sqlLiteDB.runUpdateOrDelete(
    "DELETE FROM USERS WHERE USER_NAME = ?",
    ["steve.balmer"],
    () => {}
  );

  console.log("====================");
  console.log("= Delete record... =");
  console.log("====================");
  console.log(runDeleteResponse);

  const closeResponse = await sqlLiteDB.close();
  console.log(closeResponse);
  console.timeEnd("Benchmark");

  // Return the results of the database operations
  return {
    openResponse: openResponse,
    getMultiResponse: getMultiResponse,
    getSingleResponse: getSingleResponse,
    runInsertResponse: runInsertResponse,
    runUpdateResponse: runUpdateResponse,
    runDeleteResponse: runDeleteResponse,
    closeResponse: closeResponse,
  };
}

main().then((mainResult) => {});

5. Right click on SQLLite-Sample folder and click on Open in Integrated Terminal. If SQLLite-Sample is already the root folder then go to the top menu and click on ... > Terminal > New Terminal.

6. Type the following command below and press enter. This should download all the required software packages.

npm install

7. Type the following command below and press enter. This should build the app.

npm run build

8. Type the following command below and press enter. This should execute the app.

npm run start

9. Copy the output of the terminal and name it: db.png.

10. Go back to index.ts and comment all instances of the word console by typing //.

From: console.time("Benchmark");

To: // console.time("Benchmark");

11. Right click on SQLLite-Sample folder and click on New File… and name it: index.test.ts.

12. Copy the code below:

// Developer: Roderick Bernardo
// Purpose: Testing file using jest

// Import jest module
import { describe, expect, test, beforeAll } from "@jest/globals";

// Import app to test
import { main } from "./index";

// These are the expected values, the test passes if the retured values are equivalent to this
const mainResultToCompare = {
  openResponse: { isConnected: true, message: "Connected to: BACKEND_DB.db." },
  getMultiResponse: [
    {
      ID: 1,
      USER_NAME: "bill.gates",
      EMAIL: "bill.gates@microsoft.com",
      CREATED_AT: "2024-06-24 22:49:00",
    },
    {
      ID: 2,
      USER_NAME: "elon.musk",
      EMAIL: "elon.musk@tesla.com",
      CREATED_AT: "2024-06-24 22:49:00",
    },
  ],
  getSingleResponse: [
    {
      ID: 1,
      USER_NAME: "bill.gates",
      EMAIL: "bill.gates@microsoft.com",
      CREATED_AT: "2024-06-24 22:49:00",
    },
  ],
  runInsertResponse: { insertedId: 81 },
  runUpdateResponse: { rowsAffected: 1 },
  runDeleteResponse: { rowsAffected: 1 },
  closeResponse: {
    isDisconnected: true,
    message: "Disconnected to: BACKEND_DB.db.",
  },
};

let mainResult: any;

// Before starting the test, invoke the app to get the result
beforeAll(async () => {
  mainResult = await main();
});

// This is to test all the db operations such as:
// Open db
// Select multiple rows
// Select single row
// Insert a row
// Update a row
// Delete a row
// Close db
describe("Database operation testing", () => {
  // Test - Open database test
  test("Open database test", () => {
    // Compare with the result with what is expected
    expect(mainResult.openResponse).toEqual(mainResultToCompare.openResponse);
  });

  // Test - Select all users test
  test("Select all users test", () => {
    // Compare with the result with what is expected
    expect(mainResult.getMultiResponse).toEqual(
      mainResultToCompare.getMultiResponse
    );
  });

  // Test - Select one user test
  test("Select one user test", () => {
    // Compare with the result with what is expected
    expect(mainResult.getSingleResponse).toEqual(
      mainResultToCompare.getSingleResponse
    );
  });

  // Test - Insert a user test
  test("Insert a user test", () => {
    // Compare with the result with what is expected
    expect(mainResult.runInsertResponse).toHaveProperty("insertedId");
  });

  // Test - Update a user test
  test("Update a user test", () => {
    // Compare with the result with what is expected
    expect(mainResult.runUpdateResponse.rowsAffected).toBe(1);
  });

  // Test - Delete a user test
  test("Delete a user test", () => {
    // Compare with the result with what is expected
    expect(mainResult.runDeleteResponse.rowsAffected).toBe(1);
  });

  // Test - Close database testing
  test("Close database testing", () => {
    // Compare with the result with what is expected
    expect(mainResult.closeResponse).toEqual(mainResultToCompare.closeResponse);
  });
});

13. Type the following command below and press enter. This should build the app.

npm run build

14. Type the following command below and press enter. This should execute the jest test app.

npm run test

15. Copy the output of the terminal and name it: db-test.png.

Lab Tips

• This lab uses the following table structure with preloaded data.

CREATE TABLE USERS (
    ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    USER_NAME TEXT NOT NULL,
    EMAIL TEXT NOT NULL,
    CREATED_AT TEXT DEFAULT (datetime('now', 'localtime'))
);

• Feel free to download SQLite Browser to create or update tables and adjust the sample app / test code as you see fit.

Submission

1. Create a folder named submit.

2. Copy all (db.png and db-test.png) the previously saved screenshots to this folder.

3. Create a zip file of this folder.

4. Navigate back to where the lab was originally downloaded, there should be a Submissions section (see below) where the zip file can be uploaded.