Skip to content
TrailBase

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.

screenshot

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

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

Importing Data

Before building the app, let’s import some data. Keeping it simple, we’ll use the sqlite3 CLI1 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 migrations2.

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

Terminal window
$ 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:

Terminal window
$ trail run

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

UPDATE coffee SET embedding = vec_f32(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 run3, JavaScript and TypeScript files under traildepot/scripts will be executed.

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 vec_distance_L2(
           embedding, FORMAT("[%f, %f, %f, %f]", $1, $2, $3, $4))
         LIMIT 100`,
      [+aroma, +flavor, +acid, +sweet],
    );
  }),
);

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

Terminal window
$ 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);


  type Record = [string, number, number, number, number];
  const [data, setData] = useState<Record[]>([]);


  useEffect(() => {
    const URL = import.meta.env.DEV ? "http://localhost:4000" : "";
    const params = new URLSearchParams({
      aroma: `${aroma}`,
      flavor: `${flavor}`,
      acidity: `${acidity}`,
      sweetness: `${sweetness}`,
    });


    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:

Terminal window
$ 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:

Terminal window
$ npm install && npm build

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

Terminal window
$ 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.

All4 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. ↩