A big omission is dbunit. It’s oldschool but very very powerful. I have used it for many things, but perhaps the must useful is extracting a subset of data out of oracle and inserting it into mysql in order to test components of production systems locally.
My issue with these awesome lists is that a lot of entries are out of date and may not even run anymore. The first entry, AnyMaestro's website has a news section last updated in 2016 (and it looks dated af), so why would I trust that tool with my DB credentials?
This is excellent. I had just very recently thought to build this same thing for php and typescript. Haven't started it yet, but great to see the same thing out there for other languages.
sqlc generates Go code, nothing else, but the web page of sqlc.dev does not mention this anywhere. Of course, this is a .dev site, and .dev is owned by Google, so it is only to be expected, I guess?
It is Go first and foremost, but there are work-in-progress generators for other languages, including Kotlin and (in my opinion, somewhat oddly,) Python.
Unrelated, but I am happy I managed to avoid using any kind of DB in my last few small projects.
User authentication? Hard code my credentials, and SSO for users. I dont want to store user accounts (als gets rid of all this password mail and reset stuff). Storing small amounts of data from users? Pickle it to files. Caching data? Keep it in-memory, retrieve it again when server restarts. Need to keep some data? Pickle it all 10 minutes and on sig-int.
Saving so much time and nerves to not having to handle SQL, relations, foreign keys, docker-compose and all the other things.
While I agree with what you've said, but "skill of SQL" makes me cringe. It sounds like some fundamental skill, like running or swimming. But it's more like "navigating your local govt bureucracy skill" – you generally better off when you have it, but there is nothing to be proud about.
SQL as a query language is truly horrible once you get past simple data queries. Most people never question if SQL is even good for its job, or if there is an alternative to the SQL, and blindly work on their SQL skills. It's still better than working on the "MongoDB skills", though. But in 2022 the DB interfaces exploration space is expanding, and there are some good alternatives to SQL to learn too.
SQL as a query language is truly horrible once you get past simple data queries.
Oh, really?
I've personally written SQL queries for complex purposes of lengths up to around 1000 lines. I've also had to take what had been SQL queries and rewrite them in a variety of other ways outside of SQL. And vice versa.
SQL feels horribly verbose, right until you compare it with equivalent procedural code. I'm confident in my ability to match or beat the SQL optimizer, I know that doing so will take me significant extra work. And I've seen enough code to know that most developers are unable to do so.
The only tools that I've seen that come close to SQL are themselves thin wrappers around SQL. And I've yet to see them not lose some important capability that SQL has. For example analytic queries (aka window functions) are not often available in other tools.
> I've personally written SQL queries for complex purposes of lengths up to around 1000 lines.
How does it refute the argument about SQL being horrible for data query?
Being proficient in some language or tool doesn't necesarilly mean that thing is not horrible. There are a lot of people proficient in JavaScript, you know.
To expand a bit on my definition of horrible, I see a tool as a good choice for its job when two criteria match:
1) tool was deliberatly designed for the job (and not used as an afterthought, as with html/css/js, for example)
2) it matches well the mental model of human thinking in that problem domain.
For example, when I need to fetch some nested data from db, I think about my request in terms of relations between objects (like "give me the list of people who are the actors for those movies nominated for Oscars and who are started directors careers recently") instead of JOINS and GROUPS. The fact that I have to reshape my way to think (aka boost my SQL skills) is a testimony to how poorly it scores in my number 2 criteria. Basically whenever you need to reshape your thinking to do some simple stuff with a tool, the tool is horrible. Like describe relatively simple spatial abstract knowledge in terms of class hierarchies (and start thinking in classes, which is not how human brain organizes knowledge). Or build modern UX interfaces thinking in typesetting primitives like "<p>" or "<img 'src='". A lot of popular things are horrible in software engineering unfortunately.
I think you missed the line, "SQL feels horribly verbose, right until you compare it with equivalent procedural code."
Having written SQL of varying complexity, and having rewritten it both directions between SQL and procedural, I'm in a good position to compare. And SQL is very good for its purpose. This is why nothing has replaced it.
I don't buy this logic. It's like saying Facebook is a good social network and this is why nothing has replaced it.
SQL obviously is an industry standard, the first thing you learn at IT bootcamp or CS classes, the first thing you've being asked at a job as a junior, etc. Decades of experience made most popular SQL engines stupidly effective. Yet, SQL as a query language was designed long time ago, when the data was orders of magnitude smaller and assumptions about data queries needs were way different from today.
Let me put it this way – if data query language was designed today from scratch, with all the knowledge and experience we have now - it'll look very different from SQL. I mean, it's just obvious that with more time and real-world practice we get more understanding of how to shape our tools. And then they shape us, of course.
SQL would have looked different back in the 1970s if it had been designed from scratch. In fact it was. One example was QUEL, the query language for Ingres, which was an influential RDBMS developed at UC Berkeley by Stonebraker and Wang. [0, 1] So far none of the many attempts to develop a different language has succeeded.
IMHO the only way SQL will be replaced is if some variant becomes dominant because of a strong implementation that extends the language in useful ways, kind of how Latin turned in to French in the Middle Ages. Open source databases like MySQL and ClickHouse that ignore SQL standards BS and just put in useful features have shown the way.
Query languages existed long before SQL was invented in the 1970s. For example IMS, still widely used for financial transactions was created in the 1960s. Even if you limit yourself to relational databases, there was long a competitor named QUEL. (Postgres originally used a dialect of QUEL as its query language.) Query languages continue to regularly be introduced. Practically every "NoSQL" system out there has one. Most object-relational mappers at some point introduce a query language that they think is better than SQL.
I long ago lost count of how many query languages I've had to do something with for environments as different as MongoDB to Redis to custom languages for doing MapReduce on distributed systems. Some of those last indefinitely - both MongoDB and Redis are doing well. Other tools eventually sprout SQL layers like Postgres and Google BigQuery did. And SQL systems often sprout alternate layers as well, for example multiple projects exist to allow your SQL database to turn into a GraphQL one instead.
The influence of SQL is not an artifact of history. It is due to the fact that, in a dynamic ecosystem filled with competition, nothing has managed to dethrone SQL since it successfully swam to the top and became dominant back in the 1980s. And ever since then, the end of SQL has been predicted. The next big thing is always around the corner. Maybe https://www.codemag.com/article/0305091/XQuery-the-Query-Lan... is right and XQuery will finally slay the SQL dragon. Mind you I read articles like that 10 years ago and SQL is still standard. I also read articles 20 years ago like that with the same result. So it is probably a good idea to hedge your bets and learn SQL just in case history repeats itself.
Thanks for the mentions of IMS and QUEL – I didn't know about them.
I guess the main point I disagree with you here is about "dynamic ecosystem filled with competition". I totally agree that SQL won the battle of dominance, and that's the main reason why SQL stays popular and not so much competitors even emerge (sorry, MongoDB query language is a joke).
Take QWERTY as an example. It's been proven countless times that it is suboptimal layout. It was designed with suboptimal trade-offs and with different goal in mind (minimize jamming of a typewriter). But it got it success and become dominant because of commercial success of typewriters with this keyboard layout. The rest is a pure network effect.
So when I read your comments on SQL I hear something like "QWERTY is objectively the best layout, because nobody managed to dethrone it in a highly competitive settings, and because I type 800+ chars per minute with it". So that's true that SQL is dominant, but is it objectively the best we can do? I don't think so.
Also, what's your experience with EdgeQL? I know it's a layer on top of existing SQL, but I had incredibly positive experience with it (after 20+ years with SQL). And I can totally see it as a standard for data query by itself. I would love to have something serverless like EdgeQLIte, in fact.
First of all you might want to refresh your memory on the QWERTY layout. As it happens, in grad school a group of us bought into the Dvorak myth, and spent several months retraining on Dvorak. We didn't see great results. And then by chance one of us ran across https://reason.com/1996/06/01/typing-errors/ and we realized that, based on research we had not known existed, there was in fact every reason to believe that QWERTY is superior in practice to Dvorak.
And this brings us to the subject of network effects. It is easy to see, and experience, that network effects are real. And certainly they contribute to why existing networks dominate, and alternate ones struggle. However, on multiple lines of evidence, network effects are far smaller than people suppose. In fact they scale as O(n log(n)), see http://www.dtc.umn.edu/~odlyzko/doc/metcalfe.pdf for multiple lines of evidence supporting that estimate. This is a big barrier to getting modest adoption. But if a better technology gets there, it isn't a barrier to dethroning the leader. And so, despite network effects clearly favoring Perl at the dawn of the web, Perl got overtaken by PHP got overtaken by node.js got overtaken by Python. Lock-in to popularity is clearly less of a factor than it may seem. (And none of those technologies actually went away. It is likely that more people program in Perl today than in 1998.)
And there are no shortage of potential competitors out there. The number of query tools that I have had to learn and use is a testament to that. And, just in case I needed more evidence, you bring up another. As it happens, I have not used EdgeQL. After reading a bit about it, I'm not sure why I'd want to. But if it gains any popularity, then some day I'm sure I'll have to.
But looking at it, I'm reminded of a fact about programming languages. The perfect is the enemy of the good. Different people have different notions of what perfection looks like. And therefore attempts to create a perfect system inevitably splinter into a million fragments, none of which can gather the critical mass to overcome network effects. Look at the history of Lisp to see this in operation.
And yet the best ideas do tend to emerge. Back when I first learned JavaScript, closures were a weird feature that books didn't even want to try to explain because programmers wouldn't understand them. (JavaScript has them because it was created by a Lisp hacker.) Today they are considered standard, and even languages like Java have been forced to add them.
I've done (and do) substantial work in SQL as well. Currently almost the entirety of our business logic is implemented in it, both to avoid the delay of round-tripping large data sets and for security reasons.
To be clear, this isn't CRUD code. There's a lot of custom ML bits, vector algebra, etc., running in Aurora Postgres where C and Python are off limits. Without PL/pgSQL many of the functions would be impractical.
The optimizer isn't the main source of headaches, but it doesn't help. I've caught it doing things it shouldn't be doing -- Aurora's fault, not Postgres' -- and having so much complexity locked away behind a magical "feature" is endlessly frustrating. Not everything is visible in a query plan.
When you're trying to run general purpose code inside of the database, you're beyond what it is good for. I've written a compiler in PL/pgSQL (for a special purpose language for time series data) and agree that there is a limit to what you want to do there.
SQL is a query tool. Not a general purpose programming platform. But it is good for surprisingly complex queries on surprisingly large and complex data sets.
We're really just debating the meaning of "simple" from:
> SQL as a query language is truly horrible once you get past simple data queries.
I think things start to get dicey as soon as you're past basic SELECT, FROM, JOIN, WHERE [1], ORDER BY. CTEs, subqueries, cross joins, case expressions, etc. certainly aren't advanced, but they do surprising things at times, and it can be hard to tell what's going on under the hood with the optimizer. I'd prefer to work with a full-fledged programming language or the underlying primitives the optimizer translates SQL into, but SQL isn't a problem for me personally. The problem is that I've had teammates be utterly defeated by it.
1. And even then you can get in trouble, e.g. not knowing that OR short-circuiting isn't part of the standard, and Postgres for example doesn't do it.
As someone who only uses SQL for analytical/OLAP purposes ... I'm cool with people avoiding object relation impedance mismatch if it helps them with app dev.
If your need is for CRUD, then using an ORM makes sense. In the case of Python that's easily achieved by using Django. And the skill required in how to use it is directly transferrable to how more complex software is actually written.
If a person is struggling with the basics, "you must learn all the things at once" isn't great advice. Reduce scope.
Also foreign keys are a mixed bag. See https://stackoverflow.com/a/83393/585411 for some of the pluses and minuses. I've seen very experienced people in significant projects go both was on the value of foreign keys. And a lot of the complications are more of a barrier for people who are just learning.
Care to offer any arguments beyond it being your opinion?
For the record, I first encountered arguments against foreign keys around 20 years ago from a VERY experienced Oracle DBA. He knew what he was talking about, and had actually written some of the courses that Oracle used to certify DBAs. He pointed out that our transactional database for our high volume website was already pushing the limits of what our hardware could do. Maintaining unnecessary indexes or integrity constraints was going to take our database down.
Since then I've kept track. And found that the benefits versus downsides are more finely balanced than I would have guessed.
Comments like yours makes me happy. I once gave a lightning talk [1] in the same vein describing project that would be typical REST/API/Cloud/App setup and ended up being just a mobile app with not servers whatsoever. Just by carefully inspecting your data, unique requirements and asymmetries, tech stack can be drastically changed and unsual trade-offs can be taken.
In this case, bragging about the steps taken to produce a strictly worse solution because the dev didn't want to learn what should be a basic dev skill.
That said, criticizing all Python developers with this brush was very unfair.
You see, before you overwrite the actual file with the new content, you should wrote the new content in a separate temporary file first. That way, when your app detect corrupted files on startup due to power loss, you can program your app to look for the temporary files and write them to the main files if they pass your data validation.
... and before long, you'll reinvent database servers but shittier.
https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu...