Databases with Node.js

Store data in a real database from Node.js: connect to SQLite with the built-in node:sqlite module, create tables, insert and query with prepared statements, prevent SQL injection, use transactions, wire a database into an Express API, and know where PostgreSQL drivers and ORMs fit.

Lesson 18 of 43Advanced18 min read

Arrays in memory disappear every time a server restarts, and they can't be shared between several server instances. Real applications keep their data in a database, which stores it safely on disk, lets many users read and write at once, and answers questions such as "the ten newest orders over £50" efficiently.

Node.js works with every popular database. Relational databases such as PostgreSQL, MySQL and SQLite store data in tables and are queried with SQL, covered in the SQL tutorial. Document databases such as MongoDB store JSON-like documents, covered in the MongoDB tutorial. You talk to a database through a driver package, or through an ORM or query builder that adds conveniences on top.

This lesson uses SQLite through Node's built-in node:sqlite module (Node 22.5 and later), so there's nothing to install and no server to run. The same ideas apply directly to PostgreSQL and MySQL: you'll create tables, insert and query data with prepared statements, see exactly why building SQL from strings is dangerous, group changes in transactions, and organise database code in a repository module used by an Express API.

Connecting and querying

JavaScript
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync('shop.db');       // or ':memory:' for a temporary database
db.exec(`CREATE TABLE IF NOT EXISTS products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL CHECK (price > 0)
)`);

const insert = db.prepare('INSERT INTO products (name, price) VALUES (?, ?)');
insert.run('Mug', 8.5);

const byId = db.prepare('SELECT * FROM products WHERE id = ?');
console.log(byId.get(1));                     // { id: 1, name: 'Mug', price: 8.5 }
  • exec runs SQL that returns nothing, such as creating tables.
  • prepare compiles a statement with placeholders (? or named like :name).
  • run executes a change and reports changes and lastInsertRowid; get returns the first row; all returns every row.

Prepared statements and SQL injection

Never build SQL by joining strings with user input:

JavaScript
// DANGEROUS
db.exec(`SELECT * FROM users WHERE email = '${req.query.email}'`);

If someone sends ' OR '1'='1, the query changes meaning and returns every user. This is SQL injection, one of the most damaging and common security bugs. Placeholders fix it completely: the database receives the SQL and the values separately, so values can never be interpreted as SQL.

JavaScript
db.prepare('SELECT * FROM users WHERE email = ?').get(req.query.email);

Every driver supports placeholders: $1 in PostgreSQL's pg, ? in MySQL and SQLite.

Transactions, pools and ORMs

A transaction groups several changes so they all succeed or all fail: placing an order must both create the order and reduce stock, never just one of them. Wrap the statements in BEGIN and COMMIT, and ROLLBACK if anything throws.

For PostgreSQL, the pg package provides a connection pool, a set of reusable connections shared by all requests: const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL }), then await pool.query('SELECT ... WHERE id = $1', [id]).

Many teams add an ORM or query builder such as Prisma, Drizzle, Kysely, Sequelize or Knex. They generate SQL from code, provide types in TypeScript, and manage migrations, versioned scripts that change the database structure step by step.

Node.js Advanced Syntax

Database essentials

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)');
const { lastInsertRowid } = db.prepare('INSERT INTO t (name) VALUES (?)').run('Ada');
const row = db.prepare('SELECT * FROM t WHERE id = ?').get(1);
const rows = db.prepare('SELECT * FROM t WHERE name LIKE :q').all({ q: '%a%' });
db.exec('BEGIN'); /* ... */ db.exec('COMMIT');   // or ROLLBACK on error

// PostgreSQL with pg
const { rows: users } = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Node.js Advanced Summary

  • Databases store data safely and let many users read and write at once.
  • node:sqlite's DatabaseSync runs SQLite with no install; exec, prepare, run, get and all cover most needs.
  • Always use placeholders in prepared statements; string-built SQL allows SQL injection.
  • Transactions make groups of changes all succeed or all fail.
  • Use a pool with pg for PostgreSQL, consider an ORM, and keep queries in a repository module.

Node.js Advanced Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Creating, inserting and querying

Node.js
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price REAL NOT NULL CHECK (price > 0)
  )
`);

const insert = db.prepare('INSERT INTO products (name, category, price) VALUES (?, ?, ?)');
for (const [name, category, price] of [['Mug', 'kitchen', 8.5], ['Lamp', 'home', 24], ['Kettle', 'kitchen', 29.99], ['Rug', 'home', 79]]) {
  insert.run(name, category, price);
}

const result = insert.run('Pen', 'office', 1.2);
console.log(`New id: ${result.lastInsertRowid}, rows changed: ${result.changes}`);

console.log(db.prepare('SELECT name, price FROM products WHERE id = ?').get(3));
const kitchen = db.prepare('SELECT name FROM products WHERE category = :cat ORDER BY price DESC').all({ cat: 'kitchen' });
console.log(kitchen.map((r) => r.name));
console.log(db.prepare('SELECT category, COUNT(*) AS n, ROUND(AVG(price), 2) AS avg FROM products GROUP BY category ORDER BY category').all());

try {
  insert.run('Broken', 'home', -5);
} catch (err) {
  console.log(`Rejected: ${err.message}`);
}
Output
New id: 5, rows changed: 1
[Object: null prototype] { name: 'Kettle', price: 29.99 }
[ 'Kettle', 'Mug' ]
[
  [Object: null prototype] { category: 'home', n: 2, avg: 51.5 },
  [Object: null prototype] { category: 'kitchen', n: 2, avg: 19.24 },
  [Object: null prototype] { category: 'office', n: 1, avg: 1.2 }
]
Rejected: CHECK constraint failed: price > 0
Line-by-line explanation
  1. Line 3':memory:' creates a temporary database; pass a file name to keep the data.
  2. Line 9A CHECK constraint lets the database itself refuse invalid prices.
  3. Line 13prepare compiles the INSERT once, and run executes it with new values each time.
  4. Line 18run reports the new row's id and how many rows changed.
  5. Line 21get returns a single row; rows are plain objects without a prototype.
  6. Line 22Named placeholders like :cat take an object of values.
  7. Lines 26 to 30The database rejects data that breaks its rules, even if the app forgot to check.
Example 2

SQL injection and the fix

Node.js
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, role TEXT)");
db.exec("INSERT INTO users (email, role) VALUES ('ada@example.com', 'admin'), ('ben@example.com', 'customer')");

const attack = "nobody@example.com' OR '1'='1";

const unsafeSql = `SELECT email, role FROM users WHERE email = '${attack}'`;
const leaked = db.prepare(unsafeSql).all();
console.log(`String-built query returned ${leaked.length} users`);

const safe = db.prepare('SELECT email, role FROM users WHERE email = ?').all(attack);
console.log(`Prepared statement returned ${safe.length} users`);

const real = db.prepare('SELECT email, role FROM users WHERE email = ?').get('ben@example.com');
console.log(real.role);
Output
String-built query returned 2 users
Prepared statement returned 0 users
customer
Line-by-line explanation
  1. Line 7A malicious "email" containing SQL.
  2. Lines 9 to 10Pasting it into the SQL string changes the query into "email = ... OR true", leaking every user.
  3. Line 13With a placeholder, the whole input is treated as a single text value and matches nothing.
  4. Line 16Normal values work exactly as expected.
Example 3

A transaction for placing an order

Node.js
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE stock (product TEXT PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0));
  CREATE TABLE orders (id INTEGER PRIMARY KEY, product TEXT, qty INTEGER);
  INSERT INTO stock VALUES ('Mug', 3), ('Lamp', 1);
`);

function placeOrder(product, qty) {
  db.exec('BEGIN');
  try {
    db.prepare('INSERT INTO orders (product, qty) VALUES (?, ?)').run(product, qty);
    db.prepare('UPDATE stock SET qty = qty - ? WHERE product = ?').run(qty, product);
    db.exec('COMMIT');
    return 'placed';
  } catch (err) {
    db.exec('ROLLBACK');
    return `failed (${err.message})`;
  }
}

console.log(placeOrder('Mug', 2));
console.log(placeOrder('Lamp', 5));
console.log(db.prepare('SELECT * FROM stock ORDER BY product').all().map((r) => `${r.product}: ${r.qty}`).join(', '));
console.log(`Orders saved: ${db.prepare('SELECT COUNT(*) AS n FROM orders').get().n}`);
Output
placed
failed (CHECK constraint failed: qty >= 0)
Lamp: 1, Mug: 1
Orders saved: 1
Line-by-line explanation
  1. Line 11BEGIN starts a transaction; nothing is final until COMMIT.
  2. Lines 13 to 14The order is inserted, then stock is reduced.
  3. Line 15COMMIT makes both changes permanent together.
  4. Line 18If the stock update fails, ROLLBACK also undoes the order insert.
  5. Line 26The failed Lamp order left no half-finished data behind: only one order exists.

Common Node.js Advanced Mistakes

Building SQL with template strings

Don't
db.prepare(`SELECT * FROM products WHERE name = '${req.query.name}'`).all();
Do
db.prepare('SELECT * FROM products WHERE name = ?').all(req.query.name);

Creating a new connection per request

Don't
app.get('/users', async (req, res) => {
  const client = new pg.Client(process.env.DATABASE_URL);   // slow, and exhausts connections
  await client.connect();
});
Do
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
app.get('/users', async (req, res) => res.json((await pool.query('SELECT id, name FROM users')).rows));

Related changes without a transaction

Don't
await createOrder(order);
await reduceStock(order);         // if this fails, the order exists but stock wasn't reduced
Do
await db.transaction(async (tx) => {
  await createOrder(order, tx);
  await reduceStock(order, tx);
});

Node.js Advanced in Real Projects

Databases in Node applications

Most production Node APIs use PostgreSQL through pg, Prisma or Drizzle, often hosted on services such as Neon, Supabase or Amazon RDS, with the connection string in DATABASE_URL. SQLite is popular for small apps, tools, tests and edge deployments, and MongoDB with the official driver or Mongoose is common for document-shaped data. Migrations run automatically during deployment so every environment has the same schema.

Node.js Advanced FAQ

Is node:sqlite ready for production use?

In Node 22 it works, but it's still marked experimental and prints a warning when you first load it, so its API may change between versions. It's a convenient choice for learning, small tools and tests. For production apps, many teams use the long-established better-sqlite3 package for SQLite, or a server database such as PostgreSQL.

Should I use an ORM or write SQL myself in Node.js?

Plain SQL with placeholders gives you full control and nothing new to learn beyond SQL itself. An ORM or query builder such as Prisma, Drizzle or Knex adds migrations, TypeScript types and less repetitive code, at the cost of another layer to understand. Small projects do well with either; whichever you choose, keep the queries in one repository module.

How many connections should a database pool have?

Start with the driver's default (for pg it's 10) and measure. More connections don't automatically mean more speed, because the database server has its own limit and each connection uses memory there. Remember that the total is the pool size multiplied by the number of app instances you run.

How do I connect Node.js to MongoDB?

Install the official mongodb driver and create one MongoClient with your connection string from an environment variable, then reuse it for the life of the app. Many projects use Mongoose on top of it to define schemas and validation. The rule about never building queries from raw user input applies here too: validate types so a user can't send an object like { "$ne": null } where you expected a string.

Why do I get "database is locked" with SQLite?

SQLite allows only one writer at a time, so a long write or an unfinished transaction can make other writes wait and eventually fail. Keep transactions short, always COMMIT or ROLLBACK them, and use a single connection per process. If many processes write at once, a server database such as PostgreSQL is a better fit.

Node.js Advanced Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query so the value is passed safely.

db.prepare('SELECT * FROM users WHERE id = ').get(userId);
Multiple choiceeasy

Which method returns every matching row from a prepared statement?

Multiple choicemedium

What is SQL injection?

Predict the outputmedium

A transaction inserts an order and then the stock update throws. After ROLLBACK, how many new orders exist?

Multiple choicehard

Why use a connection pool with PostgreSQL?

Node.js Advanced Quiz

5 questions to check what you learned.

  1. Which built-in module gives Node SQLite support?

    • node:sql
    • node:db
    • node:database
    • node:sqlite

  2. What does run() return after an INSERT?

    • The inserted row
    • true
    • Nothing
    • An object with changes and lastInsertRowid

  3. What's a database migration?

    • Moving to a new server
    • A versioned script that changes the database structure step by step
    • A backup
    • A slow query

  4. What placeholder style does the pg driver use?

    • ?
    • :name only
    • $1, $2 and so on
    • {0}

  5. Why put queries in a repository module?

    • Queries live in one place, are easy to change and can be replaced with fakes in tests
    • SQL only runs there
    • It's faster
    • ORMs require it

Related Node.js topics

13REST APIDesign and build a complete REST API with Express: resource-based URLs, CRUD with GET, POST, PUT, PATCH and DELETE, correct status codes, pagination, filtering and sorting, consistent JSON responses, and a service layer that keeps routes thin.20SecurityProtect a Node.js API from common attacks: injection, XSS, path traversal, brute force and oversized requests, with security headers, rate limiting, careful CORS, safe secrets handling, dependency audits and the principle of least privilege.19AuthenticationLet users sign up and sign in securely: hash passwords with scrypt, compare them safely, issue signed tokens or sessions, store them in secure cookies, protect routes with middleware, and understand when to use sessions, JWTs or an identity provider.
Esc
↑↓ to navigateEnter to open