Trailbase: Fast, single-file, open-source app server built using Rust and SQLite | Hacker News Reader

Trailbase: Fast, single-file, open-source app server built using Rust and SQLite

github.com

314 pointsthunderbong99 comments

One of the things I appreciate about pocketbase is GG's attention to detail with respect to security.

Is the string interpolation straight into the sql from the query string in your getting started docs (https://trailbase.io/getting-started/first-ui-app) safe? It smells a bit.

From a quick look (on mobile) I can see that the function takes parameters, but you're not using that.

parsePath(…).query is a URLSearchParams, so those parameters are just strings, and it’s an untagged template string, so it is indeed a trivial SQL injection vector. And the params argument was right there! I don’t understand quite how you make a mistake like that, but it’s extremely worrying. This is fundamental, basic stuff.

The fun aspects of this are that:

(a) I could actually imagine trailbase.js contents that would make it not SQL injection: you could have parsePath(…).query.get(…) return objects with a toString() that escaped SQL. This would raise even more questions, and I was sure it wouldn’t be the case, but it’s possible.

(b) You could make it work safely, converting interpolation into parameters, by using a tagged template string. This could require only a tiny change:

  return await query(
    sql`SELECT Owner, Aroma, Flavor, Acidity, Sweetness
      FROM coffee
      ORDER BY vec_distance_L2(
        embedding, '[${+aroma}, ${+flavor}, ${+acid}, ${+sweet}]')
      LIMIT 100`
  );

(You could even make it query`…`, but I think query(sql`…`) is probably wiser. As for the plusses I put in there, that’s to convert from strings to numbers.)

This is a concept that’s definitely been done seriously. The first search result I found: https://github.com/blakeembrey/sql-template-tag.

it looks like that the query function has a second parameter with a "params" array.

probably something like this would work then too (didn't test):

  return await query(
      `SELECT Owner, Aroma, Flavor, Acidity, Sweetness
         FROM coffee
         ORDER BY vec_distance_L2(
           embedding, '[?,?,?,?]')
         LIMIT 100`,
      [+aroma, +flavor, +acid, +sweet],
    );

it is nice because the query string is constant then and a prepared query could be cached..

So we’re clear, the tagged template string approach gives you a string that uses parameters properly (see the example in the README of that project that does it); it’s perfectly compatible with prepared-statement caching.

And actually on second consideration, you probably can’t use binding parameters here: you’re trying to slot numbers in as JSON values inside a string! Maybe you’d have to write vec_distance_L2(embedding, ?), with parameter JSON.stringify([+aroma, +flavor, +acid, +sweet]).

you are right, great comment!

:hide: thanks for pointing out this embarrassing plunder. No excuse, just feeling silly especially after being so careful throughout the rust base. It's fixed now. Please keep yelling if you find more issues

Well… a couple of things that could be done better in that example:

—⁂—

  const [data, setData] = useState<Array<Array<object>> | undefined>();

That type stinks. Kill the undefined bit by giving it an initial value, then you can skip the `?? []` later too; and replace Array<object> with the actual row type, probably naming it, maybe like this:

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

—⁂—

  const params = Object.entries({ aroma, flavor, acidity, sweetness })
    .map(([k, v]) => `${k}=${v}`)
    .join("&");

No need to construct the query string manually:

  const params = new URLSearchParams({ aroma, flavor, acidity, sweetness });

I confess I’m puzzled about this one too, as trailbase.js suggests you know about URLSearchParams, which is what you should use for all typical query string manipulation, parsing and generation.

Done. Thanks

There’s this whole thread about string handling. Does the Rust API into SQLite not support prepared statements?

You can insert arguments into an SQL statement with the (?1) style syntax. See the example here:

https://docs.rs/rusqlite/latest/rusqlite/

no, but the guy can also look at postgres.js that already implmented alot of the sql`SELECT * FROM ${table_name}` Where there vars are escaped. It ONLY works for postgres, but I'm sure you can make it work with SQLite as well.

It does: https://docs.rs/rusqlite/latest/rusqlite/struct.Statement.ht...

Mucking about with SQL strings, to me, is akin to writing your own crypto: Don't. Unless you must (because, say, it doesn't exist yet.) Doing this is asking for security problems later. Trust that the SQLite team (or any other SQL engine) have more experience and have provided a correct interface. And if that interface isn't correct, don't just run off an make your own- contribute a fix so we can keep all the lessons learned together.

Interesting to see all these Rust and TS server-side projects fumbling on the same mistakes early PHP 4 was crucified for.

What would actually be interesting is if you told us what those mistakes were, instead of just vagueposting.

I found the Trailbase documentation did a very poor job of describing what this is. It solely describes Trailbase in terms of differences from Pocketbase and Supabase (and that's even the first entry in the FAQ), which assumes the reader knows what Pocketbase and Supabase are. In contrast, the Pocketbase documentation got to the point very quickly.

I was aware of Supabase, but still was confused as to what this project actually does. The mention of "1 file" and "app server" wasn’t helpful either. Does that mean a single binary? One SQLite file? Does it execute other binaries? I’m not sure. In contrast, when I visited the PocketBase website - it provided a much clearer explanation of its purpose.

I thought it was going to be like OpenResty but with rust instead of lua.

"Application server" is an umbrella term and the documentation doesn't really help identifying what the perimeter is.

Beside, it states that it is so fast that there's no need for cache, and supports SQLite only. So it seems to target only very simple applications, eg. straightforward relational DB <-> Json Rest/HTTP.

> So it seems to target only very simple applications, eg. straightforward relational DB <-> Json Rest/HTTP.

Could you expand a bit on what more advanced capabilities you're missing for anything beyond a very simple app? I'm genuinely curious but also see SQLite often undersold

Processing beyond simple DB data retrieval or io dependent processes can be time consuming and fully justify a cache. Eg, geographic processing that goes beyond geoDB capabilities with millions of rows, eventually involving retrieval of data from third party providers can take long so you'll want to cache results one way or another.

No denying SQLite is very capable. However in my understanding it's designed for being used by a single application, it's not really scalable, and its functionalities are still limited compared to the big DBs. Anyway there's no single fit for all cases, and for core components flexibility is really important.

Thanks for expanding.

> retrieval of data from third party providers can take long so you'll want to cache results one way or another.

Agreed. Caching of slow external sources will always be beneficial (at least if you need it more than once :) ). You should use whatever makes the most sense. The argument is more that data from TB is already pretty quick, quick enough that you could even use it as a cache.

> However in my understanding it's designed for being used by a single application,

SQLite by design allows for concurrent writes and parallel reads from any number of processes.

> it's not really scalable,

What do you want to scale to? Postgres is fairly similar in terms of scalability, i.e. master writes + read replicas. Once you're going beyond that scale you get a lot more benefit out of more specialized, problem-oriented solutions.

> and its functionalities are still limited compared to the big DBs.

It full SQL and it's extensible. Depends on what you need. It certainly doesn't have the same rich ecosystem or flexibility of posgres where you can even swap out the storage engine. I was mostly curious, if there's some specific functionality you're missing.

> and for core components flexibility is really important.

Agreed: the right kind of flexibility for your problem.

I'll play devils advocate here: imagine you'd start to deeply depend on one specific postgres plugin and then that plugin get discontinued or you're hitting performance bottlenecks and you're to locked-in to move to a more specialized solution. Don't take this too seriously, I just wanna say that there's a balance.

Not entirely clear what it is, sounds a bit like https://redbean.dev/ with more app languages but no universal executable?

I love how the comparison articles are so flattering to the projects that this is inspired by and competes with.

https://trailbase.io/comparison/pocketbase/ https://trailbase.io/comparison/supabase/

They're great, mature and polished projects :). TrailBase has ways to go to get there

as a supabase team member, I must say that I also found it flattering.

nice work OP - i like this version of tech where we all get along. the project looks great, good luck!

Looks cool, but in what world is any of that 'single-file'? Like yes your examples are built out of single files, several of them in fact. And the sever certainly isn't a single file...

Pretty sure they mean the server itself is statically linked. It still depends on external files for any configuration / the applications themselves.

I guess "statically linked" just doesn't have the same ring to it. (And even with that, it still suggests using docker...)

You folks are spot on. Single file in this case means single binary that is statically liked, i.e. w/o dependencies on shared library allowing you to just copy it from machine to machine similar to e.g. k3s.

Whether to use docker or not is really up to you. It's just a way to deploy other assets, e.g. a static html bundle. More importantly it saves me the trouble of providing windows builds at this current time :)

People do refer to these standalone apps as “single-file” [1] but I find “single executable” [2] to be less ambiguous.

[1]: https://learn.microsoft.com/en-us/dotnet/core/deploying/sing...

[2]: https://nodejs.org/api/single-executable-applications.html

Java .wars, but native and talking HTTP.

That's "single-file deployment." I've never heard anyone shorten single-file deployment as just "single-file," until now.

You download a single executable file and execute it and it runs everything needed.

What is "application server" ? The documentation fails to address the most crucial aspect of their project: its purpose.

I'm missing something here. I'm familiar with TomCat, but that's an application server for JVM programs. Here the project specifies which technologies are used to develop it, but which kind of applications will it serve? Are the applications supported by this server using a specific runtime? Will it serve native applications? Sorry for the naive questions, I'm not into web development.

It's a continuation of a trend in which open source projects mimick the functionality of a tool called Firebase that Google bought in 2014 and is now part of Google Cloud (see also supabase and pocketbase). All these projects seem to be sort of like Redis in as much as they stick a datastore behind an API saving users from needing to use SQL and offering them a higher level API instead. And it also seems to do some sort of auth things.

I think it's meant for people who want to build a front end without building a backend.

It's kinda like only the backend part of a cms system. Say if you took the backend part of Wordpress/Joomla/Drupal and exposed apis for a user developed frontend.

> stick a datastore behind an API saving users from needing to use SQL and offering them a higher level API instead

supabase team here. Yes, we offer an auto-generated API (using PostgREST). But users can also connect directly to their Postgres database or through a connection pooler. People have preferences, so we offer options.

I don’t think this was (is? I don’t follow it too closely) the use case for Redis.

You don't think Redis is a datastore with an API?

pocketbase on rust. The demo gives a good impression, if I have time I'll play with your project

I came here to understand what is the use-case of this, thank you!

I also found it kind of hard to understand what it's for from the docs. Logging into the demo UI (linked on the GitHub README) helped a lot.

And uses V8 as the JS runtime so you can use modern JS language features (goja in pocketbase is ES5 only).

Well yes but how often do you have to write a JS extension for PocketBase that needs to be super performant and use modern JS features?

All JS I write in 2024 is ES6+ (usually more like ES2022+)

I think probably if you worked through line-by-line you’d find a lot of it is valid ES5 as well (and GoJa does actually support a lot of ES6, see https://github.com/dop251/goja/milestone/1?closed=1).

Also, what features do you need to code in JavaScript to extend PocketBase functionality?

Anything you want to write in JS. After all we are comparing Trailbase to Pocketbase, both of which allow you to write application logic in JS.

They do. My point is that for the vanishingly small number of use cases where you need to write application logic in PocketBase, and you need to write it in JavaScript, and you need to use unsupported super modern ES6 features, you might prefer Trailbase, but otherwise PocketBase is more polished at this point.

IMHO, this will greatly depend on your use-cases. If you have a rich client app, e.g. mobile or PWA, most of your logic will go into your client by default. However, there might be some code you want to run on the server in a trusted environment, or use some existing JS library for mobile, or have an otherwise mostly static web app, ... depends

This looks quite nice overall and the demo is slick.

It looks like backend APIs need to be written in JS and and then deployed as separate files. Not quite what I want, but I can appreciate why they went that route.

It'd be neat to have a project like this as a Rust crate, where you can write your own APIs in Rust and compile the whole thing to a single file.

How did you log in to the demo when the stated credentials in the README don’t work?

I haven't tried it but a local demo is said to be like a mkdir and a docker run incantation: https://trailbase.io/getting-started/starting-up

They worked for me last night.

"this as a Rust crate, where you can write your own APIs in Rust and compile the whole thing to a single file"

not quite the same but try loco.rs, for me its great

Always interesting to me these type of solutions choose SQLite (which is great) and not something like Postgres with Orioledb. With SQLite you have to also use Litestream for S3 backups and this is always something I'm surprised isn't found in these types of solutions.

You can no longer have a single executable running, you also need Postgres running.

That already exists and is called supabase. If you want to compete, you have to be different.

The project looks great !

But I wonder who the audience is... The website says "serve millions of customers from a tiny box". Who does this appeal to ?

Solo developers who have million of users, need very low latency, yet are happy with a just a SQLite database for their backend ?

I think the audience for these projects are frontend developers who don't want/need to write much of a backend. They need some kind of data store + a dumb CRUD API to use with their mobile app or javascript SPA.

But aren't these folks already served well by PocketBase ?

And by the time they get a million user, they will probably not be hosting on a single "tiny box" anyway. At what point do you think : okay, I need PocketBase, but ten times faster ?

I don't question people's projects. Creator obviously thinks there was a need and that they could do better than PocketBase. It's the same as asking why BSD when we have Linux. Why Wayland when we have Xorg. Who knows, maybe this will succeed PocketBase. Since when has competition been a bad thing?

If you want a reactive backend then this is similar to Convex’s open source backend, which also uses TS for writing backend functions and SQLite for persistence: https://github.com/get-convex/convex-backend You get a consistent client for React, simple file storage, built-in text search…

its very hard to host it, there is no official docker compose file

Agree it would be nice to have, there is an open PR with one.

The docs specifically warn against it as well. A “here be dragons”, at least they are honest.

The demo credentials return 401 Uncaught Error

same here, but you just have to try once or twice more, then it worked for me

Sorry, this is folks logging in and changing the password. Should have seen this coming :hide:. Instance gets reset every 60min. Not great but something to improve

I know naming a project is difficult so please don’t take offence when I write my first thought on reading the name trailbase and thinking “why would a project that wants to lead with speed name itself for moving slowly?” I’m sure they’re thinking of the other use of trail, not meandering or dragging.

I assumed it was meant to evoke "trailblaze".

Same.

That's very valid and also why it's funny. Naming is hard. To be honest, I didn't think much about "trailing", certainly trailing in terms of adoption :). I did like the sound of "trail run" on the command line.

Definitely considered a bunch of alternatives. "SecondBase" was fun :)

I find the "in 1 file" message confusing.

Does it mean sqlite, so just 1 file which is the database?

It means the application compiles down to one binary.

I wonder if they're using appendvfs, too https://www.sqlite.org/src/file/ext/misc/appendvfs.c

> This file implements a VFS shim that allows an SQLite database to be appended onto the end of some other file, such as an executable.

(A quick search of their codebase reveals no results for "appendvfs", so I guess not. So single-file was a lie!)

"single file" terminology in the context of applications does not typically include the DB. It is common for single file executables to use external config files and DB files.

But then how should redbean describe itself? There you have the executable plus any configuration files or other files you might possible need zipped inside one single file (and if you need a DB, also one more file for the sqlite database next to it, OK).

I'm aware, the same definition was given in the comment I was replying to.

I'm no rustacean but to me it looks like start() in https://github.com/trailbaseio/trailbase/blob/main/trailbase... consumes a file and returns a database connection. If it were to step into the file and seek out an embedded file system I suspect that function would look different.

It sounds weird to emphasize “rust and SQLite”, where the first one is a language and second is an application. Anyway, SQLite is C.

"single file"

https://github.com/trailbaseio/trailbase/blob/main/Cargo.loc...

I think the author means that it stores data on disk in a single file (aka SQLite!)

It's a single, statically linked binary to run everything. You don't have to worry about dynamic libraries dependencies or other assets.

Storage on disk is actually a couple of files of data bases, uploaded files, keys, config, ...

The admin UI is not good, text is overlapping everywhere, and want to see how you show relations in the UI, for me this is the first thing I look for in any DB UI

he uses solid, should choose sveltekit if he want beatifull UI

At least myself and one other have realized we can add users, 10/10 demo. Only the best test users.

How does this differ from Pocketbase?

From a quick reading:

- Lang: Go vs Rust

- JS Runtime: goja (ES5 only) vs V8

https://trailbase.io/comparison/pocketbase/

The tech looks cool but I don't understand what problem it solves

Demo site crashes with "uncaught error 401"

Sorry, this is folks logging in and changing the password. Should have seen this coming :hide:. Instance gets reset every 60min. Not great but something to improve

haha that's both hilarious and sad.

Who the hell changes passwords on a demo site?

Note the license is similar to AGPL. Don't expect to use it in any production apps, stick with Pocket base for now.

Reply on news.ycombinator.com