Lots of caveats about difficulty to test and weird syntax.
But it is just that SQL is the most terse and standard way so express logic.
And that in itself is the most important factor to avoid bugs. Not what testing strategy you choose.
As someone who has written a ton of complex SQL... I couldn't disagree more.
Trying to shoehorn things that can trivially and intuitively be expressed in a couple of for-loops with a couple of variables, into SQL expressions making use of joins and aggregate functions and GROUP BY's and (god forbid) correlated subqueries... having to replicate subqueries because their results in one part of the query can't be re-used in another part... teaching people arcane terminology distinctions like between WHERE and HAVING... not having basic functions for calculating percentiles or even a basic median... certain basic kinds of logic operations that simply can't be done... flipping a coin on whether the query engine will use the index and execute in 10 ms, or decide to follow a different execution plan and take 5 minutes to run...
I have never encountered more bugs in business logic than in dealing with SQL. It obfuscates what should be clear. SQL isn't a solution for avoiding bugs, it's what causes so many of them.
Some similarities, but a lot of differences too.
Imagine the fun we're having...
Occasionally it's going to be some very specific aspect of performance limits, or a feature like spatial indexes that work in a special way. And occasionally it's going to be about cost or better integration with a particular cloud or other specific piece of tooling.
First and most prominent is that Sybase was bought by SAP and SAP has pivoted SQLAnywhere from a general RMDBS to focus on mobile applications (ie more embedded). Apart from product development changing focus we've struggled to buy licenses for our customers, so the writing on the wall was clear.
The seconds is that we've got some big customers who run their own MSSQL servers, and they've been pushing harder and harder for us to use their servers so they can handle administration and access control directly.
A third motivation is that while we've been primarily on-prem, a lot of customers these days don't want that, so we've started offering cloud hosted options. Since we're a Windows shop Azure made sense, and as such the hosted MSSQL also makes sense and is quite convenient.
Fourth is that, while MSSQL is quite anemic in terms of SQL language functionality, it does support transactional schema updates, which makes schema upgrades much less error prone.
Given that MSSQL syntax support is quite limited and we now have developed tooling and library support for multiple databases, we'll probably also add support for PostgreSQL later.
It also helps me understand my colleagues.
It may be how our brains are wired. For me whenever I see a for loop I see bugs.
It is also about that you get the concurrency from map/reduce-type jobs for free without having to think about it.
But yeah there are a couple of places you need to be careful. Avoiding duplicates and handling of NULL.
> not having basic functions for calculating percentiles or even a basic median
I don't think this is really true anymore; windowing functions are pretty prolific, and I think every major database will have some percentile functions
They're not though. That's why excellent coders often despise having to learn and use SQL, and basically just refuse to. SQL is uniquely terrible -- and I say this as someone with a career spanning from Win32 C++ to every major web technology.
> every major database will have some percentile functions
MySQL still doesn't. MariaDB only added it in 2018, and I've been writing queries for a lot longer than that. (And MySQL only added window functions in 2018 as well.)
And remember that, for various reasons, you're often (usually?) stuck using a database engine in production that's several years old, since upgrading a database engine is not something to be taken lightly.
This is basically a strawman + appeal to authority argument. I've no desire to bicker. "Uniquely terrible" is quite subjective, we'll have to agree to disagree on if it applies to sql.
I'm not going to write an article on this for you, but you can certainly see the sentiment frequently expressed on HN that the rise of NoSQL and of ORM's is a direct result of otherwise capable programmers not wanting to deal with SQL and its conceptual building blocks such as joins. Take that for what you will, but you might suspect there's a reason for it, and that it's not merely the learning curve for "every language".
SQL isn’t going away any time soon. At its heart, it’s relational algebra. If you can learn DS&A, you can learn relational algebra.
You're right that it isn't going away, but when it does, I'll be the first to dance on its grave.
I think I saw that sentiment a lot 5+ years ago. The last 2-3 years have been a lot more for postgresql and writing SQL directly.
Either way I don't think that "excellent coders" "despise" learning SQL, almost every senior coder that has interacted with a medium-to-large database has understood and valued SQL in my experience.
I've been coding professionally for 20 years and at every job I've ever had we've made the decision to move away from ORMs or as much as possible given the constraints of our system in preference of directly writing SQL. My previous employer dumped Rail in favor of a Go backend (a language whose userbase almost entirely eschews the idea of ORMs) and my current employer never even had one to start with, choose to write SQL directly in a typescript backend.
I'm not going to pull the stats for you but this very website is FULL of articles talking about the benefits of writing your own SQL and I can't even remember the last time someone tried to pitch me a shiny new ORM.
> teaching people arcane terminology distinctions like between WHERE and HAVING
This is akin to complaining about needing to teach people arcane terminology distinctions between FOR and WHILE. I think it's a crime that many CS degrees I see don't include a course in database and SQL. There are of course some arcade corners of SQL (I sometimes have to look up the different JSONB operators in postgres), but WHERE and HAVING ain't it.
> flipping a coin on whether the query engine will use the index and execute in 10 ms, or decide to follow a different execution plan and take 5 minutes to run
Heavens, if you think using an ORM is going to help you here, have you got another think coming.
Your take on this is so foreign to me I was honestly shocked to see it. That being said, you probably aren't the only one, so perhaps my perspective is more narrow than I had assumed as well.
Databases follow the COBOL model of extensibility — tis for me and not for thee. It is only to be done by the vendor, and maybe some third party specialists
Some co-workers and I inherited a code-base where the authors went down the views and stored procedures route. It was basicallly impossible to untangle; there was no knowing what relied on a view or a proc, so you couldn’t touch them at all, there were no docs, there were duplicates of everything (and again, no way to know what’s used). A good number of them had all kinds of side effects and weird performance characteristics. They weren’t version controlled.
The issues just kept going.
They can and should most definitely be testable, documentable, trackable and version controlled.
Should one also judge all backend programming by PHP standards circa 1998?
An array instead of a linked list or vice versa. A dequeue instead of a set or vice versa. A binary tree instead of a B+ tree. You can't just throw column names at a DB schema and hope for the best. There's actual engineering to be done, even if many programmers refuse to admit it when interacting with a relational database engine.
Do you have to read about it? Sure, just like all programming languages and platforms. But, interestingly, SQL was designed for non-technical analysts, so (to your point) it's more accessible than those others.
SQL schema creation and maintenance is all about choosing the right data structures for the job at hand.
If you neglect attention to your data structures, no algorithms will save you, and you really only have yourself to blame. It's a still to be learned and honed like any other.
In fact testability is one of the best parts. You can safely test read-only queries against a prod secondary database to see that it gives reasonable results on real data, and use the repl to explore parts of your query to really get a sense that things are working.
Sometimes it's a hard problem.
That said, I think triggers need very well-maintained dependency charts in the docs/comments to ensure they don't ever go cyclical. Those are bad days.
(But again, events in app logic are in no way shape or form immune to cyclic runaways.)
Re orm, I'm strongly biased towards only using them when they (1) are absolutely necessary or provide some extreme benefit (2) are able to output the raw SQL to be executed on the db (3) the SQL to be executed is understood.
I've seen them used as a substitute for understanding SQL/databases and, personally, think that's a pretty bad idea.
For some reason, folks assume SQL must be written badly since they have only written it badly or seen it written so.
It is absolutely possible and preferable to write maintainable SQL logic into user defined functions, stored procedures, views, materialized views, CTEs, temporary tables, etc. If you're looking at one huge pile of monolithic, untestable SQL, the problem isn't the SQL.
One doesn't write O(n^3) algorithms in C++ and then blame C++ for it being slow. For some reason, folks seem pleased with themselves to do as such with SQL though every day and twice on Sunday.
Got subselects in each of the fifteen outer joins with NULLs all over your schema, and now you're upset performance is horrible and inconsistent? PEBKAC.
A join or a group by is going to be much clearer than writing the code the query plan is going to generate, creating temporary hash maps, doing nested loops, etc.
We are headed back in the other direction, but with some hedge. Code with a very simple & transparent CI/CD experience seems equivalent to some SQL configurable thing in our minds now, but with way more potential upside.
In our latest code-based abstractions, nothing is stopping you from breaking out a SQL connection and running any arbitrary query. In fact, all of the data is still in SQL and it's still authoritative, you just now have access to way more powerful tooling to work with that data. Code+SQL together is the answer.
For non-expert team members, modern C# is turning out to be way more intuitive than SQL the moment you encounter a 3+ table join scenario.
> For non-expert team members, modern C# is turning out to be way more intuitive than SQL the moment you encounter a 3+ table join scenario.
Partly because this is so inefficient that it’s never going to work at scale, but also because of how you’re moving the complexity into the realm of “magic”.
I know it’s very easy and intuitive to use one of C#s ORMs, but with that comes an reliance on things like linq and a model builder, both of which may not work the way you think they do. If your developers think about it at all, and having seen so many C# developers use IEnumerable where they really should have been using IQuerable… well if your developers can’t do relatively basic SQL then I’m not sure I’d trust them with the abstraction either.
I don’t think you’re really, wrong, either, but I think it’s much more a question of building developer tools to help your developers handle good data access than it is about picking a particular tech on the consuming side.
As far as data storage goes, however, I think we’re just beginning to see the move away from the classics because the classics just weren’t build to support how we use data in 2023.
I can’t begin to tell you the nightmare it is to update old school SQL “data wells” to be capable of temporality, good BI access as well as being compliant with the various EU legislative rules, and almost none of that can be done above the database. Well I guess you could, but you’d probably go insane. And that’s just path of it, the other part is just how much data we transact now. It used to be that banks were basically the only organisations to move massive amounts of tiny bits of data, and now we all do it. Like, a single solar plant moves a gazillion points of data into your various systems a minute, where 30 years ago the entire data for that place might’ve been a couple of kb a year, it’s now mbs a day.
When teammates look at my code, and logic is all right there instead of scattered around, and there's a schema file backing it all that makes it clear what all the relations are, they have an easy time making tweaks or adding on. Yes it's very testable too.
This also kinda depends on having a multi-service architecture if your system is large. Separate database for each. That's a good thing anyway.
To me the most infuriating thing is the "SQL query scattered around multiple files" pattern, where a backend engineer will decompose a perfectly fine SQL query into 3 or 4 files, with multiple functions, often with very artificial separations (for example: a function just for the "select ..." part, another for the joins).
All that in the name of having small files, small functions, small lines. You take complexity away from the "micro" parts and embed it into the invisible parts of your program.
I'm not sure about that. SQL was the first language I learned and the language that has always been there throughout the decades, and is also where I make the most mistakes.
The issues I have with SQL are simpler things like `timestamp with time zone` not really storing a time zone and actually being the thing you want for tz-independent timestamps.
I suppose it can be avoided with extreme care, like properly managing memory in C, but a good language will help you out. Much like C, SQL is old so those traps may be excusable in a legacy language, but it is shame that we haven't put much effort into building something more modern. Where is the 'Rust' of SQL?
The closest thing to the 'Rust' of SQL is probably Datalog. Even something like QUEL, which Postgres used for the first decade of its life, would have likely left us in a much better place.
But Oracle won the database wars, so we got left with the junk that came with it – for compatibility initially, and now we can't seem to move past SQL because the typical developer somehow has come to think SQL and relational algebra/calculus are the exact same thing...
The only real alternative I can think of is the map-reduce pattern (see PySpark), but it's for a different use case with some overlap. I have replaced SQL queries with map-reduce once before, and only because the application really didn't make sense to use SQL for.
That's exactly it. Nothing has come anywhere close to seeing the same kind of effort as SQL put into it. SQL is the best application of relational calculus (or close approximation, at least) we have – but that doesn't make it good. A concerted effort to build a better application of relational calculus would undoubtedly yield a major improvement for developers, on the order of how something like Rust has improved over C, and as I said before it is unfortunate that we as an industry are not showing interest in a better language like we do with regards to general purpose computing.
SQL gets the job done, but we can do better.
> The only real alternative I can think of is the map-reduce pattern
The alternative is to build a better language, not jump to a completely different and unrelated model. Indeed, there is a place for map-reduce for certain problems, but they are outside of where you would use the relational model.
Transaction-safety is also sublime for business logic.