Connecting a Database

Reviewed & published by Brayan K

A database makes your data persist on disk so it survives restarts, replacing the in-memory arrays you've used so far with real storage you query using SQL.

Part of the free Node.js course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

By the end of this lesson you'll open a SQLite database with Node's built-in node:sqlite, create a table, insert and read rows safely with parameter placeholders, and wire it into an Express API.

What You'll Learn in This Lesson

• Why a database persists data where an array can't
• Opening a SQLite database with node:sqlite
• Creating a table with CREATE TABLE
• Inserting and reading rows with prepared statements
• Using ? placeholders to prevent SQL injection
• Wiring a database into an Express API

1️⃣ Opening a Database and Creating a Table

First you open a database (here, a single file on disk) and create a table to hold your data. A table has typed columns; CREATE TABLE IF NOT EXISTS makes setup safe to run every time your app starts.

// A DATABASE makes your data PERSIST — it survives restarts, unlike an
// in-memory array. We'll use SQLite, which is built into modern Node as
// the "node:sqlite" module (no install, no separate server).
const { DatabaseSync } = require("node:sqlite");

// Open (or create) a database file on disk.
const db = new DatabaseSync("app.db");

// Run SQL to create a table IF it doesn't already exist.
// A table is like a spreadsheet: columns with types, and rows of data.
db.exec(`
  CREATE TABLE IF NOT EXISTS books (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL
  )
`);

console.log("Database ready, books table exists.");

db.close();

2️⃣ Inserting and Reading Rows Safely

Use db.prepare() to compile a statement, then .run() to write, .get() for one row, or .all() for many. The ? placeholders bind your values safely — never build SQL by concatenating user input.

// Reading and writing rows. ALWAYS use parameter placeholders (?) instead
// of building SQL strings by hand — this prevents SQL-injection bugs.
const { DatabaseSync } = require("node:sqlite");
const db = new DatabaseSync("app.db");
db.exec("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT)");

// prepare() compiles a statement once; the ? is a safe placeholder.
const insert = db.prepare("INSERT INTO books (title) VALUES (?)");
insert.run("Dune");        // the string "Dune" replaces the ? safely
insert.run("1984");

// Query rows. .all() returns every matching row as an array of objects.
const rows = db.prepare("SELECT id, title FROM books").all();
console.log(rows);

// Look closely at what that prints: each row is shown as
//   [Object: null prototype] { id: 1, title: 'Dune' }
// That prefix is not an error and not something you did. node:sqlite builds
// each row with NO prototype, so it has no inherited toString, hasOwnProperty
// or constructor. That is deliberate: a column called "constructor" or
// "__proto__" then cannot collide with anything, which matters when the
// column names come from a schema you did not write. Read the values exactly
// as you would from any object; only Object.create(null) machinery differs.

// .get() returns a single row (or undefined). The ? is bound to 1.
const one = db.prepare("SELECT title FROM books WHERE id = ?").get(1);
console.log("First book:", one.title);

db.close();

The ? is the single most important habit here: it keeps data and SQL apart, which closes the door on SQL-injection attacks.

3️⃣ Wiring the Database into Express

Now swap the in-memory array from earlier lessons for real queries. Each route reads or writes the database, so your API's data finally persists between restarts.

// Putting it together: a tiny Express API backed by SQLite.
const express = require("express");
const { DatabaseSync } = require("node:sqlite");

const app = express();
app.use(express.json());

const db = new DatabaseSync("app.db");
db.exec("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT)");

// GET /books -> read all rows from the database.
app.get("/books", (req, res) => {
  const books = db.prepare("SELECT id, title FROM books").all();
  res.json(books);
});

// POST /books -> insert a new row, then return it.
app.post("/books", (req, res) => {
  if (!req.body.title) return res.status(400).json({ error: "title required" });
  const result = db.prepare("INSERT INTO books (title) VALUES (?)").run(req.body.title);
  // lastInsertRowid is the id the database assigned to the new row.
  const book = { id: Number(result.lastInsertRowid), title: req.body.title };
  res.status(201).json(book);
});

app.listen(3000, () => console.log("API with database on 3000"));

// Now data survives restarts — it lives in app.db, not in memory.

Your turn. Fill in the two ___ blanks to create a users table and insert a row, then run it.

// 🎯 YOUR TURN — finish the database code, then run: node store.js
const { DatabaseSync } = require("node:sqlite");
const db = new DatabaseSync("store.db");

// TODO 1: create a "users" table with id (primary key) and name (text).
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    ___  TEXT NOT NULL
  )
`);                          // 👉 the column name for the user's name

// TODO 2: insert a user named "Ada" using a ? placeholder.
db.prepare("INSERT INTO users (name) VALUES (?)").run(___);   // 👉 "Ada"

// Read it back.
const users = db.prepare("SELECT id, name FROM users").all();
console.log(users);

db.close();

// ✅ Expected output:
//    [ { id: 1, name: 'Ada' } ]

Mini-Challenge: A Persistent Scores Table

Create a scores table, insert two players, then query only those with more than 50 points, highest first. Use placeholders for every value.

// 🎯 MINI-CHALLENGE: a persistent "scores" table.
// Save as scores.js and run: node scores.js
//
// 1. Open a database file "game.db".
// 2. Create a table "scores" with columns: id (primary key),
//    player (TEXT), points (INTEGER).
// 3. Insert two players with different point totals (use ? placeholders).
// 4. Query and print only players with points greater than 50,
//    ordered highest first:  SELECT ... WHERE points > ? ORDER BY points DESC
//
// ✅ Example output (depends on your data):
//    [ { player: 'Sam', points: 90 } ]

const { DatabaseSync } = require("node:sqlite");

// your code here

Common Errors (and the fix)

• "Cannot find module 'node:sqlite'" — Your Node is too old. Update to the latest LTS (v22+), or use the better-sqlite3 package instead.
• "no such table" — You ran a query before creating the table. Run CREATE TABLE IF NOT EXISTS at startup, before any read or write.
• Wrong number of bindings — The count of ? placeholders must match the arguments you pass to .run()/.get().
• Building SQL with string concatenation — Never do "... = " + userInput. Use ? placeholders to stay safe from injection.
• Expecting an array from .get() — .get() returns one row or undefined; use .all() when you want every match.

Pro Tips

• 💡 Prepare once, run many times: store a prepared statement in a variable and reuse it in a loop — it's faster.
• 💡 Use lastInsertRowid: after an INSERT, result.lastInsertRowid gives the new row's id so you can return it.
• 💡 Keep secrets out of code: for real databases, put the connection string in an environment variable — the next lesson covers exactly that.

📋 Quick Reference — node:sqlite

🎉 Lesson Complete!

• ✅ A database persists data on disk where an array can't
• ✅ SQLite via node:sqlite needs no separate server
• ✅ CREATE TABLE IF NOT EXISTS sets up storage safely
• ✅ .run() writes, .get() reads one, .all() reads many
• ✅ Always use ? placeholders to prevent SQL injection

Practice quiz

Continue this course

• Previous: REST APIs with Express
• Next: Environment & Config — Manage secrets and settings with environment variables and .env files
• Quick reference: Node.js cheat sheet

Frequently asked questions

Why use a database instead of just an array in my code?

An in-memory array only exists while your program runs — restart the server and every record is gone. A database persists data to disk (or a separate server), so it survives restarts, crashes, and deploys. Databases also handle things arrays can't: efficient lookups over millions of rows, querying and filtering with SQL, enforcing structure with column types and constraints, and letting many parts of your app (or many servers) safely read and write the same data at once.

Do I need to install a database to follow this lesson?

Not for SQLite, which is what this lesson uses. SQLite stores an entire database in a single file and runs inside your process — there's no separate server to install or start. Modern Node.js even ships a built-in node:sqlite module, so you can require it directly. For larger production apps you might later move to PostgreSQL or MySQL, which run as separate servers, but the SQL concepts you learn here carry over directly.

What are the ? placeholders for, and why not just build the SQL string?

Placeholders (parameterized queries) keep your data separate from your SQL command. When you write db.prepare("... WHERE id = ?").get(userInput), the database treats userInput strictly as a value, never as code. If you instead glue user input into the SQL string, a malicious value like "1; DROP TABLE books" could be executed as a command — that's a SQL-injection attack. Parameterized queries prevent this entirely, and they're also faster because the statement is compiled once and reused.

What's the difference between .all(), .get(), and .run()?

They match what you expect back from a statement. Use .run() for writes — INSERT, UPDATE, DELETE — where you don't fetch rows; it returns metadata like lastInsertRowid and changes. Use .get() for a SELECT that should return a single row; you get one object (or undefined if nothing matched). Use .all() for a SELECT that returns many rows; you get an array of objects. Picking the right one avoids surprises like getting an array when you wanted one record.