JavaScript SQLite3 Tutorial for Beginners (Node.js)

In this tutorial we will explore how to set up, install and use the SQLite3 Database in JavaScript.

What is SQLite?

SQLite is a lightweight, serverless, and embedded relational database management system (RDBMS) that is widely used, especially in applications with moderate data storage needs.

SQLite offers several benefits that make it a popular choice for many use cases:

1. Lightweight and Embedded:
   • SQLite is a self-contained, serverless, and zero-configuration database engine. It is lightweight and can be embedded directly into applications, requiring minimal setup.
2. Zero Configuration:
   • No separate server process or setup is needed. Applications can connect to an SQLite database by simply opening a file, making it easy to deploy and manage.
3. Cross-Platform Compatibility:
   • SQLite is cross-platform and can run on various operating systems without modification. This makes it suitable for applications that need to work seamlessly across different platforms.
4. Low Resource Consumption:
   • SQLite has low memory and disk space requirements. This is beneficial for resource-constrained environments, such as mobile devices or embedded systems.
5. Ease of Use:
   • SQLite is known for its simplicity and ease of use. It uses a straightforward SQL syntax, making it accessible to developers who may not have extensive database administration experience.

Pre-requisites

There are two pre-requisites to using SQLite in JavaScript:

• You need to have Node.js installed on your system. You can check whether you have node by using the node -v command in the terminal. If you don’t have node, visit the official website, download the installer, and execute it.
• The SQLite library which can be installed using Node.js. This library installs both the SQLite software, and the node library required to interface with SQLite.

To install the SQLite software, run the following command in the terminal:

npm install sqlite3

This will add the sqlite3 library to your node modules, and make it available for use within your project.

Setting up a Database in SQLite3

The first thing to do is setup an SQLite connection.

const sqlite3 = require('sqlite3').verbose();

The verbose() method here is optional. Using this method will create more detailed logs and stack traces (good for debugging).

let db = new sqlite3.Database('./tutorial.db', (err) => {
  if (err) {
    return console.error(err.message);
  }
  console.log('Connected successfully.');
});

There is an optional parameter you can pass after the file path, which defines the mode in which the database is opened. There are three modes available to pick from:

1. sqlite3.OPEN_READONLY: open the database for read-only.
2. sqlite3.OPEN_READWRITE : open the database for reading and writting.
3. sqlite3.OPEN_CREATE: open the database, if the database does not exist, create a new database.

By default, SQLite uses the OPEN_READWRITE | OPEN_CREATE mode (modes can be concatenated using the pipe operator). This default mode means that if the database does not exist, a new database will be created (at the specified file path) and be ready for reading and writing.

Creating a Table

Here we create a table called “users” which will store some basic information about people, such as their name, email, and age. Each of these pieces of information is represented as a column in the table, and given a suitable datatype.

db.run(`CREATE TABLE IF NOT EXISTS users(
        name text,
        email text, 
        age integer
)`)

You can use the back-tick characters to denote a multiline string in JavaScript. You do not have to use a multiline string, but it does look better for the sake of readability, especially when creating large tables.

It is important to note that the db.run() function is asynchronous, and will return immediately, before the query has finished executing. So if a query about table creation is followed by a query selecting data from the table, there is likely to be an error, as the table has not been created when the second query runs.

There are two good ways of handling this. One, we use async functions. Or two, we can use the callbacks provided to us by the run function.

Using async functions:

async function create_table() {
    await db.run(`CREATE TABLE IF NOT EXISTS users(
        name text,
        email text, 
        age integer
    )`)
}

create_table() // Will not proceed to the next line till the function is complete

// Code to execute 

Using callbacks:

db.run(`CREATE TABLE IF NOT EXISTS users(
        name text,
        email text, 
        age integer
)`, () => {
    // Code to execute 
})

There is also a third option, which is a great choice for certain cases, called sequelize (will be covered later in this tutorial).

Inserting Data into a Table

Next, its time to add some data into our table. This can be done using the “INSERT INTO” statement. To simplify things, first we will write our query out as a string:

insert_query = "INSERT INTO users(name, email, age) VALUES(?, ?, ?)"

The format of this query is as follows:

query = "INSERT INTO <table-name>(<col1-name>, <col2-name>, <col3-name>, ...) VALUES(?, ?, ?, ...)"

The question marks here are placeholder values, whose purpose is to create a parameterized query, preventing SQL injections. The number of placeholders should be equal to the number of columns mentioned after the name of the table.

We will then defines the values we wish to enter as an array:

values = ["John", "[email protected]", 18]

Now we will pass both our query, and this list of values to the db.run function.

  insert_query = "INSERT INTO users(name, email, age) VALUES(?, ?, ?)"
  values = ["John", "[email protected]", 18]
  db.run(insert_query, values)

The values from the list will then automatically replace the placeholders in the query, in the order that they were defined (first value in the list replaces the first placeholder, and so on).

Retrieving Data from Tables

To retrieve records from the database, use the db.all function. This function takes three parameters, first the sql query, followed by a list of parameters (if any), and then a callback.

A simple SQL query would be to select all the details from the users table. This query can be constructed by using the SELECT keyword, followed by the columns we wish to fetch (or we can use * to retrieve all), followed by the FROM keyword and the name of the table we want to fetch the data from.

query = "SELECT <cols> FROM <table-name>"

Here is our query for the users table:

  db.all("SELECT * FROM users", [], (err, rows) => {
      console.log(rows)
  })

We don’t have any parameters in this query (placeholders), hence the second parameter is an empty array. The third parameter is our callback, which executes after the data has been fetched.

Our data:

[
  { name: 'John', email: '[email protected]', age: 18 },
]

To include parameters in your queries (e.g. you want to retrieve a specific user), you can do the following:

  db.all("SELECT * FROM users WHERE name=? AND age=?", ["John", 18], (err, rows) => {
      console.log(rows)
  })

Updating Data

At this point, you should be familiar with the nuances of the sqlite3 library in Node.js. Anything after this simply requires a good command of SQL commands.

To update a record in the table (e.g. change name) we can use the UPDATE – SET – WHERE chain of statements.

  let sql = `UPDATE users
             SET name = ?
             WHERE name = ?`;
          
  db.run(sql, ["Smith", "John"], (err, rows) => {
      db.all("SELECT * FROM users", [], (err, rows) => {
          console.log(rows)
      })
  })

We add a callback which prints out the contents of the user table after the update query has been executed, which shows us the following:

[
  { name: 'Smith', email: '[email protected]', age: 18 },
]

Deleting Data

We will now come full circle, and delete the record we entered to begin with.

This is what the record currently looks like (after updating the name):

[
  { name: 'Smith', email: '[email protected]', age: 18 },
]

We will now delete this user. Ideally, we should have created an “id” field which uniquely identifies each user. But we will have to make do. We can assume that the emails are unique (since there can’t be duplicates) and delete on the basis of the email.

    let sql = `DELETE FROM users WHERE email=?`
    db.run(sql, ["[email protected]"], (err) => {
        if (err) {
            return console.log(err)
        }
        db.all("SELECT * FROM users", [], (err, rows) => {
            console.log(rows)
        })
    })

As you can from the output:

[]

There are no more records in the table.

This marks the end of the Javascript SQLite3 Tutorial for Beginners Tutorial. Any suggestions or contributions for CodersLegacy are more than welcome. Questions regarding the tutorial content can be asked in the comments section below.

Share on Facebook

Tweet

Follow us