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 isADD COLUMN
/ DROP COLUMN
by table rewrite, withDROP
cascading to dependentdb_begin
/db_commit
/db_rollback
,SQR_LOCKED
,db_set_readonly
demotes a writer to let readers in.error stop
in library codestat
/ errmsg
arguments.db_insert(db, ...)
ordb%insert(...)
.sqrsh
, a cmdgraph state-graph shell over the engine,sqlsh
, a small SQL subset (a separate sql
front-end layer thatifx
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
shellsqrsh
is a small state-graph REPL over the engine. The command set:
root: open <dir> close readonly tables desc <table>
create <table> use <table> drop <table> quit
creator: col <name> <type> done cancel quit
table: insert ... select get <id> delete <id> compact
addcolumn <name> <type> dropcolumn <name>
index [unique] <col>... dropindex <col>... verify
find <col> <value> range <col> <lo> <hi> match <col> <regex>
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 <dir>
, .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 | |
<table>.schema |
|
| per-table schema header + column definitions | |
<table>.dat |
|
fixed-size records (recl = record_size ) |
|
<table>.blob |
|
length-prefixed DT_TEXT values |
|
<table>__i<slot>.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.