logoalt Hacker News

mike_hearn • today at 12:08 PM • 5 replies • view on HN

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

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.

Replies

elcritch • today at 12:32 PM

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.

1: https://github.com/Araq/ormin

bazoom42 • today at 1:44 PM

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.

➕ show 1 reply
Smalltalker-80 • today at 1:03 PM

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 • today at 12:35 PM

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.

➕ show 1 reply
alpinisme • today at 12:30 PM

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.

➕ show 1 reply