Forem: TrailBase

TrailBase: Fast & Open FireBase Alternative

TrailBase — Thu, 28 Nov 2024 14:03:09 +0000

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 itself claims

In this article, we'll compare TrailBase's performance to a few amazing, and certainly more weathered alternatives such as SupaBase, PocketBase, and vanilla SQLite.

Disclaimer

Generally benchmarks are tricky, both to do well and to interpret.
Benchmarks never show how fast something can theoretically go but merely how fast the author managed to make it go. Micro-benchmarks, especially, offer only a key-hole insights, which may be biased and may not apply to your workload.

Performance also doesn't exist in a vacuum. If something is super fast but doesn't do what you need it to do, performance is an illusive luxury. Doing less makes it naturally easier to go fast, which is not a bad thing, however means that comparing a specific aspect of a highly specialized solution to a more general one may be misleading, unfair or irrelevant for you.

We tried our hardest to give all contenders the best chance¹ ² and were initially surprised by the observed performance gap ourselves. We suspect that given how quick SQLite itself is for simple queries, even small overheads weigh heavily. If you have any suggestions on how to make anyone go faster, make it more apples-to-apples or generally see any issues, let us know. We hope that the results can still provide some interesting insights even with a chunky grain of salt. Ultimately, nothing beats benchmarking your own setup and workloads.

Insertion Benchmarks

Total Time for 100k Insertions

The graph shows the overall time it takes to insert 100k messages into a mock chat-room table setup. The less time it takes, the better.

Unsurprisingly, in-process vanilla SQLite is the quickest ³. All other setups add additional table look-ups for authorization, IPC overhead⁴, and layers of features on top. Think of the vanilla SQLite point as an upper bound on how fast one could go or the cost for adopting any of the other systems.

The measurement suggests that for this specific setup TrailBase can insert 100k records almost 70 times faster than Payload², 9 to 16 times faster than SupaBase⁵, and roughly 6 to 7 times faster than PocketBase ¹.

Total time of inserting a large batch of data tells only part of the story. Let's have a quick look at resource consumption to get an intuition for provisioning or footprint requirements, i.e. what kind of machine one would need:

TrailBase & PocketBase Utilization

The graph shows the CPU utilization and memory consumption (RSS) of both PocketBase and TrailBase. They look fairly similar apart from TrailBase finishing earlier. They both load roughly 3 CPUs with PocketBase's CPU consumption being slightly more variable ⁶. The little shelf at ~1 CPU after the TrailBase run is likely due to SQLite check-pointing.

Both consume only about 140MB of memory at full tilt, which makes them a great choice for running on a tiny VPS or a toaster.

SupaBase is a bit more involved due to its layered architecture including a dozen separate services providing various functionality:

SupaBase Memory Usage

Looking at SupaBase's memory usage, it increased from from roughly 6GB at rest to 7GB fully loaded. This means that out of the box, SupaBase has roughly 50 times the memory footprint of either PocketBase or TrailBase. In all fairness, a lot of SupaBase' itself claimss functionality isn't needed for this benchmark and it might be possible to shed less critical services, e.g. removing supabase-analytics would save ~40% of memory. That said, we don't know how feasible this is in practice.

SupaBase CPU utilization

Looking at the CPU usage, one can see it jump up to roughly 9 cores (the benchmark ran on a machine with 8 physical cores and 16 threads: 7840U). Most of the CPUs seem to be consumed by supabase-rest, the API frontend, with Postgres itself hovering at only about 0.7 cores. Also, supabase-analytics definitely seems to be in use.

Latency and Read Performance

Let's take a closer look at latency distributions. To keep things manageable we'll focus on PocketBase and TrailBase, which are architecturally simpler and more comparable.

For TrailBase, reads were on average 3.5 and insertions 6 times faster. That latter is in line with the throughput results we've seen above.

Looking at the latency distributions we can see that the spread is well contained for TrailBase. For PocketBase, read latencies are also generally well contained and predictable. However, insert latencies show a more significant "long tail" with the p90 latency being roughly 5 times slower than p50. Slower insertions can take north of 100ms. This may or be related to GC pauses, scheduling, or more generally the CPU variability we observed earlier.

JavaScript-Runtime Benchmarks

The benchmark sets up a custom HTTP endpoint /fibonacci?n=<N> using the same slow recursive Fibonacci implementation for both, PocketBase and TrailBase. This is meant as a proxy for a computationally heavy workload to primarily benchmark the performance of the underlying JavaScript engines: goja for PocketBase and V8 for TrailBase. In other words, the impact of any overhead within PocketBase or TrailBase is diminished by the time it takes to compute fibonacci(N) for sufficiently large N.

We found that for N=40, V8 (TrailBase) is around 40 times faster than goja (PocketBase):

Interestingly, PocketBase has an initial warm-up of ~30s where it doesn't parallelize. Not being familiar with goja's execution model, one would expect similar behavior for a conservative JIT threshold in combination with a global interpreter lock 🤷. However, even after using all cores completing the benchmark takes significantly longer.

With the addition of V8 to TrailBase, we've experienced a significant increase in the memory baseline dominating the overall footprint. In this setup, TrailBase consumes roughly 4 times more memory than PocketBase. If memory footprint is a major concern for you, constraining the number of V8 threads will be an effective remedy (--js-runtime-threads).

Final Words

We're very happy to confirm that TrailBase's APIs and JS/ES6/TS runtime are quick. The significant performance gap we observed, especially for the APIs, might just be a consequence of how much even small overheads matter given how quick SQLite itself is.

With the numbers fresh off the press, prudence is of the essence and ultimately nothing beats benchmarking your own specific setup and workloads. In any case, we hope this was at least somewhat insightful. Let us know if you see anything that can or should be improved. The benchmarks are available on GitHub.

Trying to give PocketBase the best chance, the binary was built with the latest go compiler (v1.23.1 at the time of writing), CGO_ENABLED=1 (which according to PB's own documentation will use a faster C-based SQLite driver) and GOAMD64=v4 (for less portable but more aggressive CPU optimizations). We found this setup to be roughly 20% faster than the static, pre-built binary release. ↩
We picked Payload as representative of popular Node.js CMS, which itself claims to be many times faster than popular options like Strapi or Directus. We were using a v3 pre-release, as recommended, also using the SQLite/drizzle database adapter marked as beta. We manually turned on WAL mode and filed an issue with payload, otherwise stock payload was ~210x times slower. ↩
Our setup with drizzle and node.js is certainly not the fastest possible. For example, we could drop down to SQLite in C or another low-level language with less FFI overhead. That said, drizzle is a great popular choice which mostly serves as a point-of-reference and sanity check. ↩
The actual magnitude on IPC overhead will depend on the communication cost. For the benchmarks at hand we're using a loopback network device. ↩
The SupaBase benchmark setup skips row-level access checks. Technically, this is in its favor from a performance standpoint, however looking at the overall load on its constituents with PG being only a sliver, it probably would not make much of an overall difference nor would PG17's vectorization, which has been released since the benchmarks were run. That said, these claims deserve re-validation. ↩
We're unsure as to what causes these 1-core swings. Runtime-effects, such as garbage collection, may have an effect, however we would have expected these to show on shorter time-scales. This could also indicate a contention or thrashing issue 🤷. ↩

TrailBase: Building an App with UI, Admin Dash and Vector Search in ~100 Lines

TrailBase — Sat, 23 Nov 2024 10:37:11 +0000

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 CLI¹ 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 shortly².

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.

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:

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.

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.

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