Node.js and Express course menu

Node.js Database Tutorial: SQLite with Express

Store API data in a real database: Node's built-in SQLite module, creating tables, inserting and querying with prepared statements, preventing SQL injection, updating and deleting, transactions, connecting it to Express routes, and when to choose PostgreSQL, MySQL or MongoDB.

Intermediate13 min readUpdated October 25, 2026

On this page

Open a database and create a table
Insert and query with prepared statements
Why placeholders matter: SQL injection
Constraints in action
Transactions
Connecting the database to Express
Choosing a database
Common mistakes
Practice
Key takeaway

Data kept in memory disappears when the server restarts, and JSON files break when two requests write at once. Real applications store data in a database. This lesson uses SQLite, a full SQL database stored in a single file, which Node.js includes as the built-in node:sqlite module, so there is nothing to install. The SQL you write is the same you would use with PostgreSQL or MySQL; if SQL is new to you, see the SQL course.

Open a database and create a table#

db-setup.js

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("shop.db");          // creates the file if it does not exist

db.exec(`
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    price REAL NOT NULL CHECK (price > 0),
    stock INTEGER NOT NULL DEFAULT 0
  )
`);
console.log("Tables:", db.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'products'").all());
db.close();

Terminal

node db-setup.js

Output

Tables: [ [Object: null prototype] { name: 'products' } ]

exec runs SQL that returns no rows. The constraints (NOT NULL, UNIQUE, CHECK) make the database itself reject bad data, a second line of defence after validation.

Insert and query with prepared statements#

Prepared statements have placeholders for values; the values are passed separately:

db-crud.js

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("shop.db");
const insert = db.prepare("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)");

for (const [name, price, stock] of [["Notebook", 60, 120], ["Gel pen", 25, 0], ["Backpack", 1299, 8]]) {
  const result = insert.run(name, price, stock);
  console.log(`Inserted ${name} with id ${result.lastInsertRowid}`);
}

console.log(db.prepare("SELECT * FROM products WHERE id = ?").get(3));
console.log(db.prepare("SELECT name, price FROM products WHERE stock > ? ORDER BY price").all(0));

const update = db.prepare("UPDATE products SET stock = stock + :amount WHERE name = :name");
console.log(update.run({ amount: 50, name: "Gel pen" }));

console.log(db.prepare("DELETE FROM products WHERE stock = 0").run().changes, "rows deleted");
db.close();

Terminal

node db-crud.js

Output

Inserted Notebook with id 1
Inserted Gel pen with id 2
Inserted Backpack with id 3
[Object: null prototype] {
  id: 3,
  name: 'Backpack',
  price: 1299,
  stock: 8
}
[
  [Object: null prototype] { name: 'Notebook', price: 60 },
  [Object: null prototype] { name: 'Backpack', price: 1299 }
]
{ changes: 1, lastInsertRowid: 3 }
0 rows deleted

run() executes a statement and returns changes (rows affected) and lastInsertRowid.
get() returns the first row (or undefined); all() returns every row as an array of objects.
Placeholders are ? (by position) or :name (by name, with an object).

Rows are plain objects created without a prototype, which console.log shows as [Object: null prototype]; use them like any object (and res.json sends them normally). The UPDATE's lastInsertRowid is just the id of the last insert and only matters after an INSERT. No rows were deleted at the end because the gel pen had been restocked one step earlier.

Why placeholders matter: SQL injection#

Never build SQL by inserting user input into a string. Input such as ' OR '1'='1 changes the meaning of the query:

injection.js

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("shop.db");
const userInput = "nothing' OR '1'='1";

const unsafe = db.prepare(`SELECT name FROM products WHERE name = '${userInput}'`).all();
console.log("String building returned", unsafe.length, "rows:", unsafe.map((r) => r.name));

const safe = db.prepare("SELECT name FROM products WHERE name = ?").all(userInput);
console.log("Placeholder returned", safe.length, "rows");
db.close();

Terminal

node injection.js

Output

String building returned 3 rows: [ 'Backpack', 'Gel pen', 'Notebook' ]
Placeholder returned 0 rows

The string-built query returned every product, because the input became part of the SQL. With a placeholder, the input is only ever treated as a value. The same attack can read private data or delete tables, so always use placeholders.

Constraints in action#

constraints.js

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("shop.db");
const insert = db.prepare("INSERT INTO products (name, price) VALUES (?, ?)");

for (const [name, price] of [["Notebook", 70], ["Ruler", -5]]) {
  try {
    insert.run(name, price);
  } catch (err) {
    console.log(`${name}: ${err.message}`);
  }
}
db.close();

Terminal

node constraints.js

Output

Notebook: UNIQUE constraint failed: products.name
Ruler: CHECK constraint failed: price > 0

An API catches these errors and turns them into 409 Conflict (duplicate) or 400 Bad Request responses.

Transactions#

A transaction groups several statements so that either all succeed or none do. Placing an order must reduce stock and record the order together:

transaction.js

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("shop.db");
db.exec("CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, product_id INTEGER, qty INTEGER)");

function placeOrder(productId, qty) {
  db.exec("BEGIN");
  try {
    const { changes } = db.prepare("UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?").run(qty, productId, qty);
    if (changes === 0) throw new Error("Not enough stock");
    db.prepare("INSERT INTO orders (product_id, qty) VALUES (?, ?)").run(productId, qty);
    db.exec("COMMIT");
    return "Order placed";
  } catch (err) {
    db.exec("ROLLBACK");
    return `Order failed: ${err.message}`;
  }
}

console.log(placeOrder(3, 5));
console.log(placeOrder(3, 5));
console.log(db.prepare("SELECT stock FROM products WHERE id = 3").get());
console.log(db.prepare("SELECT COUNT(*) AS orders FROM orders").get());
db.close();

Terminal

node transaction.js

Output

Order placed
Order failed: Not enough stock
[Object: null prototype] { stock: 3 }
[Object: null prototype] { orders: 1 }

The second order failed and was rolled back, so stock and orders stayed consistent. The AND stock >= ? condition also prevents two simultaneous orders from overselling.

Connecting the database to Express#

api.js

import express from "express";
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("shop.db");
const app = express();
app.use(express.json());

app.get("/api/products", (req, res) => {
  res.json(db.prepare("SELECT * FROM products ORDER BY name").all());
});

app.get("/api/products/:id", (req, res) => {
  const product = db.prepare("SELECT * FROM products WHERE id = ?").get(Number(req.params.id));
  if (!product) return res.status(404).json({ error: "Product not found" });
  res.json(product);
});

app.post("/api/products", (req, res) => {
  const { name, price, stock = 0 } = req.body;
  try {
    const { lastInsertRowid } = db.prepare("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)").run(name, price, stock);
    res.status(201).json(db.prepare("SELECT * FROM products WHERE id = ?").get(lastInsertRowid));
  } catch (err) {
    if (err.message.includes("UNIQUE")) return res.status(409).json({ error: "A product with this name exists" });
    res.status(400).json({ error: "Invalid product" });
  }
});

app.listen(3000);

Terminal

curl -s localhost:3000/api/products
curl -s -w " [%{http_code}]\n" -X POST localhost:3000/api/products -H "Content-Type: application/json" -d '{"name":"Desk lamp","price":899,"stock":4}'
curl -s -w " [%{http_code}]\n" -X POST localhost:3000/api/products -H "Content-Type: application/json" -d '{"name":"Desk lamp","price":899}'
curl -s localhost:3000/api/products/4

Output

[{"id":3,"name":"Backpack","price":1299,"stock":3},{"id":2,"name":"Gel pen","price":25,"stock":50},{"id":1,"name":"Notebook","price":60,"stock":120}]
{"id":4,"name":"Desk lamp","price":899,"stock":4} [201]
{"error":"A product with this name exists"} [409]
{"id":4,"name":"Desk lamp","price":899,"stock":4}

Restart the server and the data is still there, because it lives in shop.db. (A real API would also validate the body with a schema, as in Validation, before touching the database.)

node:sqlite is synchronous, which is fine for SQLite's fast local queries. Drivers for network databases (PostgreSQL, MySQL) are asynchronous: you await each query.

Choosing a database#

Database	Type	Good for
SQLite	SQL, single file	learning, small apps, prototypes, desktop and mobile apps
PostgreSQL	SQL server	most web applications; rich features, reliable
MySQL / MariaDB	SQL server	web applications, widely hosted
MongoDB	document (JSON-like)	flexible, nested data structures
Redis	in-memory key-value	caching, sessions, rate limits, queues

Popular Node.js libraries: pg (PostgreSQL), mysql2, the official mongodb driver or Mongoose, and ORMs/query builders such as Prisma, Drizzle and Knex, which generate SQL and help with migrations.

Common mistakes#

Practice#

Practice exercise

Using an in-memory database (new DatabaseSync(":memory:")), create a students table with name and marks, insert four students with a prepared statement, and print the average marks (1 decimal) and the top student using SQL.

Show hint

SELECT ROUND(AVG(marks), 1) AS average FROM students and SELECT name, marks FROM students ORDER BY marks DESC LIMIT 1.

Show solution

students.js

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");
db.exec("CREATE TABLE students (name TEXT NOT NULL, marks INTEGER NOT NULL)");
const insert = db.prepare("INSERT INTO students (name, marks) VALUES (?, ?)");
for (const [name, marks] of [["Aarav", 78], ["Diya", 92], ["Kabir", 65], ["Sara", 88]]) insert.run(name, marks);

console.log(db.prepare("SELECT ROUND(AVG(marks), 1) AS average FROM students").get());
console.log(db.prepare("SELECT name, marks FROM students ORDER BY marks DESC LIMIT 1").get());

Terminal

node students.js

Output

[Object: null prototype] { average: 80.8 }
[Object: null prototype] { name: 'Diya', marks: 92 }

Key takeaway#

Next: Structuring an Express Project.

Finished this lesson?

Node.js and ExpressBuild a REST API with Node.js and Express (CRUD Example)Design and build a complete REST API in Express: resources and URLs, CRUD with GET, POST, PUT, PATCH and DELETE, validation, filtering, sorting and pagination, consistent responses, and testing every endpoint with curl.Intermediate9 min read Node.js and ExpressHow to Structure an Express.js Project (Routes, Controllers, Services)Organise a growing Express API: a folder structure with routes, controllers, services and data access, separating the app from the server for testing, configuration in one module, shared middleware and errors, and how requests flow through the layers.Intermediate10 min read

Open a database and create a table#

Insert and query with prepared statements#

Why placeholders matter: SQL injection#

Constraints in action#

Transactions#

Connecting the database to Express#

Choosing a database#

Common mistakes#

Practice#

Key takeaway#

Related lessons