Not every technology needs to be like JavaScript ecosystem. It's blessed to have established, log-lived technologies like SQL, UNIX and REST in this fast-moving industry.
“Any application that can be written in JavaScript, will eventually be written in JavaScript.” they are coming for you buddy. you cannot stop it. you will not stop it.
to be clear, I'm not advocating for getting *rid* of long-lived tech! that's great and needs to stay, I'm just interested in trying something new in an area that seems to have very little innovation!
@@BarnabyKeenei am not bashing you for wanting innovation, but why care about dbs of all things? Sql dbs are fundamental, solid and extremely boring structures since thats what they need to be. Feels like wanting to redo the plumbjng in an old house that was already working without any issues, it creates more problems than it solves imo
@@fullmontis i think there is plenty of room for improvement in DBs. change for the sake of change is bad, but i could see DBs improving as part of a bigger change
I'm not a developer, but work in analysis and use SQL daily. It is so useful that most databases use SQL, because otherwise I would have to learn so many languages. Even though every SQL dialect is a little bit different its close enough that hopping from MSFT SQL server, to Oracle, to Big Query to Postgresql isn't an issue. Would be a real pain if they all worked fundamentally different.
there are indeed differences - sometimes very significant ones - but it's great that there will be like 80% or 90% common turf. Getting into the world of PL/SQL or PSQL then there's all manner of divergences
@@magfal same can be said about Oracle. You can be a master ninja with oracle's hints and never get the same performance from any other DB, making some queries fundamentally impossible
@@NJ-wb1cz I've been challenged to solve quite a few complex problems in postgres by Oracle DBAs to see if performance can match. I've wound up with better performance each time so far, even when one colleague limited me to the ancient 9.6 version.
Its funny to me how many people think that if a piece of technology does not constantly evolve it means its automatically worse than ecosystem that changes all the time. What if the technology that remained is simply good? If the goal has been achieved there is no need for constant changes. Not everything needs to be javascript where new "better" ways to do thing are invented (or reinvented) every few months.
yep. i think it's okay to reinvent the wheel every so often, but i hate it when so many other things get worse in the new way of doing things (regressions). i think it's partially because of the way humans developed software (fragmentation & NIH)
I think the argument was not that SQL is bad but that we don't even try to see if we can find something else that's good or at least decent. A bit like haskel. No one says "C-based languages are nice, but Haskell is way better for production", but Haskell still exists. What they want is not for something to replace SQL, is for the option to exist, because that'd mean people actually tried and SQL is still the better option.
What makes SQL so prevalent is it's closeness to the mathematical branch that originated it. Relational algebra was first developed through mathematical tools, then converted into routines. Not the other way around. In maths when something is proven it's permanent, many of the problems relational algebra solved were demostrated to be optimal, what exactly is there improve? Calculus was invented 400 years ago, why isnt him bitching about it too?
@@Michallote By that logic, Raku is perfect. Languages are meant to be opiniated abstractions. That guy doesn't want to replace SQL, he wants there to be options.
The thing with SQL is based on the relational model for databases by the legend Edgar F. Codd. TLDR it has a tons of math to back it up, not just hype.
Codd himself would disagree with that statement, and indeed does vociferously in "The Relational Model for Database Management" Version 2. (ISBN 978-0-201-14192-4) In terms of the relational model, every query would, at the very least, need to effectively be qualified with "UNIQUE". Also, the original model had two types of NULL (missing but applicable and missing but inapplicable) - which relate to how aggregates work. There are many, many more differences between SQL and Codd's idea of a Relational Language. I was hoping this was the angle the OP would take to disparage SQL, but was somewhat disappointed by the actual article. CJ Date's 1983 essay "A Critique of the SQL Database Language" also goes through many of the objections, and is possibly more accessible.
@@robgrainger5314 being based on doesn't mean identity. The theoretical ideas almost always get organically adapted to reality during implementation. If anyone still longed for two nulls and thought it was a good idea in the 80s and 90s, I think Javascript pretty much wiped all of them since then
Not gonna lie, bright-eyed engineers that complain about tech choice and get mad when their preferred quirky tech isn't given the attention they think it deserves, are so difficult to work with.
I was that guy in college. Hated my db modules (oracle sql, in fairness the dinosaur prof did a bad job too) Thought nosql was the future and preached it. Once i got an internship and worked with production sql, i realised how much better sql is for a lot of use cases and that new and shiny isnt always better. Then i aced another db administration focused module which used postgres because i had a much better understanding of the how's and the why's
They should probably adjust or move on but sometimes industry standards are just groupthink. React blows and is over used, even a word press site would be more appropriate at times.
I see your point, however the opposite of that is crankly old engineers doing anything to prevent fresh perspectives changing the status-quo (coming from someone who isn't bright eyed anymore but is yet to be old and cranky, no offense to anyone :P)
This article screams to me the engineer who comes to every meeting like "GUYS DID YOU SEE THIS NEW SHINY THING THAT WE SHOULD IMPLEMENT!" Worked with a couple of those guys and it always make for unsustainable production code. SQL is just about as straightforward as database interface as you can get. You wanna create some new interface that can do everything SQL can do, go ahead.
Ten minutes in and I am still shouting at the screen that there are a whole class on databases literally called NoSQL, and losing my mind that MongoDB was even casually mentioned without that coming up
In 1998 (I believe - may be off by a year), at WWDC, there was a Q&A session. A woman asked Steve Jobs what happened to "think different" because he announced that they'd be adopting some standard thing instead of continuing use of an obscure proprietary thing (forget what the thing was). The woman named a few things the Apple thing did better than the standard thing. And Steve Jobs replied "There's probably plenty of things that does better and some things does better. But the point of Think Different was never to be different for the sake of it. The point is to be better. You can't be better by being the same, but if the standard thing is the best you shouldn't cling to being different for the sake of it. Keep to the goal; Being better. And when you're not the best, it's probably better to be compatible with what's already there and improve it from there" (very paraphrased. Been many years since I heard it from his mouth)
Also "Bad artists copy, good artists steal" etc. I think the main reason we think highly of Steve Jobs is because he died before we really knew him. Imagine if Musk died a few years ago - we would remember him as one of the greatest humans who ever lived, despite him being the same person he is today. Steve Jobs and Musk were good managers and had a vicious perfectionistic streak that together with luck allowed them to organize people around themselves to help others create something great, but they're still managers not creators. These quotes and phrases are quite meaningless because they don't relate to what the person was actually doing - human management and human interaction and using humans as resources to satisfy their inner imbalances. It's the sort of live love laugh nonsense your manager tells you while you're creating the product for them on your own, praying that they don't get in your way, while they're telling everyone how this is their project and how hard they are working on it
I mean… SQL is essentially a superset of a field of math… the only way to compete with SQL is to make an entire field of math to compete with relational algebra and then make a query language to interface with data using that new math IMO where the competition lies is in making a DBMS that better optimizes and runs existing SQL queries
I just started studying about rdbms and all I learnt for 3 days is maths. Relational Algebra and function dependency. I did also learn SQL but I mostly skipped since i already knew SQL. But it was a pleasant experience learning the maths behind it.
SQL is based on mathematics. There is no other/better way to do it for a relational database. It is a standard. Developers can use the same language on every relational database product. Moving applications from one db vendor to another doesn’t require a rewrite of all the code.
@@Akronymus_ I don’t understand. The “from” statement comes after the “select col1, col2, …”. Or are you making a joke? If so you should use some kind of emoji.
Imho Datalog (either the original or the datomic variant with the lispy syntax) is the best alternative syntax for SQL that manages to match it in expressiveness and is used by the relational database research community in papers whenever they publish on new concepts like incremental materialized views. Graph databases basically offer a weakened version of datalog with query languages like Cypher. Datalog is nicer than SQL for CRUD operations, for queries that don't use negation, and for recursive queries. It is way less nice than the postgresql dialect when you actually need to optimize your queries and need to reason about the memory layout of your tables/indexes or the query plan that actually gets run. In many ways, datalog is to SQL what lisp is to the C family.
Yes! Datalog is super nice and expressive, though I do find the syntax a little intimidating, at least it's *actually* structured, unlike "S"QL which is based on the most unstructured language possible: English!
The thing is, we all agree JSON has some weak points. So what they'd like is just to have another API that doesn't have SQL's pain points, but maybe has some different pain points. It doesn't really matter if it's worse than SQL, it just needs to show people tried and SQL is still the better alternative. But rn you don't really have anything to compare SQL to. You have SQL, PostgreSQL which is another flavor of SQL, SQLitewhich is another flavor of SQL, Cassandra which pretends to be noSQL, but CQL is really a flavor of SQL, etc...
@@Exilum I wish JS/HTML was as well designed as SQL is, they don't make things like they did anymore. SQL , a 4th generation language that really did what it promised. Remember when there was this idea that you wouldn't need to know the implementation of HTML/CSS/JS to create pages, the computer would write it for you. (well, now we have GPT to copy-paste it for you from stackoverflow or something, not the same thing)
29:02 The reason for that is actually a legal one. SQLite wants to keep the project in the public domain which means that they need to do a lot of legal work too besides the implementation and maintenance work. But here's the thing: Not everybody is able to put their stuff into the public domain. Heck, in some countries it's literally ONLY possible by dying and then waiting for it to run out which isn't really a nice way.
SQL is not a string literal problem. Look at the bytes on the wire, they are fully typed. I.e. its a binary API. Many client libraries exist to move datatypes 1:1, with syntax to optimize for query plans.
@@doyouwantsli9680 Yup most mongodb apps are basically used exactly like a SQL DB would be used in my experience. I've seen very few cases where not having a standardized schema is a benefit.
But no one uses NoSQL because they want a different query language. They use it because they want a model that supports extremely fast, high volume reads on large tables, and they don't require transactional locking for concurrent writes.
Yeah the OP is mixing two totally different things. SQL is a standard but DB has their own implemantation that varies in Syntax but he is pointing problems for ORM's or how language interacts with SQL. why SQL should care about that if you go to an SQL tool you got all those features he is asking for.
I lived through dBase, btrieve, and even a few cobol banking databases. SQL was successful because of its compatibility and high level. A huge improvement over when the APIs was "give me the record index for this key". All in one process with so many server round trips.
For years some software gave you the option to select between two SQL DBs. That’s the proposition of using a standard format of communication. With not too much of a rewrite you could use either MSSQL or Postgres for example.
The problem with this article is he never explains why SQL is holding back design, other than some vague sweeping complaints about the spec. My only complaint about SQL is that a KV database is objectively simpiler, faster and easier to use, but you only ever find ACID compliant SQLite, or non-ACID json or ad-hoc configuration files, or some custom serialization format, instead of ACID complaint Redis or lmdb store. lmdb is just as reliable as SQLite, and faster, but no one uses it, everytime I play Terraria or Factorio I just wish it didn't hitch every time it went to save. You can prevent item duplication bugs with transactions and save at 120fps with restore points and all sorts of crazy stuff. SQL is overkill if you just want ACID compliant data storage.
Working in a database first company where more than half of our business logic is written in scalar functions and stored procedures, I have to say I understand his sentiment. If there would be an actual programming language that would interact directly with a database (and not just an orm that translates to sql), it would be a very interesting new approach. It would give all the benefits of (modern) programming languages without sacrificing performance and without it becoming impossible to debug/maintain because you can't understand why the orm converts it the wrong way.
I have been in several discuss about moving newer application to a non-SQL solution. One of the biggest reason to stay with SQL is the countless non software engineers (Business Analyst, Report Writers, etc) that are just not going to be cheaply (or at all) retrainable to do their work in a new non SQL environment.
My department has many consultants next to software development and we all (broadly) deal with data management in business intelligence tools. In my experience you are spot on and its the same reason Excel is still a favorite tool of choice for managing and analyzing data (to the horror of everyone interested in BI). Unless you are a huge corporation, databases need to be relatively accessible to people outside of IT and you will never come up with a system that can compete with the millions of hours of experience people have painstakingly gathered in SQL. Same goes for Excel. People working in these areas suffer from a small aneurysm if you change the UI a little, imagine what happens if you change how the formulas work they have adopted over the course of a decade.
This rant from the article's author is very strange. SQL is a declarative language and as such describe what information and how the user wants the information to be presented (always in grid form). It is implementation independent (for the most part). Make your own. There are other database noSQL paradigm: objects or documents based.
The other thing I'm not sure this guy understands about SQL (and databases in general) is that it's *imperative* to have longevity. Records stored in databases may be referred back to decades in the future. If you have a fast-moving tool which increases the amount of migration work you have to perform, the chance of data loss increases too. Databases are not microservices that can be hot-swapped on a whim. There is little scope for trial and error. If you want to play that game, you need to rethink and break apart the entire database architecture
The text aspect is a feature. You write your operation and send it to the db to compile to prepared statements once. You get new features and optimizations without relying on a binary protocol. Queries then use the prepared statements to communicate with the db in a terse binary format. If you don’t use prepared statements your integration layer sucks or you should switch databases.
I was disappointed by the original article here, but probably as I was hoping for a better critique of SQL on a theoretical basis. Similar to CJ Date's 1983 "A Critique of the SQL Database Language" or discussed in Codd's (the original designer of Relational Databases) "The Relational Model for Database Management: Version 2" from 1990.
While yes, both languages written for the JVM and for LLVM will be able to run on most systems, and both first compile down to an intermediate language, their back ends are very difficult. JVM takes the JVM-byte-code and then interprets it, while LLVM instead takes the LLVM-IR and compiles it down to machine for the system you're compiling to.
And so does the CLR, which consumes IL and compiles that into machine code, and which the author totally forgot to mention. Yes, I am talking about DotNet. But on the other hand, since LLVM is mostly for low level languages, that one should be compared to GCC as well, the most important compiler platform on Linux, but compiles for more platforms as well.
The argument against SQL fails for the same reason arguments against JavaScript fail. Everyone knows JS is bad but who can really kill it at this point? Are we not doomed to live in a world where JS is both the most used language and the most broken at the same time?
Saying SQL needs to be diverse is like saying we should have browsers that throw out the back button and the address bar. Or why do we use for loops in all programming languages? Why not invent a new loop type?
Honestly I get the point, and I somewhat agree there needs to be people trying new things, but I also get the risk aversion and the difficulty of changing something everyone considers so fundamental. You just have to look at how long it's taking for ARM processors to get as popular as they are now, or how long it could take for RISC-V to even get any sort of wide support. Things you consider fundamental are hard to change or propose alternatives to. But the fact SQL isn't actually all that fundamental should've seen at least some level of innovation over so many years. What I like about the programming language landscape is that while there are a bunch of languages built over C, there are also some that aren't. And that diversity is why programming has seen the advances it has. Rust probably wouldn't have existed if C-based languages was the only thing around forever.
Lackluster article. The author provided no actual practical examples of what's wrong with SQL. Sure, acquiring the standard is costly (if you're not *resourceful* in other ways) but SQL itself is by no means as crufty as some of the other things that we rely on. It is one of the few things from the 20th century that I actually have no issues with whatsoever. Structure your data in 3NF and you will be fine. Use one-to-many, many-to-one and many-to-many relationships only, if you can (and yes you really can if you try hard enough). It's really not rocket science or as footgun-prone as something like C. Heck, modern tools / libraries do even provide type-safe abstractions and can even explain your goofy ass query to you by drawing a graph. That being said, I'd be curious to see what a hypothetical query language that could supersede SQL would look like.
I mean there was MDX with cubes, MQuery with PowerBI, DAX, and now GraphQL. You could say many are for reporting but they are essentially query languages meant to query data with joins.
The biggest thing holding back sql is the lack of good tooling for it (I say this as someone trying to learn sql better). We really just need a good sql lsp that communicates with your database to understand the schema
@@apoggione8you can though, but you actually need to be connected to the DB. Most dB client I use (Beekeeper, DBeaver, BigQuery, hell IntelliJ DB Client) has intellisense on table and column name. Though you're right about needing to go back and forth cursor wise because you need to declare from first before the LSP can suggest columns
Here are some more alternatives to SQL (as a database language) that I haven't seen here - for the perspective: Tutorial D - from C.J. Date's database book; it is not an industrial language, but IMO could be made as such (like, Pascal was also a tutorial language at the beginning 😜) Prolog - that's logical language, but its queries-predicates are "relational"; just need to make it more practical from the database point of view (like, field selection and all such things) I'm personally OK with SQL, although sometimes it's getting a bit wordy when you need to do some complicated joins - would be great if we could have some expressive/recursive stuff 🙃
I agree. And they talk about the JVM and everything, but LOL they forget the entire CLR from DotNet, which is does groundbreaking things and is still more performant than the Java platform due to all the low level things and the ahead of time compilation thing. The problem is that people do not like the large corporation that created it, and sometimes for good reasons. But the dotnet team and the foundation are for an important part part a distinctive thing and I think they do seriously amazing work.
@@OnFireByte C# has been adding more and more FP-like features with each new version. Once they add in discriminated unions as well as some sort of way to make variables truly immutable I think it will be "FP enough" for a lot of FP fans
No one tries to change SQL because SQL is THE right way of interacting with data. It wasn't improvised by a techbro in an ephemeral startup, it was thoughtfully designed by a mathematician at IBM labs based on a solid relational algebra foundation.
I didn't hear a great reason for trying to make new query languages. First we had stored procedures, which address the argument that it should be an API managed at the server level, but that turned out to slow down development time and increase extra maintenance tasks. Then we had ORMs , which are pretty good for certain use cases, but they often generate extremely slow queries outside of making single row CRUD operations.
8:54 there are sooo many improvements that can be made to SQL even without inventing something by totally new. First, put FROM statements first. How can you get code completion in your SELECT clauses without knowing which tables the data will come from? Second, name table relationships. Instead of joining, you use the relationship name. There are so many query patterns that could be abstracted, though I guess that’s what stored procedures are for. The other thing that gets me is that all RDBMs are also inherently object/graph databases. So in theory, they could all have object/qraph QL applied. The other other thing, is that SQL or an improved derivative language could be used by clients instead of GraphQL to query APIs. The whole data persistence part could be abstracted so that you get secure decoupling between the domain data and the persisted data.
It does feel a little backwards using an API/ORM that needs to translate requests into a string literal language and not vice versa. Wouldn't it be nicer if the RDBMS provided API interfaces, and the ecosystem provided the tooling for interfacing with those via whatever method they wanted? (SQL, visual editors, raw dogging it, etc)?
I think SQL itself can defo be redone. Why you select the columns before you even get from a table is beyond me. LINQ style is far more friendly for that. I'd love to see something like LLVM, but for databases, i.e. where the query is a compiled piece, and then the language you use compiles to efficient byte code, and then you can have competing query languages and/or compilers for creating a optimal queries
As an ETL dev in a Microsoft shop I love LINQ, but defining "from" first still throws me sometimes, even 8 years into using it. I suppose there really is a bit of Stockholm Syndrome in SQL, as it didn't occur to me until I saw your comment that I just default to writing "select * from", defining the table and any joins, then going back to the select to define the columns. Been writing queries this way in SSMS for so many years now that it's just muscle memory at this point.
Why need diversity when even a Business Analyst, a Sales Manager or even a Marketing Specialist can easily learn and write Squeal to get the information they need? Squeal is not really invented for programmers. FYI, I'm DBA/Data engineer and I grant read access to those people.
I don't know why but I strongly prefer pandas/pyspark df apis over sql. Sql abstractions are great for me but i completely hate pure sql syntax. For some reason FROM foo SELECT c1, c2 looks terrible in my mind but foo.select(c1,c2) looks perfectly fine.
I think there are two reasons why SQL sticks around. First, the way we use databases hasn't really changed, so SQL is still perfectly adequate for its use case. Second, you usually aren't pumping out hundreds or thousands of lines of SQL every day like you are with other languages. If I'm tasked with fetching some data from a database and presenting it on a web page then the SQL is a tiny fraction of the code I need to write, so it's not really the part of my workflow that I'm most eager to optimize.
It just sounds like they want a "good" abstraction from SQL I don't think that SQL needs a replacement since it is easy to understand and use as long as you understand Set Theory - and SQL itself isn't really comparable to a programming language as it pretty much only describes what the result should look like, not how to get there (HTML/CSS is more similar to SQL in that regard than a "real" programming language) although to be fair, databases extend SQL in a way to give control over how it is done too (with transaction controls, lock controls, database settings etc.)
In a way, SQL *is* the abstraction, I rather want access to the underlying layer to write a *different* abstraction. The problem is, that layer is often very tightly coupled to the actual parsing of SQL itself. I almost managed it with Postgres' but writing C makes my small brain hurt :(
Or, now hear me out, they want something theu don't need an abstraction layer on. How is it nobody sees the m benefit of a usable system without 10 layers of obfuscation?
If you're interacting directly with the database, sure, SQL is fine. But it's horrid API design because it's all text and someone can easily bobby tables your production database.
@@__Brandon__ you could argue that query plans are the assembly of databases, and SQL is the... javascript!? but maybe what I want is a common query plan format that's akin to LLVM IR!
The minor difference between different version of SQL already causes me enough headaches. I don't need a new version of something that is actively supported and already does what I want it to do. If anything, SQL could be more unified if not for proprietary nonsense.
SQL is difficult to work with because it comes from the days of yore when everything had its own text representations. (This is why piping a bunch of unix commands together is essentially custom parsing at each layer.) This makes it harder for machines to build it dynamically. This is the reason ORM's suck, etc. For an example of a relational db that demonstrates much smoller syntax, have a look at Datomic. It's syntax is based on Datalog, and the queries are insanely powerful.
I'm not a software enginer by a long stretch, but I feel like the only reason why you'd want to send bytes instead of text for querying your DB server is for performance. The rest can all be done by changing your library/way you construct those queries.
There is another database query language that was invented by one of the early database academics (Date) called D, implemented by some relational database attempts that have been made (and subsequently abandoned..) over the last few decades. The language though is not SQL-compatible, primarily because it is designed to operate on true relational databases. Which leads to my problem with SQL databases. The biggest problem I have with modern "relational" databases is that they are not relational. A relation is a set of tuples. A relational database is a set of relations. A relational database management system operates on relations and returns relations, and allows relations and tuples to be parameters. The relational model itself is an abstraction on the underlying implementation of the database. In theory, you could build a relational database on top of a key-value store of some kind, a flat file system, you name it. The relational model part just provides a useful abstraction (i.e. one that can be reasoned about, and represent relationships between data in a way that provides for integrity) for interaction with that underlying system. All these things have strict meanings and loose meanings. SQL databases all take the 'loose' meaning of these. Back to the meaning of 'relational database' - a "set" implies that every item in it is unique. You could never have a duplicate row in a relation, but you definitely could in a SQL table, or the result of a SQL query (which is also not a relation). Also, in the relational model there is no concept of a 'null' value, and null results in three-valued logic which makes it much more trying to reason (or construct formal mathmatical statements) about such a system. However, the only way to construct a modern database that would usually use NULLable columns would be to apply a level of normalization that would be tedious given current tools. Basically, for any column of 'optional' data, that needs to be coming from a separate relation that may or may not have data for any given key on the relation it's related to, i.e. a foreign key constraint. This might also make it more difficult for the user to reason about their data because they have to hold so many things in their head at once. There's no such thing in that world as an 'outer join' because it makes no sense to combine two relations that have different tuple definitions. We need some kind of bridge that can be built upon the relational model, and reasoned about in ways that reflect peoples understanding of their own data. SQL is genius in the sense that it's a very high level, declarative language that abstracts the fundamental database operations (identify data boundaries, extract the fields needed to compute relationships, match keys, optimize queries, etc) and uses some concepts from the relational model to allow people to structure their databases in relational-esque ways. But, SQL is not relational, and the things it omits/allows that the relational model doesn't hobbles its ultimate performance and soundness.
I mean SQL is just the query language. It sounds like you have an issue with the data structures themselves. If the data isn't normalized to whatever extent you need, it's not the fault of the query language, it's the data architecture. It'd be bad no matter what query language you are using. While it is de facto true (I think. I could be wrong) that any used RDBMS I can think of uses SQL, it's not a requirement. I guess what you're wanting is RDBMSs to require a minimum of first normal form on all tables? Or is it that the D query language absolutely won't work for things that aren't relations?
It's not just about ability to reason about the data. It's also about performance, efficiency of the operations, and fulfilling ACID requirements. I'd like to see anyone implement a performant relational DB on top of a tuple store.
I would say trying to remove SQL and replace it, would be like trying to replace ASCII. Not in the sense of extending it like with UTF-8, but actually replacing it in a manner which is fundametnally incompatible. For example, one "great idea" would be to put the numbers from 0 to 9, followed directly by capital letters for easy representation of numbers in base 10 or hex or even base 36.
Postgres's SQL syntax and out of the box set of functions is ergonimically 1 million times better than any ORM or alterate query interface that I've tried.
While I can't say for postgres's SQL specifically, I can easily get behind the rest. When I tried to learn an ORM, and saw how requests were formed, my thought was : "How is it more desirable than writing raw SQL ?" Modifying raw SQL is just far easier and readable than changing its object mapped counterpart
@@nlight8769 I've reached the point where I will build everything that's a backend project in postgrest in the proof of concept stage of a project. Skip the middle man. A full shipping stable, fast and secure backend that deploys anywhere easily and for most things you could use rest for scales to 10K concurrent users. All inside a database where i can either write in SQL or rust (or a bunch of languages i won't use voluntarily) Most things will have all it's endpoints done when the schema is done and an hour or two of development time has been added.
Only thing that I have to complain about sql is that there is no good LSP for sql, and the standard spec of sql also prevents it to be good. Like in sqlc you have to write schema and query in separate files. Since SQL didn’t have typical ”importing” schema definition, it’s pain in the ass to write table name/field without weird typo.
No offense, but I think it needs to be rewritten when you're sober. Your this years article from April reads far better, though I would argue it doesn't go far enough in better implementation of alternative query data structures.
@@Nors2Ka appreciate the blunt and honest feedback, it's the best kind of feedback! I am definitely going to rewrite this. It was half-assed at best and takes WAY too long to get the the actual point. Now that I've actually designed and partially built this SQL-less-relational-database product it'll be much clearer in my mind to articulate what I was trying to get across back then. Thanks again!
@@BarnabyKeene Sounds good. As it happens I have a "write a database the way I'd like it" on my bucket list as well, so could be interesting to see what you come up with.
With respect: you needed to *start* with substantive, concrete criticisms of SQL the language; that's largely why nobody understood what you meant. Linking to the EdgeDB piece wasn't enough because Primeagen hasn't read it and neither has anyone else who watched. Just witness how many people think SQL is synonymous with the relational algebra itself. People would never make that mistake in any other context; it's like thinking C is synonymous with manual memory management, and that doing it any other way or with a new language is unthinkable.
I had to get data from a PIC database once. It was hellish, for those that do not know, each field on each record can hold one piece of data in every type, so not only do you need to know the table and field (which the query language doesn't seem to like giving you) but you also have to know what datatype the specific data is and hope like hell the interface forced the users to use specific datatypes for the fields (they never do). I wished it had more tools like SQL does. That being said, when you deal with objects (where the data is always an n-dimensional object which may then relate to other objects of varying dimensional depth) vs 2 dimensional related data a relational database may not be appropriate, for instance velocity db is a NoSQL db that is used in air traffic control due to the speed and reliability features. Each aeroplane has a distinct set of objects associated with it which are never related by themselves. So here is a different method of db interaction than using SQL or a 2d relational model like most SQL dbs are. They generally interact with your development language in it's own native manner (in most cases). Mongo DB now uses Realm's (NoSQL) paid cloud sync as it's backend instead of the original mongo db, the foundation of Realm is a completely open source project and it uses APIs in languages to access it in an object oriented way, this includes the migration, the schema, and it means if you really want you can change the schema in dynamic ways (personally I would avoid this;). So I'm unsure why he thinks there is no competition, there is and in certain areas it is actually becoming more prevalent. In this manner you can interact with your data in the same way you would any native object you had created rather than using a different language between your code and your data. When I was testing using benchmarks between postgres, mssql, realm, velocitydb, raven and others for loading and reading 1 million small animal records (40 data points + % accuracy, + history, + the pedigree with all their own records recursively) the slowest were the SQL dbs. In our testing, loading the animal data into the dbs took SQL hours (MSSQL would crash unless you treated it special, postgres coped ok), on the same server Raven took ~31-40s, Realm took 24-26s, & VelocityDB took 12s. These were all saved then reloaded fresh, and random lookups were then conducted and benchmarked again. SQL would take ~15-40s for a single animal record with 6 gens of pedigree (up to 65 individuals), something that when you need to push 500-1000 animals through the crush can slow things down immensely, just the loading times for the animal data we worked out to around ~7-10 hours, let alone then checking the data, recording ~5-10 trait data points, and then herding them into the correct areas and waiting for the data to be saved before the next animal loaded. When we tested using the NoSQL db it could pull up all the same info in ~15-25ms (most of this being the I/O time). So yeah no idea where he gets the idea that there is no competition with SQL. He seems to be conflating SQL with SQL tools for writing queries, the relational db model, and dodgy methods of using SQL inside other languages (using strings for queries is a good way to get hammered by injection attacks, please start setting up and using stored procedures), I still work with SQL on a daily basis and write new SQL queries by hand as I have never found a tool that works really well at making good, optimised queries, it's not hard (heck I found react and angular harder in the day).
I'm using Active Record to interact with RDBMS. Of course I do know how to writes tests (using RSpec) to make things reliable and maintainable. Personally, I don't have much complain, everything works smoothly.
18:59 SQL isn’t just a string literal. It’s really no different in concept from sending shaders to a GPU or sending bytecode to a JVM, or instructions to a CPU, or a request to an API.
@@br3nto No, it's _exactly_ the issue as the "string literal" comment. (Stop calling it string literal, it's just a string. String literal is something else).
SQL database is a mathematical abstraction of how to store and manipulate data using tables, the SQL is tied closely to it. it not going to change. if you have other ways of abstracting data you are welcome to try but tables are just so easy visualised and used
what prime is saying is despite every sql are the same. their differences and their underlying function i.e maybe its for analytical, transactional, horizonal reading,real-time, all db are still different even if they use the same familar dialect they have some degree of specialized parts.. its like if else and loops its just there for familiarity.. heck even in plain human languages
SQL is 2 things packed together: - A protocol for an app to store and retrieve information on a database - A high level language to process, analyze and retrieve data for Data Analysts Because it's such a standard in outside software engineering - where learning new languages every other month is common - SQL will not move. However we could totally think about a way to optimize the interaction between apps and databases with a fast and type-safe system, for the first use-case. But the system will still need to be SQL compatible :)
As for trying to change how procs were written in the database, SQL Server (I think it was SQL Server 2005 or 2008) added the ability to use C# to write procs. It was touted in the conference to promote the latest version of SQL Server at the time. I have yet to run across a proc in Sql Server that used C# instead of SQL. Oracle I believe also (at least they were going to try and add this ability) added the ability to use Java to write procs and scripts within Oracle, but I have not seen many Oracle tutorials that emphasize using Java instead of PL/SQL. But the market it seems has rejected these ideas. End users don't care what language the database uses, they care only when their data has been corrupted or lost.
@@magfal I am not talking about utility functions written outside the database. I am talking about UDFs or stored procs within SQL Server itself. I am not saying that someone has not written any, I just have not seen any in the databases that I have worked with. The only time I actually wrote any was when I was writing documentation on how to do it for SQL Server's spatial additions for Microsoft.
i have encountered these. a nice thing is that you get access to .NET functions, and from the outside you get a stack trace when there is an exception. but to me it feels like a step in the wrong direction, as it goes against the declarative nature of SQL. i personally prefer to have as much as possible in queries, so that things are as stateless as possible. so similarly i also don't like people creating stored procedures when a SELECT query would would have done (which i see way too often)
@@xybersurfer It's a double-edged sword. If you write the select queries outside the database then you make it hard to adjust the indexes to improve query performance. On the other hand if you create everything as stored procs, you may make it harder for you team to troubleshoot issues especially if you don't have database developers, but just developers that are familiar with application languages C#, JavaScript, etc.
@@alexaneals8194 right. i prefer the SELECT statements to be Views or UDFs so that they can be used as part of other queries (so in the database). indeed application languages limit who can troubleshoot, good point
Surreptitiously brainstorm an acronym to have in your pocket for that meeting and get a branch or the database or something named PIG (PRIVATE INTERNAL GIT) or SWINE, or something. Once it's established, that's when you bust out the PIGGY squeeeels
Spring Data JDBC is the right approach in Java - gives you the basic operations for free and allows you to write raw sql for anything more complicated.
I had the same mindset... Then I started asking myself one question can I write this in JPQL/HQL?, 99% the time I could. Raw SQL is unmaintainable and I try to avoid it at all cost. So being able to write complex SQL in JPQL/HQL is a big win for me when it comes to JPA/hibernate as my go to ORM. There is no such thing as a simple application. especially after 3 years of developments, all sorts of unforeseen requirements are throw at you and JPA/hibernate has never let me down...yet.
@@hardcorecode I had to write a ton of native queries in JPA at my last job, because JPQL has failed me a lot. Also sometimes the JPQL queries would work on an Oracle db, but not on Postgres (or vice versa), but that could've just been the old version of Hibernate we were using.
I think what the article is trying to say is that how data is needed in code is very different from how fundamentally SQL is designed. SQL is designed for humans to query data from a database but not for a machine and so we end up with tons of ORMs that translate your codes data needs to this human oriented query language called SQL. This translation from a code data requirement to SQL might often lead to the ORM producing subpar queries where as if the database exposed APIs to do the dirty work the entire translation step would be skipped and ORMs might become better and probably the defacto way we talk to a database
SQL is a bad language for thinking about how to construct and operate on datasets, its syntax doesnt reflect the process of thought, you kinda have to invert it. It quickly becomes unreadable on sufficently complex operation. Examples of good aproaches to to this problem are Hadley Wickhams dplyr or sparksql, you chain verbs and get what you want
Tbh there are two good reasons. 1) We haven't found a way to interface with databases in code that doesn't suck way harder than just using SQL 2) I do a lot of work in an SQL REPL and I'd much prefer not having to write JavaScript to see what's in the table (I'm looking at you, mysqlsh, though I know you can switch to SQL). Trying to use mysqlsh to dump a table from a Non-JS or non-Python language is enough of a pain as it is. Generating Javascript code in bash is not fun... On the other hand I'd be all for a sequel to SQL. The language is an unstructured nightmare, and parsing it is a massive undertaking.
Complaining about relational databases using a textual representation for their query description is not significantly different from complaining about browsers using a textual representation for their document description. Both are universally used within their domain, provide interoperability through a common language specification, and neither provides static analysis or type safety within their host server language, nor do they “foster an ecosystem of novel approaches to interface design.” That said, HTML is a *bit* lower level, and is broadly context-free. I could write an object model for an HTML-like language (e.g. a tree where nodes have metadata) without much difficulty, whereas doing so with SQL is a larger - but by no means impossible - undertaking. (Arel in Ruby and Linq in C#/.NET both come to mind.) The bigger complaints here seems to be “SQL queries are hard to compose” and “I have to issue SQL queries via an abstract API, so my ResultSet comes back untyped” - neither of which is wrong, but both of which are solvable with a little library code (plus an LSP, if you feel like treating yourself).
SQL isn't all the same either, there are SQL flavors and variants based on the providers and engine. Postgresql is slightly different from MYSQL and MSSQL
The most basic premise of this is completely wrong. SQL is not a relational database, a relational database is not SQL. A relational database can be queried by any query language you want if you just create it. A form of SQL can be used to query anything if you make your own extensions to it. The relational database, like pure functional programming, is not just an idea a programmer just thought of, it's based on mathematical theory. SQL was not supposed to be the production query language when it was first created, it was a quick hack. The IBM product managers decided it was good enough and released it as is. "Select *" is not supposed to be there. It's supposed to be better defined. ANSI standards are just that, which is why there is ANSI C for example. When a product is ANSI standard you know it is supposed to comply with the standard. The licensing fees are to pay for the overhead of running the ANSI commitees, which are numerous and expensive. The reason the SQL/Relational database has persisted so long is that it is based on sound mathematical theory. Object databases were proposed and created in the 1990s. They all failed because the OO Model is deeply flawed. The relational database is essential for many business requirements. It guarantees commits are consistent, that data is consistent. It is highly flexible and can perform highly complex queries. It is well known and understood. There have been many years spent optimising queries. People do come up with other database models, such as tree based, but they are really just old inventions that were deprecated because they are not as good as the relational model.
APL used to be used for databases, in fact one of the use cases is still databases. I would have actually preffered to use it instead of SQL at uni... tho I'm not planning on doing DB related work so maybe it shouldn't really count. A friend of mine who is a chemical engineer but ended up as a sales manager& basic computer guy loves SQL unironically.
SQL facilitates backup and redundancy and disaster recovery using its transaction log, we can call it a leaky abstraction. We can criticise SQL as an API to safely manage transactions but referring to it as an SDK is wrong. The article neglects all database operating aspects, database operating is usually the important part when you consider which database you use, the query language is an after-thought and that's how it should remain. You want your DB as strong as a cockroach, not your API.
As a beginner that has started out in C: I can see the value in starting with C, but I gave up. C is a very difficult language to grasp, if you have no idea of programming concepts.
I do think that structuring around the SQL syntax does make an ORM different in it's constraints from a dedicated api/library solution, in theory, but it is maybe also an open problem. Still it is pretty annoying that even using sqlite in rust requires me to install DLLs on windows, so I generally feel like it would be nice if basic database stuff would be included by now in any ecosystem - and also ORMs are limited trying to transform their ideas to different SQL syntaxes as well, and that again enforces the feeling that there is something to innovate about
ORMs are make your life easier for most simple queries, but they occasionally can't be used for less typical queries. in those situations, i usually drop to using SQL syntax (a lot of ORMs allow this on the same database connection, which is nice for transactions). that's the nice thing. you don't have to choose between ORMs and SQL syntax. just use both
@@xybersurfer Good! Even OOP folks by now say that composition >> inheritance. And as far as random hierarchies are concerned those are all equally slightly awkward in a relational model whereas with OOP it's either trivial or, if it doesn't fit 100%, completely convoluted.
@@aoeuable not sure if that is actually true to be honest, and not just a prejudice against what people perceive as "OOP", given ORMs can just as well result in unstructured data, or record style data in a functional context. Just as much as the "simple queries are fine in an orm" argument kind of falls apart, as most really complex queries are usually optimized statements to get around specific db engine decisions, and might be actually easier to reason in the builder pattern most ORMs employ, than the select you end up using. In fact, in an orm you might be able to keep both around, and switch between implementations, without touching the business code in front. It really depends which ORMs we are talking about.
I like EdgeDB for an RDBMS that uses a language that's more programmer friendly than SQL, migrates itself and does it well, and has a better schema definition format.
Counterpoint: Cypher for neo4j. It makes a LOT of sense, and the only downside is that all the existing integrations for things assume you're using SQL.
Not every technology needs to be like JavaScript ecosystem.
It's blessed to have established, log-lived technologies like SQL, UNIX and REST in this fast-moving industry.
But then these morons have to learn things that aren't JS, and that's just too much to ask.
“Any application that can be written in JavaScript, will eventually be written in JavaScript.”
they are coming for you buddy.
you cannot stop it.
you will not stop it.
to be clear, I'm not advocating for getting *rid* of long-lived tech! that's great and needs to stay, I'm just interested in trying something new in an area that seems to have very little innovation!
@@BarnabyKeenei am not bashing you for wanting innovation, but why care about dbs of all things? Sql dbs are fundamental, solid and extremely boring structures since thats what they need to be. Feels like wanting to redo the plumbjng in an old house that was already working without any issues, it creates more problems than it solves imo
@@fullmontis i think there is plenty of room for improvement in DBs. change for the sake of change is bad, but i could see DBs improving as part of a bigger change
SQL is not a string only language. SQL only tends to become a string inside other languages, as a result of the way most languages integrate with SQL
SQLC W
This one is a real problem. If it wasn't a string, injection attacks wouldn't be possible. There should be something like a query builder for sql.
@@seasong7655you mean like stored procedures?
a good exercise for any developer is to implement the SQL language
You mean something like stored procedures?@@seasong7655
I'm not a developer, but work in analysis and use SQL daily. It is so useful that most databases use SQL, because otherwise I would have to learn so many languages. Even though every SQL dialect is a little bit different its close enough that hopping from MSFT SQL server, to Oracle, to Big Query to Postgresql isn't an issue. Would be a real pain if they all worked fundamentally different.
The pain is when going from Postgres to lesser SQL servers.
You keep having to remind yourself of the limitations.
there are indeed differences - sometimes very significant ones - but it's great that there will be like 80% or 90% common turf.
Getting into the world of PL/SQL or PSQL then there's all manner of divergences
@@TheSulross pl/SQL has a compatibility layer you can buy for postgres if I remember correctly.
@@magfal same can be said about Oracle. You can be a master ninja with oracle's hints and never get the same performance from any other DB, making some queries fundamentally impossible
@@NJ-wb1cz I've been challenged to solve quite a few complex problems in postgres by Oracle DBAs to see if performance can match.
I've wound up with better performance each time so far, even when one colleague limited me to the ancient 9.6 version.
Did you know that the technique for making bricks used today predates the epic of Gilgamesh? Sometimes things not changing is OK.
Sql has some unfortunate issues that bricks don't though ;)
Do you mean data bricks or brick bricks?
@@Martinit0 Yes, the prehistoric data bricks. Aka stone tablets.
Its funny to me how many people think that if a piece of technology does not constantly evolve it means its automatically worse than ecosystem that changes all the time. What if the technology that remained is simply good? If the goal has been achieved there is no need for constant changes. Not everything needs to be javascript where new "better" ways to do thing are invented (or reinvented) every few months.
yep. i think it's okay to reinvent the wheel every so often, but i hate it when so many other things get worse in the new way of doing things (regressions). i think it's partially because of the way humans developed software (fragmentation & NIH)
It’s not a free market unless the new thing I like is at the forefront…
I think the argument was not that SQL is bad but that we don't even try to see if we can find something else that's good or at least decent. A bit like haskel. No one says "C-based languages are nice, but Haskell is way better for production", but Haskell still exists. What they want is not for something to replace SQL, is for the option to exist, because that'd mean people actually tried and SQL is still the better option.
What makes SQL so prevalent is it's closeness to the mathematical branch that originated it. Relational algebra was first developed through mathematical tools, then converted into routines. Not the other way around. In maths when something is proven it's permanent, many of the problems relational algebra solved were demostrated to be optimal, what exactly is there improve? Calculus was invented 400 years ago, why isnt him bitching about it too?
@@Michallote By that logic, Raku is perfect. Languages are meant to be opiniated abstractions. That guy doesn't want to replace SQL, he wants there to be options.
The thing with SQL is based on the relational model for databases by the legend Edgar F. Codd. TLDR it has a tons of math to back it up, not just hype.
Codd himself would disagree with that statement, and indeed does vociferously in "The Relational Model for Database Management" Version 2. (ISBN 978-0-201-14192-4)
In terms of the relational model, every query would, at the very least, need to effectively be qualified with "UNIQUE". Also, the original model had two types of NULL (missing but applicable and missing but inapplicable) - which relate to how aggregates work. There are many, many more differences between SQL and Codd's idea of a Relational Language.
I was hoping this was the angle the OP would take to disparage SQL, but was somewhat disappointed by the actual article.
CJ Date's 1983 essay "A Critique of the SQL Database Language" also goes through many of the objections, and is possibly more accessible.
@@robgrainger5314 being based on doesn't mean identity. The theoretical ideas almost always get organically adapted to reality during implementation.
If anyone still longed for two nulls and thought it was a good idea in the 80s and 90s, I think Javascript pretty much wiped all of them since then
Not gonna lie, bright-eyed engineers that complain about tech choice and get mad when their preferred quirky tech isn't given the attention they think it deserves, are so difficult to work with.
I hate them they don't shut up
I was that guy in college.
Hated my db modules (oracle sql, in fairness the dinosaur prof did a bad job too)
Thought nosql was the future and preached it.
Once i got an internship and worked with production sql, i realised how much better sql is for a lot of use cases and that new and shiny isnt always better.
Then i aced another db administration focused module which used postgres because i had a much better understanding of the how's and the why's
honestly same 😂
They should probably adjust or move on but sometimes industry standards are just groupthink. React blows and is over used, even a word press site would be more appropriate at times.
I see your point, however the opposite of that is crankly old engineers doing anything to prevent fresh perspectives changing the status-quo
(coming from someone who isn't bright eyed anymore but is yet to be old and cranky, no offense to anyone :P)
Intelligence is knowing that Frankenstein wasn't the monster. Wisdom is knowing that Frankenstein was the monster. :)
This article screams to me the engineer who comes to every meeting like "GUYS DID YOU SEE THIS NEW SHINY THING THAT WE SHOULD IMPLEMENT!" Worked with a couple of those guys and it always make for unsustainable production code. SQL is just about as straightforward as database interface as you can get. You wanna create some new interface that can do everything SQL can do, go ahead.
Ten minutes in and I am still shouting at the screen that there are a whole class on databases literally called NoSQL, and losing my mind that MongoDB was even casually mentioned without that coming up
In 1998 (I believe - may be off by a year), at WWDC, there was a Q&A session. A woman asked Steve Jobs what happened to "think different" because he announced that they'd be adopting some standard thing instead of continuing use of an obscure proprietary thing (forget what the thing was). The woman named a few things the Apple thing did better than the standard thing. And Steve Jobs replied "There's probably plenty of things that does better and some things does better. But the point of Think Different was never to be different for the sake of it. The point is to be better. You can't be better by being the same, but if the standard thing is the best you shouldn't cling to being different for the sake of it. Keep to the goal; Being better. And when you're not the best, it's probably better to be compatible with what's already there and improve it from there" (very paraphrased. Been many years since I heard it from his mouth)
Also "Bad artists copy, good artists steal" etc.
I think the main reason we think highly of Steve Jobs is because he died before we really knew him. Imagine if Musk died a few years ago - we would remember him as one of the greatest humans who ever lived, despite him being the same person he is today.
Steve Jobs and Musk were good managers and had a vicious perfectionistic streak that together with luck allowed them to organize people around themselves to help others create something great, but they're still managers not creators. These quotes and phrases are quite meaningless because they don't relate to what the person was actually doing - human management and human interaction and using humans as resources to satisfy their inner imbalances. It's the sort of live love laugh nonsense your manager tells you while you're creating the product for them on your own, praying that they don't get in your way, while they're telling everyone how this is their project and how hard they are working on it
I mean… SQL is essentially a superset of a field of math… the only way to compete with SQL is to make an entire field of math to compete with relational algebra and then make a query language to interface with data using that new math
IMO where the competition lies is in making a DBMS that better optimizes and runs existing SQL queries
I just started studying about rdbms and all I learnt for 3 days is maths.
Relational Algebra and function dependency. I did also learn SQL but I mostly skipped since i already knew SQL. But it was a pleasant experience learning the maths behind it.
@@pianissimo7121can you link any sources that you used?
Except SQL is a bad adaptation of that field of math, that loses many of the properties that make it so useful
SQL is based on mathematics. There is no other/better way to do it for a relational database. It is a standard. Developers can use the same language on every relational database product. Moving applications from one db vendor to another doesn’t require a rewrite of all the code.
nothing is better than raw math
Although I’ve only used SQL like twice, I too see the value of standardization.
I quite like SQL. But no, the language couldve been much better. For example, declaring tables you query before the columns.
Take a look at PRQL, it's much better IMO
@@Akronymus_ I don’t understand. The “from” statement comes after the “select col1, col2, …”. Or are you making a joke? If so you should use some kind of emoji.
Imho Datalog (either the original or the datomic variant with the lispy syntax) is the best alternative syntax for SQL that manages to match it in expressiveness and is used by the relational database research community in papers whenever they publish on new concepts like incremental materialized views. Graph databases basically offer a weakened version of datalog with query languages like Cypher.
Datalog is nicer than SQL for CRUD operations, for queries that don't use negation, and for recursive queries. It is way less nice than the postgresql dialect when you actually need to optimize your queries and need to reason about the memory layout of your tables/indexes or the query plan that actually gets run. In many ways, datalog is to SQL what lisp is to the C family.
Yes! Datalog is super nice and expressive, though I do find the syntax a little intimidating, at least it's *actually* structured, unlike "S"QL which is based on the most unstructured language possible: English!
@@BarnabyKeene Slavic languages almost lack any word order, talk to me about unstructured English🙃
@chupasaurus
And yet are more structured by it being immediately obvious which abstract position a word has therby reducing ambiguity.
@@SystemAlchemist For example "You do smth?" and "You smth do?" are 2 different questions, totally zero ambiguity.
If you think of databases as APIs using their own format (SQL instead of JSON), many of the problems magically disappear.
The thing is, we all agree JSON has some weak points. So what they'd like is just to have another API that doesn't have SQL's pain points, but maybe has some different pain points. It doesn't really matter if it's worse than SQL, it just needs to show people tried and SQL is still the better alternative. But rn you don't really have anything to compare SQL to. You have SQL, PostgreSQL which is another flavor of SQL, SQLitewhich is another flavor of SQL, Cassandra which pretends to be noSQL, but CQL is really a flavor of SQL, etc...
@@Exilum I wish JS/HTML was as well designed as SQL is, they don't make things like they did anymore.
SQL , a 4th generation language that really did what it promised.
Remember when there was this idea that you wouldn't need to know the implementation of HTML/CSS/JS to create pages, the computer would write it for you. (well, now we have GPT to copy-paste it for you from stackoverflow or something, not the same thing)
the problems are in your head
@@monad_tcp wait, didn’t you like Microsoft FronPage?? 😝
@@Pictor13 no, I was making fun of it on the other comment
29:02 The reason for that is actually a legal one.
SQLite wants to keep the project in the public domain which means that they need to do a lot of legal work too besides the implementation and maintenance work.
But here's the thing: Not everybody is able to put their stuff into the public domain. Heck, in some countries it's literally ONLY possible by dying and then waiting for it to run out which isn't really a nice way.
Primeagen's pronounciation of SQL will never not trigger me lmao
Ikr, I genuinely hate it but find it funny at the same time
Does it make you squeal?
It seemed weird at first but these days it's the only acceptable pronunciation
I know. I’m both triggered and find myself using it. It’s just too funny to not.
Haha the same! Though to be fair calling it "sequel" it triggering me just as much --it has to be specifically "Ess Queue Ell" to feel right.
SQL is not a string literal problem. Look at the bytes on the wire, they are fully typed. I.e. its a binary API. Many client libraries exist to move datatypes 1:1, with syntax to optimize for query plans.
Any sufficiently complicated NoSQL database contains an ad hoc, informally-specified, bug-ridden, slow implementation of half of SQL.
Totally right. And most people that use them impose a relational-style schema with their 'driver' ORM, for example mongoose
@@doyouwantsli9680 Yup most mongodb apps are basically used exactly like a SQL DB would be used in my experience. I've seen very few cases where not having a standardized schema is a benefit.
But no one uses NoSQL because they want a different query language. They use it because they want a model that supports extremely fast, high volume reads on large tables, and they don't require transactional locking for concurrent writes.
I mean, all browser use HTML. That isn't very diverse either, right?
And it's well more smeIIy of a RUclips fire!
Totally right. Html CSS and js
xml was a compeditor
The only DB abstraction i need, is LINQ - essentially a typesafe-SQL, nothing more.
NB! C# compiles to WebAssembly - C# *is* web.
F# with type providers is even better.
Yeah the OP is mixing two totally different things. SQL is a standard but DB has their own implemantation that varies in Syntax but he is pointing problems for ORM's or how language interacts with SQL. why SQL should care about that if you go to an SQL tool you got all those features he is asking for.
exactly
Yep table designs, search design, there is so much you can do
I lived through dBase, btrieve, and even a few cobol banking databases. SQL was successful because of its compatibility and high level. A huge improvement over when the APIs was "give me the record index for this key". All in one process with so many server round trips.
For years some software gave you the option to select between two SQL DBs. That’s the proposition of using a standard format of communication. With not too much of a rewrite you could use either MSSQL or Postgres for example.
It doesn't matter what tech stack you're using, there's a guy like this author that is ready to crap on it.
The problem with this article is he never explains why SQL is holding back design, other than some vague sweeping complaints about the spec. My only complaint about SQL is that a KV database is objectively simpiler, faster and easier to use, but you only ever find ACID compliant SQLite, or non-ACID json or ad-hoc configuration files, or some custom serialization format, instead of ACID complaint Redis or lmdb store. lmdb is just as reliable as SQLite, and faster, but no one uses it, everytime I play Terraria or Factorio I just wish it didn't hitch every time it went to save. You can prevent item duplication bugs with transactions and save at 120fps with restore points and all sorts of crazy stuff. SQL is overkill if you just want ACID compliant data storage.
Working in a database first company where more than half of our business logic is written in scalar functions and stored procedures, I have to say I understand his sentiment. If there would be an actual programming language that would interact directly with a database (and not just an orm that translates to sql), it would be a very interesting new approach. It would give all the benefits of (modern) programming languages without sacrificing performance and without it becoming impossible to debug/maintain because you can't understand why the orm converts it the wrong way.
PL/SQL?
EdgeDB is basically heading in this direction and gets most of the way there even now!
I have been in several discuss about moving newer application to a non-SQL solution. One of the biggest reason to stay with SQL is the countless non software engineers (Business Analyst, Report Writers, etc) that are just not going to be cheaply (or at all) retrainable to do their work in a new non SQL environment.
My department has many consultants next to software development and we all (broadly) deal with data management in business intelligence tools. In my experience you are spot on and its the same reason Excel is still a favorite tool of choice for managing and analyzing data (to the horror of everyone interested in BI). Unless you are a huge corporation, databases need to be relatively accessible to people outside of IT and you will never come up with a system that can compete with the millions of hours of experience people have painstakingly gathered in SQL. Same goes for Excel. People working in these areas suffer from a small aneurysm if you change the UI a little, imagine what happens if you change how the formulas work they have adopted over the course of a decade.
Finally found a rival lunatic. I pronounce SQL like "school"
I heard people call it "shekel" (with a soft 'l') a few times.
@@vitalyl1327shekel is the Israeli currency wtf
@@AlLiberali not just Israeli, it is an ancient Sumeran word, and spelled very similar to "SQL"
I pronounce it Structured query language
This rant from the article's author is very strange. SQL is a declarative language and as such describe what information and how the user wants the information to be presented (always in grid form). It is implementation independent (for the most part). Make your own. There are other database noSQL paradigm: objects or documents based.
The other thing I'm not sure this guy understands about SQL (and databases in general) is that it's *imperative* to have longevity.
Records stored in databases may be referred back to decades in the future. If you have a fast-moving tool which increases the amount of migration work you have to perform, the chance of data loss increases too.
Databases are not microservices that can be hot-swapped on a whim. There is little scope for trial and error. If you want to play that game, you need to rethink and break apart the entire database architecture
Back in 2005 you also had to pay ISO if you wanted the full ISO/IEC 16262 / ES3 specification and it came in a crappy PDF.
The text aspect is a feature. You write your operation and send it to the db to compile to prepared statements once. You get new features and optimizations without relying on a binary protocol.
Queries then use the prepared statements to communicate with the db in a terse binary format.
If you don’t use prepared statements your integration layer sucks or you should switch databases.
I was disappointed by the original article here, but probably as I was hoping for a better critique of SQL on a theoretical basis. Similar to CJ Date's 1983 "A Critique of the SQL Database Language" or discussed in Codd's (the original designer of Relational Databases) "The Relational Model for Database Management: Version 2" from 1990.
While yes, both languages written for the JVM and for LLVM will be able to run on most systems, and both first compile down to an intermediate language, their back ends are very difficult. JVM takes the JVM-byte-code and then interprets it, while LLVM instead takes the LLVM-IR and compiles it down to machine for the system you're compiling to.
Unless you use the Graal tools, because then you can compile to native too.
And so does the CLR, which consumes IL and compiles that into machine code, and which the author totally forgot to mention. Yes, I am talking about DotNet.
But on the other hand, since LLVM is mostly for low level languages, that one should be compared to GCC as well, the most important compiler platform on Linux, but compiles for more platforms as well.
The argument against SQL fails for the same reason arguments against JavaScript fail.
Everyone knows JS is bad but who can really kill it at this point? Are we not doomed to live in a world where JS is both the most used language and the most broken at the same time?
Saying SQL needs to be diverse is like saying we should have browsers that throw out the back button and the address bar. Or why do we use for loops in all programming languages? Why not invent a new loop type?
Honestly I get the point, and I somewhat agree there needs to be people trying new things, but I also get the risk aversion and the difficulty of changing something everyone considers so fundamental.
You just have to look at how long it's taking for ARM processors to get as popular as they are now, or how long it could take for RISC-V to even get any sort of wide support.
Things you consider fundamental are hard to change or propose alternatives to.
But the fact SQL isn't actually all that fundamental should've seen at least some level of innovation over so many years.
What I like about the programming language landscape is that while there are a bunch of languages built over C, there are also some that aren't. And that diversity is why programming has seen the advances it has. Rust probably wouldn't have existed if C-based languages was the only thing around forever.
Lackluster article. The author provided no actual practical examples of what's wrong with SQL. Sure, acquiring the standard is costly (if you're not *resourceful* in other ways) but SQL itself is by no means as crufty as some of the other things that we rely on. It is one of the few things from the 20th century that I actually have no issues with whatsoever. Structure your data in 3NF and you will be fine. Use one-to-many, many-to-one and many-to-many relationships only, if you can (and yes you really can if you try hard enough). It's really not rocket science or as footgun-prone as something like C. Heck, modern tools / libraries do even provide type-safe abstractions and can even explain your goofy ass query to you by drawing a graph.
That being said, I'd be curious to see what a hypothetical query language that could supersede SQL would look like.
7NF is king 😂😂😂
I mean there was MDX with cubes, MQuery with PowerBI, DAX, and now GraphQL. You could say many are for reporting but they are essentially query languages meant to query data with joins.
The biggest thing holding back sql is the lack of good tooling for it (I say this as someone trying to learn sql better). We really just need a good sql lsp that communicates with your database to understand the schema
Even a sql lsp can’t guess though what columns you might be typing, since select comes before the from clause. Otherwise, ye you right
@@apoggione8you can though, but you actually need to be connected to the DB.
Most dB client I use (Beekeeper, DBeaver, BigQuery, hell IntelliJ DB Client) has intellisense on table and column name.
Though you're right about needing to go back and forth cursor wise because you need to declare from first before the LSP can suggest columns
19:06 I think ORM and query builders are 2 different things. but they can be designed to be plugged together
Here are some more alternatives to SQL (as a database language) that I haven't seen here - for the perspective:
Tutorial D - from C.J. Date's database book; it is not an industrial language, but IMO could be made as such (like, Pascal was also a tutorial language at the beginning 😜)
Prolog - that's logical language, but its queries-predicates are "relational"; just need to make it more practical from the database point of view (like, field selection and all such things)
I'm personally OK with SQL, although sometimes it's getting a bit wordy when you need to do some complicated joins - would be great if we could have some expressive/recursive stuff 🙃
I really want Prime to take a look at how C# is currently and give his thoughts, I'm sensing a bit of a hive mind in the chat
Blazor has good future
C# all the way.
I agree. And they talk about the JVM and everything, but LOL they forget the entire CLR from DotNet, which is does groundbreaking things and is still more performant than the Java platform due to all the low level things and the ahead of time compilation thing.
The problem is that people do not like the large corporation that created it, and sometimes for good reasons.
But the dotnet team and the foundation are for an important part part a distinctive thing and I think they do seriously amazing work.
He hate OOP and Java, so C# won’t be his taste
@@OnFireByte C# has been adding more and more FP-like features with each new version. Once they add in discriminated unions as well as some sort of way to make variables truly immutable I think it will be "FP enough" for a lot of FP fans
No one tries to change SQL because SQL is THE right way of interacting with data. It wasn't improvised by a techbro in an ephemeral startup, it was thoughtfully designed by a mathematician at IBM labs based on a solid relational algebra foundation.
The whole "SQL is too hard" crowd need to build something at least as good before they go criticising.
I didn't hear a great reason for trying to make new query languages. First we had stored procedures, which address the argument that it should be an API managed at the server level, but that turned out to slow down development time and increase extra maintenance tasks. Then we had ORMs , which are pretty good for certain use cases, but they often generate extremely slow queries outside of making single row CRUD operations.
8:54 there are sooo many improvements that can be made to SQL even without inventing something by totally new. First, put FROM statements first. How can you get code completion in your SELECT clauses without knowing which tables the data will come from? Second, name table relationships. Instead of joining, you use the relationship name. There are so many query patterns that could be abstracted, though I guess that’s what stored procedures are for. The other thing that gets me is that all RDBMs are also inherently object/graph databases. So in theory, they could all have object/qraph QL applied. The other other thing, is that SQL or an improved derivative language could be used by clients instead of GraphQL to query APIs. The whole data persistence part could be abstracted so that you get secure decoupling between the domain data and the persisted data.
Named table relationships is a cool idea.
OP has big "I've been writing software for 6 years and I think I know everything now" energy.
It does feel a little backwards using an API/ORM that needs to translate requests into a string literal language and not vice versa. Wouldn't it be nicer if the RDBMS provided API interfaces, and the ecosystem provided the tooling for interfacing with those via whatever method they wanted? (SQL, visual editors, raw dogging it, etc)?
I think SQL itself can defo be redone. Why you select the columns before you even get from a table is beyond me. LINQ style is far more friendly for that. I'd love to see something like LLVM, but for databases, i.e. where the query is a compiled piece, and then the language you use compiles to efficient byte code, and then you can have competing query languages and/or compilers for creating a optimal queries
As an ETL dev in a Microsoft shop I love LINQ, but defining "from" first still throws me sometimes, even 8 years into using it. I suppose there really is a bit of Stockholm Syndrome in SQL, as it didn't occur to me until I saw your comment that I just default to writing "select * from", defining the table and any joins, then going back to the select to define the columns. Been writing queries this way in SSMS for so many years now that it's just muscle memory at this point.
Why need diversity when even a Business Analyst, a Sales Manager or even a Marketing Specialist can easily learn and write Squeal to get the information they need? Squeal is not really invented for programmers.
FYI, I'm DBA/Data engineer and I grant read access to those people.
Sql can get very complex though .
@@ci6516 That's when the big bosses spend big money on BI platforms such as PowerBI or Tableau
I don't know why but I strongly prefer pandas/pyspark df apis over sql. Sql abstractions are great for me but i completely hate pure sql syntax. For some reason FROM foo SELECT c1, c2 looks terrible in my mind but foo.select(c1,c2) looks perfectly fine.
I think there are two reasons why SQL sticks around. First, the way we use databases hasn't really changed, so SQL is still perfectly adequate for its use case. Second, you usually aren't pumping out hundreds or thousands of lines of SQL every day like you are with other languages. If I'm tasked with fetching some data from a database and presenting it on a web page then the SQL is a tiny fraction of the code I need to write, so it's not really the part of my workflow that I'm most eager to optimize.
It just sounds like they want a "good" abstraction from SQL
I don't think that SQL needs a replacement since it is easy to understand and use as long as you understand Set Theory - and SQL itself isn't really comparable to a programming language as it pretty much only describes what the result should look like, not how to get there (HTML/CSS is more similar to SQL in that regard than a "real" programming language)
although to be fair, databases extend SQL in a way to give control over how it is done too (with transaction controls, lock controls, database settings etc.)
In a way, SQL *is* the abstraction, I rather want access to the underlying layer to write a *different* abstraction.
The problem is, that layer is often very tightly coupled to the actual parsing of SQL itself. I almost managed it with Postgres' but writing C makes my small brain hurt :(
SQL is the assembly of databases
Or, now hear me out, they want something theu don't need an abstraction layer on.
How is it nobody sees the m benefit of a usable system without 10 layers of obfuscation?
If you're interacting directly with the database, sure, SQL is fine. But it's horrid API design because it's all text and someone can easily bobby tables your production database.
@@__Brandon__ you could argue that query plans are the assembly of databases, and SQL is the... javascript!? but maybe what I want is a common query plan format that's akin to LLVM IR!
The minor difference between different version of SQL already causes me enough headaches. I don't need a new version of something that is actively supported and already does what I want it to do. If anything, SQL could be more unified if not for proprietary nonsense.
SQL is difficult to work with because it comes from the days of yore when everything had its own text representations. (This is why piping a bunch of unix commands together is essentially custom parsing at each layer.) This makes it harder for machines to build it dynamically.
This is the reason ORM's suck, etc.
For an example of a relational db that demonstrates much smoller syntax, have a look at Datomic. It's syntax is based on Datalog, and the queries are insanely powerful.
what do you call a guy who digs up cadavers and stitches them together...?
....A monster
No mention of datalog use (in datomic/datascript/datahike/xtdb) in this article seems like a huge oversight.
I'm not a software enginer by a long stretch, but I feel like the only reason why you'd want to send bytes instead of text for querying your DB server is for performance. The rest can all be done by changing your library/way you construct those queries.
There is another database query language that was invented by one of the early database academics (Date) called D, implemented by some relational database attempts that have been made (and subsequently abandoned..) over the last few decades. The language though is not SQL-compatible, primarily because it is designed to operate on true relational databases. Which leads to my problem with SQL databases.
The biggest problem I have with modern "relational" databases is that they are not relational. A relation is a set of tuples. A relational database is a set of relations. A relational database management system operates on relations and returns relations, and allows relations and tuples to be parameters. The relational model itself is an abstraction on the underlying implementation of the database. In theory, you could build a relational database on top of a key-value store of some kind, a flat file system, you name it. The relational model part just provides a useful abstraction (i.e. one that can be reasoned about, and represent relationships between data in a way that provides for integrity) for interaction with that underlying system.
All these things have strict meanings and loose meanings. SQL databases all take the 'loose' meaning of these. Back to the meaning of 'relational database' - a "set" implies that every item in it is unique. You could never have a duplicate row in a relation, but you definitely could in a SQL table, or the result of a SQL query (which is also not a relation). Also, in the relational model there is no concept of a 'null' value, and null results in three-valued logic which makes it much more trying to reason (or construct formal mathmatical statements) about such a system.
However, the only way to construct a modern database that would usually use NULLable columns would be to apply a level of normalization that would be tedious given current tools. Basically, for any column of 'optional' data, that needs to be coming from a separate relation that may or may not have data for any given key on the relation it's related to, i.e. a foreign key constraint. This might also make it more difficult for the user to reason about their data because they have to hold so many things in their head at once. There's no such thing in that world as an 'outer join' because it makes no sense to combine two relations that have different tuple definitions.
We need some kind of bridge that can be built upon the relational model, and reasoned about in ways that reflect peoples understanding of their own data. SQL is genius in the sense that it's a very high level, declarative language that abstracts the fundamental database operations (identify data boundaries, extract the fields needed to compute relationships, match keys, optimize queries, etc) and uses some concepts from the relational model to allow people to structure their databases in relational-esque ways. But, SQL is not relational, and the things it omits/allows that the relational model doesn't hobbles its ultimate performance and soundness.
I mean SQL is just the query language. It sounds like you have an issue with the data structures themselves. If the data isn't normalized to whatever extent you need, it's not the fault of the query language, it's the data architecture. It'd be bad no matter what query language you are using. While it is de facto true (I think. I could be wrong) that any used RDBMS I can think of uses SQL, it's not a requirement. I guess what you're wanting is RDBMSs to require a minimum of first normal form on all tables? Or is it that the D query language absolutely won't work for things that aren't relations?
It's not just about ability to reason about the data. It's also about performance, efficiency of the operations, and fulfilling ACID requirements. I'd like to see anyone implement a performant relational DB on top of a tuple store.
I just knew that Prime would talk about the Sisyphus show on Netflix, when he read Sisyphean. And I agree, it was pretty nice!
I would say trying to remove SQL and replace it, would be like trying to replace ASCII. Not in the sense of extending it like with UTF-8, but actually replacing it in a manner which is fundametnally incompatible. For example, one "great idea" would be to put the numbers from 0 to 9, followed directly by capital letters for easy representation of numbers in base 10 or hex or even base 36.
Postgres's SQL syntax and out of the box set of functions is ergonimically 1 million times better than any ORM or alterate query interface that I've tried.
While I can't say for postgres's SQL specifically, I can easily get behind the rest. When I tried to learn an ORM, and saw how requests were formed, my thought was : "How is it more desirable than writing raw SQL ?"
Modifying raw SQL is just far easier and readable than changing its object mapped counterpart
@@nlight8769 I've reached the point where I will build everything that's a backend project in postgrest in the proof of concept stage of a project.
Skip the middle man.
A full shipping stable, fast and secure backend that deploys anywhere easily and for most things you could use rest for scales to 10K concurrent users. All inside a database where i can either write in SQL or rust (or a bunch of languages i won't use voluntarily)
Most things will have all it's endpoints done when the schema is done and an hour or two of development time has been added.
Only thing that I have to complain about sql is that there is no good LSP for sql, and the standard spec of sql also prevents it to be good. Like in sqlc you have to write schema and query in separate files. Since SQL didn’t have typical ”importing” schema definition, it’s pain in the ass to write table name/field without weird typo.
CouchDB has an HTTP api and suppoets mongo query language
Cypher, which is used in neo4j, is a pretty interesting query language, since it's a graph db
Yeah, I've been using only neo4j for 5+ years now. I would never chose SQL by choice.
@@pinatacolada7986 what do you use it for out of curiosity?
thanks for covering my article! I think it's about 2 years old so definitely ready for a refresh, great feedback on the content too!
No offense, but I think it needs to be rewritten when you're sober.
Your this years article from April reads far better, though I would argue it doesn't go far enough in better implementation of alternative query data structures.
@@Nors2Ka appreciate the blunt and honest feedback, it's the best kind of feedback! I am definitely going to rewrite this. It was half-assed at best and takes WAY too long to get the the actual point.
Now that I've actually designed and partially built this SQL-less-relational-database product it'll be much clearer in my mind to articulate what I was trying to get across back then.
Thanks again!
@@BarnabyKeene Sounds good.
As it happens I have a "write a database the way I'd like it" on my bucket list as well, so could be interesting to see what you come up with.
@@Nors2Ka for sure! do you have a twitter/github?
With respect: you needed to *start* with substantive, concrete criticisms of SQL the language; that's largely why nobody understood what you meant. Linking to the EdgeDB piece wasn't enough because Primeagen hasn't read it and neither has anyone else who watched. Just witness how many people think SQL is synonymous with the relational algebra itself. People would never make that mistake in any other context; it's like thinking C is synonymous with manual memory management, and that doing it any other way or with a new language is unthinkable.
I had to get data from a PIC database once. It was hellish, for those that do not know, each field on each record can hold one piece of data in every type, so not only do you need to know the table and field (which the query language doesn't seem to like giving you) but you also have to know what datatype the specific data is and hope like hell the interface forced the users to use specific datatypes for the fields (they never do). I wished it had more tools like SQL does.
That being said, when you deal with objects (where the data is always an n-dimensional object which may then relate to other objects of varying dimensional depth) vs 2 dimensional related data a relational database may not be appropriate, for instance velocity db is a NoSQL db that is used in air traffic control due to the speed and reliability features. Each aeroplane has a distinct set of objects associated with it which are never related by themselves. So here is a different method of db interaction than using SQL or a 2d relational model like most SQL dbs are. They generally interact with your development language in it's own native manner (in most cases).
Mongo DB now uses Realm's (NoSQL) paid cloud sync as it's backend instead of the original mongo db, the foundation of Realm is a completely open source project and it uses APIs in languages to access it in an object oriented way, this includes the migration, the schema, and it means if you really want you can change the schema in dynamic ways (personally I would avoid this;). So I'm unsure why he thinks there is no competition, there is and in certain areas it is actually becoming more prevalent. In this manner you can interact with your data in the same way you would any native object you had created rather than using a different language between your code and your data.
When I was testing using benchmarks between postgres, mssql, realm, velocitydb, raven and others for loading and reading 1 million small animal records (40 data points + % accuracy, + history, + the pedigree with all their own records recursively) the slowest were the SQL dbs.
In our testing, loading the animal data into the dbs took SQL hours (MSSQL would crash unless you treated it special, postgres coped ok), on the same server Raven took ~31-40s, Realm took 24-26s, & VelocityDB took 12s. These were all saved then reloaded fresh, and random lookups were then conducted and benchmarked again. SQL would take ~15-40s for a single animal record with 6 gens of pedigree (up to 65 individuals), something that when you need to push 500-1000 animals through the crush can slow things down immensely, just the loading times for the animal data we worked out to around ~7-10 hours, let alone then checking the data, recording ~5-10 trait data points, and then herding them into the correct areas and waiting for the data to be saved before the next animal loaded.
When we tested using the NoSQL db it could pull up all the same info in ~15-25ms (most of this being the I/O time).
So yeah no idea where he gets the idea that there is no competition with SQL. He seems to be conflating SQL with SQL tools for writing queries, the relational db model, and dodgy methods of using SQL inside other languages (using strings for queries is a good way to get hammered by injection attacks, please start setting up and using stored procedures), I still work with SQL on a daily basis and write new SQL queries by hand as I have never found a tool that works really well at making good, optimised queries, it's not hard (heck I found react and angular harder in the day).
I'm using Active Record to interact with RDBMS.
Of course I do know how to writes tests (using RSpec) to make things reliable and maintainable.
Personally, I don't have much complain, everything works smoothly.
18:59 SQL isn’t just a string literal. It’s really no different in concept from sending shaders to a GPU or sending bytecode to a JVM, or instructions to a CPU, or a request to an API.
I think the author of the article would benefit from reading file systems specifications, or a bit on how ext or btrfs or zfs xfs fuSE work.
Yeah except you can't bobby tables your GPU or JVM.
@@isodoubIet sure, but that’s a different issue to the string literal comment.
@@br3nto No, it's _exactly_ the issue as the "string literal" comment. (Stop calling it string literal, it's just a string. String literal is something else).
@@isodoubIet how?
Someone show this guy the monstrosity of the query language of ElasticSearch and then let's hear him complain about SQL.
29:54 Does Arduino's subset of C (is it a subset of C?) count as coding in C?
All the mentioned stuff applies, so: yes.
Think of it as programming in C in the 80s where memory is a scarce commodity.
SQL database is a mathematical abstraction of how to store and manipulate data using tables, the SQL is tied closely to it. it not going to change. if you have other ways of abstracting data you are welcome to try but tables are just so easy visualised and used
At 11:45 DJ stands for "Database Joiner" in this case.
what prime is saying is despite every sql are the same. their differences and their underlying function i.e maybe its for analytical, transactional, horizonal reading,real-time, all db are still different even if they use the same familar dialect they have some degree of specialized parts.. its like if else and loops its just there for familiarity.. heck even in plain human languages
Lol basically like saying “All languages use verbs and nouns, where’s the free market place of ideas?”
SQL is 2 things packed together:
- A protocol for an app to store and retrieve information on a database
- A high level language to process, analyze and retrieve data for Data Analysts
Because it's such a standard in outside software engineering - where learning new languages every other month is common - SQL will not move.
However we could totally think about a way to optimize the interaction between apps and databases with a fast and type-safe system, for the first use-case. But the system will still need to be SQL compatible :)
SQL is something like 50 years old, lets guess where the shiny new meme languages are in 50 years.
As for trying to change how procs were written in the database, SQL Server (I think it was SQL Server 2005 or 2008) added the ability to use C# to write procs. It was touted in the conference to promote the latest version of SQL Server at the time. I have yet to run across a proc in Sql Server that used C# instead of SQL. Oracle I believe also (at least they were going to try and add this ability) added the ability to use Java to write procs and scripts within Oracle, but I have not seen many Oracle tutorials that emphasize using Java instead of PL/SQL. But the market it seems has rejected these ideas. End users don't care what language the database uses, they care only when their data has been corrupted or lost.
I wrote 10 utility functions in C# to get it closer to postgres' shins in developer ergonomics and performance.
@@magfal I am not talking about utility functions written outside the database. I am talking about UDFs or stored procs within SQL Server itself. I am not saying that someone has not written any, I just have not seen any in the databases that I have worked with. The only time I actually wrote any was when I was writing documentation on how to do it for SQL Server's spatial additions for Microsoft.
i have encountered these. a nice thing is that you get access to .NET functions, and from the outside you get a stack trace when there is an exception. but to me it feels like a step in the wrong direction, as it goes against the declarative nature of SQL. i personally prefer to have as much as possible in queries, so that things are as stateless as possible. so similarly i also don't like people creating stored procedures when a SELECT query would would have done (which i see way too often)
@@xybersurfer It's a double-edged sword. If you write the select queries outside the database then you make it hard to adjust the indexes to improve query performance. On the other hand if you create everything as stored procs, you may make it harder for you team to troubleshoot issues especially if you don't have database developers, but just developers that are familiar with application languages C#, JavaScript, etc.
@@alexaneals8194 right. i prefer the SELECT statements to be Views or UDFs so that they can be used as part of other queries (so in the database). indeed application languages limit who can troubleshoot, good point
If I ever work on a database or an ORM, I am 100% calling it Squeal
Surreptitiously brainstorm an acronym to have in your pocket for that meeting and get a branch or the database or something named PIG (PRIVATE INTERNAL GIT) or SWINE, or something. Once it's established, that's when you bust out the PIGGY squeeeels
Spring Data JDBC is the right approach in Java - gives you the basic operations for free and allows you to write raw sql for anything more complicated.
I had the same mindset... Then I started asking myself one question can I write this in JPQL/HQL?, 99% the time I could. Raw SQL is unmaintainable and I try to avoid it at all cost. So being able to write complex SQL in JPQL/HQL is a big win for me when it comes to JPA/hibernate as my go to ORM. There is no such thing as a simple application. especially after 3 years of developments, all sorts of unforeseen requirements are throw at you and JPA/hibernate has never let me down...yet.
@@hardcorecode I had to write a ton of native queries in JPA at my last job, because JPQL has failed me a lot. Also sometimes the JPQL queries would work on an Oracle db, but not on Postgres (or vice versa), but that could've just been the old version of Hibernate we were using.
most products compete to be the worst they can while still ruling the market, that's pretty bad
I think what the article is trying to say is that how data is needed in code is very different from how fundamentally SQL is designed. SQL is designed for humans to query data from a database but not for a machine and so we end up with tons of ORMs that translate your codes data needs to this human oriented query language called SQL.
This translation from a code data requirement to SQL might often lead to the ORM producing subpar queries where as if the database exposed APIs to do the dirty work the entire translation step would be skipped and ORMs might become better and probably the defacto way we talk to a database
SQL is a bad language for thinking about how to construct and operate on datasets, its syntax doesnt reflect the process of thought, you kinda have to invert it. It quickly becomes unreadable on sufficently complex operation. Examples of good aproaches to to this problem are Hadley Wickhams dplyr or sparksql, you chain verbs and get what you want
I am blessed to have learned dplyr exists and its a shame it's such a small subset of people that even know of its existence
don't get me triggered by even mentioning anything R-related
I'm a DJ (database jockey). Seriously, ask anyone I work with and they'll tell you my queries remix the data in ways they never imagined possible.
damn
Tbh there are two good reasons.
1) We haven't found a way to interface with databases in code that doesn't suck way harder than just using SQL
2) I do a lot of work in an SQL REPL and I'd much prefer not having to write JavaScript to see what's in the table (I'm looking at you, mysqlsh, though I know you can switch to SQL). Trying to use mysqlsh to dump a table from a Non-JS or non-Python language is enough of a pain as it is. Generating Javascript code in bash is not fun...
On the other hand I'd be all for a sequel to SQL. The language is an unstructured nightmare, and parsing it is a massive undertaking.
Complaining about relational databases using a textual representation for their query description is not significantly different from complaining about browsers using a textual representation for their document description.
Both are universally used within their domain, provide interoperability through a common language specification, and neither provides static analysis or type safety within their host server language, nor do they “foster an ecosystem of novel approaches to interface design.”
That said, HTML is a *bit* lower level, and is broadly context-free. I could write an object model for an HTML-like language (e.g. a tree where nodes have metadata) without much difficulty, whereas doing so with SQL is a larger - but by no means impossible - undertaking. (Arel in Ruby and Linq in C#/.NET both come to mind.)
The bigger complaints here seems to be “SQL queries are hard to compose” and “I have to issue SQL queries via an abstract API, so my ResultSet comes back untyped” - neither of which is wrong, but both of which are solvable with a little library code (plus an LSP, if you feel like treating yourself).
SQL isn't all the same either, there are SQL flavors and variants based on the providers and engine. Postgresql is slightly different from MYSQL and MSSQL
The most basic premise of this is completely wrong. SQL is not a relational database, a relational database is not SQL. A relational database can be queried by any query language you want if you just create it. A form of SQL can be used to query anything if you make your own extensions to it.
The relational database, like pure functional programming, is not just an idea a programmer just thought of, it's based on mathematical theory.
SQL was not supposed to be the production query language when it was first created, it was a quick hack. The IBM product managers decided it was good enough and released it as is. "Select *" is not supposed to be there. It's supposed to be better defined.
ANSI standards are just that, which is why there is ANSI C for example. When a product is ANSI standard you know it is supposed to comply with the standard. The licensing fees are to pay for the overhead of running the ANSI commitees, which are numerous and expensive.
The reason the SQL/Relational database has persisted so long is that it is based on sound mathematical theory. Object databases were proposed and created in the 1990s. They all failed because the OO Model is deeply flawed.
The relational database is essential for many business requirements. It guarantees commits are consistent, that data is consistent. It is highly flexible and can perform highly complex queries. It is well known and understood. There have been many years spent optimising queries.
People do come up with other database models, such as tree based, but they are really just old inventions that were deprecated because they are not as good as the relational model.
So you could send Asts or IR over the wire in minified & minimal form. Should simplify the parsing on the DB side.
Not sure about the benefits overall
APL used to be used for databases, in fact one of the use cases is still databases. I would have actually preffered to use it instead of SQL at uni... tho I'm not planning on doing DB related work so maybe it shouldn't really count. A friend of mine who is a chemical engineer but ended up as a sales manager& basic computer guy loves SQL unironically.
SQL facilitates backup and redundancy and disaster recovery using its transaction log, we can call it a leaky abstraction. We can criticise SQL as an API to safely manage transactions but referring to it as an SDK is wrong. The article neglects all database operating aspects, database operating is usually the important part when you consider which database you use, the query language is an after-thought and that's how it should remain. You want your DB as strong as a cockroach, not your API.
barmaby has all the exp and skills he should just make the future of database aka BARNABYQL
As a beginner that has started out in C: I can see the value in starting with C, but I gave up. C is a very difficult language to grasp, if you have no idea of programming concepts.
whaaaaaatttttt😂😂
4:48 is Deno ugly too?
One must imagine Sisyphus indexed
I do think that structuring around the SQL syntax does make an ORM different in it's constraints from a dedicated api/library solution, in theory, but it is maybe also an open problem. Still it is pretty annoying that even using sqlite in rust requires me to install DLLs on windows, so I generally feel like it would be nice if basic database stuff would be included by now in any ecosystem - and also ORMs are limited trying to transform their ideas to different SQL syntaxes as well, and that again enforces the feeling that there is something to innovate about
The problem with ORM is not SQL but the object model you're trying to coerce relational data into.
ORMs are make your life easier for most simple queries, but they occasionally can't be used for less typical queries. in those situations, i usually drop to using SQL syntax (a lot of ORMs allow this on the same database connection, which is nice for transactions). that's the nice thing. you don't have to choose between ORMs and SQL syntax. just use both
@@aoeuable true. but i do have a small annoyance: support for inheritance is usually very limited in a database
@@xybersurfer Good! Even OOP folks by now say that composition >> inheritance. And as far as random hierarchies are concerned those are all equally slightly awkward in a relational model whereas with OOP it's either trivial or, if it doesn't fit 100%, completely convoluted.
@@aoeuable not sure if that is actually true to be honest, and not just a prejudice against what people perceive as "OOP", given ORMs can just as well result in unstructured data, or record style data in a functional context. Just as much as the "simple queries are fine in an orm" argument kind of falls apart, as most really complex queries are usually optimized statements to get around specific db engine decisions, and might be actually easier to reason in the builder pattern most ORMs employ, than the select you end up using. In fact, in an orm you might be able to keep both around, and switch between implementations, without touching the business code in front. It really depends which ORMs we are talking about.
I like EdgeDB for an RDBMS that uses a language that's more programmer friendly than SQL, migrates itself and does it well, and has a better schema definition format.
Counterpoint: Cypher for neo4j. It makes a LOT of sense, and the only downside is that all the existing integrations for things assume you're using SQL.