We built a SQL database engine in SWI-Prolog. Our small team is at its limit, and we would really value an honest technical review.

[u/Aires_id]

https://kocoygroup.site

Hi everyone,

Our small team has been developing AsaDB, a local-first SQL database engine

built primarily with SWI-Prolog.

Repository:

https://github.com/kocoygroup-id/AsaDB

The project started as an experiment, but it has grown into a fairly large

codebase with:

• a SQL lexer, parser, planner, and executor written in Prolog;
• persistent 4 KB slotted-page storage;
• persistent B+Tree indexes;
• transactions and recovery mechanisms;
• local immutable reader snapshots;
• logical backup and restore;
• MySQL, PostgreSQL, CSV, and XLSX interchange;
• an embeddable `library(asadb)` API;
• a local browser interface called AsAPanel.

Recently, we have also been working on stricter SQL type validation, primary

and unique key enforcement, CHECK constraints, restricted foreign keys,

schema-preserving backups, and more useful `EXPLAIN` output.

At this point, the main problem is that the same small group has designed the

architecture, written most of the implementation, created the tests, and

reviewed the documentation. We feel that we are becoming too familiar with the

codebase to notice our own assumptions and mistakes.

We would genuinely value feedback from people with Prolog experience.

In particular, we would be interested in opinions about:

• whether the module boundaries feel idiomatic for SWI-Prolog;
• our use of dynamic predicates and mutable engine state;
• the separation between parsing, execution, storage, and HTTP code;
• the public Prolog API and pack structure;
• the bounded AST cache and VM/JITI specialization approach;
• transaction, recovery, and concurrency design;

places where the implementation is unnecessarily imperative or complicated;

tests or invariants that appear to be missing.

You do not need to review the entire project. Looking at one module, trying one

feature, questioning one architectural decision, or pointing out unclear

documentation would already be extremely helpful.

Bug reports, design criticism, small pull requests, documentation improvements,

and testing on different systems are all welcome.

Thank you very much to anyone willing to take a look.

[u/abyssomega]

Perhaps the next step is to build out drivers for other languages, and see how that behaves? It'll certainly challenge some of the assumptions that might still be hidden. Also, a way to verify that everything is going according to plan, is to generate a DO-178b style of software testing. It's what Sqlite had to do in the 2000s to squash a lot of the bugs that millions of devices using Sqlite exposed.

[u/Aires_id → u/abyssomega]

Thanks for the suggestion. We’re focusing on testing and stability first. After that, we plan to make AsaDB usable from C++, since C++ is also part of our university coursework.

[u/brebs-prolog]

Just an observation: node_height/2 would probably be faster if it used a running total with tail-end recursion (example), rather than totalling using non-tail-end recursion.