3 ms·
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 c
by 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 agoThe 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 agoI 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 agoSQL is end to end type safe.
- bazoom42 2mo agoIsn’t sql weakly typed? Or does this depend on the engine?
- groundzeros2015 2mo agoSQLite 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 2mo agoNo 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 2mo agoWhat happens if a query compares a string to a number?
- mike_hearn 2mo agoYou get a type error from the database.
- bazoom42 2mo agoAs far as I can tell, some engines will implicitly coerce types so “7” = 7
- moljac024 2mo agoBut 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.
- elcritch 2mo agoThere'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 https://github.com/Araq/ormin
- ltbarcly3 2mo agoJust 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.
- elcritch 2mo agoI 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.
- antihero 2mo agoI 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.
- adzm 2mo agoAgreed 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.
- bbkane 2mo agohttps://sqlc.dev/ https://sqlc.dev/ does this for me. Its been nice!
- truculent 2mo agoThe 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.
- Smalltalker-80 2mo agoAgreed, 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 2mo agoA 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 2mo agoIsn'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 2mo agoThe comment mentioned partioning schemes which is defined using SQL but belongs in the physical layer. Indexes are also defined in SQL.
- bbkane 2mo agoThanks. 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 2mo 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 ]).
- znpy 2mo ago> 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 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", }, } } Or you have to do it from the postgresql side: ALTER ROLE myuser IN DATABASE mydatabase SET search_path = myapp, public; It's not ergonomic at all.