Reading Data with SELECT

Reading data is the most common database operation. This lesson covers the SELECT queries that power the Bulletin API’s feed, post detail, and user profile pages.

Basic SELECT

-- All columns, all rows
SELECT * FROM posts;

-- Specific columns
SELECT id, title, created_at FROM posts;

-- With better-sqlite3
const posts = db.prepare('SELECT id, title, created_at FROM posts').all()

Prefer selecting specific columns over * — it’s more explicit and prevents accidentally leaking sensitive data like password_hash.

WHERE clause

Filter rows by condition:

SELECT * FROM posts WHERE user_id = 3;
SELECT * FROM users WHERE username = 'alice';
SELECT * FROM posts WHERE upvotes > 10;

const userPosts = db.prepare('SELECT * FROM posts WHERE user_id = ?').all(userId)
const user = db.prepare('SELECT id, username, created_at FROM users WHERE username = ?').get(username)

ORDER BY

Sort results:

SELECT * FROM posts ORDER BY created_at DESC;     -- newest first
SELECT * FROM posts ORDER BY upvotes DESC;         -- most upvoted first
SELECT * FROM posts ORDER BY created_at ASC;       -- oldest first

const sort = req.query.sort === 'top' ? 'upvotes DESC' : 'created_at DESC'
// Note: don't use req.query values directly in SQL strings
// This is safe because 'upvotes DESC' and 'created_at DESC' are hardcoded
const posts = db.prepare(`SELECT * FROM posts ORDER BY ${sort}`).all()

LIMIT and OFFSET (pagination)

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;  -- page 1
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 20; -- page 2

const limit = 20
const page = parseInt(req.query.page as string || '1', 10)
const offset = (page - 1) * limit

const posts = db.prepare(
  'SELECT * FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?'
).all(limit, offset)

JOIN — combining tables

The Bulletin feed needs the author’s username alongside each post:

SELECT
  posts.id,
  posts.title,
  posts.body,
  posts.upvotes,
  posts.created_at,
  users.username AS author_username
FROM posts
JOIN users ON posts.user_id = users.id
ORDER BY posts.created_at DESC;

const posts = db.prepare(`
  SELECT
    posts.id,
    posts.title,
    posts.body,
    posts.upvotes,
    posts.created_at,
    users.username AS author_username
  FROM posts
  JOIN users ON posts.user_id = users.id
  ORDER BY posts.created_at DESC
  LIMIT ? OFFSET ?
`).all(limit, offset)

COUNT

Count rows without fetching all the data:

SELECT COUNT(*) as total FROM posts WHERE user_id = 3;

const result = db.prepare(
  'SELECT COUNT(*) as total FROM posts WHERE user_id = ?'
).get(userId) as { total: number }

console.log(result.total) // 42

Checking if a row exists

For things like “has this user upvoted this post”:

const upvote = db.prepare(
  'SELECT 1 FROM post_upvotes WHERE user_id = ? AND post_id = ?'
).get(userId, postId)

const hasUpvoted = upvote !== undefined

Exercise

1. Write a query that returns all posts by a specific user, newest first.
2. Write a query that returns a single post with the author’s username (using JOIN).
3. Write a query that returns all comments for a post, joined with the commenter’s username.
4. Test these queries in the REPL or a test script.

Recap

• SELECT columns FROM table — basic read.
• WHERE condition — filter rows.
• ORDER BY column ASC|DESC — sort results.
• LIMIT n OFFSET m — paginate results.
• JOIN table ON condition — combine rows from multiple tables.
• COUNT(*) — count rows efficiently.
• Always parameterize user-supplied values; hardcoded sort strings are safe in template literals.