TrailBase is a fast, single-file, open-source application server with type-safe APIs, built-in JS/ES6/TS Runtime, Auth, and Admin UI built on Rust+SQLite+V8.
It's the kind of thing that let's you bootstrap a backend for you rich client-side application - mobile, desktop, PWA - in no time.
You can check out a demo of the admin dashboard online:
- user: admin@localhost
- password: secret
In this article we'll test-drive TrailBase by bootstrapping a database with coffee data, implementing a custom TypeScript HTTP
handler for finding the best matches using vector search, and deploying 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
CLI1 directly to import
examples/coffeesearch/arabica_data_cleaned.csv
with the following SQL
script:
-- First create the strictly typed "coffee" table.
CREATE TABLE IF NOT EXISTS 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 temporary data into the "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 to add the embedding later as part of our initial
database migrations shortly2.
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
3, 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:
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:
$ 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:
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.
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.
-
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 ↩ -
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. ↩ -
Unless explicitly disabled. ↩
-
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. ↩
Top comments (0)