SQLite CRUD Tutorials in JavaScript/Node.js: A Step-by-Step Guide

SQLite is a lightweight, file-based relational database management system that can be easily integrated with JavaScript and Node.js applications. In this tutorial, we will explore how to perform CRUD (Create, Read, Update, Delete) operations with SQLite using JavaScript and Node.js. By the end of this article, you’ll have a solid understanding of how to interact with an SQLite database from your JavaScript applications.

Prerequisites

1. Node.js installed on your machine.
2. Basic knowledge of JavaScript and Node.js.

Step 1: Install Required Packages

Before we start working with SQLite, let’s make sure we have the necessary packages installed. We’ll use the sqlite3 package for database operations.

npm install sqlite3

Step 2: Create and Connect to the Database

In your Node.js application, you need to create a SQLite database and establish a connection to it. Create a JavaScript file (e.g., db.js) and add the following code:

const sqlite3 = require("sqlite3").verbose()

// Create and connect to the SQLite database
const db = new sqlite3.Database(
  "mydatabase.db",
  sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE,
  (err) => {
    if (err) {
      console.error("Error connecting to SQLite database:", err)
    } else {
      console.log("Connected to SQLite database")
    }
  }
)

module.exports = db

This code creates a new SQLite database file named mydatabase.db and establishes a connection to it.

Step 3: Create a Table (Create - C)

Now, let’s create a table to store our data. Create a JavaScript file (e.g., createTable.js) and add the following code:

const db = require("./db")

// Create a 'users' table
db.run(
  `CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  email TEXT
)`,
  (err) => {
    if (err) {
      console.error("Error creating table:", err)
    } else {
      console.log("Table created: users")
    }
  }
)

This code defines a table schema for storing user information.

Step 4: Insert Data (Create - C)

To insert data into the database, create a JavaScript file (e.g., insertData.js) and add the following code:

const db = require("./db")

// Insert a new user into the 'users' table
db.run(
  "INSERT INTO users (name, email) VALUES (?, ?)",
  ["John Doe", "[email protected]"],
  function (err) {
    if (err) {
      console.error("Error inserting data:", err)
    } else {
      console.log(`New record added with ID: ${this.lastID}`)
    }
  }
)

This code inserts a new user into the ‘users’ table and retrieves the last inserted ID.

Step 5: Retrieve Data (Read - R)

To read data from the database, create a JavaScript file (e.g., retrieveData.js) with the following code:

const db = require("./db")

// Retrieve all users from the 'users' table
db.all("SELECT * FROM users", [], (err, rows) => {
  if (err) {
    console.error("Error retrieving data:", err)
  } else {
    rows.forEach((row) => {
      console.log(`ID: ${row.id}, Name: ${row.name}, Email: ${row.email}`)
    })
  }
})

This code retrieves all user records from the ‘users’ table and prints them to the console.

Step 6: Update Data (Update - U)

To update existing data, create a JavaScript file (e.g., updateData.js) with the following code:

const db = require("./db")

// Update a user's email based on their ID
db.run(
  "UPDATE users SET email = ? WHERE id = ?",
  ["[email protected]", 1],
  function (err) {
    if (err) {
      console.error("Error updating data:", err)
    } else {
      console.log(`Record updated: ${this.changes} rows affected`)
    }
  }
)

This code updates a user’s email based on their ID.

Step 7: Delete Data (Delete - D)

To delete data from the database, create a JavaScript file (e.g., deleteData.js) with the following code:

const db = require("./db")

// Delete a user based on their ID
db.run("DELETE FROM users WHERE id = ?", [1], function (err) {
  if (err) {
    console.error("Error deleting data:", err)
  } else {
    console.log(`Record deleted: ${this.changes} rows affected`)
  }
})

This code deletes a user record based on their ID.

Conclusion

In this article, we’ve covered the fundamental CRUD operations using SQLite with JavaScript and Node.js. You’ve learned how to

create and connect to a database, create tables, insert, retrieve, update, and delete data. SQLite is a lightweight and versatile database that can be used for various applications, especially when you need a local, file-based database solution. Remember to handle errors gracefully and adapt these examples to suit your specific project requirements.