Getting Started SQLite3 with Nodejs

We will see how to setup sqlite3 and run different types of SQL queries from Nodejs on the sqlite3 database.

Topics Covered

• What is SQLite
• Communication Between SQLite and Nodejs
• Retrieving All Rows
• Retrieving A Single Row
• Retrieving Data Based on Placeholder
• Executing run() Method
• Using SQLite each() Method Instead of forEach()
• Running Queries Synchronously

What is SQLite

SQLite is a relational database. The details related to a database is stored in a file. Copying a database from one machine to another is just a file with no complex commands or anything. Just transfer the file.

SQLite database is very lightweight and easy to set up and use. No complex data server setup or heavy connection object.

This database is not suitable for concurrent access by multiple users. As the data is stored in a file, we need to take care of the security too. Also, it doesn’t validate the data types.

Binaries file for setting up SQLite in different types of OS.

DB browser for viewing the database in UI form, writing queries, and executing them.

Sample DB file which will be used to explain the concepts and methods.

Communication Between SQLite and Nodejs

Install sqlite3 in your workspace folder location. Run following command in cmd

npm install sqlite3

Import the sqlite3 module to facilitate the connection between them in your code editor.

const sqlite3 = require('sqlite3');

This module has methods that will help us to communicate with sqlite3 from Nodejs.

Opening a Database, the database is a single file in SQLite, so we just need to pass the path of the database. Here we will use the DB file for which the link is given above.

const db = new sqlite3.Database('./chinook.db');

Retrieving All Rows

For getting information from DB we use query, a query is a statement that asks for specific information from the database.

Get Sachin Sarawgi’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

For getting all the rows we use all() method. This method allows us to execute a query and call a callback to access the rows in the result set.

The error object contains the error details if there is any problem in the execution of the query passed else it will be null. The rows object contains the result of the query.

Retrieving A Single Row

There may be some cases where we want to check if our query is working fine or not, and we don’t want to retrieve all the results, just one row would be enough. Also in some cases, we may be sure that our query will return one row like querying based on Primary Key.

In all the above cases we can use get () method.

Retrieving Data Based on Placeholder

In queries when we use where condition we may want to pass a dynamic value to the condition added, else without dynamic value we have to hard code it which may not be the case we always want.

To satisfy the above scenario we use a placeholder, Whatever value we pass to the placeholder will get replaced in the query thus giving it dynamic nature.

Here we have used a placeholder for the title to search based on ‘Sales Support Agent’, replacing this value will change the query without touching it.

Executing run() Method

All the above methods return some rows as a result, but there are some queries that don’t return any result like creating a table, dropping a table, inserting a row.

For these cases, we use the run() method. It will not return any result but may attach an additional property to this keyword within the scope of the callback. Like in the case of inserting a row it will attach lastID to this, it’s a property that stores the value of the last inserted row ID.

Using SQLite each() Method Instead of forEach()

There may be a case where we want to perform a similar operation on all the returned rows from the query. In our db.all() method example we use javascript forEach() method to iterate over each row.

We can use each() method, this method takes the query and a callback function as an argument, it will run the callback on each row of the result.

Running Queries Synchronously

Suppose a requirement where you are dropping a table, then create a table, and then insert some rows. If we put all these queries separately and execute, they all will run parallel. It may happen drop query got executed and before executing create query insert query got executed which will result in an error.

The above scenario can also be solved by writing queries in the callback. Like create query can go into callback of drop query similarly, insert query can go into callback of creating table query.

However we have to keep indenting the next query in the callback of the previous one at some point, it may become complex to handle.

To avoid the above kind of scenario we need to run them in a synchronized manner. We use the serialize() method for this.

Hope this blog will help you to understand concepts and method related to SQLite and Nodejs. Click here for more on SQLite and Nodejs. Click here for the above code.

If you enjoyed reading this, don’t forget the applause. 👏
Thank you.