Connecting a Database

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.

Learn Connecting a Database in our free Node.js course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick reference.

Part of the free Node.js course at LearnCodingFast — hands-on lessons with examples you run in your browser, 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

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.

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.

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.

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

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

📋 Quick Reference — node:sqlite

Practice quiz

Why does a database persist data where an in-memory array does not?

  • Arrays are slower to read than databases
  • A database stores data on disk so it survives restarts and crashes
  • Arrays cannot hold objects
  • Databases keep everything in RAM only

Answer: A database stores data on disk so it survives restarts and crashes. An in-memory array vanishes when the process stops; a database writes to disk so the data survives restarts.

Which built-in Node module does this lesson use to open a SQLite database?

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

Answer: node:sqlite. Modern Node ships node:sqlite, exposing DatabaseSync with no separate server to install.

What does CREATE TABLE IF NOT EXISTS give you?

  • It safely creates the table only when it is missing, so startup can run every time
  • It deletes the table first
  • It always throws if the table exists
  • It creates a temporary table that is wiped on close

Answer: It safely creates the table only when it is missing, so startup can run every time. IF NOT EXISTS makes the create idempotent, so running it at every startup is safe.

Why should you use ? placeholders instead of concatenating user input into SQL?

  • It makes queries return faster sorting
  • It changes the column types automatically
  • It prevents SQL-injection by keeping data separate from the SQL command
  • It is required to open the database

Answer: It prevents SQL-injection by keeping data separate from the SQL command. Placeholders bind values as data, never as code, which closes the door on SQL injection.

Which prepared-statement method writes a row and returns metadata like lastInsertRowid?

  • .get()
  • .all()
  • .each()
  • .run()

Answer: .run(). .run() is for writes (INSERT/UPDATE/DELETE) and returns metadata such as lastInsertRowid and changes.

Which method returns every matching row as an array of objects?

  • .all()
  • .get()
  • .one()
  • .rows()

Answer: .all(). .all() returns an array of all matching rows; .get() returns a single row.

What does db.prepare(...).get(1) return when no row matches?

  • An empty array
  • An object with empty fields
  • undefined
  • null with an error

Answer: undefined. .get() returns one row or undefined when nothing matched.

After an INSERT with .run(), how do you read the id the database assigned?

  • result.insertId
  • result.lastInsertRowid
  • result.id
  • result.rowId

Answer: result.lastInsertRowid. The run result exposes lastInsertRowid, the id assigned to the newly inserted row.

What does db.prepare() do for you?

  • It opens a second database file
  • It compiles a SQL statement once so it can be reused safely with bound values
  • It deletes the table
  • It converts SQL to JSON

Answer: It compiles a SQL statement once so it can be reused safely with bound values. prepare() compiles the statement once; you then bind values to its ? placeholders and run it.

Which error usually means you queried before creating the table?

  • EADDRINUSE
  • Wrong number of bindings
  • no such table
  • Cannot find module

Answer: no such table. A 'no such table' error means a read or write ran before CREATE TABLE IF NOT EXISTS at startup.