# Fortran Relational Database **URL:** \ **Category:** Announcements\ **Created:** [June 18, 2026, 8:30pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974 "2026-06-18T20:30:47Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![simong](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@simong](https://fortran-lang.discourse.group/u/simong)\ **Post date:** [June 18, 2026, 8:30pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/1 "2026-06-18T20:30:47Z")
Fortran Relational Database This is a self-contained relational database written entirely in Fortran, see the README below for details. All the code has been written by Claude Opus 4.8 - about 13k lines in total including about 5.6k for the engine. My contribution has been the overall design, style, refactoring, testing requirements, clean builds, platform requirements and code reviews. It has had many reviews for which I used Codex, Antigravity, Fable 5 as well as Opus 4.8 in _ultrathink_ mode. We even found a bug in gfortran (125866) which is being fixed. All development was done on Linux (Mageia 9). Both ‘make’ and ‘fpm’ can be used to build it and FORD documentation is included. It is known to build and run on Linux with ifx 2026.0.0, gfortran 16.1.0, flang 23.0.0git ; on Windows with mingw64 and tested with Wine in podman. So where might this be useful? In keeping with the development, I asked Claude: > A Fortran program that needs indexed, transactional, queryable local storage — but doesn’t want to leave the language, link C/SQLite, or stand up a database server. Anywhere you’d currently reach for ad-hoc flat files, unformatted dumps, or a fragile NetCDF/CSV scheme, sqr offers indexed lookup + crash-safety instead. > **[GitHub - sgeard/sqr: A pure Fortran relational database with ACID...](https://github.com/sgeard/sqr)** > > A pure Fortran relational database with ACID transactions (single-writer) ``` Abridged README.md ``` # sqr — a pure-Fortran relational store `sqr` is a lightweight, embeddable relational storage engine written entirely in modern Fortran. It stores tables as fixed-record binary files in a directory, with on-disk B+tree secondary indices, a physical rollback journal for crash-safe transactions, and two interactive front-ends — a state-graph shell (`sqrsh`) and a small SQL-subset REPL (`sqlsh`). It is deliberately scoped for the small-to-medium workloads a single program needs (10⁴–10⁶ rows), not for postgres-scale concurrency. Access is **single-writer / multi-reader** : an advisory lock admits one read-write connection _or_ any number of read-only ones at a time — there is no concurrent-writer (per-row / MVCC) isolation. The design goal is **integrity first** : every mutation is write-ahead journalled and survives a crash, even at the cost of an `fsync` per write. * * * ## Features - **A database is a directory.** No server, no daemon — open a path. - **Typed columns:** `DT_INT` (32-bit), `DT_REAL` (64-bit), `DT_CHAR` (fixed-width, NUL-padded), `DT_TEXT` (length-prefixed blob, binary-safe). - **Per-row NULL bitmap** — a NULL column reads back as absent and is omitted from any index it belongs to (partial-index semantics). - **On-disk B±tree indices** , including composite keys, uniqueness, range scans and cursors. The `b_tree` module is fully decoupled from `sqr` and is independently reusable. - **Schema evolution:** `ADD COLUMN` / `DROP COLUMN` by table rewrite, with row ids preserved (no index rebuild) and `DROP` cascading to dependent indices. - **Crash-safe transactions:** explicit `db_begin`/`db_commit`/`db_rollback`, plus auto-commit brackets around every single-row mutator. See [ACID guarantees](#acid-guarantees) below. - **Single-writer / multi-reader locking:** an advisory lock taken on open admits one writer or many readers; contention is reported as `SQR_LOCKED`, and `db_set_readonly` demotes a writer to let readers in. - **No `error stop` in library code** — every entry point reports via optional `stat` / `errmsg` arguments. - **Procedural _and_ object-oriented APIs:** call `db_insert(db, ...)` or `db%insert(...)`. - **Two front-ends:** `sqrsh`, a cmdgraph state-graph shell over the engine, and `sqlsh`, a small SQL subset (a separate `sql` front-end layer that only calls the public API — no SQL in the store). See below. - **Portable:** clean on `ifx` and `gfortran`, builds under `fpm`, and cross-builds to Windows (mingw-w64, wine-validated). * * * ## Quick start (Fortran API) ```fortran use :: sqr type(db_t), target :: db type(column_t) :: cols(2) character(len=:), allocatable :: buf integer :: st, ti integer(int32) :: rid call db_open(db, 'mydb', stat=st) ! a database is a directory cols(1)%name = 'id'; cols(1)%dtype = DT_INT; cols(1)%csize = 4 cols(2)%name = 'name'; cols(2)%dtype = DT_CHAR; cols(2)%csize = 32 call db_create_table(db, 'people', cols, st) ti = db_table_index(db, 'people') call row_alloc(buf, db%tables(ti)%record_size) call row_set_int (buf, db%tables(ti)%cols(1), 1_int32) call row_set_char(buf, db%tables(ti)%cols(2), 'Ada') call db_insert(db, 'people', buf, rid, st) ! rid = new row id call db_create_index(db, 'people', 'id', st) ! on-disk B+-tree call db_find_by_int(db, 'people', 'id', 1_int32, rid, st) call db_close(db, st) ``` Wrap a group of changes in an explicit transaction when you need them to commit (and fail) as a unit: ```fortran call db_begin(db, st) ! ... several inserts / updates / deletes ... call db_commit(db, st) ! durable here; or db_rollback(db, st) ``` * * * ## The `sqrsh` shell `sqrsh` is a small state-graph REPL over the engine. The command set: ```auto root: open close readonly tables desc create
use
drop
quit creator: col done cancel quit table: insert ... select get delete compact addcolumn dropcolumn index [unique] ... dropindex ... verify find range match getk ... delk ... back quit ``` * * * ## The `sqlsh` shell (SQL subset) `sqlsh` is a second, independent front-end: a familiar SQL “shop window” over the same engine. It is a **front-end layer only** — the `sql` module (lexer, parser, executor) and the REPL call nothing but the public `db_*` API, so the dependency runs one way (`sql` uses `sqr`, never the reverse) and nothing about the on-disk format changes. The store itself has no notion of SQL. ```auto sqlsh mydb < script.sql # run a script (results on stdout) sqlsh mydb # interactive (prompts/errors on stderr) ``` Meta-commands: `.open `, `.close`, `.tables`, `.schema [table]`, `.help`, `.quit`. Everything else is SQL: ```sql CREATE TABLE employee (id INTEGER, name CHAR(20), dept CHAR(12), salary REAL); CREATE INDEX ON employee (dept); INSERT INTO employee VALUES (1,'Alice','eng',55000.0), (2,'Bob','eng',48000.0); SELECT name, salary FROM employee WHERE dept = 'eng' ORDER BY salary DESC LIMIT 5; UPDATE employee SET salary = 50000.0 WHERE dept = 'sales' AND salary < 50000.0; DELETE FROM employee WHERE salary < 40000.0; ``` * * * ## On-disk layout A database is a directory containing: | File | Contents | | --- | --- | | `_catalog.dat` | top-level catalog: the list of table names | | `
.schema` | per-table schema header + column definitions | | `
.dat` | fixed-size records (`recl = record_size`) | | `
.blob` | length-prefixed `DT_TEXT` values | | `
__i.idx` | one paged B±tree per secondary index | | `_journal.dat` | rollback (undo) journal; present only while a txn is open or pending recovery | | `_lock` | zero-byte sentinel carrying the advisory open lock | Each record is: 1 status byte (`ROW_ALIVE` / `ROW_TOMBSTONE`), then a `(ncols+7)/8`-byte NULL bitmap, then column data at fixed offsets. * * * ## ACID guarantees `sqr` is honest about what it provides. The store is **single-writer / multi-reader**: at most one read-write connection, or any number of read-only connections, at a time — enforced by an advisory lock taken on `db_open`. It does _not_ provide fine-grained (per-row / MVCC) isolation between concurrent writers. | Property | Status | How | | --- | --- | --- | | **Atomicity** | ✅ Yes | Physical undo journal; a failed or rolled-back transaction restores every touched region. | | **Consistency** | ✅ Yes, including across a crash | Recovery on `db_open` replays the journal, restoring data, blob and index files together. | | **Durability** | ✅ Yes | Strict write-ahead: each undo image is `fsync`’d to the hot journal _before_ the base write it guards. Commit `fsync`s every modified file, then voids the journal header — the single durable commit point. | | **Isolation** | ◑ Coarse | An advisory lock on `_lock` admits one writer **xor** many readers. A second writer (or a reader while a writer is active) is refused with `SQR_LOCKED`. `db_set_readonly` downgrades a writer so readers may attach. No concurrent-writer / row-level isolation. | Locking is whole-database advisory: `flock(2)` on POSIX, `LockFileEx` on Windows. It is released on `db_close` and automatically by the OS if the process dies, so a crashed writer never wedges the database. The durability path is deliberately conservative — an `fsync` per write — because the project prioritises integrity over throughput. * * * ---
**Author:** ![Arjen](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@Arjen](https://fortran-lang.discourse.group/u/Arjen)\ **Post date:** [June 19, 2026, 9:12am UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/2 "2026-06-19T09:12:50Z")
I tried to build it on a plain Windows system, but it is lacking the “touch” command and the default compiler, ifx, complains about a few options. No success yet. ---
**Author:** ![Arjen](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@Arjen](https://fortran-lang.discourse.group/u/Arjen)\ **Post date:** [June 19, 2026, 9:21am UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/3 "2026-06-19T09:21:07Z")
When I built it under WSL (on the same system), it all worked fine (using the gfortran compiler). The tests all pass. I did explicitly set the compiler to gfortran. ---
**Author:** ![Arjen](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@Arjen](https://fortran-lang.discourse.group/u/Arjen)\ **Post date:** [June 19, 2026, 9:33am UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/4 "2026-06-19T09:33:49Z")
(I only later saw the build instructions: on Windows you need MinGW 😊) ---
**Author:** ![simong](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@simong](https://fortran-lang.discourse.group/u/simong)\ **Post date:** [June 19, 2026, 1:15pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/5 "2026-06-19T13:15:11Z")
I don’t have a Win machine so mingw64 and Wine in podman was the only way to test it. ---
**Author:** ![Arjen](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@Arjen](https://fortran-lang.discourse.group/u/Arjen)\ **Post date:** [June 19, 2026, 2:54pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/6 "2026-06-19T14:54:03Z")
Clear enough, I can try and see if I can make it work on plain Windows. It looks simple enough (that is: just a few libraries whose source is included and then some programs). ---
**Author:** ![Arjen](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@Arjen](https://fortran-lang.discourse.group/u/Arjen)\ **Post date:** [June 19, 2026, 2:58pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/7 "2026-06-19T14:58:42Z")
(A long time ago I wrote an interface from Fortran to SQLIte. I have been contemplating rewriting it using ISO\_C\_BINDING and the like, but that will probably have to wait until my retirement. Which is not all that long in the future 😊) ---
**Author:** ![simong](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@simong](https://fortran-lang.discourse.group/u/simong)\ **Post date:** [June 19, 2026, 3:51pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/8 "2026-06-19T15:51:47Z")
I have now retired which is why these projects that have been on the back-burner for the last twenty years are finally getting some tlc. ---
**Author:** ![eugene\_epshteyn](https://avatars.discourse-cdn.com/v4/letter/e/8797f3/32.png) [@eugene\_epshteyn](https://fortran-lang.discourse.group/u/eugene_epshteyn)\ **Post date:** [June 21, 2026, 12:30pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/9 "2026-06-21T12:30:08Z")
Very nice! Question: are there any sqlite tests that could be ported/adapted for this project? ---
**Author:** ![Arjen](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@Arjen](https://fortran-lang.discourse.group/u/Arjen)\ **Post date:** [June 21, 2026, 12:41pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/10 "2026-06-21T12:41:29Z")
I made a start changing the makefile to be suitable for plain Windows, but the Intel oneAPI Fortran I use (version 2025.0.0) gives an ICE on sqr\_record.f90. So I will have to upgrade if I want to use ths library in combination with ifx. ---
**Author:** ![simong](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@simong](https://fortran-lang.discourse.group/u/simong)\ **Post date:** [June 21, 2026, 1:19pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/11 "2026-06-21T13:19:55Z")
I’m happy to include a native way of building on Windows. _make_ isn’t native there so maybe that _devenv_ is the way to go. That would keep any twister-style changes out of the existing build system. ---
**Author:** ![hkvzjal](https://yyz2.discourse-cdn.com/free1/user_avatar/fortran-lang.discourse.group/hkvzjal/32/3055_2.png) [@hkvzjal](https://fortran-lang.discourse.group/u/hkvzjal)\ **Post date:** [June 21, 2026, 1:32pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/12 "2026-06-21T13:32:30Z")
If you want to propose a transparent build experience for anyone on any platform, I would advise using FPM and CMake, both work on any platform and allow for users to easily build on top of projects, and nothing blocks allowing a project to be built with either build-system. ---
**Author:** ![simong](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@simong](https://fortran-lang.discourse.group/u/simong)\ **Post date:** [June 21, 2026, 1:46pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/13 "2026-06-21T13:46:24Z")
_fpm_ is already supported but I have no way of testing it, so if someone wants to and maybe update the _fpm.toml_ file I’ll include it. My experience of _CMake_ is incredibly negative so I would never use it in any of my projects. ---
**Author:** ![hkvzjal](https://yyz2.discourse-cdn.com/free1/user_avatar/fortran-lang.discourse.group/hkvzjal/32/3055_2.png) [@hkvzjal](https://fortran-lang.discourse.group/u/hkvzjal)\ **Post date:** [June 21, 2026, 2:24pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/14 "2026-06-21T14:24:16Z")
I managed to build and run all tests on Windows using GNU compilers (16.1.0) and FPM. Note, I had to use `--flag "-D_WIN32"` because Fortran compilers do not pass the preprocessing flags automatically. This has been a real crux. ```auto fpm test --flag "-D_WIN32" ... sqr tests: 509 passed, 0 failed ``` My advise: for wrapping OS related intrinsics, you could wrap them in a C file with a single wrapping signature. And on the Fortran side, use bind(c, name = … ) to your own local C wrapper. Otherwise you could use FPM’s recently added profiles to have specific profiles with such flags preset. Also, for fortran files needing preprocessing, the standard practice is to use the upper case “.F90” which signals to all compilers that this file is to be preprocessed. ---
**Author:** ![simong](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@simong](https://fortran-lang.discourse.group/u/simong)\ **Post date:** [June 21, 2026, 10:12pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/15 "2026-06-21T22:12:41Z")
Why does that need to be set? Don’t the MS compilers always define \_WIN32 or is it for something else? Using uppercase letters to distinguish file type (.F vs .f) fell by the wayside really once windows (well _dos_ really) came along. I remember that when C++ first arrived at work all the source files were given the suffix .C to distinguish them from .c (c code). All the time we were on Unix that was fine but once we had to port to Windows the names had to change. These days I just set the compiler option if required. ---
**Author:** ![simong](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@simong](https://fortran-lang.discourse.group/u/simong)\ **Post date:** [June 21, 2026, 10:21pm UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/16 "2026-06-21T22:21:41Z")
Good idea - I didn’t know about them. I’ve added an _sqlt_ subdirectory that contains a subset of the _sqlite_ tests. Navigate to it and the README.md will give you more info. ---
**Author:** ![davidpfister](https://yyz2.discourse-cdn.com/free1/user_avatar/fortran-lang.discourse.group/davidpfister/32/3949_2.png) [@davidpfister](https://fortran-lang.discourse.group/u/davidpfister)\ **Post date:** [June 22, 2026, 7:20am UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/18 "2026-06-22T07:20:44Z")
gfortran does not have the macro \_WIN32 defined by default (unlike the intel compilers). I think gcc does, that’s why it was suggested to wrap them into some C code. Otherwise, the [features and profiles](https://fpm.fortran-lang.org/spec/features.html) in fpm are a good alternative. ---
**Author:** ![simong](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@simong](https://fortran-lang.discourse.group/u/simong)\ **Post date:** [June 22, 2026, 9:58am UTC](https://fortran-lang.discourse.group/t/fortran-relational-database/10974/19 "2026-06-22T09:58:36Z")
I have followed your suggestion and done that ([Issue 1](https://github.com/sgeard/sqr/issues/1)). _fpm_ builds out of the box with no extra flags. There are no _#ifdef_ in the Fortran code any more.