Module Recap

Module 05 replaced in-memory arrays with a real SQLite database. Your API data now survives restarts and supports all the queries needed for Bulletin.

What you learned

Relational databases store data in tables with relationships. Tables have rows and columns; rows have primary keys; foreign keys create relationships between tables. SQL is the universal language for querying and modifying this data.

better-sqlite3 is synchronous and fast. Connect with new Database(path). Prepare statements with db.prepare(sql). Execute with .run() (write) or .get() (one row) or .all() (all rows). Always parameterize — never interpolate user input into SQL strings.

The Bulletin schema models real community board data. Users, posts, comments, and a composite post_upvotes junction table that prevents duplicate upvotes by design. ON DELETE CASCADE ensures orphaned data doesn’t accumulate.

SELECT queries shape what data the API returns. WHERE filters, ORDER BY sorts, LIMIT/OFFSET paginates, and JOIN combines tables. Count rows with COUNT(*) for pagination metadata.

INSERT/UPDATE/DELETE modify data. Check result.changes — 0 means no rows matched. Use lastInsertRowid to return the new row’s ID. db.transaction(fn) makes multi-statement operations atomic.

How this connects to Bulletin

The database layer the API uses in Modules 06 and 07:

// Bulletin queries used across the API
export const queries = {
  // Auth
  getUserByUsername: db.prepare('SELECT * FROM users WHERE username = ?'),
  createUser: db.prepare(
    'INSERT INTO users (username, password_hash) VALUES (?, ?)'
  ),

  // Posts
  listPosts: db.prepare(`
    SELECT posts.*, users.username AS author_username
    FROM posts JOIN users ON posts.user_id = users.id
    ORDER BY posts.created_at DESC LIMIT ? OFFSET ?
  `),
  getPostById: db.prepare(`
    SELECT posts.*, users.username AS author_username
    FROM posts JOIN users ON posts.user_id = users.id
    WHERE posts.id = ?
  `),
  createPost: db.prepare(
    'INSERT INTO posts (title, body, user_id) VALUES (?, ?, ?)'
  ),
  deletePost: db.prepare(
    'DELETE FROM posts WHERE id = ? AND user_id = ?'
  ),

  // Upvotes
  addUpvote: db.prepare(
    'INSERT INTO post_upvotes (user_id, post_id) VALUES (?, ?)'
  ),
  incrementUpvotes: db.prepare(
    'UPDATE posts SET upvotes = upvotes + 1 WHERE id = ?'
  ),
}

Key terms

Term	What it means
Table	Structure that holds rows of data
Primary key	Unique row identifier — usually id INTEGER PRIMARY KEY AUTOINCREMENT
Foreign key	Column referencing another table’s primary key
ON DELETE CASCADE	Auto-delete dependent rows when the referenced row is deleted
Prepared statement	Pre-compiled SQL for efficiency and injection prevention
.run(params)	Executes INSERT/UPDATE/DELETE
.get(params)	Returns one row from SELECT
.all(params)	Returns all rows from SELECT
result.changes	Number of rows affected — 0 means no match
db.transaction(fn)	Atomic multi-statement operation

What is next

Module 06 — Authentication and Security →

Module 06 secures the Bulletin API. You’ll hash passwords with bcrypt, issue and verify JSON Web Tokens, build the authenticate middleware that protects private routes, and add CORS, Helmet, and rate limiting. After this module, the API knows who is making each request.