Async-Await-SQLITE

Purpose

SQLite is popular embedded database engine used in mobile devices and computers. Although powerfull and lightweight, its asynchronous nature can be a stumbling block for beginners.

AA-SQLite is a promise based SQLite wrapper that allows applications to interact with SQLite3 databases synchronously. When using AA-SQLite to query a database, application execution is paused until the result of the query has been resolved. AA-SQlite supports foreign key integration, and the following database operations:

1. Select
2. Delete
3. Insert
4. Update

IMPORTANT LINKS

• Link to github repository
• [Alternative Implementation] (https://www.scriptol.com/sql/sqlite-async-await.php)
• [SQLite Tutorials] (https://www.sqlitetutorial.net/sqlite-nodejs/)

Technologies Used

• SQLite
• Node.js with Async/Await

Setup

In order to use this package, you will need to create a SQLite3 project, by installing the following required technologies.

Required Technologies

1. Node.js LTS
2. NPM
3. Git & Git Bash
4. SQLite

Installation and Configuration

1. Install all required technologies
2. [Optional] Enable SQLite foreign key constraint:
   1. Open the command prompt
   2. Type "sqlite3" [ENTER], to enter the SQLITE prompt
   3. Type "PRAGMA foreign_keys = ON;" [ENTER]
   4. Click "CTRL+C" [ENTER], to exit SQLite prompt
3. Create a new project in your editor of choice
4. Install the AA-SQLite package
   1. npm i aa-sqlite --save
5. See instructions on database creation.

Create a Sample Database

1. Create a database.js file
   1. Require the sqlite3 package
      • const sqlite3 = require('sqlite3').verbose();
   2. Set the location of your database file.
      • const DBSOURCE = "./geo_db.sqlite";
   3. Open a database connection, create a table, and populate the table:

      let db = new sqlite3.Database(DBSOURCE, (err) => {
          if (err) {
              console.error("DB Error", err);
              return;
          } else { 
              console.log('Connected to the SQLite geo_db database.');
              db.run(`CREATE TABLE geo_table(
                  id INTEGER,
                  latitude DECIMAL(5, 2), 
                  longitude DECIMAL(5, 2), 
                  city TEXT, 
                  state TEXT,
                  PRIMARY KEY(id)
              )`,
                  (err) => {
                      if (err) {
                          console.log("geo_table NOT created", err);
                      } else {
                          console.log("geo_table created");
                          var insert = 'INSERT INTO geo_table (lat, lon, city, state) 
                              VALUES (?, ?, ?, ?)';
                          db.run(insert, [33.75, 84.39, "Atlanta", "Georgia"]);
                          db.run(insert, [34.05, 118.24, "Los Angeles", "California"]);
                          console.log("geo_table populated");
                      }
                  });
          }//db created
      });

   4. Close the database connection

      db.close((err) => {
          if (err) {
              console.error(err.message);
          }
          console.log('geo_db connection is closed.');
      });

   5. Run the javascript file
      • At a command prompt, change the directory to the location of the database.js file.
      • Type: "node database.js" [Enter]

Query the Database with AA-SQLite

1. Prerequisites:
   1. Set the location of your database file.
      • const DBSOURCE = "./database_name.sqlite";
   2. Require the AA-SQLite package
      • const aaSqlite = require("aa-sqlite");
2. Open the database connection:
   1. Syntax:
      • await aaSqlite.open(DBSOURCE: string, Enable_Foreign_Key: boolean)
3. Query the database:
   1. [SELECT Query - Syntax1] : Used to return a single unique value, such as a key.
      • await aaSqlite.get(query: string, values: array);
   2. [SELECT Query - Syntax2] : Used to return all matching records.
      • await aaSqlite.get_all(query: string, values: array);
   3. [INSERT, UPDATE, or DELETE Query - Syntax] : Used for queries that do not return a value.
      • await aaSqlite.push(query: string, values: array);
   4. Parameters:
      1. query: Any of the sqlite3 query strings mentioned above.
      2. values: Array containing the actual values to be inserted in an INSERT INTO VALUES clause. If the values are contained in the query, or the query does not require them, this parameter should be provided an empty array [].
4. Close the database:
   1. Syntax:
      • aaSqlite.close();

Use

1. This repo is available for public non-commercial use only.