How to use an SQLite database with Node.js

SQLite is a lightweight, serverless database that can be easily used in Node.js applications.

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

Connecting 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 done
db.close();

In this example, we are creating an in-memory SQLite database (: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 so as follows:

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.

Data Creation

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

Data Reading

Now let’s see how to retrieve 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 getting data:', error);
  }
}

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

Data Update

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

Data Deletion

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