Skip to content

How to work with SQLite in Node.js

In this article, we will learn how to work with SQLite in Node.js.

What is SQLite?

SQLite is a lightweight, serverless, self-contained, and zero-configuration SQL database engine. It is widely used in embedded systems, mobile applications, and small-scale websites due to its simplicity and ease of use.

Setup Project

First, let’s create a new Node.js project and install the sqlite3 package using npm.

mkdir sqlite-nodejs
cd sqlite-nodejs

npm i -y
npm install express sqlite3

Create a Database

Create a new file called database.db. This will be our SQLite database file.

To initialize it with some data, use a SQLite client, or you can use Node.js to create the table programmatically.

Implement app.js file

// app.js

const express = require("express");
const sqlite3 = require("sqlite3").verbose();
const app = express();
const PORT = 3000;

// Middleware to parse JSON
app.use(express.json());

// Connect to SQLite database
const db = new sqlite3.Database("./database.db", (err) => {
  if (err) {
    console.error("Error connecting to SQLite database:", err.message);
  } else {
    console.log("Connected to SQLite database.");
  }
});

// Create a table (if it doesn't exist)
db.run(
  `CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT
  )`
);

// Routes

// Get all users
app.get("/users", (req, res) => {
  db.all("SELECT * FROM users", [], (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message });
    } else {
      res.json({ users: rows });
    }
  });
});

// Get a single user by ID
app.get("/users/:id", (req, res) => {
  const { id } = req.params;
  db.get("SELECT * FROM users WHERE id = ?", [id], (err, row) => {
    if (err) {
      res.status(500).json({ error: err.message });
    } else {
      res.json({ user: row });
    }
  });
});

// Create a new user
app.post("/users", (req, res) => {
  const { name, email } = req.body;
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", [name, email], function (err) {
    if (err) {
      res.status(500).json({ error: err.message });
    } else {
      res.json({ id: this.lastID });
    }
  });
});

// Update a user
app.put("/users/:id", (req, res) => {
  const { id } = req.params;
  const { name, email } = req.body;
  db.run(
    "UPDATE users SET name = ?, email = ? WHERE id = ?",
    [name, email, id],
    function (err) {
      if (err) {
        res.status(500).json({ error: err.message });
      } else {
        res.json({ changes: this.changes });
      }
    }
  );
});

// Delete a user
app.delete("/users/:id", (req, res) => {
  const { id } = req.params;
  db.run("DELETE FROM users WHERE id = ?", [id], function (err) {
    if (err) {
      res.status(500).json({ error: err.message });
    } else {
      res.json({ deletedID: id });
    }
  });
});

// Start server
app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});

Run and Test the Application

node app.js

Check the following routes:

# Get all users: 
GET http://localhost:3000/users

# Get user by ID: 
GET http://localhost:3000/users/:id

# Create a user: 
POST http://localhost:3000/users 
# with JSON body {"name": "John Doe", "email": "john@example.com"}

# Update a user: 
PUT http://localhost:3000/users/:id 
# with JSON body {"name": "Jane Doe", "email": "jane@example.com"}

# Delete a user: 
DELETE http://localhost:3000/users/:id