If you’re modeling relational data, it doesn’t seem like you can get around using a DB that uses SQL, which to me is the worst: most programmers aren’t DB experts and the SQL they output is quite often terrible.
Not to dunk on the lemmy devs, they do a good job, but they themselves know that their SQL is bad. Luckily there are community members who stepped up and are doing a great job at fixing the numerous performance issues and tuning the DB settings, but not everybody has that kind of support, nor time.
Also, the translation step from binary (program) -> text (SQL) -> binary (server), just feels quite wrong. For HTML and CSS, it’s fine, but for SQL, where injection is still in the top 10 security risks, is there something better?
Yes, there are ORMs, but some languages don’t have them (rust has diesel for example, which still requires you to write SQL) and it would be great to “just” have a DB with a binary protocol that makes it unnecessary to write an ORM.
Does such a thing exist? Is there something better than SQL out there?
You must log in or register to comment.
most programmers aren’t DB experts and the SQL they output is quite often terrible.
Isn’t that looking at it the wrong way / missing the point?
If you’re fine with simple queries, use an ORM of your tech stack. Once you have to understand querying performance and weigh usage patterns, constraints, and cache performance and size, then it’s not about SQL anymore. It’s about understanding the DBMS you use.
You may ask “why can’t I use a different language for the querying part of it”. But I haven’t seen anything better yet.
Having a common, well-understood, established, documented, inter-product compatible language has a lot of value. Using a different language to the well-established industry standard is in itself an increased hurdle to adoption.
Getting back to the original quote: I don’t think anything else would serve bad developers / non-experts any better.
I’d recommend everyone check out https://prql-lang.org/. It’s SQL, but readable and writable in a sane way.
And no, SQL is NOT readable or writable for anything involving more than a single join.
You can write selects with many joins, as long they are regular and either add a column or reduce the result set. You have to write the joins explicitly though. Just shoving all of the restrictions into the where clause will definitely confuse everybody.
No, SQL is great for relational data. There’s like decades of research into it. It’s the best.
SQL is horrible as a language to read or write. There’s a million different variants, because it lacks so many basic things. And when used in other code, you generally end up string concatinating one language in another language, with all the HORRIBLE bugs something like that brings about.
Imagine Backend People said we should just write adhoc Javascript for the frontend by concatinating the “correct” code in the backend.
I used to be full on the ORM train. Now I’m a little less enthusiastic. What I actually think people need most of the time is something closer to ActiveRecord. Something that can easily map a result set into a collection of typed objects. You still generally write parameterized SQL, but the work of translating a db decimal into the correct target type on a record object in your language is handled for you (for example). In .net, Dapper is a good example.
I also think most people overemphasize or talk about how other programmers “suck at SQL” waaayy too much.
IMO, for most situations, these are the few high-level things that devs should be vigilant about:
- parameterize all sql.
- consider the big-o of the app-side lookup/write methods (sometimes an app join or pulling a larger set and filtering in memory is better than crafting very complex projections in sql). This is a little harder to analyze with an ORM, but not by much if you keep the mappings simple and understand the loading semantics of the ORM.
- understand the index coverage of queries and model table keys properly to maintain insert performance (monotonically increasing keys).
- stop fixating on optimizing queries that run in a few seconds, a few times a day. Optimize the stuff that you run on every transaction - if you need to.
On most of those points, if you don’t have aggregate query counts/metrics on query performance on your clusters, starting to get cute with complex queries is flying blind, and there’s no way to prioritize what to optimize.
For the vast majority of cases, simple, obvious selects that don’t involve special db features are going to do the job for most applications. When the database becomes a bottleneck, there are usually much more effective ways to handle them than to try to hand optimize all the queries.
Lastly, I have a little bit of a theory that part of the reason people do/do not like looking at SQL in code is because it’s a hard context switch from one language to another, often requiring the programmer to switch to “stringly-typed” mode, something we all learn causes huge numbers of headaches in our first few months of programming. Some developers accept that there’s going to be different languages/contexts and not all of them are going to be as fluent or familiar, but accept that this is par for the job. Others recoil from the unfamiliar and want to burn it down. IMO, the former attitude is a lot more productive.
Without a DSL for writing SQL, any sufficiently complex program will end up with string concatinating all over the place. Basically, writing a language with ZERO checks or highlighting or anything. That’s asking for trouble.
But coming from Java, I agree that some ORMs go way too far.
