3. A database

Shelf keeps its links in SQLite: a database in one file, with nothing to install or run beside the app. Tern's own packages work with PostgreSQL just as well, if you'd rather use it later.

Add a SQLite driver written in Go, so the app still builds into one static binary:

go get modernc.org/sqlite

#A migration

The database's tables are made by migrations: SQL files that run once each, in order, when the app starts. Create the first one:

go tool tern migrate new create links

It creates migrations/<timestamp>_create_links.sql. Write the table into it:

CREATE TABLE links (
	id       INTEGER PRIMARY KEY,
	url      TEXT NOT NULL,
	title    TEXT NOT NULL,
	read     BOOLEAN NOT NULL DEFAULT FALSE,
	added_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- +down
DROP TABLE links;

The part after -- +down undoes it, for migrate.Down. The app applies the migrations itself, so embed them in it. Create migrations/embed.go:

// Package migrations holds the database migrations, applied in order when
// the app starts.
package migrations

import "embed"

//go:embed *.sql
var FS embed.FS

#Reading the links

Add the database to internal/shelf/shelf.go, with a function that lists the links:

// Package shelf keeps the links in a SQLite database.
package shelf

import (
	"context"
	"database/sql"
	"errors"
	"time"

	_ "modernc.org/sqlite" // the SQLite driver, in pure Go
)

// Link is a page saved to read later.
type Link struct {
	ID      int64     `json:"id"`
	URL     string    `json:"url"`
	Title   string    `json:"title"`
	Read    bool      `json:"read"`
	AddedAt time.Time `json:"addedAt"`
}

// ErrNotFound is returned for a link that doesn't exist.
var ErrNotFound = errors.New("link not found")

// DB is the database, opened by Open.
var DB *sql.DB

// Open opens the SQLite database at path, creating it if needed.
func Open(path string) error {
	db, err := sql.Open("sqlite", "file:"+path+"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)")
	if err != nil {
		return err
	}
	DB = db
	return nil
}

// List returns the links, the newest first. show is "unread", "read" or
// anything else for all of them.
func List(ctx context.Context, show string) ([]Link, error) {
	query := `SELECT id, url, title, read, added_at FROM links`
	switch show {
	case "unread":
		query += ` WHERE NOT read`
	case "read":
		query += ` WHERE read`
	}
	rows, err := DB.QueryContext(ctx, query+` ORDER BY id DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var links []Link
	for rows.Next() {
		var l Link
		if err := rows.Scan(&l.ID, &l.URL, &l.Title, &l.Read, &l.AddedAt); err != nil {
			return nil, err
		}
		links = append(links, l)
	}
	return links, rows.Err()
}

The options after the file name are the usual ones for a web app on SQLite: write-ahead logging, so reads don't wait for writes; a wait of up to 5 seconds when the database is busy; and foreign keys checked. List takes show already; step 6 uses it.

This tutorial writes the SQL in Go with database/sql, to show what happens underneath. Tern can also write that Go for you: put the queries in queries/*.sql, and it generates typed functions, checked against the schema your migrations build (SQL queries). The SaaS starter template works that way.

#Opening it

Open the database in main.go, and add the migrations as a plugin, so they run before the server starts listening:

package main

import (
	"log"

	"github.com/Unfield/Tern/kit"
	"github.com/Unfield/Tern/migrate"

	"shelf/internal/shelf"
	"shelf/migrations"
)

func main() {
	if err := shelf.Open("shelf.db"); err != nil {
		log.Fatal(err)
	}
	app, err := ternApp(kit.WithPlugins(migrate.Plugin(shelf.DB, migrations.FS)))
	if err != nil {
		log.Fatal(err)
	}
	if err := app.ListenAndServe(); err != nil {
		log.Fatal(err)
	}
}

ternApp is generated by Tern from your routes, in tern.gen.go. Its options configure the app: plugins, hooks, middleware, the logger.

The load now reads from the database. In src/routes/page.server.go:

package routes

import (
	"github.com/Unfield/Tern/kit"

	"shelf/internal/shelf"
)

var Access = kit.Public

type Data struct {
	Links []shelf.Link `json:"links"`
}

func Load(ctx *kit.Ctx) (Data, error) {
	links, err := shelf.List(ctx, "")
	if err != nil {
		return Data{}, err
	}
	return Data{Links: links}, nil
}

ctx is a context.Context too, so it goes straight to the database: when the visitor goes away, the query is cancelled.

The dev server restarts and logs applied migration … name=create_links. The page says "Nothing saved yet": the table is empty. Add shelf.db* to .gitignore, so the database stays out of git.

If a migration has a mistake, the server doesn't start, and the dev server shows the error. Once a migration has run, don't edit it: Tern notices and refuses to start. Add a new migration instead, as step 7 does.

Next: Saving links.