Getting Started with SQLite Using Node.js

About SQLite

SQLite is a lightweight, open-source, serverless relational database management system.  It can be built into all mobile phones and most computers. The SQLite file format is stable and cross-platform. It is free to use.

The database file is used as a container to transfer the rich content between the system and the format can be copied freely from 32-bit to 64-bit systems.

 

 

SQLite does not have a separate server, it reads and writes files directly to ordinary disk files. It is an outright SQL database with multiple tables, indices, triggers, and views contained in a single disk file.

There are over one trillion SQLite databases in active usage and the current version of SQLite is 3.40.1.


Why SQLite?

SQLite is mainly used to develop embedded software for devices like televisions, cell phones, cameras, etc. It can convert files into smaller size archives with lesser metadata. Furthermore, it is used as a temporary dataset to get processed with some data within an application.


SQLite Features

  • Zero configuration
  • Self-Contained
  • Serverless
  • Transactional


Advantages of SQLite

  • No Installation needed
  • Better Performance
  • Lightweight
  • Reliable
  • Portable
  • Accessible
  • Reduce Cost and Complexity

    

Disadvantages of SQLite

  • Database size is confined to 2GB in most cases.
  • SQLite is not designed to handle large-scale, high-concurrency workloads. It is best suited for small to medium-sized applications that require local storage.


SQLite NodeJs

In this section, let's learn the following by using the sqlite3 module.

  • How to interact SQLite with NodeJs
  • Connecting the database
  • Do Insert, Update, and Delete operations. 

The current version shown in the example is 5.1.4.


Connecting To SQLite Database Using Node.js

Install the sqlite3 module in the application and connect the database named sqlitenod.db. Once the database is connected, then the sqlitenode.db file will be created automatically on the disk.

We can maintain all the tables in sqlitenode.db file.

Create a Table And Insert The Data

We can follow the below steps to create and insert the data into the table

  • Open database connection
  • Execute INSERT statement
  • Close database connection

The run() method is used to CREATE, INSERT, UPDATE, and DELETE operations in the database

 

Create Table
 Use the run() method to create the table 1 <<country>>


 

Insert Data Into Table
 Use the same run() method to insert the data into the table using the SQL query  


Querying Data From Tables

sqlite3 provides three methods for querying the data such as all(), get() and each(). 

  1. all() method is used to fetch and access all the rows from the table. 
  2. get() method is used to get only one row based on the parameters provided. 
  3. each() method is used to fetch and access every row from the table.

all() Method

all() method is used to fetch data from the table and callback all the rows based on the parameters provided.

each() Method

each() method will get all the data from the table based on the parameters provided and allows to access every data in the loop.


get() Method

get() method is used to get a particular row for specified parameters and we can perform aggregate functions such as sum(), count(), min(), max(), etc.,


Updating Data IN SQLite From NodeJs

 Using the run() method, the data can be updated in the SQLite database.

 

Deleting Data IN SQLite From NodeJs

 Using the run() method, the data can be deleted from the SQLite database.

 

Conclusion

In this article, we have learned what is SQLite and how it interacts with Node.js.

Besides having many advantages, it is important to consider its disadvantages and memory size limitation while selecting SQLite for any application.

Are you looking for skilled nodejs developers to help you implement SQLite and Node.js in your next project?

Look no further! Hire our team of experienced developers today and take your project to the next level.

 

 

 

Recommended Articles

1. PostgreSQL vs MySQL: A Quick Guide

2. Nodejs Cache with Express Node.js

3. Nodejs Cluster Concept

Sundar

Sundar

Senior Fullstack Developer

Comments