Rendered at 20:55:40 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
mike_hearn 9 hours ago [-]
The issue with defining schemas in a non-SQL programming language is they always lag behind what the underlying database can do. Sure, your ORM-like framework can define basics like primary keys and maybe uniqueness constraints, but can it define partitioning schemes, compression methods or more advanced constraints?
And then consider that other databases have even more. If you manage your schemas in code then you lose access to all of those, and will eventually need to write SQL anyway.
For queries it isn't such a problem, especially if you have a nice compiler. However, I recently lost faith in SQL wrappers/abstractions. The usual justification was that a lot of developers don't know SQL well, but LLMs are great at it. It's easier for the LLM to write SQL than some less familiar DSL. And SQL was written to be relatively easy to understand, especially if you do things like use CTEs and views correctly it should be possible to factor logic out to make even complex queries understandable.
The question for frameworks like Acadia is really: assuming I am fluent in SQL and know every feature of my database, what does the framework buy me? Because that's the perspective an LLM comes to it with.
elcritch 8 hours ago [-]
There's a lot of benefit in these systems, though there's rough edges and I agree about the basics like PK's and uniqueness.
I've been using Ormin [1] in Nim which works by parsing the SQL tables and uses it to compile time check queries:
# Multiple joins with pagination
let page = query:
select Post(title)
join Person(name) on author == id
join Category(title) on category == id
orderby desc(post.creation)
limit 5 offset 10
I think that's better since defining SQL should be the source-of-truth for the DB and the code. ORM's always ended up causing trouble in my experience.
Things like indexes, defaults, partitions, etc generally aren't expressible in code without a lot of kludges. Then each DB engine have pretty different rules, syntax, etc for tables.
However having the queries compile time checked, type conversions handled, and the nuances between SQL query syntax handled is rather nice. As you mention it's a much easier subset.
Just learn SQL. I believe all these SQL replacement layers are just because people don't like SQL and don't learn it, so they learn a training wheels version of it that will cripple their ability to grow because it's simplifications remove expressiveness that caused SQL to be more complex to begin with.
Just learn SQL, it's not that hard. A lot of very very smart people put a lot of effort into it. It's very good. The things that are annoy you about it are often there because of something you don't yet even realize is something you need to be aware of, or because your fundamental understanding of things is just wrong or incomplete.
antihero 3 hours ago [-]
I think the issue is that while ORMs etc, stuff like ecto…whilst they’re never going to be database native like actual SQL, the value in the abstraction isn’t making querying easier, but making more robust and useful the integration into the host language. It brings it out of database domain and into application domain so that doesn’t have to to constantly reinvented.
You can always be more expressive and portable in raw SQL, that’s obvious, but the things you’re doing have to be used somewhere, so at some point the things you are doing have to cross a barrier. For the 90% use case, ORMs are a pragmatic choice because the good abstractions aren’t about the syntax, they’re about allowing you to talk about and mutate data within the language paradigms that everything else is written in.
wpollock 1 hours ago [-]
> Just learn SQL....
I agree. In my experience, ORMs are more complex and harder to learn to an expert level than SQL. Knowing Java (but not SQL) doesn't help much with learning Java ORMs (Again, to an expert level). Besides not supporting all the SQL features of some DB, ORMs also covers other things such as caching.
Learning ORMs is likely just as difficult as learning SQL. It is likely harder to learn how to optimize performance with ORMs.
SQL as opposed to code has the advantage that it can be kept in a separate file, and thus modified by experts in databases without changing the code. The article claims the author found migrations harder with SQL than with his framework. I would think it would depend a great deal on the database one is migrating.
I'm not convinced that LLMs make things easier, you still need an expert to verify the generated code, and to tune it, as often the database is business critical with serious consequences if wrong, slow, or turns out to be infringement of someone's copyright.
Just learn SQL!
setr 2 hours ago [-]
Learning SQL doesn’t absolve you from the fact that, from the perspective of your PL, you’re smashing arbitrary strings together like a Neanderthal, and you can be offered all the support otherwise given to your string smashing problems (exactly none)
It also doesn’t absolve the fact that SQL is not a particularly well-designed language for smashing strings together like a Neanderthal. In fact, you might even say it’s absolutely horrid at it, with random keywords, extraneous syntax, and general lack of compositional capabilities.
The relational model is fantastic — Codd is Godd, after all. The engines are a work of art. The SQL language is a shitshow. PL/SQL and all its variants are a crime upon the PL community. The programmatic interface to a database is a shitshow, because it is SQL and only SQL. The SQL standard is a joke and standardizes nothing.
None of this is contentious, or should be, once you’ve learned SQL.
pjmlp 1 hours ago [-]
Only because some people are very opinated in avoiding stored procedures, and think smashing strings together is a much better solution.
setr 33 minutes ago [-]
PL/SQL is cursed and the unstandardized library system means every DB’s ecosystem is anemic.
Instead of smashing strings, you can code with all the affordances of C90 and still get the chance to smash strings together if you need to do anything beyond utilizing simple variables (EXECUTE) — now with an even worse string manipulation stdlib. And you also get the privilege of working with the some of the most worthless parser errors known to modern man. As an added bonus, DB IDEs are universally worse at text-editing & refactoring than the equivalent application editor
You can reuse code through extensions/external instead, and have access to real programming languages with actual libraries… but now you’re kicked out of managed environments because it’s not whitelisted, and even if you do run it, you’re back to smashing strings together like a Neanderthal trying to communicate to your DB.
Sprocs/functions are useful because they do useful engine things — they run locally with the data, they have an easier time playing with transaction flow, some logic is much easier to express with a cursor instead of set logic and you get to avoid most of the penalties you’d have otherwise.
They do absolutely nothing to make SQL a less terrible interface to your database, except by stuffing it under a rug (CALL).
pjmlp 15 minutes ago [-]
PL/SQL is great and using SQL Developer definitely better than smashing strings together.
If only C90 was half as good.
elcritch 3 hours ago [-]
I already know SQL which is why I like the above. It's SQL with some tweaks to match Nim syntax and to have less ambiguous table/column identification.
Meanwhile embedding SQL in a string with `?` everywhere, manually converting the results, and remembering some of the SQL syntax is annoying.
threethirtytwo 2 hours ago [-]
No. SQL is just bad. It's an old way of doing things. It's not hard but it's not good.
Take this for example. Why do we have static type checking for typescript? Why do we have a build step for this?
Why DON'T we have it for SQL? Why is it runtime strings? So no static checking and the only way to test if a query works is to run it?
The purpose of these replacement layers is to get it all under one language. Once it's all under one language you get full safety and fusion across the two concepts. Query builders and ORMs are shooting for an ideal, and the ideal makes sense. It's just a nightmare to implement and thus fundamentally there are compatibility issues and that's why a lot of people in general don't like orms.
There's also a sync step where the model in the language has to be aligned with the model in the database which is just an extra mutating state layer which further compounds the bugs.
alpinisme 8 hours ago [-]
The point is end to end type safety. Whether that is worth the tradeoff of losing direct developer access to the db primitives is another question.
groundzeros2015 6 hours ago [-]
SQL is end to end type safe.
sharno 6 hours ago [-]
Which end? This moves one end to reach frontend code
victorbjorklund 4 hours ago [-]
Only backend to database. This is talking about typesafe from database - backend - frontend.
groundzeros2015 4 hours ago [-]
Yes, and that’s an architectural choice you’re making.
Instead of using all the consistencies provided in the database process - including types, but also date/time, constraints, transactions, triggers etc. you are exiting the system and losing all guarantees.
This system also doesn’t solve that problem.
rzmmm 3 hours ago [-]
You can write raw sql and use the "describe" clause in script, and then generate code with the result. This gives full db-backend-frontend type safety with raw sql queries
bazoom42 6 hours ago [-]
Isn’t sql weakly typed? Or does this depend on the engine?
pjmlp 1 hours ago [-]
No it is strongly typed, there is no accident that all PL extensions to the base query language have such a Ada/Pascal similarity.
Additional DML has plenty of options to enforce rules that keep data consistency.
While they make the life harder to delete/update/insert items in specific sequences, they can save the day on bad queries.
groundzeros2015 5 hours ago [-]
SQLite is the only one I know of that doesn’t enforce types by default, but I don’t know what the SQL spec requires.
whattheheckheck 8 hours ago [-]
I agree with end to end type safety but that needs more details to sell what problem its solving. Folks dont buy it for itself
Smalltalker-80 8 hours ago [-]
Agreed, that's why I chose to implement a simple ORM for my language's multi-platform database library. It has a mandatory 'id' column, for simple updating and deleting, but table creation and complex queries are done in plain SQL.
adzm 8 hours ago [-]
Agreed with you here. In my experience the best solutions go the opposite way, and parse the SQL in ways that can be used from the application.
A core idea of the relational model is to seperate the logical model from the physical layer including optimizations, indexes etc.
So it makes sense to only expose the logical model at the ORM layer.
The problem comes if you want to define the database schema through the ORM layer, rather than just represet it.
bbkane 7 hours ago [-]
Isn't SQL already a logical abstraction language over a "physical layer"? I'm not updating indexes or deciding when to flush or fiddling with MVCC when I write SQL
bazoom42 7 hours ago [-]
The comment mentioned partioning schemes which is defined using SQL but belongs in the physical layer. Indexes are also defined in SQL.
bbkane 5 hours ago [-]
Thanks. Indices are defined in SQL, but they're not updated in SQL. Once defined, an INSERT/UPDATE updates relevant indexes automatically. That's the abstraction layer SQL provides.
Unironcally, yesterday i was vibe-coding a small app for personal use using Django and was quite shocked to discover that Django's orm does not support something as simple as specifying a database schema other than the default "public" one out of the box.
You either have to add options specific from libpq:
ALTER ROLE myuser
IN DATABASE mydatabase
SET search_path = myapp, public;
It's not ergonomic at all.
ux266478 6 hours ago [-]
[dead]
CopyOnWrite 5 hours ago [-]
By now I stopped counting the attempts to replace SQL.
There is a lot of valid critic for SQL and I would be very happy if some things would have been designed different.
OTOH the architecture and mathematics behind relational databases are simple, composable and stood the test of time more than most other designs, methodologies or approaches to software development.
Though SQL can be improved, even with my average SQL skills I never had trouble getting information out of a database and fancy stuff like window functions make to my understanding even standard SQL Turing complete.
SQL has the native database support, for most companies the data and the database will outlive any specific application or even the whole ecosystem of a programming language/platform (Visual Basic, Visual FoxPro, Python 2, ...)
Further, we have fantastic books, knowledge, ORMs, query builders and a gigantic ecosystem in tools for SQL and SQL databases.
Acadia might be brilliant from a technological point of view, but it does not matter, because it does not look like a big enough improvement compared to SQL that it seems worth to invest in it. I will rather improve my knowledge of standard SQL or my knowledge for a specific relational database.
Finally Acadia does not really seem to raise the bar compared to other ORMs/Query builder. I get that from a FP point of view map/filter are nicer than a SELECT ... WHERE, but at some point in the projects I participated one would end up interacting directly with the database anyway, and at that moment I am back at SQL, so what did I gain?
ninkendo 5 hours ago [-]
SQL has one flaw: The verb should come last. So, "FROM users WHERE id = 1 DELETE" or "FROM users WHERE email = 'foo@example.com' SELECT id". That'd cut back on some accidental "oops I dropped the whole table" because I submitted a delete query before writing the where clause.
Other than that, it's perfect, no notes.
closeparen 5 hours ago [-]
The part that has stood the test of time and genuinely seems to carve reality at the seams is the query part. The data definition and data manipulation parts are just ok.
senderista 4 hours ago [-]
The problem with the query part is that query fragments aren't composable.
gopalv 3 hours ago [-]
CTEs are how you compose SQL.
I don't quite like how the same CTE lives in 60 different places in my codebase, but at least the WITH clause changed things for me.
Also really liked Snowflake's result_scan for composing chains, mostly because I don't rerun expensive parts again and again. You can use ->> as a shortcut, but I don't think it uses results caching internally to skip waiting for them to all re-run & actually optimizes the whole thing.
ModernMech 5 hours ago [-]
SQL is based on the relational model but doesn't really conform to the mathematics e.g. doesn't exhibit set semantics.
It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.
ModernMech 4 hours ago [-]
A result in SQL can contain duplicate items unless you tell it explicitly to deduplicate, so uses multiset/bag semantics. The relational model is built on set semantics, where every item is unique. Just because it can express those operations doesn't mean the idea is baked into the language semantics. e.g. the difference between Haskell and Python + first class functions; you can do functional programming in Python but it's not a functional language.
cmrdporcupine 4 hours ago [-]
What they're saying is: The relational data model and algebra are based on set semantics. Relations (equivalent of SQL's "tables") are sets of sets (tuples), not bags of "rows". There's no such thing or possibility as duplicate tuples ("rows" of "columns").
This has a number of elegant properties (and also improves the kinds of optimizations a query planner / execution stage can apply.)
A similar divergence is that the relational model has no concept of nulls. Presence/absence is expressed through "item not in set" in various ways, and by properly normalizing the data.
SQL also isn't properly expression oriented or composable at all. A relational algebraic language absolutely can be, and can lend itself to much more elegant data handling.
In many ways SQL is to "relational" like Java or C++ are to "object oriented" -- it got in very early to market, got mainstream success, and dominated the field, and in so doing it mangled people's perceptions of what a database is, and also made people either define "relational" as "SQL" (sigh), and even worse because they misunderstand what relational is while also hating SQL, they try to throw the baby out with the bathwater with their successors.
senderista 4 hours ago [-]
Relations also have no concept of ordering. But bag semantics is both closer to efficient implementations and closer to user expectations than set semantics. Using set semantics everywhere also makes queries harder to optimize, because you have to selectively "de-deduplicate" for efficiency, instead of just sticking DISTINCT operators where they're needed.
ModernMech 4 hours ago [-]
> In many ways SQL is to "relational" like Java or C++ are to "object oriented"
Very cogent.
treebeard901 21 minutes ago [-]
The main arguments presented are that databases do not support modern types.. And that this system replaces tested authentication systems by emailing a UUID in plain text?
It does mention UInt64 which is not a modern type and as far as I know is supported by every database.
It also compiles to SQL but it isnt clear where the advantage comes from other than using a different syntax to do things.
dwohnitmok 10 hours ago [-]
I'm wary of languages that seek to own the database. In particular, the claim "Coexist with SQL" seems a bit suspect given that e.g. sum types have a custom binary encoding, which likely makes them difficult to interop with from other languages. This makes the claimed interop with other languages really more of a temporary stopping point towards full Acadia adoption rather than a viable long-term equilibrium, unless you e.g. eschew using sum types. (I also suspect that trying to natively support sum types can lead to a kind of FP-equivalent of ORMs' impedance mismatch. The ways I model data with relational logic can be pretty different than the ways I model data with algebraic datatypes and I wonder if trying to force fit the latter into the former doesn't lead to the same problems as force fitting objects into relational logic).
This makes the database closer to something that Acadia compiles to, rather than something Acadia sits on top of. From my own developer experience this feels off, because I generally expect the data layer to be king and application code to revolve around that, rather than having data representation created in code and the database created off that (this is why I also dislike things like ORMs).
In general I view databases as usually having more longevity than application code, especially as you accumulate more data over time. For serious production applications, the database often outlives multiple rewrites of the production application.
I suspect though my concerns are overall rather minor. The ergonomics of the language itself seem enjoyable. Acadia seems like it would be great as an embedded DSL. It's a bit unfortunate that it currently seems coupled to creating an HTTP server. I think that Acadia has greater ambitions beyond just the database, as evidenced by creating a binary web connection with frontend Elm code to presumably obviate the need for encode-decode layers. It seems like Acadia is meant to be a stepping stone towards a closer frontend-backend fusion. But I agree with mjaniczek that something like Lamdera seems a better fit for that.
But given how early Acadia is, I'm still very excited for where it goes. What I've listed is surmountable and I also feel that often a closer frontend-backend fusion might be worthwhile.
exidex 10 hours ago [-]
I think, the reality is SQL being simply to old to coexist with a web app use case. All the nice things that article talks about are not possible to nicely integrate with SQL. Current development is done by either writing SQL by hand or by letting ORMs to autogenerate it. Both feel bad because of how bad SQL is. But there is no other option. I hope https://substrait.io/ will gain traction and will be supported natively by databases
fastforwardius 7 hours ago [-]
I'm curious what you meant by the web app use case and why you find SQL bad?
Shorel 7 hours ago [-]
He probably is still drinking the NoSQL koolaid of 2015 :)
exidex 5 hours ago [-]
Just as with any kind of programming I want to be able to detect as much amount of issues as early as possible. That includes issues like invalid queries (both on syntax and types level) but also stuff like will specific transaction isolation level be just enough (from correctness and performance point) for my specific use case, or will the migration query lock the whole db or take multiple days to execute because I didn't know some niche quirk. To me it is obvious that you will not beable to do that with SQL, one because it is old so it accumulated all the weird quirks, that were done in the name of backward compatibility, two is that by it's nature of being script language you just could not do more complicated cross query static analysis. See also https://www.scattered-thoughts.net/writing/against-sql which nicely describes other issues. See languages like PRQL for better syntax, or https://www.languagesforsyste.ms/MixT/ for static analysis possibilities
I am mostly aligned with the article on what I want from next generation of web development. But I don't think using specific library in a specific language or specific query language is a viable long term solution. Hance the mention of Substrait. The solution that I think is needed, is something like LLVM but for databases.
As for the NoSQL, I think it was the worse thing that happened to databases in the last 20 years, probably more
gbjcantab 10 hours ago [-]
This looks reasonably interesting, and Evan is extremely thoughtful about design; I know he’s put a huge amount of work into this.
Personally, I’d be very cautious about adopting closed-source software with such a restrictive license as part of an application, especially given the context of Elm’s trajectory. When Elm went through breaking changes or regressions, or was not worked on publicly for years, users had access to the source and the right to modify it. With Acadia’s licensing, you’d be stranded.
happyraul 10 hours ago [-]
On the other hand, with Elm there was no correlation between adoption and funding for development. With Acadia, he's trying a different funding model, so that might mean better support for both Acadia and Elm.
ModernMech 9 hours ago [-]
The Elm project forked into a bunch of different Elms because Evan basically abandoned / killed it. Then he got more interested with this project. What’s to say that won’t happen again?
happyraul 9 hours ago [-]
I think it's fair to say there are other ways to interpret what happened with Elm. What if Evan stopped working on it because he needed to make a living and working on Elm wasn't going to achieve that? In that case, if working on Acadia will earn him a a living, it seems reasonable to believe he will keep working on it.
leftyspook 9 hours ago [-]
> So even if “open core” is a strictly better model, we lack the intuition and experience to feel confident starting there. By thinking of Elm as the “open core” at first, we give ourselves time to learn and flexibility to expand the core later.
The way I read this, Acadia is an attempt to finance working on both it and Elm.
ModernMech 9 hours ago [-]
That is fair but I think that there’s not a clear thing from Evan we can point to which explains it contributes to the uncertainty in this new project. Makes me feel we should be wary of a repeat. If anything it seems what he learned from his experience with Elm is that the project should not have been open, and his main problem seemed to be community relations. Evan’s reputation precedes him so I’m sure Acadia will be a brilliant technical artifact, but I wouldn’t get burned twice trying to be a member of that community or contributing to it technically.
NoDodgeQuestion 9 hours ago [-]
Do you nurse a personal grudge?
ModernMech 9 hours ago [-]
No, I don't know Evan and have never personally interacted with him. I'm wary of BDFLs because you can invest in a tool and then that time and energy is wasted if the wind changes.
brodo 7 hours ago [-]
I would make the larger point that I do not like my software to depend on any software with a bus factor of one that I can't control. Elm had this problem and Acadia has it too.
G4BB3R 6 hours ago [-]
The bus factor for Elm is currently 2, since Tereza (his Wife) works on both Elm and Acadia.
6 hours ago [-]
jeremyjh 9 hours ago [-]
I don't see anything special here. Haskell has had stuff like this for more than a decade, Selda is probably the one closest to Acadia: https://valderman.github.io/selda/
Despite their claims, this is not substantially different from ORM platforms in many languages.
I won't be able to use Acadia at work, and I don't have the risk tolerance to use it for personal projects, but I'm looking forward to seeing how/if this model pays the bills. Can it compete with more liberally licensed code?
let_rec 8 hours ago [-]
It seems like this is a few things:
1. An Elm-like programming language that lives in .db files
2. A compiler from this language to strongly-typed database procedures in a target backend language
This has more in common with a semantic layer than an ORM.
What you gain is a shared language that connects the table definitions (say a SQL migrations folder) and your API language (often handwritten SQL). This can be type checked and optimized for you.
But for me the big question is what functionality do you lose? Can I express everything that PostgreSQL can?
honungsburk 13 hours ago [-]
New functional query language for PostgreSQL and SQLite by Evan Czaplicki the author of Elm
pelagicAustral 11 hours ago [-]
So this is capable of turning a one-liner of SQL into six lines of barely readable code?
fwlr 10 hours ago [-]
It seems that is the price you pay for the power to turn a 600-line nightmare SQL query into 60 lines of barely readable code.
bazoom42 10 hours ago [-]
I would like to see that example then.
I’m all for improving on SQL, but this syntax does not even solve the dangling comma issue as far as I can tell from the example.
preg_match 5 hours ago [-]
I'd rather take the 600 lines of SQL, provided it's not dynamically constructed. SQL is a very high level language, it's fine IMO.
janderland 10 hours ago [-]
SQL is a horrible language. I’d gladly program in something composable like Elm.
ch4s3 10 hours ago [-]
Unfortunately Evan removed GROUP BY in 0.19 and left to buy cigarettes.
quikoa 10 hours ago [-]
It'd definitely need a solid team behind this and not just Evan Czaplicki if I were to trust a database with my data.
Datalog is awesome but I just don't think it's going to get mainstream adoption at this point.
tclancy 10 hours ago [-]
As a programming language? Sure. As a way to work with relational data? It may be my favorite "language" across all domains because of the terse beauty. I am a self-taught, no CS coder but SQL is the one place where I feel like I get all the math I should know.
An opinionated, possibly hot take would be to call SQL "A more elegant weapon of a civilized age".
bazoom42 10 hours ago [-]
Or “the worst query language ever, except for all the alternatives”
7 hours ago [-]
ModernMech 9 hours ago [-]
Maybe so, but my father in law, who is a salesman and knows nothing about computers and programming still knows SQL.
SQL is a horrible language in the same way Excel is -- programmers hate it but the what makes it a horrible programming language to developers is what makes it accessible to non programmers.
gampleman 5 hours ago [-]
Some interesting features here:
- sum types/ADTs have been long missing from database data modeling and this is welcome change. It's not entirely clear to me how the migration strategy here will work with things like removing a variant, etc.
- first class enforced RLS - this seems like a fantastic way to ensure safety/security guarantees. Secure by construction is always preferable to bolt-on security controls.
- composability with a strong module system. I think this will work well in ensuring large schemas can evolve over time. I wonder if there will be package manager in the future.
mjaniczek 11 hours ago [-]
Having reusable functions and pipelines compiling to SQL sounds amazing. (EDIT: and sum types!) Will want to try this out on some side project later.
Although for my Elm + backend needs I feel like I still prefer Lamdera: https://dashboard.lamdera.app/ - WebSocket communication and being able to push new data to clients immediately instead of juggling HTTP endpoints and the client having to pull/refresh. `sendToBackend`, `sendToFrontend`, `broadcast` are a great primitive.
geophile 2 hours ago [-]
I have a very long history with language interfaces to databases.
- As a grad student in the 80s, I read a lot about "database programming languages", which aimed to provide persistence and query capabilities to conventional programming languages, in a seamless way.
- The next step to putting those ideas into practice: Participated in a research project on adding database capabilities to a programming language (anyone remember Ada?)
- I designed and developed most of the modeling and query language features of one of the major object-oriented database systems, back in the early 90s.
- I also designed and contributed to a SQL interface to our OODB, as well as an ORM, taking our model and query language, and mapping it to SQL.
- Turned down an offer from a software giant of the late 90s, to add database capabilities to one of their main languages, (basically bringing to their language what I had built at the OODB company).
- Designed and built a Java ORM (late 90s).
And after working on this stuff for something like 20 years, I concluded that it's all misguided. For all of its ugliness and weirdness, SQL was designed to address a certain set of requirements, and has succeeded wildly. New database programming languages face huge problems of acceptance, and needing to solve the exact same problems that SQL handles now. (This was easier 30 years ago since it was still early days for SQL. Now it's basically impossible.) ORMs are a terrible idea, in the "now you have two problems" category. Not only do you need to write high-performance queries, but you have to get your ORM to actually issue those queries. (Yes, ORMs have escapes to raw SQL. The existence of these escapes proves my point.) And schemas change, and the mapping to your language model has to change, and it's a mess.
Just use SQL. It's the right tool for the job it was designed for. Use a database driver to integrate with your language. It's just not that hard.
setr 2 minutes ago [-]
The thing I’ve never understood is why SQL itself is not the target of attack. There’s already an inherent language abstraction with the planner; Postgres in theory could be the JVM with any number of languages implemented on top. Including a language that lends itself to composition and auto generation of PL functions.
ORMs are fundamentally difficult because of the mapping problem, but SQL code builders should be trivial. Auto-generating and exposing every DB functionality as a type-safe $LANG function should be trivial. Instead, they’re also accidentally difficult because building SQL is difficult.
Outside of SQL, you’ve got datalog… and that’s about it. And I guess whatever horrors the NoSQL crowd keeps coming up with
pjmlp 52 minutes ago [-]
Given your experience, what is your opinion on stored procedures?
I love them, think that what can be done in the database should stay in the database, and many of these abstraction on top are all ways to avoid just having to implement them.
And the main reason, DB portability, seldom happens in reality, most product die still using the database they were original created with.
raumgeist 11 hours ago [-]
Looks very nice.
Last year I took up rust, coming from c++, and some of the modern features rust brings are just so nice to have (even something as simple as not having to forward declare a class).
This year I started working with postgres and you just can't help but notice how sql is coming from the c-Era of programming. Having better and more modern ways to express my queries would be great to improve correctness and performance.
schaefer 10 hours ago [-]
> …can't help but notice how sql is coming from the c-Era of programming. Having … more modern ways to express my queries would be great to improve correctness …
SQL is based in pure mathematics: set theory, relational algebra.
The process of applying mathematical rigor to your database design to prove correctness is referred to as normalization.
I don’t mind criticisms like “It’s old, yuck”, but criticisms like “it’s not correct” mean you haven’t studied or applied the mathematical underpinnings of sql.
dminik 9 hours ago [-]
Syntax aside, programmers and mathematicians have a very different view on how things should be done.
Programmers look at data and see opportunities for running a pipeline of transformations (map/filter/...). And they tend to write their SQL like this as well. Or use something like Linq or one of the various pipe syntax SQL extensions.
I would say that this is a major reason why there is this sentiment of "SQL is yucky" by developers. The mental models just don't match.
pjmlp 56 minutes ago [-]
Or they did a proper Software Engineer degree that teached on how to use SQL properly, including implementing their own toy SQL engine backed by B-Tree indexes, with raw i-node blocks for storage.
skydhash 9 hours ago [-]
Data storage and retrieval is a different domain than data processing. SQL is very good at the former, not so great for the latter.
SQL is closer to array programming than the usual imperative implementation of looping (and stream programming like the one in Java and Javascript). A better implementation is functional programming like haskell and clojure (lazy and composition of functions).
I think developers should be able to switch their mental model on the fly according to the current domain instead of getting stuck in the first paradigm they have learned.
mkehrt 9 hours ago [-]
This isn’t talking about correctness of SQL. It’s talking about correctness of queries.
huahaiy 6 hours ago [-]
It is older than C. It is based on COBOL era idea of structured English as a computer language. There are better alternatives, e.g. Datalog.
schaefer 32 minutes ago [-]
I'm curious if you've personally used datalog in any projects.
I've written some prolog, but haven't ever worked with datalog.
Minigraph looks promising for some introductory goofing around.
pjmlp 56 minutes ago [-]
These kind of comments don't age well in the days of AI programming using English.
senderista 4 hours ago [-]
COBOL is indeed the spiritual predecessor of SQL. We have learned a lot since then about PL design, to put it mildly.
pjmlp 55 minutes ago [-]
Yes, we now programm in straight English, and hope the machine gets it right.
1saadcodes 3 hours ago [-]
The idea of treating database programming more like regular programming is nice. I'm just not sure how much complexity this actually removes versus moving that complexity somewhere else
DarkNova6 11 hours ago [-]
I was hoping for an alternative to PLSQL or stored procedures. But this isn’t about „Database Programming“, it’s a SQL replacement…
pjmlp 10 hours ago [-]
It isn't that bad, at least for those of us that like Ada, and feel at home on SQL Developer.
crabmusket 8 hours ago [-]
Reading this, I mistook it for a slightly different idea: using these functional languages directly inside the database process, avoiding SQL altogether.
I've wanted to try that out with e.g. Roc and a reimplementation of SQLite's on-disk format. (Of course, that's a non-starter for production use, but it could be an interesting experiment to see what that programming model was like.) The database would become kind of like a library you use to build your tables and queries with.
Also, thank you for calling it a 1+n query, not an n+1 query ;)
SkiFire13 10 hours ago [-]
I agree with the premises, but the result proposed here doesn't look like anything I would like to use unfortunately. Even just looking at a glance you cannot see what it's doing and what each part means.
Still might be viable, but would be tricky to sell.
> SUBSCRIPTION TERMS
> This license is subscription-based and will remain valid only for the duration of your active subscription. Upon expiration or termination of your subscription:
> a) Your rights to use the Software will cease; b) You must uninstall and stop using the Software; and c) You may lose access to any data or content created with or stored in the Software.
hombre_fatal 9 hours ago [-]
On the other hand, norms in software right now are that suckers build and maintain software for free + "the love of the game should be enough for anyone", so it's shocking when people break the norm.
There are tons of problems with this, but the simplicity is comfy.
anentropic 9 hours ago [-]
Needs proper docs
stuff like "The endpoint keyword" just gets a mention on the front page/readme with no further detail
JoelJacobson 7 hours ago [-]
I wonder what a nontrivial multi-table query with joins look like in Acadia?
ArtemKhymenko 12 hours ago [-]
Pretty nice, thanks
4 hours ago [-]
akoboldfrying 10 hours ago [-]
Is this at all similar to LINQ in C#? I never used it, but I'm vaguely aware of it being a functional approach to querying an RDBMS.
skydhash 9 hours ago [-]
From what I seen (not an expert). It’s mostly sql with a c# flavor and auto translation to native type.
OhMeadhbh 8 hours ago [-]
Anyone else just see a blank page when hitting this link? Maybe it doesn't like Firefox or is doing some sort of JavaScript shinanigans to defeat our AI overlords. I don't have enough coffee yet to debug it.
Exciting news!! Love Elm, can't wait to use it more
dboreham 8 hours ago [-]
Hmm. I've skimmed the article. It looks to be another ORM/FRM type thing. There are many issues with such things, but for me the most troubling is this: in most systems (obviously...it depends) you don't want to wind the database around the axle of any one software component or language. Having the data separate from the code, and defined/managed with a language that suits data management is a feature not something to be designed out. My hunch is that people who come up with these "solutions" fail to realize this. They then condemn everyone using their layer to endless hair pulling trying to figure out "what SQL did it make from that?" and "how do I make it do this SQL?".
I dont see how this isn't just an ORM (like Entity Framework in dot net land).
Izkata 9 hours ago [-]
Yeah, from the post it might even be more limited than Django (python) ever was. For example it allows the user to define its own fields, which was used over a decade ago in libraries to extend Django and provide json support long before it was officially supported.
weego 9 hours ago [-]
it might semantically not be an ORM because of something at an engineering level, but it's 100% ORM like from a user point of view, so it's an ORM.
DarkNova6 11 hours ago [-]
It looks like the HN hug of death has found a new victim
Look at all the features supported here:
https://www.postgresql.org/docs/current/sql-createtable.html
And then consider that other databases have even more. If you manage your schemas in code then you lose access to all of those, and will eventually need to write SQL anyway.
For queries it isn't such a problem, especially if you have a nice compiler. However, I recently lost faith in SQL wrappers/abstractions. The usual justification was that a lot of developers don't know SQL well, but LLMs are great at it. It's easier for the LLM to write SQL than some less familiar DSL. And SQL was written to be relatively easy to understand, especially if you do things like use CTEs and views correctly it should be possible to factor logic out to make even complex queries understandable.
The question for frameworks like Acadia is really: assuming I am fluent in SQL and know every feature of my database, what does the framework buy me? Because that's the perspective an LLM comes to it with.
I've been using Ormin [1] in Nim which works by parsing the SQL tables and uses it to compile time check queries:
I think that's better since defining SQL should be the source-of-truth for the DB and the code. ORM's always ended up causing trouble in my experience.Things like indexes, defaults, partitions, etc generally aren't expressible in code without a lot of kludges. Then each DB engine have pretty different rules, syntax, etc for tables.
However having the queries compile time checked, type conversions handled, and the nuances between SQL query syntax handled is rather nice. As you mention it's a much easier subset.
1: https://github.com/Araq/ormin
Just learn SQL, it's not that hard. A lot of very very smart people put a lot of effort into it. It's very good. The things that are annoy you about it are often there because of something you don't yet even realize is something you need to be aware of, or because your fundamental understanding of things is just wrong or incomplete.
You can always be more expressive and portable in raw SQL, that’s obvious, but the things you’re doing have to be used somewhere, so at some point the things you are doing have to cross a barrier. For the 90% use case, ORMs are a pragmatic choice because the good abstractions aren’t about the syntax, they’re about allowing you to talk about and mutate data within the language paradigms that everything else is written in.
I agree. In my experience, ORMs are more complex and harder to learn to an expert level than SQL. Knowing Java (but not SQL) doesn't help much with learning Java ORMs (Again, to an expert level). Besides not supporting all the SQL features of some DB, ORMs also covers other things such as caching.
Learning ORMs is likely just as difficult as learning SQL. It is likely harder to learn how to optimize performance with ORMs.
SQL as opposed to code has the advantage that it can be kept in a separate file, and thus modified by experts in databases without changing the code. The article claims the author found migrations harder with SQL than with his framework. I would think it would depend a great deal on the database one is migrating.
I'm not convinced that LLMs make things easier, you still need an expert to verify the generated code, and to tune it, as often the database is business critical with serious consequences if wrong, slow, or turns out to be infringement of someone's copyright.
Just learn SQL!
It also doesn’t absolve the fact that SQL is not a particularly well-designed language for smashing strings together like a Neanderthal. In fact, you might even say it’s absolutely horrid at it, with random keywords, extraneous syntax, and general lack of compositional capabilities.
The relational model is fantastic — Codd is Godd, after all. The engines are a work of art. The SQL language is a shitshow. PL/SQL and all its variants are a crime upon the PL community. The programmatic interface to a database is a shitshow, because it is SQL and only SQL. The SQL standard is a joke and standardizes nothing.
None of this is contentious, or should be, once you’ve learned SQL.
Instead of smashing strings, you can code with all the affordances of C90 and still get the chance to smash strings together if you need to do anything beyond utilizing simple variables (EXECUTE) — now with an even worse string manipulation stdlib. And you also get the privilege of working with the some of the most worthless parser errors known to modern man. As an added bonus, DB IDEs are universally worse at text-editing & refactoring than the equivalent application editor
You can reuse code through extensions/external instead, and have access to real programming languages with actual libraries… but now you’re kicked out of managed environments because it’s not whitelisted, and even if you do run it, you’re back to smashing strings together like a Neanderthal trying to communicate to your DB.
Sprocs/functions are useful because they do useful engine things — they run locally with the data, they have an easier time playing with transaction flow, some logic is much easier to express with a cursor instead of set logic and you get to avoid most of the penalties you’d have otherwise.
They do absolutely nothing to make SQL a less terrible interface to your database, except by stuffing it under a rug (CALL).
If only C90 was half as good.
Meanwhile embedding SQL in a string with `?` everywhere, manually converting the results, and remembering some of the SQL syntax is annoying.
Take this for example. Why do we have static type checking for typescript? Why do we have a build step for this?
Why DON'T we have it for SQL? Why is it runtime strings? So no static checking and the only way to test if a query works is to run it?
The purpose of these replacement layers is to get it all under one language. Once it's all under one language you get full safety and fusion across the two concepts. Query builders and ORMs are shooting for an ideal, and the ideal makes sense. It's just a nightmare to implement and thus fundamentally there are compatibility issues and that's why a lot of people in general don't like orms.
There's also a sync step where the model in the language has to be aligned with the model in the database which is just an extra mutating state layer which further compounds the bugs.
Instead of using all the consistencies provided in the database process - including types, but also date/time, constraints, transactions, triggers etc. you are exiting the system and losing all guarantees.
This system also doesn’t solve that problem.
Additional DML has plenty of options to enforce rules that keep data consistency.
While they make the life harder to delete/update/insert items in specific sequences, they can save the day on bad queries.
So it makes sense to only expose the logical model at the ORM layer.
The problem comes if you want to define the database schema through the ORM layer, rather than just represet it.
> https://www.postgresql.org/docs/current/sql-createtable.html
Unironcally, yesterday i was vibe-coding a small app for personal use using Django and was quite shocked to discover that Django's orm does not support something as simple as specifying a database schema other than the default "public" one out of the box.
You either have to add options specific from libpq:
Or you have to do it from the postgresql side: It's not ergonomic at all.There is a lot of valid critic for SQL and I would be very happy if some things would have been designed different.
OTOH the architecture and mathematics behind relational databases are simple, composable and stood the test of time more than most other designs, methodologies or approaches to software development.
Though SQL can be improved, even with my average SQL skills I never had trouble getting information out of a database and fancy stuff like window functions make to my understanding even standard SQL Turing complete.
SQL has the native database support, for most companies the data and the database will outlive any specific application or even the whole ecosystem of a programming language/platform (Visual Basic, Visual FoxPro, Python 2, ...)
Further, we have fantastic books, knowledge, ORMs, query builders and a gigantic ecosystem in tools for SQL and SQL databases.
Acadia might be brilliant from a technological point of view, but it does not matter, because it does not look like a big enough improvement compared to SQL that it seems worth to invest in it. I will rather improve my knowledge of standard SQL or my knowledge for a specific relational database.
Finally Acadia does not really seem to raise the bar compared to other ORMs/Query builder. I get that from a FP point of view map/filter are nicer than a SELECT ... WHERE, but at some point in the projects I participated one would end up interacting directly with the database anyway, and at that moment I am back at SQL, so what did I gain?
Other than that, it's perfect, no notes.
I don't quite like how the same CTE lives in 60 different places in my codebase, but at least the WITH clause changed things for me.
Also really liked Snowflake's result_scan for composing chains, mostly because I don't rerun expensive parts again and again. You can use ->> as a shortcut, but I don't think it uses results caching internally to skip waiting for them to all re-run & actually optimizes the whole thing.
It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.
This has a number of elegant properties (and also improves the kinds of optimizations a query planner / execution stage can apply.)
A similar divergence is that the relational model has no concept of nulls. Presence/absence is expressed through "item not in set" in various ways, and by properly normalizing the data.
SQL also isn't properly expression oriented or composable at all. A relational algebraic language absolutely can be, and can lend itself to much more elegant data handling.
In many ways SQL is to "relational" like Java or C++ are to "object oriented" -- it got in very early to market, got mainstream success, and dominated the field, and in so doing it mangled people's perceptions of what a database is, and also made people either define "relational" as "SQL" (sigh), and even worse because they misunderstand what relational is while also hating SQL, they try to throw the baby out with the bathwater with their successors.
Very cogent.
It does mention UInt64 which is not a modern type and as far as I know is supported by every database.
It also compiles to SQL but it isnt clear where the advantage comes from other than using a different syntax to do things.
This makes the database closer to something that Acadia compiles to, rather than something Acadia sits on top of. From my own developer experience this feels off, because I generally expect the data layer to be king and application code to revolve around that, rather than having data representation created in code and the database created off that (this is why I also dislike things like ORMs).
In general I view databases as usually having more longevity than application code, especially as you accumulate more data over time. For serious production applications, the database often outlives multiple rewrites of the production application.
I suspect though my concerns are overall rather minor. The ergonomics of the language itself seem enjoyable. Acadia seems like it would be great as an embedded DSL. It's a bit unfortunate that it currently seems coupled to creating an HTTP server. I think that Acadia has greater ambitions beyond just the database, as evidenced by creating a binary web connection with frontend Elm code to presumably obviate the need for encode-decode layers. It seems like Acadia is meant to be a stepping stone towards a closer frontend-backend fusion. But I agree with mjaniczek that something like Lamdera seems a better fit for that.
But given how early Acadia is, I'm still very excited for where it goes. What I've listed is surmountable and I also feel that often a closer frontend-backend fusion might be worthwhile.
I am mostly aligned with the article on what I want from next generation of web development. But I don't think using specific library in a specific language or specific query language is a viable long term solution. Hance the mention of Substrait. The solution that I think is needed, is something like LLVM but for databases.
As for the NoSQL, I think it was the worse thing that happened to databases in the last 20 years, probably more
Personally, I’d be very cautious about adopting closed-source software with such a restrictive license as part of an application, especially given the context of Elm’s trajectory. When Elm went through breaking changes or regressions, or was not worked on publicly for years, users had access to the source and the right to modify it. With Acadia’s licensing, you’d be stranded.
https://acadia.engineering/license/faq
The way I read this, Acadia is an attempt to finance working on both it and Elm.
Despite their claims, this is not substantially different from ORM platforms in many languages.
I won't be able to use Acadia at work, and I don't have the risk tolerance to use it for personal projects, but I'm looking forward to seeing how/if this model pays the bills. Can it compete with more liberally licensed code?
1. An Elm-like programming language that lives in .db files
2. A compiler from this language to strongly-typed database procedures in a target backend language
This has more in common with a semantic layer than an ORM.
What you gain is a shared language that connects the table definitions (say a SQL migrations folder) and your API language (often handwritten SQL). This can be type checked and optimized for you.
But for me the big question is what functionality do you lose? Can I express everything that PostgreSQL can?
I’m all for improving on SQL, but this syntax does not even solve the dangling comma issue as far as I can tell from the example.
An opinionated, possibly hot take would be to call SQL "A more elegant weapon of a civilized age".
SQL is a horrible language in the same way Excel is -- programmers hate it but the what makes it a horrible programming language to developers is what makes it accessible to non programmers.
- sum types/ADTs have been long missing from database data modeling and this is welcome change. It's not entirely clear to me how the migration strategy here will work with things like removing a variant, etc.
- first class enforced RLS - this seems like a fantastic way to ensure safety/security guarantees. Secure by construction is always preferable to bolt-on security controls.
- composability with a strong module system. I think this will work well in ensuring large schemas can evolve over time. I wonder if there will be package manager in the future.
Although for my Elm + backend needs I feel like I still prefer Lamdera: https://dashboard.lamdera.app/ - WebSocket communication and being able to push new data to clients immediately instead of juggling HTTP endpoints and the client having to pull/refresh. `sendToBackend`, `sendToFrontend`, `broadcast` are a great primitive.
- As a grad student in the 80s, I read a lot about "database programming languages", which aimed to provide persistence and query capabilities to conventional programming languages, in a seamless way.
- The next step to putting those ideas into practice: Participated in a research project on adding database capabilities to a programming language (anyone remember Ada?)
- I designed and developed most of the modeling and query language features of one of the major object-oriented database systems, back in the early 90s.
- I also designed and contributed to a SQL interface to our OODB, as well as an ORM, taking our model and query language, and mapping it to SQL.
- Turned down an offer from a software giant of the late 90s, to add database capabilities to one of their main languages, (basically bringing to their language what I had built at the OODB company).
- Designed and built a Java ORM (late 90s).
And after working on this stuff for something like 20 years, I concluded that it's all misguided. For all of its ugliness and weirdness, SQL was designed to address a certain set of requirements, and has succeeded wildly. New database programming languages face huge problems of acceptance, and needing to solve the exact same problems that SQL handles now. (This was easier 30 years ago since it was still early days for SQL. Now it's basically impossible.) ORMs are a terrible idea, in the "now you have two problems" category. Not only do you need to write high-performance queries, but you have to get your ORM to actually issue those queries. (Yes, ORMs have escapes to raw SQL. The existence of these escapes proves my point.) And schemas change, and the mapping to your language model has to change, and it's a mess.
Just use SQL. It's the right tool for the job it was designed for. Use a database driver to integrate with your language. It's just not that hard.
ORMs are fundamentally difficult because of the mapping problem, but SQL code builders should be trivial. Auto-generating and exposing every DB functionality as a type-safe $LANG function should be trivial. Instead, they’re also accidentally difficult because building SQL is difficult.
Outside of SQL, you’ve got datalog… and that’s about it. And I guess whatever horrors the NoSQL crowd keeps coming up with
I love them, think that what can be done in the database should stay in the database, and many of these abstraction on top are all ways to avoid just having to implement them.
And the main reason, DB portability, seldom happens in reality, most product die still using the database they were original created with.
This year I started working with postgres and you just can't help but notice how sql is coming from the c-Era of programming. Having better and more modern ways to express my queries would be great to improve correctness and performance.
SQL is based in pure mathematics: set theory, relational algebra.
The process of applying mathematical rigor to your database design to prove correctness is referred to as normalization.
I don’t mind criticisms like “It’s old, yuck”, but criticisms like “it’s not correct” mean you haven’t studied or applied the mathematical underpinnings of sql.
Programmers look at data and see opportunities for running a pipeline of transformations (map/filter/...). And they tend to write their SQL like this as well. Or use something like Linq or one of the various pipe syntax SQL extensions.
I would say that this is a major reason why there is this sentiment of "SQL is yucky" by developers. The mental models just don't match.
SQL is closer to array programming than the usual imperative implementation of looping (and stream programming like the one in Java and Javascript). A better implementation is functional programming like haskell and clojure (lazy and composition of functions).
I think developers should be able to switch their mental model on the fly according to the current domain instead of getting stuck in the first paradigm they have learned.
Minigraph looks promising for some introductory goofing around.
I've wanted to try that out with e.g. Roc and a reimplementation of SQLite's on-disk format. (Of course, that's a non-starter for production use, but it could be an interesting experiment to see what that programming model was like.) The database would become kind of like a library you use to build your tables and queries with.
Also, thank you for calling it a 1+n query, not an n+1 query ;)
Still might be viable, but would be tricky to sell.
> SUBSCRIPTION TERMS
> This license is subscription-based and will remain valid only for the duration of your active subscription. Upon expiration or termination of your subscription:
> a) Your rights to use the Software will cease; b) You must uninstall and stop using the Software; and c) You may lose access to any data or content created with or stored in the Software.
stuff like "The endpoint keyword" just gets a mention on the front page/readme with no further detail
| Not an Object-Relational Mapping (ORM).