DailyRNG
Login
Advanced

Databases with SQLite

A database stores your app's data permanently. SQLite is a self-contained file database, no server, no setup. Learn to connect to it, run queries, and read and write rows.

What you will see

Users who signed up recently: ['alice', 'bob', 'dave', 'eve']

About 2 min of reading

Try It

Store data safely in SQLite

1

Create a table and insert rows with a transaction.

import sqlite3 print("Databases with SQLite") conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, signup_date TEXT)") conn.executemany( "INSERT INTO users (name, signup_date) VALUES (?, ?)", [("alice", "2026-09-20"), ("bob", "2026-09-20"), ("carol", "2026-09-01")], ) conn.commit()

Before you run it, say out loud what it will print. Then open the answer.

What it prints
Databases with SQLite
A database handles concurrency and keeps data consistent with transactions.
2

A transaction is all-or-nothing.

try: conn.execute("BEGIN") conn.execute("INSERT INTO users (name, signup_date) VALUES (?, ?)", ("dave", "2026-09-20")) conn.execute("INSERT INTO users (name, signup_date) VALUES (?, ?)", ("eve", "2026-09-20")) conn.commit() print("Transaction committed: all rows written or none") except Exception as e: conn.rollback() print("Rolled back: %s" % e)

Before you run it, say out loud what it will print. Then open the answer.

What it prints
Transaction committed: all rows written or none
3

Ask a smart question with SQL.

rows = conn.execute("SELECT name FROM users WHERE signup_date >= '2026-09-14' ORDER BY name").fetchall() print("Users who signed up recently: %s" % [r[0] for r in rows])

Before you run it, say out loud what it will print. Then open the answer.

What it prints
Users who signed up recently: ['alice', 'bob', 'dave', 'eve']

Data Flow

Your app opens the SQLite file, runs SQL, and receives the matching rows back to use in memory.
1 App opens app.db with sqlite3
↓
2 A SQL query is sent to the engine
↓
3 SQLite scans the table on disk
↓
4 Matching rows are returned
↓
5 The callback receives them as JS objects
↓
6 The app uses the data
Line by line

Read the first example one line at a time. A line that prints shows what appears on screen; the rest set things up quietly.

import sqlite3 sets nothing on screen
print("Databases with SQLite") Databases with SQLite
conn = sqlite3.connect(":memory:") sets nothing on screen
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, signup_date TEXT)") sets nothing on screen
conn.executemany( sets nothing on screen
"INSERT INTO users (name, signup_date) VALUES (?, ?)", sets nothing on screen
[("alice", "2026-09-20"), ("bob", "2026-09-20"), ("carol", "2026-09-01")], sets nothing on screen
) sets nothing on screen
conn.commit() sets nothing on screen
Why this works
When you have a choice

Why a database instead of a file

You could save data to a plain .json file, but that breaks under real use. If two requests edit the file at once, data is lost or corrupted.

A database handles this safely:

  • It supports many users at the same time (concurrency).
  • It keeps your data consistent with **transactions*: all of a change happens, or none of it.
  • It lets you ask smart questions with SQL, like "give me all users who signed up last week."

SQLite brings all of this into a single file you can back up by copying.

Examples

Connecting to a database

Install sqlite3 and open the database. It is just a file on disk.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./app.db');

Now you can run SQL against it. Every call takes a callback so slow work never blocks the server:

db.all('SELECT * FROM users', (err, rows) => {
  if (err) throw err;
  console.log(rows);
});

The file app.db is created automatically the first time you write to it.

Creating a table

A table is a grid of rows and columns. Create one with CREATE TABLE:

db.run(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

  • INTEGER PRIMARY KEY AUTOINCREMENT: each row gets its own unique number.
  • TEXT UNIQUE: no two emails can be the same.
  • DEFAULT CURRENT_TIMESTAMP: the time is stored automatically.

Run this once when your app starts to make sure the table exists.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Dynamic pages with EJS
Previous Lesson
Dynamic pages with EJS
Next Lesson
CRUD with SQL
CRUD with SQL →