mike_hearn
3 days 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
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.
znpy
3 days ago
> Look at all the features supported here:
> 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:
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "mydatabase",
"USER": "myuser",
"PASSWORD": "mypassword",
"HOST": "localhost",
"PORT": "5432",
"OPTIONS": {
"options": "-c search_path=myapp,public",
},
}
}
ALTER ROLE myuser
IN DATABASE mydatabase
SET search_path = myapp, public;
elcritch
3 days 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
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.
ltbarcly3
3 days ago
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 days 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
3 days 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!
threethirtytwo
3 days 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.
pjmlp
2 days ago
Only true when avoiding stored procedures.
threethirtytwo
a day ago
Yeah. Most systems avoid stored procedures imo. They way to go imo is to use stored procedures for everything, but the standard pattern has stored procedures as some sort of secondary thing.
Either way the types of the stored procedures do not statically mesh well with the types of the application server. So there's a lot of syncing issues here that can only be caught at runtime.
ltbarcly3
2 days ago
Runtime strings? No static checking? I think you have used mysql and think that mysql is somehow what you should expect, because you are just saying wildly incorrect things. (You don't know what you're talking about)
threethirtytwo
a day ago
i do. default way of doing things is sending a string from server to database.
You don't know what you're talking about.
elcritch
3 days 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.
setr
3 days 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
3 days ago
Only because some people are very opinated in avoiding stored procedures, and think smashing strings together is a much better solution.
setr
3 days 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
3 days ago
PL/SQL is great and using SQL Developer definitely better than smashing strings together.
If only C90 was half as good.
ameliaquining
3 days ago
Stored procedures have the wrong versioning model. If they were version-locked to the application code, instead of to the database schema, they'd be less of a pain and people might be more willing to use them.
pjmlp
3 days ago
There are CI/CD processes for deployment, versioning problem is solved at least for 30 years.
Also it is hardly any different from handling version differences in distributed systems, or split between frontend and backend on Web applications.
ameliaquining
2 days ago
Version differences in distributed systems (including Web apps) are a real pain! In many circumstances they're unavoidable, and we've developed various techniques to make them marginally easier, but if you can avoid the issue entirely by just not having the thing be distributed, that's the more maintainable choice.
mike_hearn
2 days ago
Oracle has a feature called 'editions' that does this. Different DB sessions can have different versions of redefinable objects like stored procs and packages.
alpinisme
3 days 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
3 days ago
SQL is end to end type safe.
victorbjorklund
3 days ago
Only backend to database. This is talking about typesafe from database - backend - frontend.
groundzeros2015
3 days 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 days 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
sharno
3 days ago
Which end? This moves one end to reach frontend code
bazoom42
3 days ago
Isn’t sql weakly typed? Or does this depend on the engine?
groundzeros2015
3 days 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.
pjmlp
3 days 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.
bazoom42
2 days ago
What happens if a query compares a string to a number?
mike_hearn
2 days ago
You get a type error from the database.
moljac024
a day ago
But when do you get that type error?
This is the important bit.
You get it after the app is deployed, the query is ran and a result is expected.
When do I get a type error from my language if it's statically typed? That's right, before I even deploy.
bazoom42
2 days ago
As far as I can tell, some engines will implicitly coerce types so “7” = 7
fulafel
3 days ago
They might mean static typing in queries.
whattheheckheck
3 days 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
adzm
3 days 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.
truculent
a day ago
The problem here is that you still have to do some pointless and tedious conversion between generated data structures and your domain data structures. Maybe with LLMs, some of that tedium goes away, but you still have to test, maintain and understand that part of the code.
bbkane
3 days ago
https://sqlc.dev/ does this for me. Its been nice!
Smalltalker-80
3 days 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.
bazoom42
3 days ago
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
3 days 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
3 days ago
The comment mentioned partioning schemes which is defined using SQL but belongs in the physical layer. Indexes are also defined in SQL.
bbkane
3 days 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.
ux266478
3 days ago
> 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?
In Prolog you'd just handle those as metapredicates. There are a million different ways to skin the cat there. For example on partitioning schemes:
:- vertical_partition(profile/4, [
core(1, 2), % UserID, Username -> stored in primary memory
metadata(1, 3, 4) % UserID, Bio, Preferences -> stored in cold storage
]).