I’m a huge fan of PostgreSQL. I’ve been a user for almost 20 years. I created and maintain the pgx PostgreSQL driver for Go. However, I have run into several circumstances where PostgreSQL wasn’t an ideal fit.
Foremost among these is when an embedded database is preferred. It’s very convenient to ship a project that has no external dependencies beyond the OS. Ideally, it should use an embedded database at small scale and use a separate database at the large scale. It’s also ideal for development and testing to not require a separate database.
Unfortunately, PostgreSQL cannot be embedded. SQLite is the obvious choice and is an exceptionally good database in its own right. However, it is quite different from PostgreSQL in many ways. In particular, it has a dynamic type system with a few built-in storage classes where PostgreSQL has a strict, static type system with many built-in types.
Using SQLite and PostgreSQL would require using only the features common between them. But one of the biggest reasons I use PostgreSQL is for its advanced type system and query functionality.
Another place where PostgreSQL is an awkward fit is executing untrusted queries. PostgreSQL has excellent support for controlling access to tables and rows, but limited support for controlling resource consumption. In addition, both PostgreSQL and SQLite are written in C. Both projects have had memory safety issues that were exploitable from SQL queries.
The primary features I wanted in another database were:
To safely execute untrusted SQL several criteria must be met:
Like many other developers, I have been amazed over the last year or so as developing with AI agents has gone from tantalizing, but not necessarily useful, to the everyday norm which seems at least to greatly increase productivity.
This database is both something I wanted, and something that would be a good test of building a serious project with agents. So I decided to try. At the very least, I would learn something.
But what language should it be written in? For a memory safe language, the obvious choices for my personal use cases were Rust or Go. Rust has extremely high performance and Rust libraries can also be easily consumed from many other languages. But Go is my preferred language for web applications and while it can use Rust libraries, that requires CGO which I prefer to avoid.
Ideally, the database engine would be available in native form on every platform I use. But writing a database engine in even a single language is a daunting task for a single developer.
But if AI is doing the actual coding, then maybe the answer to Rust or Go is to do both. For that matter, why not build it in every major language or platform? Ultimately, I decided to build it in Rust, Go, and TypeScript. A Rust library gives an easy way to provide support for many other languages such as Ruby and Python. C#, Java, and Swift are left as potential follow-ups for native ports.
The high-level approach I used was to design features as specifications in Markdown files. Then implement in all three languages. I built a bespoke test format that could be consumed by all three implementations. A PostgreSQL oracle was made available as a source of truth for how SQL statements should be evaluated. When all three cores agreed with each other and the PostgreSQL oracle I could have reasonable confidence that a feature was working.
AI agents are fantastic when they have a clear goal. Over the course of a few weeks I was able to get a database that could execute much of common SQL. Major, core features like joins could be implemented with only a brief description and perhaps a few revisions. A documentation web site that embedded the database with live examples was done in hours too.
But once I started to try to use it and once I looked closer, I immediately ran into many problems. The public interface was huge. It had made seemingly everything public. The actual interface that should have been public was awkwardly shaped. The internal design wasn’t any better. 8 KiB pages ballooned to over 100 KiB in memory. Value objects were over 100 bytes. Everything was materialized rather than streamed. There were multiple implementations of similar or identical logic. And that is just a sample. It clearly wasn’t usable for anything serious.
The requirement of safe for untrusted SQL mostly arrived without much extra work. Memory safe implementations, no SQL functions that escape to the host, and computation cost metering all worked without much difficulty. Where there was a notable weakness was in memory consumption. The previously mentioned excessive memory usage also impacted buffer size limits. The limits were in terms of the cached page size, they didn’t account for the huge decoded value sizes.
And yet, however inefficient and awkward to use, it was a working SQL database. And thanks to the extensive tests, foundational refactoring is easy. A bit of back and forth in plan mode, then let the agent work for a few hours, and it’s done.
AI agents let you work at any level of detail you desire. You can generate an entire program with a single sentence or you can write paragraphs to generate a single function. Both are valuable. You can specify what you care about and let it fill in all the blanks. Many times there are vast portions of a project that simply don’t matter beyond a basic degree of functionality. But if and when you do end up caring about the blanks, you may well find that what it filled in was highly sub-optimal.
Tests and specifications are vital. Without them it would be next to impossible to perform the huge refactors needed to correct the underlying design.
An oracle of correct behavior is invaluable. In this case, the agent can automatically check the correctness of SQL execution by running the same statements against PostgreSQL and comparing results.
Creating multiple implementations in different languages at once is a mixed blessing. It strengthens the spec when the implementations disagree by forcing a decision on proper behavior. Issues that escape notice on one implementation are found on another. And the fixes can be ported to all the implementations. As a user, having an native implementation in multiple languages rather than having to wrap Rust or C is a plus. But there is a significant extra cost in time and tokens to implement the same feature multiple times.
Quickly building a prototype SQL database with agents is definitely possible. Getting it to something usable in the real world is another matter. In the coming weeks and months I plan on finding out just how hard that is. You can see the project here: https://jackc.github.io/jed/.