First App: UI & Custom Endpoints

This article is a short introduction to TrailBase and some of its features. We’ll bootstrap a database with coffee data, implement a custom TypeScript HTTP handler for finding the best matches using vector search, and deploy a simple production-ready web app all in ~100 lines of code.

This introductory tutorial is part of TrailBase’s main code repository, which can be downloaded to follow along by running:

$ git clone https://github.com/trailbaseio/trailbase.git
$ cd trailbase/examples/coffeesearch

Getting TrailBase

You can download the latest pre-built trail binary for Mac and Linux from GitHub.

Alternatively, you can run TrailBase straight from DockerHub:

$ alias trail=docker run \
      -p 4000:4000 \
      --mount type=bind,source=$PWD/traildepot,target=/app/traildepot \
      trailbase/trailbase /app/trail

or compile it yourself from source.

Importing Data

Before building the app, let’s import some data. Keeping it simple, we’ll use the sqlite3 CLI¹ directly to import examples/coffeesearch/arabica_data_cleaned.csv with the following SQL script:

examples/coffeesearch/import.sql

-- First create the strictly typed "coffee" table.
CREATE TABLE coffee (
  Species TEXT,
  Owner TEXT,

  Aroma REAL,
  Flavor REAL,
  Acidity REAL,
  Sweetness REAL,

  embedding BLOB
) STRICT;

-- Then import the data into a "temporary" table.
.mode csv
.import arabica_data_cleaned.csv temporary

-- Then import the un-typed temporary data into the typed "coffee" table.
INSERT INTO coffee (Species, Owner, Aroma, Flavor, Acidity, Sweetness)
  SELECT
    Species,
    Owner,

    CAST(Aroma AS REAL) AS Aroma,
    CAST(Flavor AS REAL) AS Flavor,
    CAST(Acidity AS REAL) AS Acidity,
    CAST(Sweetness AS REAL) AS Sweetness
  FROM temporary;

-- And clean up.
DROP TABLE temporary;

Note that we didn’t initialize the vector embedding. This is merely because sqlite3 doesn’t have the necessary extensions built-in. We’ll update the entries later on, adding the embedding as part of our initial database migrations².

From within the example/coffeesearch directory, you can execute the script above and import the coffee data by running:

$ mkdir -p traildepot/data
$ cat import.sql | sqlite3 traildepot/data/main.db -

After importing the data while still in the same directory, we can start the trail server:

$ trail run

Because trail starts for the first time the migrations in traildepot/migrations will be applied, which are essentially:

UPDATE coffee SET embedding = VECTOR(FORMAT("[%f, %f, %f, %f]", Aroma, Flavor, Acidity, Sweetness));

initializing the previously skipped coffee.embedding for all records.

Custom TypeScript Endpoint

Any time you start trail run³, JavaScript and TypeScript files under traildepot/scripts will be executed.

Note

TrailBase will automatically transpile TypeScript to JavaScript which can then execute on the underlying V8 engine. You don’t need a separate build step.

We can use this to register custom HTTP API routes among other things. Let’s have a quick look at examples/coffeesearch/traildepot/scripts/main.ts, which defines a /search API route we’ll later use in our application to find coffees most closely matching our desired coffee notes:

examples/coffeesearch/traildepot/scripts/main.ts

import { addRoute, jsonHandler, parsePath, query } from "../trailbase.js";

/// Register a handler for the `/search` API route.
addRoute(
  "GET",
  "/search",
  jsonHandler(async (req) => {
    // Get the query params from the url, e.g. '/search?aroma=4&acidity=7'.
    const searchParams = parsePath(req.uri).query;
    const aroma = searchParams.get("aroma") ?? 8;
    const flavor = searchParams.get("flavor") ?? 8;
    const acid = searchParams.get("acidity") ?? 8;
    const sweet = searchParams.get("sweetness") ?? 8;

    // Query the database for the closest match.
    return await query(
      `SELECT Owner, Aroma, Flavor, Acidity, Sweetness
         FROM coffee
         ORDER BY vector_distance_cos(
           embedding, '[${aroma}, ${flavor}, ${acid}, ${sweet}]')
         LIMIT 100`,
      [],
    );
  }),
);

While trail run is up, we can test the public /search endpoint simply by running:

$ curl "http://localhost:4000/search?aroma=8&flavor=8&acidity=8&sweetness=8"
[
  ["juan luis alvarado romero",7.92,7.58,7.58,8],
  ["eileen koyanagi",7.5,7.33,7.58,8],
  ...
]

That’s it, we’re done with the server side. This is enough to build a simple search UI. With little code and a few commands we’ve ingested CSV data and built a custom HTTP endpoint using vector search. If you’re not interested in a UI, the same approach setup could be used to identify relevant documents for AI applications.

A simple Web UI

After setting up our database, vector search and APIs, we should probably use them for good measure. For example, we could build a mobile app, have an LLM answer coffee prompts, or build a small web UI. Here we’ll do the latter. It’s quick and also lets us touch more generally on bundling and deploying web applications with TrailBase.

Note that this is not a web dev tutorial. The specifics of the UI aren’t the focus. We chose React as a well-known option and kept the implementation to less than 80 lines of code. In case you want to build your own, we recommend vite to quickly set up an SPA with your favorite JS framework, e.g.: npm create vite@latest my-project -- --template react.

Our provided reference implementation, renders 4 numeric input fields to search for coffee with a certain aroma, flavor, acidity and sweetness score:

examples/coffeesearch/src/App.tsx

import { useState, useEffect } from "react";
import "./App.css";

const Input = (props: {
  label: string;
  value: number;
  update: (v: number) => void;
}) => (
  <>
    <label>{props.label}:</label>
    <input
      type="number"
      step={0.1}
      max={10}
      min={0}
      value={props.value}
      onChange={(e) => props.update(e.target.valueAsNumber)}
    />
  </>
);

function Table() {
  const [aroma, setAroma] = useState(8);
  const [flavor, setFlavor] = useState(8);
  const [acidity, setAcidity] = useState(8);
  const [sweetness, setSweetness] = useState(8);

  const [data, setData] = useState<Array<Array<object>> | undefined>();
  useEffect(() => {
    const URL = import.meta.env.DEV ? "http://localhost:4000" : "";
    const params = Object.entries({ aroma, flavor, acidity, sweetness })
      .map(([k, v]) => `${k}=${v}`)
      .join("&");

    fetch(`${URL}/search?${params}`).then(async (r) => setData(await r.json()));
  }, [aroma, flavor, acidity, sweetness]);

  return (
    <>
      <div className="inputs">
        <Input label="Aroma" value={aroma} update={setAroma} />
        <Input label="Flavor" value={flavor} update={setFlavor} />
        <Input label="Acidity" value={acidity} update={setAcidity} />
        <Input label="Sweetness" value={sweetness} update={setSweetness} />
      </div>

      <div className="table">
        <table>
          <thead>
            <tr>
              <th>Owner</th>
              <th>Aroma</th>
              <th>Flavor</th>
              <th>Acidity</th>
              <th>Sweetness</th>
            </tr>
          </thead>

          <tbody>
            {(data ?? []).map((row) => (
              <tr>
                {row.map((d) => (
                  <td>{d.toString()}</td>
                ))}
              </tr>
            ))}
          </tbody>
        </table>
      </div>
    </>
  );
}

export const App = () => (
  <>
    <h1>Coffee Search</h1>
    <Table />
  </>
);

We can start a dev-server with the UI from above and hot-reload running:

$ npm install && npm dev

Deployment: Putting Everything Together

Whether you’ve followed along or skipped to here, we can now put everything together. Let’s start by compiling our JSX/TSX web UI down to pure HTML, JS, and CSS artifacts the browser can understand:

$ npm install && npm build

The artifacts are written to ./dist and can be served alongside our database as well as custom API by running:

$ trail run --public-dir dist

You can now check out your fully self-contained app under http://localhost:4000/ or browse the coffee data and access logs in the admin dashboard. The admin credentials are logged to the terminal on first start.

All⁴ we need to serve our application in production is:

• the static trail binary,
• the traildepot folder containing the data and endpoints,
• the dist folder containing our web app.

At the end of the day it’s just a bunch of hermetic files without transitively depending on a pyramid of shared libraries or requiring other services to be up and running like a separate database server. This makes it very easy to just copy the files over to your server or bundle everything in a single container. examples/coffeesearch/Dockerfile is an example of how you can both build and bundle using Docker. In fact,

$ docker build -t coffee . && docker run -p 4000:4000 coffee

will speed-run this entire tutorial by building and starting the app listening at http://localhost:4000/.

That’s it. We hope this was a fun little intro to some of TrailBase’s features. There’s more we haven’t touched on: CRUD APIs, auth, admin dash, file uploads, just to name a few. If you have any feedback, don’t hesitate and reach out on GitHub.

---

Footnotes

1. If you don’t have sqlite3 already installed, you can install it using brew install sqlite, apt-get install sqlite3, or download pre-built binaries ↩

2. Migrations are versioned SQL scripts that will be executed by the database on first encounter to programmatically and consistently evolve your database schema and data along with your code. For example, when you add a new column you’ll likely want all your integration tests, development setups, and production deployments to add the column so your application logic has a consistent schema to target. ↩

3. Unless explicitly disabled. ↩

4. To serve HTTPS you’ll either need a reverse proxy in front to terminate TLS or if you don’t require end-to-end encryption (e.g. you’re not using auth or handling sensitive data) you can fall back to TLS termination via a CDN like cloudflare. ↩