Sigil
Tutorial: 5. Loading data

Tutorial

5. Loading data

So far the books live in the browser and vanish on reload. In this step they move into a SQLite database on the server, and pages load them before they render.

A database module

Bun has SQLite built in. Create src/lib/server/db.ts:

src/lib/server/db.ts
import { Database } from "bun:sqlite";
import type { Book } from "../books";

const db = new Database("books.sqlite", { create: true });
db.run(`CREATE TABLE IF NOT EXISTS books (
	id INTEGER PRIMARY KEY,
	title TEXT NOT NULL,
	author TEXT NOT NULL DEFAULT '',
	read INTEGER NOT NULL DEFAULT 0
)`);

interface Row {
	id: number;
	title: string;
	author: string;
	read: number;
}

const toBook = (row: Row): Book => ({ ...row, read: row.read === 1 });

export function listBooks(): Book[] {
	return db.query<Row, []>("SELECT * FROM books ORDER BY id").all().map(toBook);
}

export function getBook(id: number): Book | undefined {
	const row = db.query<Row, [number]>("SELECT * FROM books WHERE id = ?").get(id);
	return row ? toBook(row) : undefined;
}

export function addBook(title: string, author: string): void {
	db.query("INSERT INTO books (title, author) VALUES (?, ?)").run(title, author);
}

export function setRead(id: number, read: boolean): void {
	db.query("UPDATE books SET read = ? WHERE id = ?").run(read ? 1 : 0, id);
}

if (listBooks().length === 0) {
	addBook("The Left Hand of Darkness", "Ursula K. Le Guin");
	addBook("Piranesi", "Susanna Clarke");
}

Add books.sqlite to .gitignore.

This module must only run on the server, so import it only from .server.ts files. Those run on the server and are never sent to the browser. A component that imported it would pull bun:sqlite into the browser bundle, where it can't work.

Load the list

Create src/routes/+page.server.ts next to index.tsx:

src/routes/+page.server.ts
import { listBooks } from "../lib/server/db";

export function load() {
	return { books: listBooks() };
}

A +page.server.ts belongs to the page in the same folder. Its load runs on the server before the page renders: on the first request, and again on every client-side navigation to the page. Whatever it returns becomes the page's data prop.

Update src/routes/index.tsx to use it:

src/routes/index.tsx
import { BookRow } from "../lib/BookRow";
import type { Book } from "../lib/books";

const Index = ({ data }: { data: { books: Book[] } }) => {
	let books = $state(data.books);
	let unread = $derived(books.filter((b) => !b.read).length);

	return (
		<>
			<h1>Reading list</h1>
			<ul>
				{books.map((book) => (
					<BookRow key={book.id} book={book} />
				))}
			</ul>
			<p>{unread} still to read.</p>
		</>
	);
};

export default Index;

$state works inside a component too: books starts as the loaded list, and ticking a box still updates the count. The change stays in the browser for now; step 6 saves it. The add form is gone for the same reason, and comes back as a form action.

The page arrives server-rendered, with the list already in the HTML. Then the browser adopts that HTML (hydration) and the checkboxes come alive.

Load one book

Create src/routes/books/[id]/+page.server.ts:

src/routes/books/[id]/+page.server.ts
import { error, type LoadContext } from "@sigil-dev/grimoire";
import { getBook } from "../../../lib/server/db";

export function load({ params }: LoadContext) {
	const book = getBook(Number(params.id));
	if (!book) throw error(404, "No book with that id.");
	return { book };
}

And show it in src/routes/books/[id]/+page.tsx:

src/routes/books/[id]/+page.tsx
import { Head } from "@sigil-dev/grimoire";
import type { Book } from "../../../lib/books";

const BookPage = ({ data }: { data: { book: Book } }) => (
	<article>
		<Head>
			<title>{data.book.title}</title>
		</Head>
		<h1>{data.book.title}</h1>
		<p>by {data.book.author || "unknown"}</p>
		<p>{data.book.read ? "You've read this one." : "Still on the pile."}</p>
		<p>
			<a href="/">Back to the list</a>
		</p>
	</article>
);

export default BookPage;

load receives a context with the request, params, the parsed url, and more (Load functions lists it all). Throwing error(404, …) stops the load and answers with a 404. Try /books/99.

Next: forms and actions.

Edit this page