CRUD with HTMX, Typescript, and SQLite

A while back, a little JavaScript library known as HTMX was making waves. As I had started my career in client-side rendered front-ends, communicating via HTML seemed fairly novel to me.

This has a number of added benefits:

No API lock-in - SPAs where the frontend is deployed with API validation and logic may need to be deployed anytime a change to the API is made. In SSR applications, changes to the API simply change the HTML rendered by the clients browser.
No cross-domain code - as (almost) all code now executes in the backend, libraries do not need to be compiled for the browser, potentially simplifying the build process.

I did also note a number of pitfals:

Stateful logic is more difficult - actions such as pagination and fuzzy search must happen server-side or fallback to client-side JavaScript using local storage.
Server load - as almost all actions now depend on the backend, there is an increase in network traffic and server processing to render views.

In this article, I will walk through the steps I went through to augment my Discord bot with a simple SQLite database and an admin console driven by HTMX.

The TL;DR is:

Uses SQLite as the database (hint: see Turso) with the schema managed using Atlas
Written in TypeScript to share a language with the bot
Fragments are written in TSX rendered by Preact and preact-render-to-string
Fastify server tying it all together

The Database

As the data was fairly unimportant, I did not want to go down the rabbit-hole of migrations and versioning. Atlas provides the ability to manage database schemas declaratively using HCL or SQL.

Example schema.hcl

schema "main" {
}

table "song" {
  schema = schema.main

  column "songId" {
    type = text
    null = false
  }

  column "songTitle" {
    type = text
    null = false
  }

  column "artistId" {
    type = text
    null = true
  }

  column "artistTitle" {
    type = text
    null = true
  }

  column "duration" {
    type = integer
    null = true
  }

  primary_key {
    columns = [column.songId]
  }
}

If you have an existing database, Atlas can generate a schema for you:

atlas schema inspect \
  --url sqlite://local.db > schema.hcl

# or in sql
atlas schema inspect \
  --url sqlite://local.db \
  --format '{{ sql . }}' > schema.sql

# or to visualise
atlas schema inspect \
  --url sqlite://local.db \
  --format '{{ mermaid . }}' > schema.md
# if you have a markdown previewer that supports mermaid, you can use the format
# '```mermaid{{ printf '\n' }}{{ mermaid . }}```'

Once your schema has been declared, you can use the following commands to compare and apply to your database:

# compare
atlas schema diff \
  --from sqlite://local.db \
  --to file://schema.hcl \
  --dev-url 'sqlite://dev?mode=memory'

# apply
atlas schema apply \
  --url sqlite://local.db \
  --to file://schema.hcl
# hint: use --dry-run and/or --auto-approve in a headless environment

You should now see your tables in your database

$ sqlite3 local.db --cmd ".tables" ".exit"
query  queue  song

$ sqlite3 local.db --cmd ".schema song" ".exit"
CREATE TABLE `song` (
  `songId` text NOT NULL,
  `songTitle` text NOT NULL,
  `artistId` text NULL,
  `artistTitle` text NULL,
  `duration` integer NULL,
  PRIMARY KEY (`songId`)
);

Integrating

I went with a DAO pattern as I wanted my approach to be as slim as possible.

Hint: for greater control, zod could be used to filter out unwanted properties, transform results, and be used for type inference. I will not use it in the examples here but I have implemented it in my bot.

Install dependencies:

npm install @libsql/client

Connect to your database:

database.ts

import { ResultSet, createClient } from "@libsql/client";

// converts a libsql resultset into an array of objects
export const expandResultSet = (results: ResultSet): any[] => {
  if (results.rows.length < 1) {
    return [];
  }

  const expand = (row: any[]) =>
    row.reduce(
      (acc, value, index) => ({ ...acc, [results.columns[index]]: value }),
      {}
    );

  return results.rows.map((row) => expand(Array.from(row)));
};

// use a single static client for all requests
export const client = createClient({
  url: process.env.DATABASE_URL,
  authToken: process.env.DATABASE_AUTH_TOKEN, // for use with Turso
});

Define your CRUD actions in SQL:

queries.ts

export const setSong = [
  `INSERT OR REPLACE INTO song (songId, songTitle, artistId, artistTitle, duration)`,
  `VALUES ($songId, $songTitle, $artistId, $artistTitle, $duration)`,
  `RETURNING *`,
].join(" ");

export const getSong = `SELECT * FROM song WHERE songId = $songId`;

export const deleteSong = `DELETE FROM song WHERE songId = $songId RETURNING *`;

export const listSongs = `SELECT * FROM song LIMIT $limit OFFSET $offset`;

Bringing it all together, provide a nice TypeScript interface for your CRUD actions with a data access object:

SongDAO.ts

import * as queries from "./queries";
import { expandResultSet, client } from "./database";

export interface Song {
  songId: string;
  songTitle: string;
  artistId: string | null;
  artistTitle: string | null;
  duration: string | null;
}

export class SongDAO {
  static async put(...songs: Song[]): Promise<void> {
    const batch = songs.map((song) => ({
      sql: queries.setSong,
      args: { ...song },
    }));
    await client.batch(batch, "write");
  }

  static async get(songId: string): Promise<Song | null> {
    const result = await client.execute({
      sql: queries.getSong,
      args: { songId },
    });
    return expandResultSet(result)[0] ?? null;
  }

  static async delete(songId: string): Promise<void> {
    await client.execute({
      sql: queries.deleteSong,
      args: { songId },
    });
  }

  static async list(limit: number, offset: number): Promise<Song[]> {
    const result = await client.execute({
      sql: queries.listSongs,
      args: { limit, offset },
    });
    return expandResultSet(result);
  }
}

Rinse and repeat for any other tables you may want.

The Server

Our server will be written in TypeScript using Fastify to serve content and Preact as a light-weight JSX renderer.

If you haven’t already, enable JSX in your tsconfig.json:

{
  "compilerOptions": {
    "jsx": "react-jsx",
    "jsxImportSource": "preact"
  }
}

Install the necessary dependencies:

npm install \
  fastify \
  fastify-plugin \
  @fastify/formbody \
  @fastify/helmet \
  @fastify/static \
  preact \
  preact-render-to-string

Fastify provides some powerful configuration via lifecycle hooks. We use one here so that we can write routes that return TSX directly and have it be rendered later:

hooks.ts

import { FastifyInstance } from "fastify";
import plugin from "fastify-plugin";
import { isValidElement } from "preact";
import { render } from "preact-render-to-string";

import { ContentType } from "./consts";
import { Layout } from "./Layout";

export const hooks = plugin(async (fastify: FastifyInstance) => {
  // noop so fastify doesn't attempt to stringify the raw html we rendered
  fastify.setReplySerializer((payload: any) => payload);

  // automatically render jsx components
  fastify.addHook("preSerialization", async (request, reply, payload) => {
    if (!isValidElement(payload)) {
      return JSON.stringify(payload);
    }

    // we know its html from here on out
    reply.type(ContentType.HTML);

    // htmx expects a standalone html fragment to swap in
    if (request.headers["hx-request"] === "true") {
      return render(payload);
    }

    // the browser expects a full page
    return "<!DOCTYPE html>" + render(Layout({ children: payload }));
  });
});

Define the layout which will wrap any fragment to turn it into a full page, including any necessary scripts (e.g. htmx)

Layout.tsx

import React from "preact/compat";

export const Layout = (props: {children: any}) => (
  <html>
    <head>
      <meta charset="utf-8" />
      <meta name="viewport" content="width=device-width, initial-scale=1" />
      <meta name="description" content="Huisheng administration console">
      <title>Huisheng</title>
      <link>
    </head>
    <body>
      <a hx-get="/search" hx-target="#root" hx-swap="innerHTML" hx-push-url="true">Search</a>
      <a hx-get="/queue" hx-target="#root" hx-swap="innerHTML" hx-push-url="true">Queue</a>
      <div id="root">{props.children}</div>
      <script src="https://unpkg.com/htmx.org@..."></script>
      <script src="https://cdn.jsdelivr.net/...bootstrap.bundle.min.js..."></script>
    </body>
  </html>
);

Define your router (we will flesh this out later):

router.tsx

import React from "preact/compat";
import { FastifyInstance } from "fastify";
import { Layout } from "./Layout.tsx";

export const router = async (app: FastifyInstance) => {
  app.get("/", async () => <Layout>Hello World</Layout>);
};

Tying it all together, instantiate the server:

server.ts

import path from "path";
import fastify from "fastify";
import fastifyHelmet from "@fastify/helmet";
import fastifyStatic from "@fastify/static";
import { router } from "./router";
import { hooks } from "./hooks";

export const server = fastify();
server.register(hooks);
server.register(router);
server.register(fastifyHelmet, {
  contentSecurityPolicy: {
    directives: {
      "script-src-elem": [
        // for inline scripts
        "'self'",
        // for cdns
        "https://unpkg.com/htmx.org@1.9.12",
        "https://cdn.jsdelivr.net",
      ],
    },
  },
});
server.register(fastifyStatic, {
  prefix: "/static",
  root: path.join(__dirname, "static"),
});

Somewhere in your app entrypoint:

server.listen({ port: 8000 });

Content

At this point we should have a functional server available at the port you configured (e.g. http://localhost:8000). Now we can tie everything together and create some fragments to enable functionality.

Given we model our CRUD endpoints like so:

Method	Endpoint	Description
`GET`	`/songs`	Page for songs
`GET`	`/songs/items`	Table of songs
`POST`	`/songs/items`	Create a song
`DELETE`	`/songs/items/:songId`	Delete a song

We can now implement all of our views and fragments

routes.tsx

import React from "preact/compat";
import { FastifyInstance } from "fastify";
import { Song, SongDAO } from "./SongDAO";

const SongForm = () => (
  <form
    class="SongForm"
    hx-post="/songs/items"
    hx-target=".SongForm"
    hx-swap="outerHTML"
  >
    <input type="text" name="songId" />
    <input type="text" name="songTitle" />
    <input type="text" name="artistId" />
    <input type="text" name="artistTitle" />
    <input type="text" name="duration" />
    <button type="submit">Submit</button>
  </form>
);

const SongTable = (props: { items: Song }) => (
  <table
    class="SongTable"
    // we can chain requests off custom events fired from the hx-trigger header
    // these get emitted by the dom element being swapped and bubble up to the body
    hx-trigger="refresh:songs from:body"
    hx-get="/songs/items"
  >
    <thead>
      <tr>
        <th>Song</th>
        <th>Artist</th>
        <th>Duration</th>
        <th>{/* actions */}</th>
      </tr>
    </thead>
    <tbody>
      {props.items.map((item) => (
        <tr key={item.songId}>
          <td>{item.songTitle}</td>
          <td>{item.artistTitle}</td>
          <td>{item.duration}</td>
          <td>
            <button
              hx-delete={`/songs/items/${item.songId}`}
              hx-target="SongTable"
              // we re-render and swap the entire component out
              hx-swap="outerHTML"
            >
              Delete
            </button>
          </td>
        </tr>
      ))}
    </tbody>
  </table>
);

export const router = (app: FastifyInstance) => {
  app.get("/songs", async (request) => (
    <div class="Songs">
      <SongForm />
      <div hx-trigger="load" hx-get="/songs/items" />
    </div>
  ));

  app.get("/songs/items", async (request) => {
    const { limit, offset } = request.query;
    const items = await SongDAO.list(limit, offset);
    return <SongTable items={items} />;
  });

  app.post("/songs/items", async (request) => {
    await SongDAO.put(request.body);
    const items = await SongDAO.list(limit, offset);
    return <SongForm />;
  });

  app.delete("/songs/items/:songId", async (request, reply) => {
    const { songId } = request.params;
    await SongDAO.delete();
    // we let the dom know that the contents of songs has changed
    reply.header("hx-trigger", "refresh:songs");
    return <SongTable items={items} />;
  });

  // etc
};

Rinse and repeat for other views.

The Database#

Integrating#

The Server#

Content#

The Database

Integrating

The Server

Content