An SQLite database

While posts live in an array, they disappear every time the server restarts. You need a database. For a learning blog SQLite is convenient: the whole database is one file in the project folder, and no separate server is needed. On Node.js the better-sqlite3 package works with it.

Installation

npm install better-sqlite3
npm install -D @types/better-sqlite3

The second package contains TypeScript types and is only needed during development.

Connection and tables

Create src/db.ts:

// src/db.ts
import Database from 'better-sqlite3'
 
export const db = new Database(process.env.DB_FILE ?? 'blog.db')
db.pragma('foreign_keys = ON')
 
db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
  );
 
  CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author TEXT NOT NULL,
    text TEXT NOT NULL
  );
`)
 
export type Post = {
  id: number
  title: string
  body: string
  created_at: string
}

The first time this module is imported, a blog.db file with two tables appears in the project root. IF NOT EXISTS prevents creating the tables again on later starts. The pragma line turns on relation checks: without it SQLite does not delete comments together with their post. Add blog.db to .gitignore.

Queries

A query is first prepared with prepare() and then run with one of three methods:

The ? marks in the query are replaced by the arguments in order.

Reading posts

Use the database in src/routes/posts.ts:

// src/routes/posts.ts
import { Hono } from 'hono'
import { db, type Post } from '../db.js'
 
const posts = new Hono()
 
posts.get('/', (c) => {
  const rows = db.prepare('SELECT * FROM posts ORDER BY id DESC').all() as Post[]
  return c.json(rows)
})
 
export default posts

http://localhost:3000/posts returns an empty array [] for now — the table is empty. The as Post[] cast is needed because the library does not know which columns the query returns.

Adding a post

// src/routes/posts.ts
const result = db
  .prepare('INSERT INTO posts (title, body) VALUES (?, ?)')
  .run('First post', 'Hello from SQLite')
 
console.log(result.lastInsertRowid) // the new row's number

After this call the post list returns one record. The full set of routes for creating, updating and deleting is in the next lesson.

Synchronous work

better-sqlite3 is synchronous: its methods return the result immediately, without await. For SQLite this is normal and fast — the file sits next to the program.

Other platforms

The better-sqlite3 package works only on Node.js. On Cloudflare Workers you use D1 instead — Cloudflare's SQLite database, connected as a binding and available through c.env. The SQL queries stay almost the same, but the calling methods differ and are asynchronous.

Common mistake

Putting data into SQL with a template string:

// src/routes/posts.ts
db.prepare(`SELECT * FROM posts WHERE id = ${c.req.param('id')}`).get()

If the address contains a piece of SQL, it gets executed. This is called SQL injection. Always pass values through ? and the method arguments: db.prepare('SELECT * FROM posts WHERE id = ?').get(id).