Today we are starting to use our SQL skills in our Node.js code. This is an important step where we start to connect our code to a database.
SQLite3 is a lightweight SQL database. It is often used in embedded devices such as phones and games consoles.
Follow these instructions to install SQLite:
Create a new directory for this week's work. cd into it.
run npm init to create a new package.json file.
Execute npm install sqlite3 in the directory where your package.json lives. If you get errors, try npm install sqlite3@5.0.0 instead. If you still have errors, follow the instructions below (note these are Windows specific):
package.json file is and run npm install --global --production windows-build-tools@4.0.0.npm install sqlite3@5.0.0 in the directory where your package.json lives.To check your install is successful, paste this code into a file named dbconnect.js:
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.');
});
// close the database connection
db.close((err) => {
if (err) {
return console.error(err.message);
}
console.log('Close the database connection.');
});
run the file with node dbconnect.js. You should see the console logs appear. You have successfully connected to the sqlite in-memory database.
You may find the following links useful:
Here is simplified example of how to create a table and insert some rows into the table.
const sqlite3 = require('sqlite3').verbose();
// use a persistent database named myDb.sqlite
const db = new sqlite3.Database('./restaurants.sqlite');
/**
* Executes the SQL statements one at a time.
*
* Note the use of try/finally to ensure resources get closed
* whether an error occurs or not
*
*/
try {
db.serialize(function () { // serialize means execute one statement at a time
// create the empty table with specific columns and column types
db.run("CREATE TABLE Restaurant(id INTEGER PRIMARY KEY,
name TEXT, image TEXT");
let stmt;
// insert 2 rows
try {
stmt = db.prepare(`INSERT INTO Restaurants (id, name, link) VALUES
(1, 'Bayroot', 'https://www.telegraph.co.uk/content/dam/Travel/Destinations/Europe/England/Brighton/brighton-restaurants-hotel-du-vin-bistro.jpg')`);
stmt.run();
} finally {
// release resources
stmt.finalize();
}
// select the rows and print them out
db.each("SELECT * FROM Restaurants",
function (err, rows) { // this is a callback function
console.log(rows); // rows contains the matching rows
}
);
});
} finally {
db.close();
}
This code uses a try/finally block to ensure that the statement and database are closed regardless of whether an error occurs. This is best practice to avoid memory leaks.
The code also makes use of Prepared Statements which are key for ensure your database applications are not vulnerable to SQL injection attacks.
const sqlite3 = require('sqlite3')
const db = new sqlite3.Database('./db.sqlite')
In the above lines above we import the sqlite3 package then on the next line we access a constructor on the sqlite3 object and use the new keyword to instantiate a new instance of our database for our programme. We can pass some config to our Database constructor. We are passing in a relative path where the database file either already exists, or if it doesn't exist where we would like it to be created and with what name.
This file/database will be written to disc, data we store here we can access even when our programme has stopped running. We are going to interact with our database using javascript. The challenge we can use to start working with a database in our Node.js programme is to load the data from our file of airports into our database.
WHERE clause to filter out the data you need.There is an sqlite plugin for VSCode which allows you to visualise your database.
Install the sqlite plugin for VSCode as follows:
Extensions icon and choose sqlite by alexcvzzView-Command Palette from the menuSQLite: Open DatabaseSQLITE EXPLORER window should appear at the bottom of your VSCode Explorer view. Click on this and expand it until you see the table CUSTOMERS. Click on the play icon and voila, you should see a visual representation of your database.In this lesson you will learn how to load your restaurant tables with data from file using Node.js. You will use nested iteration to loop through the data and relate data using foreign keys.
If you are on a Mac you can run the curl command below to retrieve the restaurant file content and save it to a file. If you are on Windows, you can paste the URL below into a browser and select to save it locally into your project directory.
curl https://raw.githubusercontent.com/MultiverseLearningProducts/restaurant-data/master/restaurants.json --output restaurants.json
This is called "seed" data. The idea is to seed our database with a data set we can use later. Remember to include ids and foreign keys to relate your menus to the right restaurants.
Have a look at an example of a Menu Item object in the JSON:
{
"title": "Cold Mezza/Starters",
"items": [
{
"name": "Houmous",
"price": 5.00
},
{
"name": "Baba Ganoush (Moutabal) (v)",
"price": 5.70
},
{
"name": "Stuffed Vine Leaves (Warak Enab)",
"price": 5.70
}
]
}
If you are not familiar with JSON syntax work through this JSON tutorial.
Key points to note are that
[] syntaxTo insert the data we need to iterate over our array of Restaurants, then each Menu in the Restaurant and then over each Menu Item in each Menu to form our SQL insert queries.
This sounds daunting so let's break in down into steps. We'll only show a subset of the full code as you will need to complete it in your next assignment.
Reading from a file may take a long time hence it is performed asynchronously. Here is one version of a load() method which you can use, it makes use of async/await, you can also write this using Promises or Callbacks.
const fsp = require('fs').promises; // Node.js file system module with promises
async function load() {
console.log('calling load');
// wait for the restaurant data file to be read
const buffer = await fsp.readFile('./restaurants.json');
const restaurants = (JSON.parse(String(buffer)));
return restaurants;
}
Now you have the data loaded, we need to loop through the multi-dimensional array and insert it into our 3 tables. JavaScript supports a number of different types of loop.
You will need to create Prepared Statements to do the database inserts, for example,
db.prepare("INSERT INTO MENU_ITEMS (menu_id, name, price) VALUES (?,?,?)");
All the "?" are placeholders for the different values that we will be inserting as we iterate over our array of data. When you call db.run the first argument is the string above, and the second argument is an array of all the values you want to store, the values get swapped with the "?", your values have to be in the same order as the fields.
initialiseDB.js) which contains the code to CREATE your tables, we want to keep this separate from the code which INSERTS the data.populateDB.js) which contains the code to load the JSON data and INSERTS the rows.load() which loads the JSON data as an array of arrays from a file into a variable (see above)db.serialize which uses a recursive function to load each Restaurant, Menu & MenuItem into the databasetry/catch/finally blocks to handle errors and close both the statements and database.This lesson is optional and is designed solely as an extension task for those students who complete the airport coding assignments from week 1 quickly and need a further challenge. All the content above from Lesson 5 is relevant and students should refer to it. The airport specific aspects are described below.
curl https://raw.githubusercontent.com/MultiverseLearningProducts/airports/master/airportsData.json --output airports.json
Have a look at an example of an airport object:
{
"icao": "KPAE",
"iata": "PAE",
"name": "Snohomish County (Paine Field) Airport",
"city": "Everett",
"state": "Washington",
"country": "US",
"elevation": 606,
"lat": 47.90629959,
"lon": -122.2819977,
"tz": "America/Los_Angeles"
}
What are the field names and types we will need to set up in our database? This is called the schema. Once you have figured out the schema, can you compose a query that will create the airports table if it does not exist? We will want to run this before we read the data out of our file.
CREATE TABLE IF NOT EXISTS airports(id INTEGER PRIM...etc);
Once you have your query ready how do we run it? You can use the run function see below.
db.run('YOUR SQL QUERY;', function (error) {
/* this callback function runs after Node.js has
run the query on sqlite3 database, and the database
has emitted an event saying it's finished. In this
callback function on the `this` context you can
access the id of the last record you inserted.
This will be useful later */
const id = this.lastID
})
Once our database is setup we are ready to go get the data into it. Can you read the data from your .json file into an array of javascript objects? Try this and console.log your array to your terminal. (or you can just use require to load the file see below).
const airports = require('./airport.json')
To insert the data we need to iterate over our array of airports and use the airport object to form an SQL insert query.
{
"icao": "KPAE",
"iata": "PAE",
"name": "Snohomish County (Paine Field) Airport",
"city": "Everett",
"state": "Washington",
"country": "US",
"elevation": 606,
"lat": 47.90629959,
"lon": -122.2819977,
"tz": "America/Los_Angeles"
}
INSERT INTO airports (icao, iata, name, city, state, country, elevation, lat, lon, tz) VALUES (?,?,?,?,?,?,?,?,?,?);
All the "?" are placeholders for the different values that we will be inserting as we iterate over our array of airports. When you call db.run the first argument is the string above, and the second argument is an array of all the values you want to store, the values get swapped with the "?", your values have to be in the same order as the fields.
Maybe this seems easy. We have an array of airports, we just iterate over the array and call db.run on for each airport. The thing is; inserting into a database is an async function. If we just call db.run on every item in the array what do you think will happen to the "pending callbacks" stack? Also how do we know when all the airports have been inserted into the database?
When we have a queue of async tasks we want to perform a recursive pattern is a better option that Array.forEach.
Here's an example of a Jest test which uses the beforeAll method to initialise the database with our tables if they don't already exist. Note that this uses airport tables but the concept is the same. The idea of the failing test helps you think about what you are actually trying to build.
const load = require('./index')
describe('SQLite3', () => {
beforeAll(done => {
db.exec('CREATE TABLE IF NOT EXISTS airports(...);', done)
})
test('airports are loaded into the database', (done) => {
load((db) => {
db.all('SELECT * FROM airports LIMIT 3;', (err, row) => {
expect(row.length).toBe(3)
expect(row[0].name).toBe('Shenyang Dongta Airport')
db.get('SELECT COUNT(id) AS total FROM airports;', (err, count) => {
expect(count.total).toBe(28868)
done()
})
})
})
})
})
load function that will take a callback and call it when all the airport data has been inserted into the database.insert function in your load.js file that will take; the airports array, the callback passed to load, the database instance db.load function can assume the airports table has been created i.e. CREATE TABLE IF NOT EXISTS airports.... can be run in your test setup beforeAll function.insert function you created in your load.js file with the airports array, the callback passed to load, the database instance dbinsert function check if the airports array is empty. If it is empty call the callback function with the db instance and return from the function.insert function call .pop() on the airport array to remove the last airport from the array, then insert that item into the databasedb.run callback call the insert function again with the same arguments (this is called recursion)