DEV Community

Kir Axanov
Kir Axanov

Posted on

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

#programming #gleamlang #sqlite

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();
Enter fullscreen mode Exit fullscreen mode

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
  })
}
Enter fullscreen mode Exit fullscreen mode

Enable WAL-mode

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

PRAGMA journal_mode = WAL;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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!

Top comments (0)