I’ve used Microsoft SQL Server for decades without ever seeing how it works inside. Sure, there’s ample documentation, but reading it isn’t the same thing as looking at the implementation code. Since the source is closed, I did the next best thing: I built my own relational database engine in C#, to face the same problems it must solve.
Today, my engine supports transactions, concurrency, and crash recovery. It also has a management tool. It can run both in-process and as a server, and I now rely on it in my daily work. For instance, my company’s invoicing goes through it.
I studied databases at university, and textbooks explain B-trees, SQL parsing, ACID principles, transactions, and recovery well. What they don’t show is how those pieces interact when something goes wrong at the worst possible moment. I quickly noticed that this was the thing left out of those books. And quite naturally, this led to the next question: what testing methods are needed to support this kind of work?
In this post, I wanted to share the lessons I learned.
Lesson 1: you need something to compare against
How do you know your engine works correctly and returns the correct results? Simple unit tests will get you started, but for any real workload outside basic SQL, you need something more.
My choice was to add what I’ve come to call a “differential test”: the same SQL is run side-by-side with SQL Server (the “known-good reference”) and my engine (“system under test”), and re-run for every engine change. The workloads are randomized, but with a known seed value. This allows replaying and even debugging directly inside Visual Studio, which is nice. Across tens of thousands of randomized transactions, the two engines have produced identical results.
The takeaway: differential testing works for many more systems than just databases. The basic idea is simple: any time you rewrite or replace something in the implementation, compare it with a trusted reference. Depending on the system, this can take just seconds, or some hours, like in my engine’s case.
Lesson 2: reliability improves by crashing
Happy-path testing is one thing. Making sure data stays correct when things go wrong is much harder. Since I have the engine’s source, I can throw exceptions wherever I like, but real crashes aren’t that polite: a process gets killed, or someone pulls the power plug, at whatever moment it happens to be.
So, I crash the server deliberately, at random points during heavy work, restart it, and check that nothing committed was lost and nothing uncommitted survived.
This found bugs that normal testing never would. One example: when an index page fills up, the engine splits it into two and updates the neighboring page to point to the new one. Simple so far. But if the transaction doing the split is rolled back, the new page’s contents are discarded, yet the pointer to it could still reach disk through another transaction. After a restart, the index walked into an empty page. Every transaction did something reasonable on its own; together, after a crash, they corrupted the index.
My first test reproduced this only one in five runs. That’s fine for finding a bug, but useless for proving a fix: a passing run tells you almost nothing. I set out to write a test that reproduces it every time. Now one run proves the bug, and another run proves the fix.
It made me appreciate long-standing engines like SQL Server even more. As application developers, we rarely have to think about what happens to data at the exact moment the power goes out. For database engines, that is the one thing you need to think about hardest.
Lesson 3: AI is capable, but not trustworthy in its claims
Like many developers, I’ve found AI a capable companion, if you know precisely what you want. But vague requests lead to implementation that seems to work, but when looking closely, the solutions aren’t reliable, performant, or both.
My biggest concern, however, is how confidently AI can claim something is done. When I asked for a deterministic test for the crash bug above, I was told one already existed. When I asked it to verify, the test reproduced the bug only every fifth run.
The lesson: ask for evidence, and do one small, verifiable thing at a time. Asking AI to write the whole thing at once doesn’t lead anywhere, and you end up just wasting tokens. The judgment about what to test, and what “correct” means, must stay with you.
How building this changed my thinking
After implementing transactions, locking, table joins and recovery myself, many problems that once looked hard started looking approachable. I remember thinking during a university lecture, “Those people designing relational database engines must be really smart, so that’s something out of my league.”
But with my own engine’s source code in front of me, I can see I was wrong on the last part. And even if nobody else ever uses this engine, I’ve learned a lot. That is something nobody can take away from me.
The final question remains: why didn’t I do it sooner? Realistically, building everything by hand from scratch would not have been possible for me; it would simply have taken too much time. With capable AI by my side, the question “How does a database engine really work?” became approachable.
Top comments (0)