Same for foreign keys and check constraints.
Queries are pretty intelligent, also, and mostly just work efficiently. You are right that some queries don't, and you have to know about the internals to make it do what you want. But here's what you are missing in that story: your data lives for a long time across many different applications. If you program everything yourself imperatively, you are bound to either over-engineer or under-engineer a lot of queries. The over-engineered pieces will look like premature optimization, clutter your code and tests, and be a time sink. The under-engineered pieces will come back to haunt you when your data grows and changes. Declarative relational systems actually do a pretty darn good job of handling this problem with a cost-based optimizer. Is it perfect? No, but it's an improvement in an awful lot of cases.
Also, you are ignoring places where databases do a good job with queries, like parallelization. It's easy to say that you'll just write all the queries imperatively yourself, but a lot of people don't want to spend the time to do the harder stuff even if it really matters for performance.
Because 99% of the time you don't notice that it just works. 99% of the time you don't have to bend your query or schema to make it work.
> If you have a query that needs to look at over about 5K rows and you need responses under 100ms you need to know what its doing.
Thats very unspecific, but getting 5000 rows from a table should take a few milliseconds on modern hardware.
There is zero SQL code in our app above a very low level. All high-level code calls through a DB API, and all SQL code is behind that abstraction layer.
That said, each one of these transitions was a “pull up stakes and move” kind of thing. We never tried to support multiple back-ends at once. So on those grounds, I think this is probably a good move for GitLab.
Supporting multiple databases at this point is usually not economically feasible.
That's why I don't regret putting all of our SQL behind that DB layer: to port to the new DBMS, we just had to rewrite parts of that layer, rather than go on a hunt through the whole application for one-off SQL calls, as would probably occur if we didn't enforce this discipline.
It's not just about portability, or even primarily. It's simply good application layering to put the DB code behind a layer. Intermingling SQL code in your application logic is just as bad as intermingling GUI code in your core app logic.
But even that aside, we haven't felt the lack from putting such code a single function call layer away. I suppose it may give us a slight amount of pause when we consider whether it's worth creating another function in that helper library, but that's probably a good thing. If we decide we can get our work done by calling one of the existing functions, then why write yet another DB access function?
Example: if a table is known to have at most 5 rows, and the query that would allow the DB to return just the one we actually want is complicated, and it's almost never called, why not call the existing "SELECT all" one and iterate over the rows? The performance hit of being inefficient will be immeasurable.
> Why did you move databases so often?
That's once every 8 years on average. A lot changes in the computing world over that span.
The first change did coincidentally happen about 8 years after the company was founded, and it was because much better DBMS tech became available to us, within the constraints we had on choosing.
That DBMS lasted us for the next 14 years. By that time, enough had changed in the computing world that it was worth swapping again. The new one runs about twice as fast, takes far less code space, and is simpler to use all around than the prior one.
We'd have to switch again this year in order to maintain the mean of once every 8 years. My sense is that this is more likely a nonlinearly increasing growth curve, so that the next switch will be closer to 20 years than 14, if it happens at all.
What we're discussing isn't the SQL code, it's SQL syntax and features of a particular database over another. JSONB, partial indexing, text search, extensions, etc. in Postgres are not available in MySQL, and not using those features just to have the ability to switch or run on multiple databases is almost never worth the effort.
Simple CRUD apps with an ORM can probably do so, but otherwise most business apps shouldn't avoid using functionality for some "what-if" scenario. Having to switch databases after a decade is just a normal development item if it needs to happen, and usually coincides with heavy rewrites anyway, so I don't see much value in pre-planning for that at the architectural level.
I have also seen instances where the decoupling helped us abstract out differences between different versions of the same database product during upgrades.
If you don't need performance or responsiveness though you can save a lot of effort except in very simple cases. I've seen it shine in quickly built proof of concept applications and in ad-hoc analytics queries.
Whenever a viewer showed up to the website, we’d do a relatively complicated query against this database to figure out which server to direct them to for the channel they wanted to watch. The net result was a system that showed video sooner, with less latency, and more cheaply than any of our competitors were able to achieve. I don’t know what their setup looks like these days, but this basic setup took them from before the Twitch.tv rebrand all the way to their Amazon acquisition.
There was a lot of tuning that we had to do to make this work (like running off of a ramdisk, and having a pool of read-only clones), and it certainly had its hair-tearing-out moments, but in retrospect it was absolutely the right choice. We kept expecting it to fall over, but the plan to shard the database onto multiple servers collected dust for years because there was always a way to get the performance gains we needed through basic tuning.
I don't blame them, and despite supporters, mySQL is constantly the most inconsistent non-standard SQL database of them all. I wished they'd just do a major version shift, dump the historical defaults, and be strongly consistent and compliant by default. Much like Oracle, I have no intention of ever using mySQL unless managing the thing is something I never have to do.
I'd rather use MS-SQL or PostgreSQL than mySQL.
Regarding the historical defaults and consistency, they fixed that 4 years ago in MySQL 5.7. And last year's release of MySQL 8.0 added more modern SQL features (CTEs, window functions, etc).
There's still some non-standard idiosyncrasies, but that's true of every relational DBMS :) And while it's certainly possible that MySQL is arguably still more idiosyncratic than MS-SQL or Postgres, it's not like an order-of-magnitude difference anymore.
COBOL is also English like.
I'm not negative about SQL. I'd rather write queries in SQL than having to learn for the nth time how to write SQL in language X with ORM/module Y. I'm doing it with Django, Rails and Phoenix in the same week since a long time now and it feels a bit pointless. But then there is the deserialization in the data structures of the language. I just wish those libraries would be smarter and know how to handle some hypothetical code like
sql("select * from users").each |user| do
something(user)
end
They're most used in F#, which calls them type providers:
You're still expected ask for what you want specifically.
"I know how to express what I mean in such a way that I can target two very different platforms like JS and the JVM, why isn't there something as powerful for SQL?"
I've been told a million times that SQL is declarative, meaning you say what you want, not how to get it.
I've also been told a million times that I need to write my queries in a specific way, or use a specific data structure, or add hints for the query optimizer. Otherwise it'll pick the wrong "how" for your "what".
Is there any practical difference between a declarative language with a Sufficiently Dumb Compiler, and an imperative language with weak features and an awkward syntax? However I'm forced to write it, we all know I'm really trying to do is get it to LOOP first over these items here, and not those there.
What's the point of being declarative if common tasks require us to bend over backwards to design our schema/queries/indices/hints in exactly the right way, in order for it to be performant on two popular SQL databases (when that's even possible)?
Even if the vaguely-English-like syntax that's completely unlike any other computer language weren't problematic for those reasons, it seems that the lack of abstraction is a complete buzzkill. These two databases require different implementations for "nested groups", but there's no (remotely portable) way to define a CREATED NESTEDGROUP to allow for a similar interface.
Of all the languages I have to use, SQL are the ones I hate most. From decades of writing SQL, I can say GitLab got one thing absolutely right: it's easiest to just treat it as a family of incompatible proprietary languages. It's easier for me to target JS and the JVM from the same program than two different SQL databases from the same queries.