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