6 ms·

Rethinking Database Programming

259 points by honungsburk 2mo ago

pelagicAustral 2mo ago
So this is capable of turning a one-liner of SQL into six lines of barely readable code?
- fwlr 2mo 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 2mo 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 2mo 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 2mo ago
  SQL is a horrible language. I’d gladly program in something composable like Elm.
  - ch4s3 2mo ago
    Unfortunately Evan removed GROUP BY in 0.19 and left to buy cigarettes.
    - quikoa 2mo 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.
  - tclancy 2mo 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 2mo ago
      Or “the worst query language ever, except for all the alternatives”
    - deleted 2mo ago
      [deleted]
  - ModernMech 2mo 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.
  - huahaiy 2mo ago
    Agree. Datalog could be a better alternative: https://datalevin.org/docs/preface https://datalevin.org/docs/preface
    - packetlost 2mo ago
      Datalog is awesome but I just don't think it's going to get mainstream adoption at this point.
      - huahaiy 1mo ago
        Why not?
DarkNova6 2mo ago
It looks like the HN hug of death has found a new victim
- whilenot-dev 2mo ago
  https://archive.is/fpGFV https://archive.is/fpGFV
honungsburk 2mo ago
New functional query language for PostgreSQL and SQLite by Evan Czaplicki the author of Elm
ArtemKhymenko 2mo ago
Pretty nice, thanks
somelady 2mo ago
Exciting news!! Love Elm, can't wait to use it more
DarkNova6 2mo 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 2mo ago
  It isn't that bad, at least for those of us that like Ada, and feel at home on SQL Developer.
raumgeist 2mo 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 2mo 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.
  - mkehrt 2mo ago
    This isn’t talking about correctness of SQL. It’s talking about correctness of queries.
  - dminik 2mo 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.
    - skydhash 2mo 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.
nylonstrung 2mo ago
For columnar databases, I love Vortex' Dtypes which lets you attach semantic context in a logical type to what is essentially compressed Arrow https://docs.vortex.dev/concepts/dtypes#logical-types https://docs.vortex.dev/concepts/dtypes#logical-types
mjaniczek 2mo 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/ 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.
dwohnitmok 2mo 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 2mo 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/ https://substrait.io/ will gain traction and will be supported natively by databases
akoboldfrying 2mo 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 2mo ago
  From what I seen (not an expert). It’s mostly sql with a c# flavor and auto translation to native type.
- pjmlp 1mo ago
  LINQ is based on FP ideas on data manipulation.
gbjcantab 2mo 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 2mo 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 2mo 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 2mo 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.
      - ModernMech 2mo 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.
SkiFire13 2mo 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.
NoDodgeQuestion 2mo ago
Is it an ORM?
- happyraul 2mo ago
  From the homepage https://acadia.engineering/ https://acadia.engineering/: | Not an Object-Relational Mapping (ORM).
  - LandR 2mo ago
    I dont see how this isn't just an ORM (like Entity Framework in dot net land).
    - weego 2mo 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.
    - Izkata 2mo 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.
    - speed_spread 1mo ago
      It's an external contract first ORM. It brings strict typing but doesn't go into object persistence management. It's similar to what OpenAPI/Swagger does for REST. Being contract first means that both SQL and corresponding application data bindings get generated from the same universal spec. Cons : - It can make using platform-specific features harder than in plain SQL. - It makes database app code and SQL statements dependent on the whims of evolving generators libraries. Which is not a problem 'per se' but imposes an oversight cost, especially if you customize said generators or develop your own. But it brings many architectural advantages. Compared to typical ORM - Runtime initialization time is very quick if not instant. - Bindings can be precompiled in separate lib, only rebuilt when schema changes, making faster builds. - Schema management, meaning versioning and migration strategy planning can be centralized. That to me is a big thing for long lived business projects with multiple deployments in varying environments. - From the code it makes a database closer to a standard service API. There might be a parallel to make with using stored procedures as database interface.
lucasban 2mo ago
Or just let the language be the database like https://en.wikipedia.org/wiki/MUMPS https://en.wikipedia.org/wiki/MUMPS ;)
- shigawire 2mo ago
  There are tons of problems with this, but the simplicity is comfy.
bansiwebix 2mo ago
[dead]
phoghed 2mo ago
Sorry Evan, somebody already rethought the DB https://rethinkdb.com/ https://rethinkdb.com/
anentropic 2mo ago
Needs proper docs stuff like "The endpoint keyword" just gets a mention on the front page/readme with no further detail
dwohnitmok 2mo ago
Oh man. If this lobste.rs comment is correct about the subscription terms then this feels like a really hard pill to swallow: https://lobste.rs/s/ykq7ym/rethinking_database_programming#c_43ah5v https://lobste.rs/s/ykq7ym/rethinking_database_programming#c... 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 2mo 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.
  - dwohnitmok 2mo ago
    That's not the norm that's being broken here. Most DB technologies provide a "pay for updates, if you stop paying you keep the last version you paid for" model. This is how Oracle prices its DB tech, this is how jOOQ is priced (which is probably the closest thing to Acadia), this is how MS prices its DB tech etc.
    - hombre_fatal 1mo ago
      The responses to the pricing aspect of this announcement around the web disagree. I'm not saying you can't find paid software, especially from Oracle and Microsoft, but there's a different expectation for "just-a-guy announcing his project on twitter". You can see a similar mentality regarding Elm in general where the approachability of one guy had people in some sort of parasocial entitlement to the project that you wouldn't see if, for example, it were Google or an unknown who built Elm.
      - 1mo ago
jeremyjh 2mo 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/ https://valderman.github.io/selda/ Despite their claims, this is not substantially different from ORM platforms in many languages.
mike_hearn 2mo 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? Look at all the features supported here: https://www.postgresql.org/docs/current/sql-createtable.html 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.
- alpinisme 2mo 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.
  - whattheheckheck 2mo 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
  - groundzeros2015 2mo ago
    SQL is end to end type safe.
    - bazoom42 2mo ago
      Isn’t sql weakly typed? Or does this depend on the engine?
crabmusket 2mo 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 ;)
let_rec 2mo 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?
OhMeadhbh 2mo 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.
- sgerrand 2mo ago
  Try the Internet Archive's cache: https://web.archive.org/web/20260818090328/https://acadia.engineering/blog/rethinking-database-programming https://web.archive.org/web/20260818090328/https://acadia.en...
dboreham 2mo 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?".
JoelJacobson 2mo ago
I wonder what a nontrivial multi-table query with joins look like in Acadia?
bbkane 2mo ago
I'm actually most excited about the new funding model: https://acadia.engineering/license/faq https://acadia.engineering/license/faq 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?
CopyOnWrite 2mo 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?
- ModernMech 2mo ago
  SQL is based on the relational model but doesn't really conform to the mathematics e.g. doesn't exhibit set semantics.
  - stvltvs 2mo ago
    Can you elaborate? It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.
    - cmrdporcupine 2mo 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.
gampleman 2mo 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.
deleted 2mo ago
[deleted]
deleted 2mo ago
[deleted]
1saadcodes 2mo 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
geophile 2mo 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.
- pjmlp 2mo 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.
  - geophile 2mo ago
treebeard901 2mo 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.
tempaccount420 2mo ago
Closed source :( https://acadia.engineering/license/faq https://acadia.engineering/license/faq (archive since these things change over time: https://archive.vn/oUwxDwxD https://archive.vn/oUwxDwxD)
jmutex 2mo ago
ORMs earn their keep in the 90% CRUD path. The remaining 10% where you need raw SQL is exactly where you want to write it by hand anyway.

259 points by honungsburk 2mo ago

pelagicAustral 2mo ago

So this is capable of turning a one-liner of SQL into six lines of barely readable code?

fwlr 2mo 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 2mo 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 2mo 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 2mo ago
SQL is a horrible language. I’d gladly program in something composable like Elm.
- ch4s3 2mo ago
  Unfortunately Evan removed GROUP BY in 0.19 and left to buy cigarettes.
  - quikoa 2mo 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.
- tclancy 2mo 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 2mo ago
    Or “the worst query language ever, except for all the alternatives”
  - deleted 2mo ago
    [deleted]
- ModernMech 2mo 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.
- huahaiy 2mo ago
  Agree. Datalog could be a better alternative: https://datalevin.org/docs/preface https://datalevin.org/docs/preface
  - packetlost 2mo ago
    Datalog is awesome but I just don't think it's going to get mainstream adoption at this point.
    - huahaiy 1mo ago
      Why not?

DarkNova6 2mo ago

It looks like the HN hug of death has found a new victim

whilenot-dev 2mo ago
https://archive.is/fpGFV https://archive.is/fpGFV

honungsburk 2mo ago

New functional query language for PostgreSQL and SQLite by Evan Czaplicki the author of Elm

ArtemKhymenko 2mo ago

Pretty nice, thanks

somelady 2mo ago

Exciting news!! Love Elm, can't wait to use it more

DarkNova6 2mo 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 2mo ago
It isn't that bad, at least for those of us that like Ada, and feel at home on SQL Developer.

raumgeist 2mo 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 2mo 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.
- mkehrt 2mo ago
  This isn’t talking about correctness of SQL. It’s talking about correctness of queries.
- dminik 2mo 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.
  - skydhash 2mo 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.

nylonstrung 2mo ago

For columnar databases, I love Vortex' Dtypes which lets you attach semantic context in a logical type to what is essentially compressed Arrow https://docs.vortex.dev/concepts/dtypes#logical-types https://docs.vortex.dev/concepts/dtypes#logical-types

mjaniczek 2mo 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/ 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.

dwohnitmok 2mo 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 2mo 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/ https://substrait.io/ will gain traction and will be supported natively by databases

akoboldfrying 2mo 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 2mo ago
From what I seen (not an expert). It’s mostly sql with a c# flavor and auto translation to native type.
pjmlp 1mo ago
LINQ is based on FP ideas on data manipulation.

gbjcantab 2mo 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 2mo 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 2mo 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 2mo 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.
    - ModernMech 2mo 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.

SkiFire13 2mo 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.

NoDodgeQuestion 2mo ago

Is it an ORM?

happyraul 2mo ago
From the homepage https://acadia.engineering/ https://acadia.engineering/: | Not an Object-Relational Mapping (ORM).
- LandR 2mo ago
  I dont see how this isn't just an ORM (like Entity Framework in dot net land).
  - weego 2mo 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.
  - Izkata 2mo 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.
  - speed_spread 1mo ago
    It's an external contract first ORM. It brings strict typing but doesn't go into object persistence management. It's similar to what OpenAPI/Swagger does for REST. Being contract first means that both SQL and corresponding application data bindings get generated from the same universal spec. Cons : - It can make using platform-specific features harder than in plain SQL. - It makes database app code and SQL statements dependent on the whims of evolving generators libraries. Which is not a problem 'per se' but imposes an oversight cost, especially if you customize said generators or develop your own. But it brings many architectural advantages. Compared to typical ORM - Runtime initialization time is very quick if not instant. - Bindings can be precompiled in separate lib, only rebuilt when schema changes, making faster builds. - Schema management, meaning versioning and migration strategy planning can be centralized. That to me is a big thing for long lived business projects with multiple deployments in varying environments. - From the code it makes a database closer to a standard service API. There might be a parallel to make with using stored procedures as database interface.

lucasban 2mo ago

Or just let the language be the database like https://en.wikipedia.org/wiki/MUMPS https://en.wikipedia.org/wiki/MUMPS ;)

shigawire 2mo ago
There are tons of problems with this, but the simplicity is comfy.

bansiwebix 2mo ago

[dead]

phoghed 2mo ago

Sorry Evan, somebody already rethought the DB https://rethinkdb.com/ https://rethinkdb.com/

anentropic 2mo ago

Needs proper docs stuff like "The endpoint keyword" just gets a mention on the front page/readme with no further detail

dwohnitmok 2mo ago

Oh man. If this lobste.rs comment is correct about the subscription terms then this feels like a really hard pill to swallow: https://lobste.rs/s/ykq7ym/rethinking_database_programming#c_43ah5v https://lobste.rs/s/ykq7ym/rethinking_database_programming#c... 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 2mo 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.
- dwohnitmok 2mo ago
  That's not the norm that's being broken here. Most DB technologies provide a "pay for updates, if you stop paying you keep the last version you paid for" model. This is how Oracle prices its DB tech, this is how jOOQ is priced (which is probably the closest thing to Acadia), this is how MS prices its DB tech etc.
  - hombre_fatal 1mo ago
    The responses to the pricing aspect of this announcement around the web disagree. I'm not saying you can't find paid software, especially from Oracle and Microsoft, but there's a different expectation for "just-a-guy announcing his project on twitter". You can see a similar mentality regarding Elm in general where the approachability of one guy had people in some sort of parasocial entitlement to the project that you wouldn't see if, for example, it were Google or an unknown who built Elm.
    - 1mo ago

jeremyjh 2mo 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/ https://valderman.github.io/selda/ Despite their claims, this is not substantially different from ORM platforms in many languages.

mike_hearn 2mo 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? Look at all the features supported here: https://www.postgresql.org/docs/current/sql-createtable.html 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.

alpinisme 2mo 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.
- whattheheckheck 2mo 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
- groundzeros2015 2mo ago
  SQL is end to end type safe.
  - bazoom42 2mo ago
    Isn’t sql weakly typed? Or does this depend on the engine?

crabmusket 2mo 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 ;)

let_rec 2mo 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?

OhMeadhbh 2mo 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.

sgerrand 2mo ago
Try the Internet Archive's cache: https://web.archive.org/web/20260818090328/https://acadia.engineering/blog/rethinking-database-programming https://web.archive.org/web/20260818090328/https://acadia.en...

dboreham 2mo 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?".

JoelJacobson 2mo ago

I wonder what a nontrivial multi-table query with joins look like in Acadia?

bbkane 2mo ago

I'm actually most excited about the new funding model: https://acadia.engineering/license/faq https://acadia.engineering/license/faq 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?

CopyOnWrite 2mo 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?

ModernMech 2mo ago
SQL is based on the relational model but doesn't really conform to the mathematics e.g. doesn't exhibit set semantics.
- stvltvs 2mo ago
  Can you elaborate? It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.
  - cmrdporcupine 2mo 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.

gampleman 2mo 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.

deleted 2mo ago

[deleted]

deleted 2mo ago

[deleted]

1saadcodes 2mo 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

geophile 2mo 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.

pjmlp 2mo 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.
- geophile 2mo ago

treebeard901 2mo 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.

tempaccount420 2mo ago

Closed source :( https://acadia.engineering/license/faq https://acadia.engineering/license/faq (archive since these things change over time: https://archive.vn/oUwxDwxD https://archive.vn/oUwxDwxD)

jmutex 2mo ago

ORMs earn their keep in the 90% CRUD path. The remaining 10% where you need raw SQL is exactly where you want to write it by hand anyway.