# Fortran Relational Database > Source: > Published: 2026-06-18 20:30:47+00:00 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. ``` Abridged README.md ``` `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. `DT_INT` (32-bit), `DT_REAL` (64-bit), `DT_CHAR` `DT_TEXT` (length-prefixed blob, binary-safe).`b_tree` module is fully decoupled from `sqr` and is`ADD COLUMN` / `DROP COLUMN` by table rewrite, with`DROP` cascading to dependent`db_begin` /`db_commit` /`db_rollback` ,`SQR_LOCKED` ,`db_set_readonly` demotes a writer to let readers in.`error stop` in library code`stat` / `errmsg` arguments.`db_insert(db, ...)` or`db%insert(...)` .`sqrsh` , a cmdgraph state-graph shell over the engine,`sqlsh` , a small SQL subset (a separate `sql` front-end layer that`ifx` and `gfortran` , builds under `fpm` , and ``` 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: ``` call db_begin(db, st) ! ... several inserts / updates / deletes ... call db_commit(db, st) ! durable here; or db_rollback(db, st) ``` `sqrsh` shell`sqrsh` is a small state-graph REPL over the engine. The command set: ``` 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 ``` `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. ``` 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: ``` 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; ``` 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. `sqr` is honest about what it provides. The store is **single-writer / multi-reader**: at most one read-write connection, or any number of `db_open` . It does | 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.