Raw capture — raw/rethinking-database-programming.md

url: https://acadia.engineering/blog/rethinking-database-programming date_fetched: 2026-08-21

content: 'Rethinking Database ProgrammingBy Evan Czaplicki, 18 August 2026For the past few years, I have been trying to bring the benefits of “languages like Elm” to SQL. We have such incredible database implementations, from SQLite to PostgreSQL, and I wanted to make them more convenient for a modern programmer. My goals were: More Precise Types. Many languages support “custom types” these days, but there is no easy way to store them in the database. Rust has Enums, Elm has Custom Types, Haskell has Algebraic Data Types, etc. etc. Why do I have to convert my precise and expressive types into some weird binary layout by hand? Or to JSON? Or some combination of nullable columns? Verified Migrations. I find it very scary to do SQL migrations. When I go into a live database, my body goes on high alert. What if the commands I am running are slightly off? What if my staging environment was a little different? I feel like I am moments away from disaster. Why is it like this? We know the column types. We know the types we want them to be. Why not have the compiler verify the migrations beforehand?! Friendly Error Messages. I put a lot of effort into making error messages in Elm helpful, inspiring similar efforts in languages like Rust.¹ Error messages can be nice! They should be nice for database queries as well! End-to-End types. I want to be able to share types between my client, server, and database. If I change the type of a table column, I want to see high quality error messages in my Elm code. The result is finally ready to share as a public alpha! The binary is available for download here, and you can browse through the documentation and examples for guidance. I hope you will give it a try! What is Acadia? The best way to get an overview is to see it in action: .screencast { aspect-ratio: 16 / 9; width: 100%; } You define your tables, you define your endpoints, and you use them in your server and client code! Currently we support Elm and Haskell integration, and we are planning to add more. Adding languages is pretty easy, so let us know if there is a language you are excited about! We want to prioritize based on the feedback we get during the public alpha. As shown in the screencast, a table definition will look something like this: type alias Food = { id : FoodID , name : String } type FoodID = FoodID UInt64 foods : Table Security.Unrestricted Food foods := Table.table { primary = .id , security = Security.unrestricted , indexes = [] , constraints = [] } You specify your primary key, your row-level security policy, and any indexes and constraints that you want to enforce with your table. From there, you start defining endpoints. A typical query will use map and filter like in any functional program: getFood : Cookies -> FoodID -> Transaction String getFood _ id = access foods Security.Unrestricted |> filter (\f -> f.id == id) |> map .name |> select This gets compiled down to SQL at compile time. So this particular endpoint would turn into something like: SELECT f.name FROM "Foods.foods" AS f WHERE f.id = $1 I put a lot of work into making sure the resulting SQL is high quality. Acadia prints out the SQL it is running, so you can evaluate the quality for yourself as you start experimenting. The compiler does lots of nice optimizations automatically. You can also build up more complex transactions using the “bind” syntax. It works sort of like async/await syntax. Here is an example from the database for this website. When you reset your Acadia password, I send a secret UUID to your email, and you give it back to me to confirm your identity. So my passwordResetInit endpoint creates a fresh UUID, gets the current time, and inserts this information into the resetSessions table: type EmailSecret = EmailSecret Uuid.Uuid passwordResetInit : Cookies -> Email -> Purpose -> Transaction EmailSecret passwordResetInit _ email purpose = let secret := Uuid.generate EmailSecret created := Time.now () := insert resetSessions (EmailInit email) { secret = secret , email = email , created = created , purpose = purpose } in Transaction.succeed secret -- Uuid.generate : (Uuid.Uuid -> a) -> Transaction a -- Time.now : Transaction Time.Posix -- Table.insert : Table security row -> security -> row -> Transaction () The := symbol is called a let-binding, and it lets you extract the result of a Transaction in a concise way. The result of this let is a single transaction that only commits if every step succeeded. This makes it much easier to use a value like secret in multiple places. (Contrast this with SQL where you need to use RETURNING to pass values around.) If you want to give Acadia a try, the compiler is available for download here, and you can start experimenting based on the documentation and examples. There is also a Discord where you can ask for additional guidance. The Backstory Back in 2017, I was exploring designs for server side rendering for Elm.² I needed to load data from the database, and then render HTML for that specific data. But all the designs ended up quite unsatisfying because of the type mismatch with SQL tables. “I know the data is in there, but it is not guaranteed to be in there.” There was no easy way to guarantee that my client code and database code lined up... The story was similar with JSON handling in Elm.³ By 2019, I started hearing the same feedback from lots of companies using Elm. I would ask what they are they running into in practice, and they would say something like: “The Elm code is pretty much fine. X or Y could be a little more convenient, but our major engineering challenges are with our backend code.” More and more, we were all running into the same root problem. The types in our database were not quite what we wanted, and at every level built on top of these types, we were doing a bunch of error-prone work to pointlessly convert the data between different formats. At the same time, I was wrestling with the organizational challenges outlined in The Economics of Programming Languages and trying to find a way to the hopeful path forward described in Rethinking Our Adoption Strategy. So in 2020, Tereza and I started exploring whether “stored procedures” would be a good compilation target. My initial design was basically SQL syntax with a more modern type system. It was sort of clunky, but maybe it would work. I showed the initial draft to Tereza and she immediately said, "I thought it would look like Elm, with map and filter..." A much more elegant design! A big reason I decided to work quietly “like a grad student” on this project is that I believed that it may not be possible. How would migrations work? How do you prevent 1+N queries? Would “a functional language without recursive functions” always have a clear meaning in SQL? Would the answers be different for SQLite vs PostgreSQL? Etc. I also suspected that it may take longer to complete this project than I anticipated... After many ups and downs, everything was possible in the end! The designs came out much nicer than I expected, and it has been really fun to see people working with it in the private alpha period. Next Steps I have attempted to give a high-level overview of Acadia here and on the home page, but many topics require further explanation. How are 1+N queries avoided entirely? How does table migration work with old clients? How does it avoid the issues you see with Object-Relational Mappings (ORMs)? (No objects!) Etc. I am planning to write some standalone articles about these topics over the coming weeks, so please let me know which aspects you find most interesting! I have also attempted to keep Acadia relatively minimal so that I can finally get it out the door. This release is a “public alpha” because there are still many features to add or improve, so please keep that in mind as you experiment! It should be possible to write most of your typical queries in Acadia, but more advanced features like window functions or custom aggregate functions did not make the cut for the minimum viable product. They should be expressible in Acadia in the future, and since it is just normal SQLite under the hood, you can always drop down into SQL as needed. I will be on a faster release cycle going forward, so please ask questions in the Discord and share your projects around as you experiment. This will help me decide how to prioritize things! I have not been this enthusiastic about my language work since the 2012-2014 days with Elm, and I am excited to see what you make with this early version of Acadia! Thank You Thank you to Tereza for evaluating and refining pretty much every idea in Acadia. Hopefully things go well and we can work on Elm and Acadia together full time. Thank you to all the Elm programmers who stuck with the language while this work was progressing. I am glad to be out of this “rebuilding phase” and finally able to get back to the public facing aspects of making languages. I have many blog posts lined up, and I have been working on Elm 0.19.3 as I finalize this Acadia release. Thank you to everyone who participated in the private alpha! You have given extremely high-quality feedback, and the design is much stronger as a result. Finally, thank you to the people who give Acadia a try during the public alpha! I hope you will have a pleasant time!'