Data-backed pages with the querier

In this tutorial we will build a task manager backed by SQLite. Users add, toggle, and delete tasks that persist across restarts. A public partial reloads after each mutation so the list stays fresh without a full page navigation.

Preview of the finished task manager: a browser at /tasks with an add input, three tasks (one checked with its label struck through), and a delete button per row. A SQLite database cylinder sits behind the browser to show the querier persistence layer.

You should have completed Shipping a real site first.

Step 1: Add SQLite and the querier

From the project root:

go get modernc.org/sqlite
go get piko.sh/piko/wdk/db
go get piko.sh/piko/wdk/db/db_engine_sqlite

modernc.org/sqlite is a pure-Go driver, so CGO_ENABLED=0 builds keep working.

Step 2: Write the schema migration

Create db/migrations/001_tasks.up.sql:

CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL
);

Embed the migration folder in db/schema.go:

package db

import "embed"

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

Step 3: Write SQL queries with piko annotations

Create db/queries/tasks.sql:

-- piko.query(ListTasks, many)
SELECT id, title, completed, created_at
FROM tasks
ORDER BY created_at DESC;

-- piko.query(CreateTask, one)
INSERT INTO tasks (title, completed, created_at)
VALUES (?, 0, ?)
RETURNING id, title, completed, created_at;

-- piko.query(ToggleComplete, exec)
UPDATE tasks SET completed = CASE WHEN completed = 0 THEN 1 ELSE 0 END
WHERE id = ?;

-- piko.query(DeleteTask, exec)
DELETE FROM tasks WHERE id = ?;

The header is piko.query(<name>, <command>[, optional kwargs...]). Thefirst two arguments are positional, but you can also name them aspiko.query(name: ListTasks, command: many). Supported commands areone, many, exec, execresult, execrows, batch, stream,copyfrom, and asyncexec. For the full grammar - parameter bindings(piko.param, including its optional: and kind: slice qualities),validated sort allow-lists (piko.sortable), nested-struct embeds(piko.embed), per-column overrides (piko.column), and diagnosticcodes - see the querier reference.

Step 4: Run the generator

Update cmd/generator/main.go so it knows about the SQLite database:

package main

import (
    "context"
    "os"

    "piko.sh/piko"
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_engine_sqlite"
    "piko.sh/piko/wdk/logger"
)

func main() {
    command := piko.GenerateModeManifest
    if len(os.Args) > 1 {
        command = os.Args[1]
    }

    logger.AddPrettyOutput()

    ssr := piko.New(
        piko.WithDatabase("tasks", &db.DatabaseRegistration{
            EngineConfig: db_engine_sqlite.SQLite(),
        }),
    )
    if err := ssr.Generate(context.Background(), command); err != nil {
        panic(err)
    }
}

Run it:

go run ./cmd/generator/main.go all

The generator writes typed Go to db/generated/. Each SQL file becomes a <name>.sql.go file with typed functions and result structs. Re-run after every SQL change.

Step 5: Wire the database at bootstrap

Replace cmd/main/main.go. Keep the dev-time options earlier tutorials introduced and add the database setup:

package main

import (
    "context"
    "database/sql"
    "os"

    _ "modernc.org/sqlite"

    "piko.sh/piko"
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_engine_sqlite"
    "piko.sh/piko/wdk/logger"

    taskdb "myapp/db"
    _ "myapp/dist"
)

func main() {
    logger.AddPrettyOutput()

    if err := os.MkdirAll("./data", 0o755); err != nil {
        panic(err)
    }

    database, err := sql.Open("sqlite", "file:./data/tasks.db")
    if err != nil {
        panic(err)
    }
    database.SetMaxOpenConns(1)

    _, err = database.Exec(
        "PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000",
    )
    if err != nil {
        panic(err)
    }

    executor := db.NewMigrationExecutor(database, db.SQLiteDialect())
    fileReader := db.NewFSFileReader(taskdb.Migrations)
    migrator := db.NewMigrationService(executor, fileReader, "migrations")

    if _, err := migrator.Up(context.Background()); err != nil {
        panic(err)
    }

    ssr := piko.New(
        piko.WithDatabase("tasks", &db.DatabaseRegistration{
            DB:           database,
            EngineConfig: db_engine_sqlite.SQLite(),
        }),
        piko.WithCSSReset(piko.WithCSSResetComplete()),
        piko.WithDevWidget(),
        piko.WithDevHotreload(),
        piko.WithMonitoring(),
        piko.WithFrontendModule(piko.ModuleToasts),
    )

    command := piko.RunModeDev
    if len(os.Args) > 1 {
        command = os.Args[1]
    }
    if err := ssr.Run(command); err != nil {
        panic(err)
    }
}

For the full bootstrap surface, including the WithDatabase registration model and *sql.DB retrieval, see bootstrap options reference.

Step 6: Build the task list partial

Create partials/task-list.pk:

<template public>
  <div class="task-list-container">
    <ul class="task-list" p-if="len(state.Tasks) > 0">
      <li p-for="task in state.Tasks" p-key="task.ID"
          p-class="'task-item' + (task.Completed ? ' completed' : '')">
        <button class="toggle-btn" p-on:click="handleToggle(task.ID)">
          <span p-if="task.Completed">&#10003;</span>
          <span p-if="!task.Completed">&#9675;</span>
        </button>
        <span class="task-title" p-text="task.Title"></span>
        <button class="delete-btn" p-on:click="handleDelete(task.ID)">
          &times;
        </button>
      </li>
    </ul>

    <p p-if="len(state.Tasks) == 0" class="empty-message">
      No tasks yet. Add one above.
    </p>
  </div>
</template>

<script type="application/x-go">
package main

import (
    "piko.sh/piko"
    pikoDb "piko.sh/piko/wdk/db"

    "myapp/db/generated"
)

type Task struct {
    ID        int32  `json:"id"`
    Title     string `json:"title"`
    Completed bool   `json:"completed"`
}

type Response struct {
    Tasks []Task `json:"tasks"`
}

func Render(r *piko.RequestData, props piko.NoProps) (Response, piko.Metadata, error) {
    conn, err := pikoDb.GetDatabaseConnection("tasks")
    if err != nil {
        return Response{}, piko.Metadata{}, err
    }

    queries := generated.New(conn)
    rows, err := queries.ListTasks(r.Context())
    if err != nil {
        return Response{}, piko.Metadata{}, err
    }

    taskList := make([]Task, len(rows))
    for i, row := range rows {
        taskList[i] = Task{
            ID:        row.ID,
            Title:     row.Title,
            Completed: row.Completed != 0,
        }
    }

    return Response{Tasks: taskList}, piko.Metadata{}, nil
}
</script>

<script lang="ts">
async function handleToggle(id: number): Promise<void> {
    await action.tasks.Toggle({ id }).call();
    piko.partials.reload("task-list");
}

async function handleDelete(id: number): Promise<void> {
    await action.tasks.Delete({ id }).call();
    piko.partials.reload("task-list");
}
</script>

For the public keyword and piko.partials.reload see pk-file format reference.

Step 7: Build the index page

Create pages/index.pk:

<template>
  <div id="app">
    <h1>Task Manager</h1>

    <form id="add-form" p-on:submit.prevent="handleAdd($event, $form)">
      <div class="add-row">
        <input type="text" name="title" placeholder="What needs doing?" required />
        <button type="submit">Add</button>
      </div>
    </form>

    <piko:partial is="task_list" />
  </div>
</template>

<script type="application/x-go">
package main

import (
    "piko.sh/piko"

    task_list "myapp/partials/task-list.pk"
)

type Response struct{}

func Render(r *piko.RequestData, props piko.NoProps) (Response, piko.Metadata, error) {
    return Response{}, piko.Metadata{Title: "Task Manager"}, nil
}
</script>

<script lang="ts">
async function handleAdd(event: SubmitEvent, form: FormDataHandle): Promise<void> {
    await action.tasks.Create(form).call();
    piko.partials.reload("task-list");

    const formElement = event.target as HTMLFormElement;
    const input = formElement.querySelector('input[name="title"]') as HTMLInputElement | null;
    if (input) {
        input.value = '';
        input.focus();
    }
}
</script>

The page itself has no database state. The partial handles its own data fetch.

Step 8: Write the Create action

Create actions/tasks/create.go:

package tasks

import (
    "time"

    "piko.sh/piko"
    "piko.sh/piko/wdk/db"

    "myapp/db/generated"
)

type CreateAction struct {
    piko.ActionMetadata
}

type CreateInput struct {
    Title string `json:"title" validate:"required,min=1,max=500"`
}

type CreateResponse struct {
    ID    int32  `json:"id"`
    Title string `json:"title"`
}

func (a CreateAction) Call(input CreateInput) (CreateResponse, error) {
    conn, err := db.GetDatabaseConnection("tasks")
    if err != nil {
        return CreateResponse{}, err
    }

    queries := generated.New(conn)
    row, err := queries.CreateTask(a.Ctx(), generated.CreateTaskParams{
        P1: input.Title,
        P2: int32(time.Now().Unix()),
    })
    if err != nil {
        return CreateResponse{}, err
    }

    return CreateResponse{ID: row.ID, Title: row.Title}, nil
}

P1 and P2 map to the two ? placeholders in the SQL. For named-parameter engines and the generator's naming rules see querier reference.

Step 9: Add toggle and delete

Create actions/tasks/toggle.go:

package tasks

import (
    "piko.sh/piko"
    "piko.sh/piko/wdk/db"

    "myapp/db/generated"
)

type ToggleAction struct {
    piko.ActionMetadata
}

type ToggleInput struct {
    ID int32 `json:"id" validate:"required"`
}

type ToggleResponse struct{}

func (a ToggleAction) Call(input ToggleInput) (ToggleResponse, error) {
    conn, err := db.GetDatabaseConnection("tasks")
    if err != nil {
        return ToggleResponse{}, err
    }

    queries := generated.New(conn)
    if err := queries.ToggleComplete(a.Ctx(), input.ID); err != nil {
        return ToggleResponse{}, err
    }

    return ToggleResponse{}, nil
}

And actions/tasks/delete.go:

package tasks

import (
    "piko.sh/piko"
    "piko.sh/piko/wdk/db"

    "myapp/db/generated"
)

type DeleteAction struct {
    piko.ActionMetadata
}

type DeleteInput struct {
    ID int32 `json:"id" validate:"required"`
}

type DeleteResponse struct{}

func (a DeleteAction) Call(input DeleteInput) (DeleteResponse, error) {
    conn, err := db.GetDatabaseConnection("tasks")
    if err != nil {
        return DeleteResponse{}, err
    }

    queries := generated.New(conn)
    if err := queries.DeleteTask(a.Ctx(), input.ID); err != nil {
        return DeleteResponse{}, err
    }

    return DeleteResponse{}, nil
}

Regenerate and rebuild:

go run ./cmd/generator/main.go all
go build ./cmd/main

Step 10: Run the app

./main

Visit the URL printed on startup. Type "buy milk" and press Enter. The partial reloads and the task appears. Click the toggle button and the task gains a strikethrough. Click the delete button and it disappears. The task list persists across restarts because SQLite stores the rows in ./data/tasks.db.

Next steps