u/Aires_id

cung suka kebijakan ☝️😁

link repo : Antisipasi Pejabat

Okay, I think I’m done with my database engine for now.

I started AsaDB mostly because I was curious about Prolog and logic programming. Somehow that curiosity turned into me spending about two months building a database engine almost entirely by myself.

I’m still just a university student. At my campus, I’ve only had programming-related courses for about three semesters, so most of what went into this project was something I had to learn while building it.

And honestly, I’m exhausted.

The engine does work. I managed to get persistent storage, SQL parsing, transactions, PRIMARY KEY / UNIQUE constraints, joins, views, a web panel, server mode, imports, and several other things running.

Some recent 100,000-row results:

• Plain import: 42.5 s
• PRIMARY KEY: 72.0 s
• UNIQUE: 69.5 s
• PRIMARY KEY + UNIQUE: 81.5 s
• Simple metadata COUNT: ~30 ms
• Lookups: roughly 8–11 s
• 100k × 100k JOIN: roughly 35 s
• CREATE VIEW: ~27 ms
• Simple expressions with LIMIT can be below 100 ms

But there are still problems I haven’t been able to solve properly.

PRIMARY KEY and UNIQUE lookups are sometimes no faster than a plain scan. JOIN with a small LIMIT is almost as slow as processing the full join. Subqueries can run for more than 120 seconds and fail. EXISTS and JOIN-based views have caused crashes. I tried fixing several of these problems, but every fix seems to uncover another layer involving indexing, execution planning, memory management, concurrency, or storage.

The screenshots are basically where I’m leaving it: swipl asadb starts the server workspace, a RIGHT JOIN over the 100k-row benchmark tables returns the correct 100,000 rows, and views can be created successfully.

So this isn’t really “the database never worked.”

It worked far enough that I finally discovered how difficult database engines actually are.

I think I’ve reached the point where I simply don’t have enough experience or energy to keep fighting the architecture right now.

Thank you, Prolog. I started this because I wanted to satisfy my curiosity about a logic programming language, and I ended up learning far more than I expected.

Maybe this is the end of AsaDB, maybe it’s only a very long break.

Either way, I think I need to step away from Prolog for a while.

this is AsaDB repository :D

jujur linux pertama yang enak itu PCLinuxOS :D

After the Tech Corner session with Jagoan Hosting, I came back to AsaDB with a lot of notes and suggestions. Somehow, those notes turned into version 1.3.1-rc 😅

It now has a cleaner SQL workspace, import progress, multilingual UI, better database monitoring, and a more stable Prolog backend.

It’s still far from perfect, but I’m genuinely happy seeing how much it has grown. Thank you to everyone who tested it, criticized it, and gave suggestions. A lot of this update exists because of you.

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

About a month ago, AsaDB was just a weird experiment to see how far I could push SWI-Prolog.

Now it has page-backed storage, recovery, indexing, a browser panel, and a new execution bridge called the Reservoir System.

The browser can produce large SQL workloads faster than the engine should safely accept them, so Reservoir spools heavier jobs to disk, processes writes through one worker, tracks progress, and refuses to blindly replay interrupted jobs.

Small reads still go directly to the engine.

It is still experimental, but the current build has processed a 100,000-row stress test, survived a restart, and kept the working set below 200 MB on my old laptop.

I’m maintaining it alone and targeting July 20 for the first public release featuring Reservoir.

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

Prolog is old, weird, and probably one of the last languages people would choose for a database engine.

So naturally, I built one with it.

AsaDB started as a small experiment, but large SQL scripts eventually became a problem. The browser could send data much faster than the Prolog backend and storage engine could safely process it.

That’s how reservoir.pl happened.

It spools large SQL payloads to disk, limits memory usage, queues writes through one worker, tracks progress, and marks interrupted jobs instead of blindly replaying them and risking duplicate writes.

Small read queries still go directly to the engine, while heavier jobs are routed through the Reservoir.

It currently survives my 100,000-row stress test, process restarts, duplicate submissions, cancellation, and result paging.

Still experimental, still built on an old language, but somehow it keeps getting stronger.

Targeting July 20 for the first public release with the Reservoir System.

Prolog was never dead. It was just waiting for someone irresponsible enough to build a database with it.

I’m still maintaining this alone, so code, tests, documentation, benchmarks, and design reviews are all appreciated. Even a small contribution helps.

Yesterday I posted AsaDB here, but the repo only had the Windows build. Yeah, that looked pretty sketchy, so the concerns were fair.

The source is public now under GPLv3.

AsaDB is a small local database engine. It can run SQL commands like CREATE TABLE, INSERT, SELECT, UPDATE, and DELETE. It also has a local browser interface called AsAPanel, so you can run queries and inspect tables without using the command line.

Most of the engine is written in SWI-Prolog. Right now it has 4 KB page storage, a buffer pool, B+Tree indexes, basic recovery, tests, and a Windows build.

It is still experimental. Please don’t use it for production yet, and don’t expose AsAPanel to the public internet.

I decided to open the source because maintaining the parser, storage engine, recovery, tests, and UI alone is getting a bit insane lol.

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

Feedback, bug reports, and contributions are welcome.

AsaDB Workspace

Github Repo

PowerShell Test

I built AsaDB, a local SQL database experiment powered by SWI-Prolog.

The project started as a SQL parser and executor, but larger imports exposed a more interesting problem: keeping rows as growing Prolog structures was convenient for a prototype, not for a storage engine. Version 1.2.1 rebuilds that path around disk-backed pages and bounded execution.

What is inside v1.2.1

• Fixed 4 KB slotted pages for normal user-table records.
• Persistent B+Tree equality and range indexes with linked leaf pages.
• A bounded Clock-style buffer pool with pin/unpin protection, dirty tracking, eviction, and incremental flushing.
• Streaming SQL imports using bounded statement batches and transaction rollback.
• Append undo records, page-mutation backups, checksums, and atomic catalog replacement.
• Bounded result windows and incremental browser rendering.
• A local administration UI called AsAPanel.

Prolog still handles SQL parsing, planning, execution control, and recovery orchestration. User rows now live in versioned disk pages instead of a large collection of asserted heap terms.

Measured stress test

| Rows | Import | First indexed lookup including build | Indexed ORDER/LIMIT | Peak RAM |

|---:|---:|---:|---:|---:|

| 10,000 | 7.1 s | 6.1 s | 80 ms | not separately sampled |

| 50,000 | 34.7 s | 31.6 s | 46 ms | 229.9 MB |

| 100,000 | 73.9 s | 76.1 s | 19 ms | 229.8 MB |

The 100,000-row test also restarted the engine, reopened the database, verified UPDATE/DELETE results, and checked recovery-visible state. Peak working memory stayed effectively flat from 50,000 to 100,000 rows in this run.

An earlier prototype used around 820 MB and took almost 11 minutes for the same complete scenario. The final path completed in approximately 247 seconds with a 229.8 MB peak working set.

SQL surface

AsaDB supports CRUD, ALTER TABLE, transactions, INNER/LEFT/RIGHT JOIN, GROUP BY with aggregates, basic subqueries, UNION, CASE, views, users/grants, and import/export workflows.

Honest limits

This is a database-engineering experiment, not a PostgreSQL, MySQL, or CouchDB replacement. It does not have MVCC or ARIES recovery. Some writes invalidate and lazily rebuild affected indexes, and complex plans can still materialize more intermediate data than simple indexed scans.

The public portable release is currently Windows-focused and AsAPanel is intended for localhost use.

GitHub:

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

Windows v1.2.1 release:

https://github.com/kocoygroup-id/AsaDB/releases/tag/v1.2.1

I would particularly value feedback on the page format, incremental B+Tree maintenance, planner statistics, and recovery design.

Halo r/indotech, saya ingin memperkenalkan proyek yang sedang saya bangun bernama AsaDB.

AsaDB berawal dari pertanyaan yang agak nekat: apakah Prolog hanya cocok dipakai untuk mengenali grammar SQL, atau bisa juga dipakai untuk mengatur sebuah database lokal yang benar-benar menyimpan, membaca, mengubah, dan memulihkan data?

Versi pertamanya memang sudah bisa membuat tabel, memasukkan data, dan menjalankan SELECT. Namun, ketika mulai diuji dengan data yang lebih besar, saya sadar bahwa bisa menjalankan SQL belum tentu berarti sudah memiliki storage engine yang layak. Penggunaan memori membesar, proses impor berat, dan beberapa bug seperti targeted DELETE menunjukkan bahwa bagian penyimpanannya harus dibangun ulang dengan lebih serius.

Hasil perombakan tersebut saya rilis sebagai AsaDB v1.2.1.

AsAPanel menjalankan dua query untuk membaca ringkasan benchmark 10.000, 50.000, dan 100.000 baris melalui backend Prolog lokal. Query yang terlihat pada gambar selesai dalam 21 ms.

Apa itu AsaDB?

AsaDB adalah eksperimen database SQL lokal berbasis SWI-Prolog. Paket Windows-nya dilengkapi AsAPanel, yaitu panel web lokal untuk membuat database, menjalankan SQL, mengimpor dan mengekspor data, serta memeriksa struktur tabel.

AsaDB berjalan sepenuhnya di komputer pengguna. Satu file `AsaDB.exe` menjalankan backend Prolog dan membuka panel pada alamat localhost. AsaDB tidak membutuhkan cloud server dan panelnya tidak ditujukan untuk diekspos langsung ke internet.

Kegunaan proyek

Proyek ini saya buat sebagai:

• sarana mempelajari cara kerja parser SQL dan query executor;
• eksperimen membuat storage engine berbasis page;
• tempat mempelajari B+Tree, buffer pool, transaksi, dan recovery;
• database lokal untuk latihan SQL dan pengolahan dataset;
• contoh penggunaan Prolog di luar rule engine atau tugas grammar sederhana.

AsaDB belum dimaksudkan untuk menggantikan PostgreSQL, MySQL, atau CouchDB pada sistem produksi.

Fitur yang tersedia

SQL utama yang sudah dapat digunakan meliputi:

• `CREATE DATABASE`, `DROP DATABASE`, dan `USE`;
• `CREATE TABLE`, `DROP TABLE`, dan `TRUNCATE TABLE`;
• `INSERT`, `SELECT`, `UPDATE`, dan `DELETE`;
• multi-row INSERT;
• `ALTER TABLE` untuk menambah, menghapus, mengubah, dan mengganti nama kolom;
• `WHERE`, `LIKE`, `BETWEEN`, `IN`, dan `IS NULL`;
• `ORDER BY` dan beberapa variasi `LIMIT`;
• INNER JOIN`, `LEFT JOIN`, dan `RIGHT JOIN`;
• GROUP BY` dengan `COUNT`, `SUM`, `AVG`, `MIN`, dan `MAX`;
• basic scalar subquery, `IN (SELECT ...)`, dan `EXISTS`;
• `UNION`, `CASE WHEN`, dan `CONCAT`;
• view dan transaction commands;
• basic users, grants, triggers, procedures, dan functions metadata;
• impor melalui SQL command, Choose File, dan server-side file;
• ekspor database melalui AsAPanel.

Perubahan besar di v1.2.1

Penyimpanan berbasis page 4 KB

Data user table sekarang disimpan pada fixed-size slotted page berukuran 4 KB. File `.asa` menyimpan katalog, sedangkan record tabel disimpan di direktori `.asa.store` yang menyertainya.

Setiap page memiliki metadata seperti:

• page ID dan page type;
• jumlah record aktif;
• batas free space;
• slot directory;
• previous/next sibling pointer;
• checksum.

Tujuannya agar semua record tidak harus disimpan sebagai kumpulan besar Prolog terms di heap.

Persistent B+Tree

AsaDB memiliki persistent B+Tree untuk equality lookup, range query, dan ordered scan. Leaf page dihubungkan dengan sibling pointer sehingga range iteration bisa berjalan berurutan.

Pembuatan index menggunakan external sorted runs yang dibatasi ukurannya. Proses tersebut tidak membuat satu list Prolog berisi seluruh 100.000 index entry sekaligus.

Pada implementasi saat ini, operasi tertentu dapat meng-invalidasi index. Index tersebut dibangun ulang secara lazy pada indexed access berikutnya. Incremental split/merge untuk seluruh mutation path masih menjadi pekerjaan lanjutan.

Bounded buffer pool

Buffer pool menggunakan kebijakan Clock-style replacement dan memiliki:

• configurable page limit;
• page cache;
• pin dan unpin page;
• dirty-page tracking;
• protected eviction untuk page yang masih digunakan;
• incremental flushing;
• statistik hit, miss, eviction, dirty page, dan pinned page.

Default buffer pool-nya adalah 64 page. Batas ini sengaja dibuat konservatif karena representasi byte list di SWI-Prolog memiliki overhead runtime lebih besar daripada ukuran 4 KB pada disk.

Streaming import dan bounded result

Backend membaca file SQL dalam blok 256 KB dan mengeksekusinya dalam statement batch terbatas. Proses impor berjalan dalam transaction, menampilkan progress, dan melakukan rollback ketika batch gagal.

Hasil query web juga dibatasi oleh `max_result_rows` dan dirender secara incremental agar tabel besar tidak langsung membebani browser.

Cara menjalankan

1. Buka halaman GitHub Release v1.2.1.
2. Unduh `AsaDB-v1.2.1-Windows.zip`.
3. Ekstrak ZIP tersebut.
4. Jalankan `AsaDB.exe`.
5. Tunggu browser membuka AsAPanel pada alamat `127.0.0.1`.
6. Buat atau pilih database dari sidebar.
7. Masukkan SQL pada halaman SQL Command, lalu tekan Run SQL.

Backend dan panel dijalankan oleh satu launcher. Pengguna tidak perlu membuka terminal backend secara terpisah.

Contoh query:

```sql

CREATE DATABASE IF NOT EXISTS latihan;

USE latihan;

CREATE TABLE Nilai (

ID INTEGER PRIMARY KEY AUTO_INCREMENT,

Nama VARCHAR(100),

Skor INTEGER

);

INSERT INTO Nilai (Nama, Skor) VALUES

('Aires', 95),

('Asa', 91),

('Denji', 88);

SELECT Nama, Skor

FROM Nilai

WHERE Skor >= 90

ORDER BY Skor DESC;

```

Benchmark yang saya jalankan

Pengujian dilakukan di Windows menggunakan SWI-Prolog. Data dibuat dalam bentuk tiga kolom dengan multi-value INSERT berisi 100 row per statement.

| Workload | Import | First indexed lookup termasuk build | Indexed ORDER/LIMIT | UPDATE | DELETE | Hasil 500 row | Peak RAM |

|---|---:|---:|---:|---:|---:|---:|---:|

| 10.000 row | 7,1 detik | 6,1 detik | 80 ms | 144 ms | 109 ms | 804 ms | tidak diambil terpisah |

| 50.000 row | 34,7 detik | 31,6 detik | 46 ms | 234 ms | 213 ms | 948 ms | 229,9 MB |

| 100.000 row | 73,9 detik | 76,1 detik | 19 ms | 519 ms | 401 ms | 654 ms | 229,8 MB |

Ketiga workload berhasil diselesaikan. Pada pengujian ini, peak memory relatif tetap dari 50.000 ke 100.000 row.

Pengujian 100.000 row juga mencakup shutdown, membuka ulang database, memverifikasi jumlah row setelah UPDATE dan DELETE, serta memeriksa state yang terlihat setelah recovery.

Prototype awal v1.2.1 sempat menggunakan sekitar 820 MB dan membutuhkan hampir 11 menit untuk skenario yang sama. Jalur final menyelesaikan keseluruhan benchmark dalam sekitar 247 detik dengan peak working set 229,8 MB. Perubahannya diperoleh dengan memperbaiki data path, bukan sekadar menaikkan batas RAM.

Angka tersebut adalah hasil pengujian pada environment saya dan bukan klaim bahwa AsaDB memiliki performa setara database production yang sudah matang.

Bug penting yang diperbaiki

• `DELETE ... WHERE ID = value` sudah diuji pada tabel kecil dan besar agar tidak menghapus row lain.
• Table dan column matching dibuat case-insensitive untuk penggunaan `ID`, `id`, atau mixed case.
• Menjalankan `ALTER TABLE ADD COLUMN` berulang dengan nama yang sama tidak lagi membuat duplicate physical column.
• Duplicate column dan row key lama dinormalisasi saat migration.
• Bare text value seperti `Denji` tidak lagi diam-diam berubah menjadi `NULL`.
• Dua klik Run yang sangat cepat memakai satu in-flight request dan tidak menggandakan write.
• Success/failure sound menggunakan satu active channel sehingga tidak menumpuk.
• Multipart import diagnostics tidak lagi mencemari JSON response dari backend.

Feedback persona AsAPanel saat ini menggunakan bahasa Indonesia. Pada contoh ini AsaDB mendeteksi typo `SELEC`, typo `FORM`, dan semicolon yang belum ditulis.

Integritas dan recovery

AsaDB v1.2.1 menggunakan kombinasi:

• append undo record;
• page-mutation backup;
• transaction snapshot;
• checksum;
• temporary and backup catalog file;
• atomic catalog replacement;
• startup recovery untuk interrupted append, mutation, checkpoint, dan open transaction.

Implementasi tersebut belum setara ARIES dan belum menyediakan MVCC. Namun, jalur recovery-nya sudah jauh lebih aman dibanding versi sebelumnya.

Keterbatasan saat ini

Saya ingin bagian ini tetap jelas supaya post ini tidak memberikan kesan seolah-olah AsaDB sudah production-ready:

• belum memiliki MVCC;
• recovery belum menggunakan ARIES;
• beberapa write path masih memerlukan lazy index rebuild;
• complex JOIN, GROUP BY, dan expression plan masih bisa memakai intermediate memory lebih banyak;
• optimizer dan planner statistics masih sederhana;
• paket publik saat ini difokuskan untuk Windows;
• AsAPanel hanya ditujukan untuk localhost;
• English localization untuk feedback persona belum tersedia.

Akses, repository, dan harga

AsaDB v1.2.1 dapat diakses langsung tanpa harus mengirim DM.

Repository:

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

Windows release:

https://github.com/kocoygroup-id/AsaDB/releases/tag/v1.2.1

Harga: gratis. Saat ini tidak ada biaya pembelian, subscription, paywall, maupun kewajiban donasi. Link dukungan yang ada di panel bersifat opsional dan tidak membuka fitur tambahan.

Lisensi proyek mengikuti file `LICENSE` yang tersedia di repository.

I originally started this project in VB.NET.

After getting tired of looking at `Console.WriteLine`, I considered rewriting it

in V or Erlang. Their setup annoyed me, so I tried Prolog instead.

That somehow turned into a local SQL database engine.

It is called **AsaDB**.

It currently has:

- a custom SQL lexer and parser

- local `.asa` database files and journals

- tables, indexes, views, users, and permissions

- joins, aggregates, grouping, unions, and subqueries

- transaction snapshots

- a Windows CLI and portable executable

- a browser-based administration panel called AsAPanel

- SQL import and export tools

The import path now sends selected SQL files through the Prolog backend instead

of requiring the browser to hold the complete file in memory.

One current validation dataset contains:

- 5,500 rows

- 62 statements

- 0 errors

My next goal is to make imports of around **100,000 rows** reasonably fast.

I am currently investigating insert batching, list/term reconstruction,

checkpoint and journal overhead, and whether indexes should be updated per row

or rebuilt after a bulk import.

The newest release, v1.2.0, is mainly a UX polish release. It adds random query

sounds with four success and four failure variants.

Successful SQL:

> Asa Terima ❤️

Failed SQL:

> Asa Tidak Suka! 😡

The sound system is deliberately isolated from query execution, so browsers

blocking audio cannot break the database operation.

Repository:

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

Windows release:

https://github.com/kocoygroup-id/AsaDB/releases/tag/v1.2.0

The public repository currently contains the packaged Windows runtime rather

than the internal engine source.

I would love feedback about efficient large-state representation, bulk inserts,

index construction, and profiling this kind of workload in SWI-Prolog.