SQLite is one of the relational databases. All the necessary details related to the database are stored in a file. When we copy a database from one machine to another, it is nothing but a file with no complicated commands or software we might need in our database management system. We only need to transfer files.
SQLite database is very lightweight and easy to understand. The entire installation process is also really easy to set up and use. We don’t need a complex data server setup or heavy connection objects in it.
This database also has the feature of supporting simultaneous access by multiple users of the system. As the data is stored in a file, we also need to take care of database security. It does not validate data types.
Node.js SQLite Guide
Table of Contents
Setup and Installation
We’re going to start our setup and installation process by creating a new npm package using npm init inside an empty directory called node js-sqlite.
$npm init
This utility will walk you through creating a package.json file.
It covers only the most common items and tries to assume sensible defaults
See `npm help json` for specific documentation in this area
And that’s exactly what they do.
To install a package then use `npm install <pkg> –save` and
Save it as a dependency in the package.json file.
Press ^C at any time to exit.
Name: (app) nodejs-sqlite
Version: (0.0.0) 0.1.0
Description: Code and tutorial for Node JS and Sklite
Entry point: (index.js) main.js
Test order:
git repository:
Keywords:
Author: Adam McQuistan
License: (BSD) MIT
About writing to /node-sqlite/app/package.json:
{
“name”: “nodejs-sqlite”,
“version”: “0.1.0”,
“description”: “Code and tutorial for node js and sqlite”,
“main”: “main.js”,
“script”: {
“test”: “echo \”ERROR: No test specified\” && exit 1″
},
“repository”: “”,
“Author”: “John Doe”,
“License”: “MIT”
}
Is it okay? (yes)
Now we will install the sqlite3 package via npm package which is required to proceed with the installation process. Here is the code for that:
$ npm install –save sqlite3 |
After installing sqlite3 we are going to install Bluebird. This will help us use the promise functionality known in database management programming.
$ npm install –save bluebird |
Now we will create an empty file called sqldatabase.sqlite3 right next to the package.json file to store the data in the SQLite database.
Designing the Database:
Here, we are creating the data access layer for a project and task tracking application. The basic business rules to follow for the data access layer of this application are as follows:
1. The application contains some essential projects.
2. Each project application should have one or more tasks to complete.
With all the business rules we described above, we can now take that information and start designing the tables and their fields needed by the application. It is quite clear that we will need a project table as well as a task table. For the rest, we’ll just use a little intuition, some generated test data, and finally roll with it (a common job feature for most developers in the software development industry).
Communication Between SQLite and Nodejs
Now in this section of the article, we will try to explain the connection between SQLite and Nodejs in detail. First, we will install sqlite3 in our workspace folder location by running the following command in cmd:
npm install sqlite3 |
We need to import the sqlite3 module to make linking between them easier in our code editor. This entire process can be done by running the command given below:
const sqlite3 = require(‘sqlite3’); |
This module contains methods that can be really helpful for us to communicate with sqlite3 from Nodejs. When we open a database, the database is usually a single file in SQLite, so we just need to pass the database path. Here we are going to use the DB file which is linked above in the SQLite section.
const db = new sqlite3.Database(‘./chinook.db’); |
Retrieving All Rows
Retrieving all rows in SQLite database is a really important step in database management system. If we want to get all the information from the database, we should use a query, a query is a statement in the database management system that requests some specific information from the database.
We will use the all() method to get all the rows from the database. This method will allow us to run a query and call a callback to access the final set of rows.
const sqlite3 = require(‘sqlite3’);
const dbase = new sqlite3.Database(‘./chinook.db’);
//Retrieving all rows from our system database
dbase.all(“SELECT MPID, fName FROM employees”, (error, rows) => {
rows.forEach((rows) => {
console.log(row.empId + ” ” + row.fName);
})
});
We will also get an error object containing all the error details which will tell us that if there is any problem executing the passed query, it will return null after execution. The Queue object contains the query result of the program given above.
Retrieving All Rows
There are also situations where we want to check if our query is working efficiently and we don’t want to retrieve all the rows from the database, but only one row will be enough. Also in some other cases, we may need to be sure that our query will return a row when querying based on the primary key.
So to handle above mentioned situations, we can use get() method. This helps us retrieve only one row from the system database.
const sqlite3 = require(‘sqlite3’);
const dbase = new sqlite3.Database(‘./chinook.db’);
//Retrieving a single Row from the database of our system
db.get(“SELECT empId, fName FROM employees”, (error, row) => {
console.log(row.empId + ” ” + row.fName);
});
Retrieving Data Based on Placeholder
In database management system, we use query where we want to pass a dynamic value in added condition, otherwise we have to hard code all things except dynamic value which we don’t always want for our system.
We need to satisfy the above scenario, so for that, we use a placeholder. Whatever value we need to pass to the placeholder will be substituted in the query thus giving it a dynamic nature that we always wanted.
const sqlite3 = require(‘sqlite3’);
const dbase = new sqlite3.Database(‘./chinook.db’);
//Retrieving all data based on placeholders in our database system
db.all(“SELECT EmpId, fName FROM employees WHERE title=$title”, {
$title: ‘Customer Support Officer’
},
(error, rows) => { rows.forEach((rows) => {
console.log(row.EmpId + ” ” + row.fName);
})
})
In the program given above, we have included a placeholder for the database title to search based on ‘Customer Support Officer’, replacing this value will change the query very easily without needing to touch it.
Executing run() Method:
All the above methods that we have discussed return some rows as result, but some queries return no result like: CREATE A TABLE, DROP A TABLE, INSERT A ROW.
So, in this situation, in such cases, we usually use the run() method in our database. Not only will it return no result but it can attach an additional property to this keyword within the scope of this method’s callback. If we take an instance of inserting a row, it will associate lastId with this database, which is a property used to store the last inserted row id value from the given data.
const sqlite3 = require(‘sqlite3’);
const dbase = new sqlite3.Database(‘./chinook.db’);
//Execute the run() method on our database system
db.run(`insert into playlist(name) value(?)`,
[‘indie’],
function(error){
console.log(“Added new playlist with id ” + this.lastID);
}
);
Using SQLite each() Method Instead of forEach():
As we have already described, there may be a case where we need to perform the same operation on all the rows returned from the database query. If we consider our db.all() method example, we use the javascript forEach() method to iterate over each row from the query.
So in this scenario, here we can use each() method. This is a really useful method that can play a big role in these situations. This method essentially takes the query and a callback function as an argument to your system, and then it will run the callback on each row of results from your database.
const sqlite3 = require(‘sqlite3’);
const dbase = new sqlite3.Database(‘./chinook.db’);
//Using the SQLite each() method instead of forEach() for our database
db.each(“SELECT EmpId, fName FROM employees limit 10”,
(error, row) => {
console.log(row.EmpId + ” ” + row.fName);
}
);
Running Queries Synchronously
If we assume a requirement where we need to drop a table, then we should create a table and then insert some rows. If we put all the queries separately and run them, they will run in parallel in our database system. It may also happen that the drop query will execute and before the create query insert query will execute which will generate an error for that system.
When we face the above scenario, it can be solved by writing some queries in the callback. For example when we create a query it can easily go back to the drop query callback and enter the create table query callback for an application’s required condition.
const sqlite3 = require(‘sqlite3’);
const dbase = new sqlite3.Database(‘./chinook.db’);
//Running queries synchronously without serialize method of the system
db.run(“DROP TABLE all the playlists”, function(error){
db.run(“CREATE TABLE playlists([playId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[name] NVARCHAR(120))”, function(error){
db.run(“INSERT INTO playlists (name) VALUES (‘TV shows’), (‘Movies’), (‘Web series’)”);
});
});
However, in this case, we need to indent the next query in a previous database callback, which can become complicated to handle in our database management system.
So, if we want to avoid the above type of situation, we need to run them in a synchronized manner in the database. To execute this method we use the serialize() method.
const sqlite3 = require(‘sqlite3
const dbase = new sqlite3.Database(‘./chinook.db’);
// with the serialize method
db.serialize(() => {
db.run(“all playlist drop table”);
db.run(“CREATE TABLE PLAYLIST([playId] INTEGER PRIMARY KEY AUTO NOT NULL, [NAME] NVARCHAR(120))”);
db.run(“insert into playlist (name) values (‘TV Show’), (‘Movies’), (‘Web Series’)”);
});
Conclusion
In this article on Node Js Sqlite, we have illustrated almost all Node Js Sqlite with proper examples. The article starts with a brief introduction to SQLite and then we go straight into detailing the various setups and installations in SQLite. We have given a brief description of how to design databases and the communication between SQLite and Node js. We have explained all row retrieval, single row retrieval and data retrieval based on placeholders and their examples. After the explanation, we have also illustrated topics such as running the run method, using SQLite’s each method forEach(instead of forEach) with an example to understand things better. We hope this article has been able to give you a thorough knowledge of Node Js Sqlite and how we can use it in our software development projects.