How to use an SQLite database with Node.js

SQLite is a lightweight and serverless database that we can easily use in our Node.js applications.

With Node.js and the sqlite3 package, you can quickly and easily connect your application to a SQLite database.

How to connect Node.js to SQLite

To get started, make sure you have Node.js installed on your system and then install the sqlite3 package using NPM:

npm install sqlite3

Setting up an in-memory DB

Similar to other database examples, we will create a file to handle the connection to SQLite. For example, let’s call it db.js.

import sqlite from 'sqlite3';

// Create a connection to an in-memory database
const db = new sqlite.Database(':memory:');

// Database operations
db.serialize(() => {
    // Create a table
    db.run("CREATE TABLE lorem (info TEXT)");

    // Insert data into the table
    const stmt = db.prepare("INSERT INTO lorem VALUES (?)");
    for (let i = 0; i < 10; i++) {
        stmt.run("Ipsum " + i);
    }
    stmt.finalize();

    // Query data from the table
    db.each("SELECT rowid AS id, info FROM lorem", (err, row) => {
        console.log(row.id + ": " + row.info);
    });
});

// Close the connection when you're done
db.close();

In this example, we are creating a SQLite database in memory (:memory:) and performing basic CRUD operations on it.

Creating a DB in a file

If you prefer to use a physical database file instead of an in-memory database, you can do it like this:

import sqlite from 'sqlite3';
const dbPath = './database.db'; // Path to the database file

// Connect to the database in a physical file
const db = new sqlite.Database(dbPath);

// The rest of the code is similar to the previous example
// ...

In this case, we are specifying a path to the database file instead of using the in-memory database.

CRUD Operations

Once we have the connection file, we can use it to perform CRUD (Create, Read, Update, Delete) operations on our SQLite database from Node.js.

Creating Data

First, let’s see how to insert new data into the database:

async function insertData(name, email) {
  try {
    // Insert data into the users table
    await db.run("INSERT INTO users (name, email) VALUES (?, ?)", [name, email]);
    console.log('Data inserted successfully');
  } catch (error) {
    console.error('Error inserting data:', error);
  }
}

insertData('John Doe', 'john@example.com'); // Execute the insertData function with the provided data

Reading Data

Now let’s see how to get all users from the database:

async function fetchData() {
  try {
    // Query all users from the users table
    const users = await db.all("SELECT * FROM users");
    console.log('All users:', users);
  } catch (error) {
    console.error('Error fetching data:', error);
  }
}

fetchData(); // Execute the fetchData function to get all users

Updating Data

Let’s see how we would update data in the database:

async function updateData(userId, newName) {
  try {
    // Update the name of the user with the specified userId
    await db.run("UPDATE users SET name = ? WHERE id = ?", [newName, userId]);
    console.log('Data updated successfully');
  } catch (error) {
    console.error('Error updating data:', error);
  }
}

updateData(1, 'Jane Smith'); // Execute the updateData function with the provided data

Deleting Data

Finally, let’s see how to delete data from the database:

async function deleteData(userId) {
  try {
    // Delete the user with the specified userId
    await db.run("DELETE FROM users WHERE id = ?", [userId]);
    console.log('Data deleted successfully');
  } catch (error) {
    console.error('Error deleting data:', error);
  }
}

deleteData(2); // Execute the deleteData function with the ID of the user to delete

Download the code

All the code from this post is available for download on GitHub