A database

Posts in the array in app/data.server.ts vanish every time the server restarts. They need to live in a database. React Router does not impose a particular database: loaders and actions are ordinary server code that can talk to anything. The course uses SQLite — a database in a single file that needs no separate installation — with the better-sqlite3 package.

Installing

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

The first command installs the database driver, the second its TypeScript types.

Connection and tables

Create app/db.server.ts:

// app/db.server.ts
import Database from "better-sqlite3";
 
export const db = new Database("blog.db");
 
db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL
  );
  CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL REFERENCES posts(id),
    author TEXT NOT NULL,
    text TEXT NOT NULL
  );
`);

On the first request to the server a blog.db file with two tables appears in the project root. CREATE TABLE IF NOT EXISTS leaves existing tables alone. Larger projects use migrations instead, but this is simpler to start.

Data functions

Rewrite app/data.server.ts, keeping the function names:

// app/data.server.ts
import { db } from "./db.server";
 
export type Post = { id: number; title: string; body: string };
export type Comment = { id: number; postId: number; author: string; text: string };
 
export async function getPosts() {
  return db.prepare("SELECT id, title, body FROM posts ORDER BY id DESC").all() as Post[];
}
 
export async function getPost(id: number) {
  const row = db.prepare("SELECT id, title, body FROM posts WHERE id = ?").get(id);
  return (row as Post | undefined) ?? null;
}
 
export async function createPost(title: string, body: string) {
  const result = db.prepare("INSERT INTO posts (title, body) VALUES (?, ?)").run(title, body);
  return { id: Number(result.lastInsertRowid), title, body };
}
 
export async function getComments(postId: number) {
  return db
    .prepare("SELECT id, post_id AS postId, author, text FROM comments WHERE post_id = ?")
    .all(postId) as Comment[];
}
 
export async function addComment(postId: number, author: string, text: string) {
  db.prepare("INSERT INTO comments (post_id, author, text) VALUES (?, ?, ?)").run(postId, author, text);
}

Loaders and actions notice nothing: they call the same functions. Add a post with the /posts/new form, restart npm run dev — the post is still there.

better-sqlite3 methods

Method	Returns
`prepare(sql).all(...)`	An array of rows
`prepare(sql).get(...)`	One row or `undefined`
`prepare(sql).run(...)`	Write info: `changes`, `lastInsertRowid`

The package is synchronous, so no await; the module functions stay async so the database can be swapped. For PostgreSQL or MySQL people often use an ORM such as Prisma or Drizzle; the approach is the same: the connection in a .server.ts file, calls from loader and action.

Common mistake

Putting values straight into the query text:

// app/data.server.ts
// Wrong:
db.prepare(`SELECT * FROM posts WHERE id = ${id}`).get();
// Right:
db.prepare("SELECT * FROM posts WHERE id = ?").get(id);

The value came from the address, and a piece of SQL can be typed into it — that is SQL injection. Pass values only through ? placeholders and the arguments of get, all, run.

Thanks for reading!