How to use Postgres with Biff

I've created an example repo that starts with a fresh Biff project and then modifies it to use Postgres instead of XTDB, for those who would like to use a traditional RDBMS. If you'd like to use Datomic or any other database, you can also use this as a more general guide for what parts of your project will need to be changed. If you're unfamiliar with XTDB, here's some background information on why it's the default in Biff.

I've split the steps up into separate commits; you can read those and apply the same changes in your own project (be sure to read the commits from bottom to top). I've written commentary on the changes below.

Also: since XTDB v2 is adding SQL as a first-class citizen, when that becomes stable I'll probably merge a lot of these changes into Biff's default template project (sans the actual Postgres bits). SQL would be the default since more people are familiar with it, but those who want to (including me!) can easily switch to Datalog.

1. Start a new project

Pretty straightforward. I've used com.biffweb.example.postgres for the main namespace.

2. Make `clj -M:dev dev` start a Postgres container with Docker

Diff. I copied over the source for the regular clj -M:dev dev command, updated namespaces appropriately, and then had it call a new start-postgres function in the background. That function will run docker pull postgres if needed and start up a Postgres container using the configuration you specify. The container will be deleted when you stop your app (Ctrl-C), but data will be persisted to the storage/postgres directory.

You'll need to have Docker set up already. As long as you can run docker ps successfully you should be good to go. After making the above changes, run clj -M:dev dev and make sure you don't get any error messages. You should be able to get an interactive prompt with  psql postgresql://user:abc123@localhost:5432/main.

3. Connect to Postgres from the REPL

Diff. I've added a use-postgres component that will run migrations on app startup, via a very simple approach courtesy of pesterhazy. The migrations.sql file creates all the tables corresponding to the existing schema both for the starter app and for the authentication module. I'm using next.jdbc as the Postgres client. I'm not using any SQL wrapper libs like HoneySQL or HugSQL, but you can add them yourself if desired.

Note: the diff includes a resources/config.template.env file which defines a DEV_POSTGRES_URL environment variable. You'll need to add that variable to config.env.

In dev/repl.clj, the XTDB examples have been replaced with Postgres examples. After starting up the app with clj -M:dev dev, you should be able to evaluate them via the REPL.

There is also a com.biffweb.example.postgres.util.postgres namespace which contains some functions used in the following sections.

4. Copy over the authentication module code

Diff. Biff's authentication module assumes you're using XTDB. We'll copy-and-paste it into our project so that we can modify it to use Postgres.

The original module uses a bunch of com.biffweb.impl.* namespaces which are not part of Biff's public API. All the functions are aliased in the public com.biffweb namespace however, so we can find-and-replace all the functions over to that.

5. Modify the authentication module to use Postgres

Diff. This a fairly mechanical transformation; we just look for all the places XTDB is used and translate that code to Postgres.

The original module had :biff.auth/get-user-id and :biff.auth/new-user-tx options which could be used to override the default implementations. Since the module is part of our project, there's no need for those options. If you want to change your user-creation code, you can just update util-pg/new-user-statement.

At this point, you should be able to visit localhost:8080 and log in to the app.

6. Modify the rest of your app to use Postgres

Diff. Basically the same thing as the previous step, but for the remaining parts of the codebase. I also renamed :com.biffweb.example.postgres/chat-clients to :example/chat-clients for convenience. I'll admit, some of these diffs are reminding me that even Biff's submit-tx wrapper is not as succinct as it could be for common use-cases.

This diff isn't a complete 1:1 translation because I haven't added an equivalent to XTDB's transaction listeners. Maybe there's a not-terribly-complicated way to add transaction listeners to Postgres (?), but I suspect if you want similar functionality, you'll want to add some queues (either via Postgres, or you could throw in Redis...) and publish/subscribe to those.

Normally, the starter Biff app uses transaction listeners to watch for new chat messages. Whenever a message comes in, the app sends it to all the currently connected clients via websocket. This works even if you have multiple web servers; each web server will send the chat message to their own set of clients.

In this Postgres repo, I've modified the send-message function (which normally just inserts new chat messages into XTDB) so that it also pushes new messages to websocket clients. This will work fine as long as you only have one web server, which I'm sure is the most common case for Biff users anyway.

The app should be fully functional now; head over to localhost:8080 again and give it a spin.

7. Remove the XTDB dependency

Diff. Now that all of our app's functionality has been migrated over to Postgres, we can remove any lingering XTDB usages, particularly the use-xt and use-tx-listener components. I've also set the :biff/merge-context-fn system map key to identity. The default function for that key uses the xtdb.api/db function to insert database objects into incoming Ring requests.

The final step is to exclude XTDB from our deps.edn dependencies. I haven't bothered to split Biff up into separate libraries that can be mixed-and-matched. Instead, I've created a com.biffweb/xtdb-mock library which includes replacements for all of the XTDB functions that Biff calls. If you actually call any of them, they throw an exception. Once you add this library to your dependencies, you can safely add com.xtdb/xtdb-core etc. to the :exclusions key for com.biffweb/biff.

Appendix: Biff's approach to project template customization -----------------------------------------------------------

Applying these changes manually will be fairly tedious, but at least it's easier than figuring out how to do it all on your own 🙂. For a new project, you could just fork this repo and change the namespaces, but there are a couple advantages if you instead create a new project the regular way and then apply the changes manually:

• You're guaranteed to be on the latest version of Biff (including any updates to the app template). I'm not planning to keep this repo up-to-date with new versions of Biff.
• You can combine this with other changes if you want, e.g. I'll soon be writing another how-to guide that shows how to replace/complement htmx with re-frame.
• You don't have to change the namespaces manually (including keyword namespaces in various places, like the config file).

(If you decide to fork this repo anyway, we can still be friends.)

I think leaving this as a manual process fits well with Biff, since I expect most Biff users will stick with the default stack anyway and I'd prefer to focus most my efforts there. This is probably the main conceptual difference between Biff and Kit, by the way. If you're interested in a framework that has robust support for automatically modifying the stack, even after you've created your project, then Kit's module system is definitely worth checking out.

That being said, going through this process has given me some ideas for some conventions that Biff could adopt around 3rd-party project templates. If you want to create and maintain one of those, feel free to message me or ask about it on #biff.