cljfx VS litestream

More broadly, non-Electron apps have been uncommon for a while. I just don't work in the webspace and I need to write code that's relatively performant. I understand it's all possible with Electron.. but it's a bit daunting to have a client-server web stack and multiple languages and some numbercrunching backend

JavaFX was a nice solution that's crossplatform and near-native. It has a very nice react-like library in Clojure called `cljfx`

https://github.com/cljfx/cljfx/

For desktop, there is cljfx which is a JavaFX wrapper, and has a similar API to reagent.

If you have an application server then you still have RPCs coming from your user interface, even if you run the whole DB in process. And indeed POSIX has nothing to say about this. Instead people tend to abuse HTTP as a pseudo-RPC mechanism because that's what the browser understands, it tends to be unblocked by firewalls etc.

I think there's a better way to structure things to get that same simplicity and in fact even more, but without many of the downsides. I'm planning to write about it more at some point on my company blog (https://hydraulic.software/blog.html) but here's a quick summary. See what you think.

---

In a traditional 3-tier CRUD web app you have the RDBMS, then stateless web servers, then JavaScript and HTML in the browser running a pseudo-stateless app. Because browsers don't understand load balancing you probably also have an LB in there so you can scale and upgrade the web server layer without user-visible downtime. The JS/HTML speaks an app specific ad-hoc RPC protocol that represents RPCs as document fetches, and your web server (mostly) translates back and forth between this protocol and whatever protocol your RDBMS speaks layering access control on top (because the RDBMS doesn't know who is logged in).

This approach is standard and lets people use web browsers which have some advantages, but creates numerous problems. It's complex, expensive, limiting for the end user, every app requires large amounts of boilerplate glue code, and it's extremely error prone. XSS, XSRF and SQL injection are all bugs that are created by this choice of architecture.

These problems can be fixed by using "two tier architecture". In two tier architecture you have your RDBMS cluster directly exposed to end users, and users log in directly to their RDBMS account using an app. The app ships the full database driver and uses it to obtain RPC services. Ordinary CRUD/ACL logic can be done with common SQL features like views, stored procedures and row level security [1][2][3]. Any server-side code that isn't neatly expressible with SQL is implemented as RDBMS server plugins.

At a stroke this architecture solves the following problems:

1. SQL injection bugs disappear by design because the RDBMS enforces security, not a highly privileged web app. By implication you can happily give power users like business analysts direct SQL query access to do obscure/one-off things that might otherwise turn into abandoned backlog items.

2. XSS, XSRF and all the other escaping bugs go away, because you're not writing a web app anymore - data is pulled straight from the database's binary protocol into your UI toolkit's data structures. Buffer lengths are signalled OOB across the entire stack.

3. You don't need a hardware/DNS load balancer anymore because good DB drivers (e.g. JDBC drivers) understand clustering and load balancing directly.

4. You don't need to design ad-hoc JSON/REST protocols that e.g. frequently suck at pagination or handling of large results, because you can just invoke server-side procedures directly. The DB takes care of argument serialization, streaming of results, type safety, access control and more.

5. With some databases like PostgreSQL you get server push/notifications for free.

6. The protocol gives you batching for free, so if you have some server logic written in e.g. JavaScript, Python, Kotlin, Java etc then it can easily use query results as input or output and you can control latency costs.

This architecture lacks popularity today because to make it viable you need a few things that weren't available until very recently (and a few useful things still aren't yet). At minimum:

1. You need a way to distribute and update GUI desktop apps that isn't incredibly painful, ideally one that works well with JVM apps because JDBC drivers tend to have lots of features. Enter my new company, stage left (yes! that's right! this whole comment is a giant ad for my product). Hydraulic Conveyor was launched in July and makes distributing and updating desktop apps as easy as with a web app [4].

2. You need databases with really flexible ACLs. Free DBs like PostgreSQL didn't support RLS until somewhat recently.

3. You need solid UI toolkits with modern themes. JetBrains has ported the new Android UI toolkit to the desktop [5] allowing lots of code sharing. It's reactive and thus has a Kotlin language dependency. JavaFX is a more traditional OOP toolkit with CSS support, good business widgets and is accessible from more languages for those who prefer that; it also now has a modern GitHub-inspired SASS based style pack that looks great [6] (grab the sampler app here [7]). For Lispers there's a reactive layer over the top [8].

4. There's some smaller tools that would be useful e.g. for letting you log into your DB with OAuth, for ensuring DB traffic can get through proxies.

Downsides?

1. Migrating between DB vendors is maybe harder. Though, the moment you have >1 web server you have the problem of doing a 'live' migration anyway, so the issues aren't fundamentally different, it'd just take longer.

2. Users have install your app. That's not hard and in a managed IT environment the apps can be pushed out centrally. Developers often get hung up on this point but the success of the installed app model on mobile, games consoles and Steam shows users don't actually care much as long as they plan to use the app regularly.

3. To do mobile/tablet you'd want to ship the DB driver as part of your app. There might be oddities involved, though in theory JDBC drivers could run on Android and be compiled to native for iOS using GraalVM.

4. Skills, hiring, etc. You'd want more senior devs to trailblaze this first before asking juniors to learn it.

[1] https://www.postgresql.org/docs/current/ddl-rowsecurity.html

[2] https://docs.microsoft.com/en-us/sql/relational-databases/se...

[3] https://docs.oracle.com/database/121/TDPSG/GUID-72D524FF-5A8...

[4] https://hydraulic.software/

[5] https://www.jetbrains.com/lp/compose-mpp/

[6] https://github.com/mkpaz/atlantafx

[7] https://downloads.hydraulic.dev/atlantafx/sampler/download.h...

[8] https://github.com/cljfx/cljfx

JavaFX is a mature and powerful toolkit easily accessed from many different languages. You don't have to use Java and can even layer on top entirely different paradigms, like cljfx does with a ReactJS/Compose style functional rerendering system. It's also accessible to Truffle languages. But the default Modena theme looks dated, and JavaFX lacks a small number of standard modern controls like an animated toggle switch.

cljfx is awesome

You could go the .NET + Windows SDK route if you don't care about cross platform. If you do want cross platform, and don't want to go JavaFX route, checkout pivot by apache. If you want to be cool, check out cljfx

Super excited about this. I've been using cljfx (https://github.com/cljfx/cljfx) to build a few apps and it's so developer efficient.

In Clojure you can use JavaFX through cljfx

https://github.com/cljfx/cljfx/

I haven't used React, but I think it's very similar. You have a datastructure that represents the GUI and associated callbacks that modify the GUI. It doesn't use hiccup, but it's a similar structure. I've got to say it was fantastic. The least painful GUI programming I've ever done. The core architecture isn't opinionated so there is a little boilerplate to setup at first, but then it's very smooth sailing. I release an app using it and it was very performant, used some Java CV libs and drew diagrams to the canvas and it was all very snappy.

My only minor complaint was that the final bundle was like 150MB, which given the scope of the app seemed a bit gross - but it's manageable. In theory you could trim that further with GraalVM

Emacs/Cider - https://docs.cider.mx/cider/index.html

> and library ecosystem (building websites, parallelism, GUI, etc...)

Websites - Reagent, a ClojureScript wrapper for React https://github.com/reagent-project/reagent

GUIs - Clojure has the entirety of Java to steal GUIs from - Ex: https://github.com/cljfx/cljfx

Just because the Lisp family is old and has a simple syntax does not mean it's an antiquated language or that there are not new developments going on there.

There's https://github.com/cljfx/cljfx which turns it into something like React. And you can use the opportunity to learn Clojure ;)

I have not, but I keep meaning to collate everything I've learned into a set of useful defaults just to remind myself what settings I should be enabling and why.

Regarding Litestream, I learned pretty much all I know from their documentation: https://litestream.io/

This presentation is focused on the use-case of vertically scaling a single server and driving everything through that app server, which is running SQLite embedded within your application process.

This is the sweet-spot for SQLite applications, but there have been explorations and advances to running SQLite across a network of app servers. LiteFS (https://fly.io/docs/litefs/), the sibling to Litestream for backups (https://litestream.io), is aimed at precisely this use-case. Similarly, Turso (https://turso.tech) is a new-ish managed database company for running SQLite in a more traditional client-server distribution.

This post intends to help you setup replication for SQLite using Litestream.

I've been messing around with litestream. It is so cool. And, I either found a bug in the -timestamp switch or don't understand it correctly.

What I want to do is time travel into my sqlite database. I'm trying to do some forensics on why my web service returned the wrong data during a production event. Unfortunately, after the event, someone deleted records from the database and I'm unsure what the data looked like and am having trouble recreating the production issue.

Litestream has this great switch: -timestamp. If you use it (AFAICT) you can time travel into your database and go back to the database state at that moment. However, it does not seem to work as I expect it to:

https://github.com/benbjohnson/litestream/issues/564

I have the entirety of the sqlite database from the production event as well. Is there a way I could cycle through the WAL files and restore the database to the point in time before the records I need were deleted?

Will someone take sqlite and compile it into the browser using WASM so I can drag a sqlite database and WAL files into it and then using a timeline slider see all the states of the database over time? :)

We're using SQLite in production very heavily with millions of databases and fairly high operations throughput.

But we did run into some scariness around trying to use Litestream that put me off it for the time being. Litestream is really cool but it is also very much a cool hack and the risk of database corruption issues feels very real.

The scariness I ran into was related to this issue https://github.com/benbjohnson/litestream/issues/510

Litestream is a library that allows you to easily create backups. You can probably just do analytic queries on the backup data and reduce load on your server.

https://litestream.io/

One possible strategy is to have one directory/file per customer which is one SQLite file. But then as the user logs in, you have to look up first what database they should be connected to.

OR somehow derive it from the user ID/username. Keeping all the customer databases in a single directory/disk and then constantly "lite streaming" to S3.

Because each user is isolated, they'll be writing to their own database. But migrations would be a pain. They will have to be rolled out to each database separately.

One upside is, you can give users the ability to take their data with them, any time. It is just a single file.

[0]. https://litestream.io/

Upstream Kuma uses a local SQLite database to store account data, configuration for services to monitor, notification settings, and more. To make sure that our data is available across redeploys, we will bundle Uptime Kuma with Litestream, a project that implements streaming replication for SQLite databases to a remote object storage provider. Effectively, this allows us to treat the local SQLite database as if it were securely stored in a remote database.