Setting Up SQLite with better-sqlite3

better-sqlite3 is a fast, synchronous SQLite driver for Node.js. Unlike most database drivers that are async, better-sqlite3 is synchronous — which aligns perfectly with SQLite’s single-file, zero-network design and makes code simpler.

Installing

npm install better-sqlite3
npm install --save-dev @types/better-sqlite3

Connecting to a database

src/db/index.ts

import { join } from 'path'
import Database from 'better-sqlite3'
import { config } from '../config.js'

const dbPath = join(process.cwd(), config.dbPath)
export const db = new Database(dbPath)

// Enable WAL mode for better concurrent read performance
db.pragma('journal_mode = WAL')

new Database(path) opens the database file if it exists, or creates it if it doesn’t. The database file is created at config.dbPath (typically ./bulletin.db).

Your first query

import { db } from './db/index.js'

// .run() for INSERT/UPDATE/DELETE
const result = db.prepare('INSERT INTO users (username) VALUES (?)').run('alice')
console.log(result.lastInsertRowid) // the new row's ID

// .get() for SELECT that returns one row
const user = db.prepare('SELECT * FROM users WHERE id = ?').get(1)
console.log(user) // { id: 1, username: 'alice', ... }

// .all() for SELECT that returns all rows
const users = db.prepare('SELECT * FROM users').all()
console.log(users) // [{ id: 1, ... }, { id: 2, ... }]

Prepared statements

db.prepare(sql) creates a prepared statement — a pre-compiled SQL query. This is more efficient than building SQL strings, and critically, it prevents SQL injection (more on this shortly).

Store frequently-used statements:

src/db/queries.ts

import { db } from './index.js'

export const queries = {
  getPostById: db.prepare('SELECT * FROM posts WHERE id = ?'),
  listPosts: db.prepare('SELECT * FROM posts ORDER BY created_at DESC'),
  createPost: db.prepare(
    'INSERT INTO posts (title, body, user_id) VALUES (@title, @body, @userId)'
  ),
}

Named parameters

Instead of positional ? placeholders, use named parameters with @name:

const createPost = db.prepare(
  'INSERT INTO posts (title, body, user_id) VALUES (@title, @body, @userId)'
)

createPost.run({ title: 'Hello', body: 'World', userId: 1 })

Named parameters are clearer when there are multiple values.

SQL injection prevention

Never build SQL strings with string interpolation:

// ❌ SQL injection vulnerability
const userId = req.params.id  // attacker sends: "1; DROP TABLE users;"
db.prepare(`SELECT * FROM users WHERE id = ${userId}`).get()

Always use parameterized queries:

// ✅ Safe — the value is sanitized automatically
db.prepare('SELECT * FROM users WHERE id = ?').get(req.params.id)

better-sqlite3 escapes all parameter values, making injection impossible.

TypeScript types

Type the return values of your queries:

interface Post {
  id: number
  title: string
  body: string
  user_id: number
  upvotes: number
  created_at: string
}

const post = db.prepare('SELECT * FROM posts WHERE id = ?').get(id) as Post | undefined

Exercise

1. Install better-sqlite3.
2. Create src/db/index.ts with the database connection.
3. Create a temporary script that runs an INSERT and a SELECT. Verify you can write and read data.
4. Open the .db file with DB Browser for SQLite (free GUI) and inspect the data visually.

Recap

• new Database(path) connects to or creates a SQLite file.
• .prepare(sql) creates a prepared statement — use it for all queries.
• .run(params) executes INSERT/UPDATE/DELETE; .get(params) returns one row; .all(params) returns all rows.
• Always use parameterized queries (? or @name) — never string interpolation.
• db.pragma('journal_mode = WAL') improves concurrent read performance.