SQLite is one of the relational databases. All the necessary details which are related to the database are stored in a file. When we are copying a database from one machine to another, then this is nothing but a file with no complex commands or anything which we may need in our software database management systems. We will only need to transfer the file.
SQLite database is very light and easy to understand. It is also really easy to set up the whole installation process and also the use. We do not need a complex data server setup or heavy connection object in this.
This database also has the feature to support concurrent access by multiple users in the system. As the data is stored in a file, we will also need to take care of the security of the database. It does not validate the data types.
Setup and Installation
We are going to start our set and installation process by creating a new npm package using the npm init inside an empty directory which is named as node js-sqlite.
|
$ npm init This utility will walk you through creating a package.json file. It only covers the most common items, and tries to guess sane defaults. See `npm help json` for definitive documentation on these fields and exactly what they do. Use `npm install <pkg> --save` afterwards to install a package and save it as a dependency in the package.json file. Press ^C at any time to quit. name: (app) nodejs-sqlite version: (0.0.0) 0.1.0 description: Code and tutorial for node js and sqlite entry point: (index.js) main.js test command: git repository: keywords: author: Adam McQuistan license: (BSD) MIT About to write 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", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "repository": "", "author": "John Doe", "license": "MIT" } Is this ok? (yes) |
Now we will install the sqlite3 package via npm package which is necessary for proceeding in the installation process. Here is the code for that:
|
$ npm install --save sqlite3 |
After installing sqlite3 we are going to install Bluebird. It will help us to use the familiar promise functionality in our database management programming.
|
$ npm install --save bluebird |
Now we will create an empty file right next to the package.json file which is called sqldatabase.sqlite3 so that the SQLite will store data in the database.
Become a Software Development Professional
- 13 % CAGR
- 30 %
Full Stack Java Developer
- Kickstart Full Stack Java Developer career with industry-aligned curriculum by experts
- Hands-on practice through 20+ projects, assessments, and tests
6 monthsFull Stack Web Developer - MEAN Stack
- Comprehensive Blended Learning program
- 8X higher interaction in live online classes conducted by industry experts
11 months
Designing the Database:
Here, we are building out the data access layer for a project and task tracking application. The basic business rules that needs to be followed for this application's data access layer are as follows:
- The application has some required projects
- Each project should have one or more tasks to complete in the application.
With all the business rules that we have stated above, we can now take that information and start designing the necessary tables and their fields for the application. It is totally clear that we will need a projects table as well as a tasks table. For the rest, we will just use a little intuition, some made-up test data, and at the end roll with it (a common work characteristic for most of the developers in the software development industry).
Communication Between SQLite and Nodejs
Now in this section of the article, we will try to elaborate the connection between SQLite and Nodejs. First, we will install sqlite3 in our workspace folder location by running the following command in cmd:
|
npm install sqlite3 |
It is also required to import the sqlite3 module to facilitate the connection between them in our code editor. This whole process can be done by running the command given below:
|
const sqlite3 = require('sqlite3'); |
This module has methods that can be really helpful to us to communicate with sqlite3 from Nodejs. When we open a database, the database is normally a single file in SQLite, so we just need to pass the path of the database. Here we are going to use the DB file for which the link is given above in the SQLite section.
|
const db = new sqlite3.Database('./chinook.db'); |
Retrieving All Rows
Retrieving all the rows in the SQLite database is a really crucial step in database management systems. If we want to get all the information from the database then we should use a query, a query is a statement in database management systems that requests some specific information from the database.
We will use the all() method for getting all the rows from the database. This method will allow us to run a query and call a callback to access the rows in the final set.
|
const sqlite3 = require('sqlite3'); const dbase = new sqlite3.Database('./chinook.db'); //Retrieving all Rows from the database of our system dbase.all("SELECT empId, fName FROM employees", (error, rows) => { rows.forEach((row) => { console.log(row.empId + " " + row.fName); }) }); |
We will also get an error object which contains all the error details that will tell us that if there is any problem in the execution of the query passed else it will be null after the execution. The rows object contains the result of the query in the program provided above.
Retrieving a Single Row
There are also some situations that arise where we want to check if our query is working efficiently or not, and we do not want to retrieve all the rows from the database, but just one row would be enough. Also in some other cases, we might need to be sure that our query is going to return one row as it does in querying based on the Primary key.
So to handle the situations mentioned above, we can use the get () method. It helps us to retrieve just a single row from the database in our system.
|
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 systems, we use queries where we might want to pass a dynamic value to the condition added, else without the dynamic value we have to hard code all the things which may not be the case we always wanted for our systems.
We need to satisfy the above scenario, so for that, we use a placeholder. Whatever value we need to pass to the placeholder will get replaced 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 the data based on Placeholder in our database system db.all("SELECT EmpId, fName FROM employees where title=$title", { $title: 'Customer Support Officer' }, (error, rows) => {rows.forEach((row) => { console.log(row.EmpId + " " + row.fName); }) }); |
In the program given above, we have included a placeholder for the title of the database to search based on ‘Customer Support Officer’, replacing this value will change the query very easily with no need to touch it.
Executing run() Method:
All of the above methods that we have discussed return some rows as a result, but some queries do not return any result for example: creating a table, dropping a table, inserting a row.
So, in this scenario, for cases like these, we normally use the run() method in our database. It will not just return any result but it may also attach an additional property to this keyword within the scope of the callback of this method. If we take an example of inserting a row, then it will attach lastID to this database, it’s a property that is used to store the value of the last inserted row ID from the provided data.
|
const sqlite3 = require('sqlite3'); const dbase = new sqlite3.Database('./chinook.db'); //Executing run() Method in our database system db.run(`Insert into playlists(Name) values(?)`, ['Indie'], function(error){ console.log("New playlist has been added with id " + this.lastID); } ); |
Become a Software Development Professional
- 13 % CAGR
- 30 %
Full Stack Java Developer
- Kickstart Full Stack Java Developer career with industry-aligned curriculum by experts
- Hands-on practice through 20+ projects, assessments, and tests
6 monthsFull Stack Web Developer - MEAN Stack
- Comprehensive Blended Learning program
- 8X higher interaction in live online classes conducted by industry experts
11 months
Using SQLite each() Method Instead of forEach():
As we have already described, there might be a case where we will have to perform a similar operation on all the returned rows from the query of the database. If we consider our db.all() method example, then we use javascript forEach() method to iterate over each row from the query.
So in this situation, here we can use each() method. This is a really useful method that can play a big role in these scenarios. This method mainly takes the query and a callback function as an argument for your system, and then it will run the callback on each row of the result 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 in it. If we put all the queries separately and execute them, they all will be running parallel in our database system. It could also occur that the drop query will get executed and before executing create query insert query gets executed which will create an error for that system.
When we face the above scenario, then that can also be solved by writing some queries in the callback. Like when we create a query then it can easily go into the callback of drop query again, and insert query can go into the callback of creating table query for the required condition of an application.
|
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 have to keep indenting the next query in the callback of our database of the previous one at some point, it may become complex to handle in our database management systems.
So, if we want to avoid the above kind of scenario we will need to run them in a synchronized manner of our database. We use the serialize() method for the execution of this method.
|
const sqlite3 = require('sqlite3 const dbase = new sqlite3.Database('./chinook.db'); //with serialize method db.serialize(() => { db.run("DROP TABLE all the playlists"); db.run("CREATE TABLE playlists([playId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[Name] NVARCHAR(120))"); db.run("INSERT INTO the playlists (name) VALUES ('TV Shows'), ('Movies'), ('Web Series')"); }); |
Master front-end and back-end technologies and advanced aspects in our Post Graduate Program in Full Stack Web Development. Unleash your career as an expert full stack developer. Get in touch with us NOW!
Conclusion
In this article on Node Js Sqlite, we have illustrated almost all the Node Js Sqlite with proper examples. The article starts with a brief introduction to SQLite and then we directly start elaborating the various types of setup and installation in SQLite. We have provided a brief overview of how to design the database and the communication between SQLite and Node js. We have explained retrieving all rows, retrieving a single row, and retrieving data based on placeholders and also their examples. After the explanation, we have also illustrated the topics like executing the run method, using SQLite each method instead of forEach(), and running queries synchronously with an example for a better understanding of the topic. We hope that this article was able to give you a thorough knowledge of Node Js Sqlite and how we can use it in our software development projects.
To know more about the Node Js Sqlite, you can enroll in the Post-Graduate Program In Full-Stack Web Development offered by Simplilearn in collaboration with Caltech CTME. This Web Development course is a descriptive online bootcamp that includes 25 projects, a capstone project, and interactive online classes. In addition to the Node Js Sqlite and other related concepts, the course also details everything you need to become a full-stack technologist and accelerate your career as a software developer.
Simplilearn also offers free online skill-up courses in several domains, from data science and business analytics to software development, AI, and machine learning. You can take up any of these free courses to upgrade your skills and advance your career.
If you have any questions, feel free to post them in the comments section below. Our team will get in touch with you at the earliest.