Code. Gleam, SQLite and concurrent transactions walked to a bar

Hi!

Gleam + SQLite is a nice choice for a side-project.
But when this project grows and demands parallel writes into the same DB, we can get stuck as SQLite does not support this out of the box.
Nevertheless, we can achieve trully concurrent (espetially write) transactions in SQLite if we:

• use SQLite compiled from begin-concurrent branch
• use WAL-mode
• use own connection for each writer
• wrap the query into begin concurrent transaction; - commit; block

DISCLAIMER

The actions below are semi-manual and require automation. Feel free to participate!

Replace SQLite in project

I use SQLite via the sqlight Gleam package, which uses esqlite Erlang library under the hood, which has the SQLite embedded into it.
So, all we have to do is to replace the embedded SQLite in esqlite build directory.

1. Find out the needed SQLite revision on begin-concurrent branch here.
2. Download the archive from Overview -> Downloads -> Tarball.

3. Unpack the archive (replace the REVISION):
   

   gzip -d SQLite-REVISION.tar.gz
   tar xvf SQLite-REVISION.tar
   cd SQLite-REVISION
   

4. Build the amalgamation (needs gnumake package installed):
   

   ./configure && make sqlite3.c
   

5. Build the project:
   

   gleam build
   

6. Place generated sqlite3.c and sqlite3.h to esqlite package directory (replace PROJECT_ROOT):
   

   cp sqlite3.c PROJECT_ROOT/build/packages/esqlite/c_src/sqlite3/
   cp sqlite3.h PROJECT_ROOT/build/packages/esqlite/c_src/sqlite3/
   

7. Remove previous build artifacts and rebuild (replace PROJECT_ROOT):
   

   rm -rf PROJECT_ROOT/build/dev
   gleam build
   

8. Now SQLite should be compiled to PROJECT_ROOT/build/dev/erlang/esqlite/priv/esqlite3_nif.so (I like to put it into project's git too, you do you).

Verify installed version

We can check the version of SQLite, which is used for the connection to DB, with a query:

SELECT sqlite_version();

Here is a simple PoC:

import gleam/dynamic/decode
import sqlight


pub fn main() {
  use conn <- with_db("my-test-db.sqlite")

  "SELECT sqlite_version();"
  |> sqlight.query(on: conn, with: [], expecting: decode.dynamic)
  |> echo
}

fn with_db(
  db_path: String,
  do_something: fn(sqlight.Connection) -> Result(a, sqlight.Error),
) -> Result(a, sqlight.Error) {
  { "file:" <> db_path <> "?mode=rwc" }
  |> sqlight.open()
  |> try(fn(conn) {
    let result = do_something(conn)
    let _ = sqlight.close(conn)
    result
  })
}

Enable WAL-mode

To enable the WAL-mode for the entire DB we use this pragma:

PRAGMA journal_mode = WAL;

You can use the code from previous part, just replace the query.

If you want to return to a default journal mode, then use those queries (although I'm not sure you necessary need the wal_checkpoint pragma here too):

PRAGMA wal_checkpoint;
PRAGMA journal_mode = DELETE;

Considerations

Keep in mind the following:

• writing to a table in one job will break the other job reading the same table - use intermediate tables to update data
• CREATE and DELETE queries lock the DB, so if you need some intermediate tables - create them before starting concurrent jobs
• single connection executes all queries in sequential order - use multiple connections for concurrent jobs
• long-running queries can cause BusySnapshot error - try to split them to smaller operations / datasets

Replace SQLite in NixOS

It's a good idea to use the same SQLite system-wide, so here are the steps to install it from the same revision.

1. Clone nixpkgs repo:
   

   git clone --depth=1 https://github.com/NixOS/nixpkgs.git
   

2. Find out the needed SQLite revision on begin-concurrent branch here.

3. Download the archive from Overview -> Downloads -> Tarball. Place it to nixpkgs/pkgs/development/libraries/sqlite for convenience.

4. Edit nixpkgs/pkgs/development/libraries/sqlite/default.nix to point to the downloaded archive (replace REVISION, exact url and hash values may vary):
   

   - src = fetchurl {
   -   url = "https://sqlite.org/2025/sqlite-autoconf-${archiveVersion version}.tar.gz";
   -   hash = "sha256-hKYW/9MXOORZC2W6uzqeHvk3DzY4422yIO4Oc/itIVY=";
   - };
   + src = ./SQLite-REVISION.tar.gz;
   

5. Build and install the package:
   

   nix-build . -A sqlite
   nix-env -i ./result-bin/
   

6. Check the installed version:
   

   nix-env -q  # should list the sqlite package with the version from nixpkgs repo as we didn't change it
   sqlite3 --version  # should print the exact version which we downloaded
   

Bye!