Avatar
Author Eyal Katz
Share

Node.js SQLite Tutorial

Avatar
Eyal Katz
27-Jun-2022
6 min read

Node.js is a JavaScript runtime environment that allows you to build server-side applications. SQLite is a lightweight, self-contained database popular for its ease of use and portability.

The two technologies work well together because developers can easily embed SQLite databases within a Node.js application. Therefore, it is easy to develop applications using data from an SQLite database without connecting to a separate database server.

The combination of Node.js and SQLite is popular because it provides a fast and efficient way to build web applications. Here are some reasons why.

It is easy to get started

Both node.js and SQLite are open source and have a large community of developers and users.

It is easy to deploy

Both Node.js and SQLite are lightweight and don’t require a lot of resources to run. They can run on various platforms, making them versatile, especially in mobile development.

It is easy to scale

Node.js is an open-source, cross-platform runtime environment for developing server-side and networking applications. SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain SQL database engine.

It is easy to use

With Node.js and SQLite, you can quickly build lightweight, fast, and reliable applications. Node.js and SQLite are easy to use because they are simple, efficient, and fast due to their asynchronous I/O and the use of a single thread. They are also crash-resistant, reliable, and have a small footprint.

It is easy to port

They are both easy to port as SQLite is a self-contained, serverless database, and node.js is a JavaScript runtime that can be run on any platform.

SQLite usages 

SQLite is a relational database management system (RDBMS), similar to MySQL, Oracle, PostgreSQL, and Microsoft SQL Server. However, unlike these more traditional RDBMSes, SQLite is not a client-server database engine. Instead, it is embedded into the application that accesses the database. SQLite is an ideal choice for mobile apps, web browsers, and other applications where a local database is required, but the overhead of a client-server database is not.

Traditional vs SQLite serverless architecture

SQLite is the most widely deployed database in the world, with billions of copies. SQLite is often the database for small applications, especially those built for devices with limited memory and storage capacity, such as smartphones.

In addition to being used in mobile apps, SQLite is also a popular choice for web browsers. Many web browsers use SQLite to store local data such as history, cookies, and form data. Chrome, Firefox, Safari, and Opera all use SQLite.

Some applications also use SQLite to store configuration data or other types of data that do not need to be relational. For example, the version control system Git uses SQLite to store metadata for its repositories.

Node.js SQLite Tutorial

Developers can use SQLite as the database for a Node.js application. Node.js has built-in support for SQLite3 through the sqlite3 module. Here is a quick tutorial on how to use SQLite in Node.js:

Prerequisites

To use Node.js with SQLite, you need to have:

  1. Node.js installed on your system.
  2. The sqlite3 Node.js module installed.
  3. An SQLite database file.

The sqlite3 module is available on npm.

Installing SQLite

Download SQLite

First, you will need to download SQLite on your computer. You can find the latest version of SQLite here. Scroll down to the “Precompiled Binaries For Various Platforms” section and download the SQLite library and command-line shell for your platform.

Install SQLite

Once you have downloaded SQLite, you can install it by following the instructions for your platform.

SQLite for your Node.js app

The SQLite3 module is bundled with Node.js. To use the module, you need first to install it. The easiest way to do this is with the npm command:

npm install sqlite3

Once you have installed the module, you can use it in your Node.js programs.

Here is an example of a code snippet that uses SQLite in a Node.js application:

var sqlite3 = require('sqlite3');
 ​
 var db = new sqlite3.Database('example.db');
 ​
 db.serialize(function() {
  // Create a table
  db.run("CREATE TABLE IF NOT EXISTS Foo (id INTEGER PRIMARY KEY, name TEXT)");
 ​
  // Insert data into the table
  db.run("INSERT INTO Foo (name) VALUES ('bar')");
 ​
  // Query data from the table
  db.each("SELECT id, name FROM Foo", function(err, row) {
    console.log(row.id + ": " + row.name);
  });
 });
 ​
 db.close();

Add a new database and tables

You can create a new SQLite database using the following:

 var sqlite3 = require('sqlite3').verbose();
 var db = new sqlite3.Database('mydb.db');

Now that you have a database, you can create a table:

 db.run("CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");

Inserting data

You can also insert data into the table:

db.run("INSERT INTO mytable (name, age) VALUES ('John', 30)");
 db.run("INSERT INTO mytable (name, age) VALUES ('Jane', 20)");

Query the database

To query the database, you use the db.all() method. This method takes a SQL query as a parameter and returns an array of objects, one object for each row in the result set.

For example, to query all rows in the mytable table:

 db.all("SELECT * FROM mytable", function(err, rows) {
  rows.forEach(function (row) {
    console.log(row.id + ": " + row.name + " (" + row.age + ")");
  });
 });

 The above code will output the following:

1: John (30)
 2: Jane (20)

If you only want to query a single row, you can use the db.get() method. This method takes a SQL query as a parameter and returns the first row in the result set.

For example, to query the row with id 1:

 db.get("SELECT * FROM mytable WHERE id = 1", function(err, row) {
  console.log(row.id + ": " + row.name + " (" + row.age + ")");
 });

The above code will output the following:

 1: John (30)

Summary

One of the main features of Lightrun is the ability to track and visualize your node.js performance data, which can be extremely helpful in identifying areas where your node.js applications are performing poorly.

Additionally, Lightrun also provides a variety of tools that can help you optimize your node.js applications. Lightrun works with applications written in node.js, Python, and JVM languages, regardless of how they are deployed. Get started with Lightrun to add metrics and traces to your code in real-time and on-demand without needing to bring down your production stack.

Share

It’s Really not that Complicated.

You can actually understand what’s going on inside your live applications. It’s a registration form away.

Get Lightrun

Lets Talk!

Looking for more information about Lightrun and debugging?
We’d love to hear from you!
Drop us a line and we’ll get back to you shortly.

By submitting this form, I agree to Lightrun’s Privacy Policy and Terms of Use.