Writing a SQLite Clone from Scratch in C (2022)

In software, as in most things, most of learning is done by doing. Creating a clone of something from scratch is a really useful learning technique and has the benefit of a defined specification.<p>It doesn&#x27;t even really matter if the job is even completed or if the program is ever used. The important part is the doing, not the result.<p>I confess we did similar things at uni 30 years ago, although we mostly re-implemented arcade games, not sql databases. Databases are likely more useful to understand.<p>All programmers have to go through 3 stages;<p>A) I program to prove to myself I can<p>B) I program to prove to others I can<p>C) I know I can write this, you know I can write this, but its cheaper and easier to use this existing, available, code to do the job.<p>I like c) programmers in business because they understand that the goal of the business is making money not generating code. A guy who -could- write SQLite, but then understands why -using- SQLite would be better, is enormously valuable.<p>(Not to mention his SQL chops are probably pretty good by then)

I love these type of projects. This is definitely on the top of my reading list now.<p>Another wonderful “from scratch” resource is “Crafting interpreters” [0] that I was recommended here on hn.<p>[0] <a href="https:&#x2F;&#x2F;craftinginterpreters.com&#x2F;introduction.html" rel="nofollow">https:&#x2F;&#x2F;craftinginterpreters.com&#x2F;introduction.html</a>

This seems serious, and a very nice introduction&#x2F;primer on how databases work under the hood, thanks for sharing!<p>One minor nitpick after glancing quickly at the code: there&#x27;s pointer arithmetic with &#x27;void *&#x27; pointers [1], which is a GCC extension and not portable [2]. It should be rewritten using the proper casts, in my opinion.<p>[1]: <a href="https:&#x2F;&#x2F;cstack.github.io&#x2F;db_tutorial&#x2F;parts&#x2F;part8.html#accessing-leaf-node-fields" rel="nofollow">https:&#x2F;&#x2F;cstack.github.io&#x2F;db_tutorial&#x2F;parts&#x2F;part8.html#access...</a><p>[2]:<a href="https:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;3523145&#x2F;pointer-arithmetic-for-void-pointer-in-c" rel="nofollow">https:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;3523145&#x2F;pointer-arithmet...</a>

Since SQLite is already written in C, it would be interesting to do a rewrite in a different system language like zig

How about a C clone in SQLite?

Sqlite has a fairly exhaustive test suite.<p>I wonder how far we are from auto-generating code which manages to pass these tests and maybe produce same results on random queries compared to SQLite3.<p>Two years ago, I’d have said 50 years. That estimate is now blown.

Doesn’t SQL have decades worth of optimizations? I remember something from my databases class where the professor was mentioning how it’s to the point now where nobody really understands every aspect of what goes on between the query and the machine instructions.<p>Still cool as a hobby project.

Building is a great way to learn for sure but exact cloning gets boring for me. I always end up needing to introduce some kind of twist to the original idea to keep the project interesting beyond a certain point

From Part 3, does malloc(PAGE_SIZE) actually guarantee a single full page is allocated or would the allocation be positioned arbitrarily and stretch across two pages?

How unfortunate that Sqlite Virtual Machine has opcodes to directly manipulate B-Trees. The opcode documentation[1] is even written in terms of B-Trees. This means that it would be too complicated to replace the storage engine with something else, necessitating the need for seperate products such as duckdb.<p>[1] <a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;opcode.html" rel="nofollow">https:&#x2F;&#x2F;www.sqlite.org&#x2F;opcode.html</a>

Looks like this is from 2017-2019, incomplete, and hasn&#x27;t been active much since then.<p>Unanswered issue about the likelihood of continuation: <a href="https:&#x2F;&#x2F;github.com&#x2F;cstack&#x2F;db_tutorial&#x2F;issues&#x2F;94">https:&#x2F;&#x2F;github.com&#x2F;cstack&#x2F;db_tutorial&#x2F;issues&#x2F;94</a>

I would love to see the equivalent of <i>Crafting Interpreters</i> for SQLite, including the two-part structure with a simple (but slow) Java implementation in Part 1 followed by a more-complicated (but fast) rewrite in C in Part 2.<p>But this is good too.

Did a version of this myself using Python, was a great learning experience.<p>I got hooked on the whole “build your own X” format after watching a pluralsight course called “Learn by building Redis with Ruby” by Xavier Shay - highly recommend.

Good, hopefully this will be the second implementation that the W3C standards committee will accept, so SQLite is an approved web standard instead of the disappointing thing we got.

Even as an oldschool C programmer, I would be hesitant to do somthing like this in C. Why not C++, where you can stay close to the metal but enjoy a few niceties?

cool project

This would be a good chance to outline and fix some of the worst design decisions of the various sqlite hacks and security issues.<p>No stack, so no proper subfunction model, such as triggers or functions calling other functions. Simple remote queries can be made destructive, due to the extremely hackish design. The horrible, insecure by default text search engine.<p>Unfortunately this guy treats sqlite as a role model of a good design. Maybe he will find out when it&#x27;s too late. <a href="https:&#x2F;&#x2F;github.com&#x2F;rurban&#x2F;hardsqlite">https:&#x2F;&#x2F;github.com&#x2F;rurban&#x2F;hardsqlite</a>