6 ms·

SQL has many problems: "from" should come first to help auto-complete; null handling is complicated; the syntax in general is weird. Compatibility is a problem

• noduerme 4y ago
  These are all really great complaints; and yet I still prefer writing plain SQL to using ORMs and their nifty tricks. I think the main reason is that I don't ever want to have a lot of SQL logic in my code. Short queries (say, 10 lines or less) are okay, if they're one-off and pertain to the function's logic and are never repeated by other pieces of code. But writing complex queries through an ORM layer means they're going to be in code -- and harder-to-maintain -- than if they were where they should be: In views, functions and procedures. ORMs also obscure the execution plan and make optimization more difficult, which in production isn't something I really want to worry about. Writing SQL is like uploading a program; just like a shader to a GPU. And if the network stopped being the bottleneck, the DB server's load would become the problem. Just like with a GPU, you want to upload your programs and data as infrequently as possible. But unlike a GPU, you have full flexibility to permanently store your logic right there in your SQL DB. If it's well-documented, it's much more streamlined to write views and views-on-views, than to keep all that DB logic in your code. I don't find that it becomes a mess at all, because properly crafted views and procedures strongly encourage forced indexing and checking execution plans when you refactor them; as opposed to in-code, ad-hoc queries where it's hard to know if someone ever ran tests to optimize them. Some statements do need to be very large, for efficiency. Others are better being broken up into sub-procedures, temp tables or even roundtrips. A great query is a work of art where each part of it is optimized not just for execution plan but also for read/write, network state and expected load. What I like about SQL is the flexibility to decide which way to go, and prioritize based on how often something will be used and what kind of strain it'll place on each of the layers in the stack. As with anything powerful, there are a million ways it can be abused. But what I've found in the ORM space and also with noSQL solutions is that simplicity of use comes at the price of flexibility and control. [edit] >> null handling is complicated This would probably also be my biggest complaint about the language itself; the existence of IS NULL / IS NOT NULL and both null-safe and -unsafe comparisons gets confusing (and especially painful if you're trying to write a parser). I think it's one of those things like `undefined` vs `null` vs `false` in Javascript where, on second thought, it's just too late to fix it without breaking half a billion websites.
  • dinedal 4y ago
    > I don't find that it becomes a mess at all, because properly crafted views and procedures strongly encourage forced indexing and checking execution plans when you refactor them; as opposed to in-code, ad-hoc queries where it's hard to know if someone ever ran tests to optimize them. I have to provide caution here, when you create views and stored procedures, I've always found it a mess to maintain: 1) VCS for stored procedures and views is usually non existent. Good luck understanding how these change over time and who changed them. 2) Deploying application logic alongside these systems is very painful, because unless you've versioned your views/stored procedures, when you make a breaking change you need to stop the entire application, make the change in the DB, and restart the entire application again on the new version. Zero-downtime deploys would require versioned views/stored procedures. 3) It quickly becomes a challenge to answer the question "what happens when I do X?" reliably, where X is insert, delete, or even select a row. Once you have complex enough stored procedures, modifying the data changes things in unexpected or unintended ways. A dev unaware of how the system works might assume that inserting a row simply inserts a row, but instead due to stored procedures hidden from application logic, it might cascade into inserting rows elsewhere, deleting rows, or modifying other rows in other tables. Discovering these issues without knowing that they could exist is often done around midnight during a production outage, because a new feature was released that did something in the DB that was presumed safe, but wasn't. If the code for the business logic were in one place, the application, it would been much easier to see what the change would actually do. I understand entirely that performance gains from good DB use are astronomical, but in my humble experience, I've found them to be more trouble in the long run than they are worth. e: spelling
    • barnabee 4y ago
      Re: VCS, I’ve found that with a small amount of setup, tools like Liquibase[0] allow you to maintain functions and views (with “CREATE OR REPLACE” or equivalent) as SQL files in a file system with full VCS, diffing, etc. just like any other code. [0] https://www.liquibase.org/ https://www.liquibase.org/
      • cropcirclbureau 4y ago
• thwd 4y ago
  Some databases like ArangoDB (https://www.arangodb.com/ https://www.arangodb.com/) allow you to use Javascript instead of SQL. However, using a type-unsafe (read weak typing), turing-complete language introduces the usual problems we know and love, such as infinite loops, runtime type errors, exceptions, and the like. Personally, I'm looking forward to a WASM runtime for databases -- so we can run webassembly on the database. This COULD be carefully designed to be statically checked and, possibly, make it really hard to write runaway loops.
  • thomasmg 4y ago
    Many databases support other languages as well (eg. PostgreSQL supports many including Python, by default). One challenge is lack of standardization. (SQL is a weak standard, but at least a standard). Weak typing: what about TypeScript? Slow loops: yes, this is a problem. However, SQL (and even more so, GraphQL) also has a problem of large results / operations spanning too many entries. During development, the number of entries is fine, but not in production. Specially if indexes are missing, this is also a problem in SQL. (Endless loops are actually less of a problem than slow loops: it's easier to detect endless loops during development). To process large results in a good way, often, pagination is needed; best would be keyset pagination. What if a statement returns a "continuation" statement in addition to the result set? If the client wants to get more results (or process more entries), then he would need to run the "continuation" statement. Say a database doesn't provide SQL, but instead a set of low-level APIs (plus a ton of tools). Developers would then (be forced to) write properly modularized, versioned code on top of those APIs.
• hobofan 4y ago
  At least with the substrait[0] project there is some push for standardization & interoperability when it comes to the relation algebra and query plan. I think this can unlock a lot more experimentation when it comes to SQL-alternative "languages" or approaches where plain text languages are just skipped all-together and are instead replaced by good libraries that act directly on query plans. [0]: https://substrait.io/ https://substrait.io/
• ComodoHacker 4y ago
  >SQL is used to reduce network roundtrips. Actually, SQL is used to hide DBMS implementation complexity and diversity. It was a great abstraction when RDBMS tech progressed rapidly. Now it has matured to stability, and it makes sense to try a better language.
  • dist1ll 4y ago
    RDBMS has matured? I feel like I'm seeing a new database pop up every quarter.
    • deleted 4y ago
      [deleted]
    • thesuperbigfrog 4y ago
      >> RDBMS has matured? I feel like I'm seeing a new database pop up every quarter. RDBMS's have been in use for more than 50 years: https://en.wikipedia.org/wiki/Relational_database https://en.wikipedia.org/wiki/Relational_database There are hundreds of implementations some of which are decades old: https://en.wikipedia.org/wiki/List_of_relational_database_management_systems https://en.wikipedia.org/wiki/List_of_relational_database_ma... Oracle and several other companies' (some of which are now defunct or were acquired by other companies) primary products were RDBMS software. New implementations do not indicate lack of maturity but perhaps the wide availability of tools and libraries to build RBDMS's and their utility. Perhaps you are referring to database systems in general and not relational databases?
      • dist1ll 4y ago
        Right my bad, I was thinking of storage engines in general. And of course maturity doesn't mean lack of innovation - which is still happening in the relational space.
  • thomasmg 4y ago
    Yes, it's also used to hide implementation complexity and diversity: the advantage of a standard (even a "weak" one). > SQL is used to reduce network roundtrips. What I mean is: the database has an API that allows the client to specify (almost) arbitrary complex queries and operations. Regular REST APIs don't support this. GraphQL also supports it. The client sending queries to the database is a great way to reduce network roundtrips. Key-value stores that only support get/put/remove/list require a lot more network roundtrips for slightly complicated operations. > it makes sense to try a better language. I argue that the language itself (the syntax) isn't the main problem: part of the problem might be that SQL (even with a better syntax) would _still_ have very similar problems: missing indexes / wrong query plans, missing encapsulation which results in a complex mess of entangled code, things that work fine with small data sets but are extremely slow with large data sets,...
• dspillett 4y ago
  > "from" should come first to help auto-complete I may be in a minority, but I like the projection list being first in simple queries. To help with auto-complete just type the other parts first then fill in the select list after? This falls apart with CTEs though as they have to come first syntactically, so now they exist maybe at the end is the best place for the output list so it is easy to find in a complex statement. > Many developers end up writing huge SQL statements Compossibility can definitely be a significant problem with SQL. There are structures that can help significantly, views, CTEs, etc, but they all have either potential maintainability issues or potential performance issues. Partly, from a performance PoV, this is an issue with the query optimisers. On the maintainability matter I think the syntax for CTEs and sub-queries generally could have been much better thought out – I've seen a few attempts to beautify (or replace) SQL that essentially amount to moving parts around so you can make CTEs & sub-queries look more like functions/procedures in imperative languages (transpiling the result back to SQL for submission to the database after). > Regular programming languages don't require "query optimizers". Because regular programming languages are “lower level” in that regard and require you to do that optimisation yourself. The point of a language like SQL is that you describe the shape and content of what you want and don't have to worry about the most efficient way to retrieve that information. And come to think of it, many regular languages do have optimisers. Think of all the work a compiler is doing or the JIT compiler in JS engines and their ilk – they don't just blindly interpret each line of C/other into a distinct selection of CPU instructions. Consider declarative frameworks for those languages too, like many used for SPAs which deal with DOM manipulation optimisation for you, and the many attempts at no-code or low-code development solutions which seek to abstract such concerns away from the solving of problems that require programming (by doing what SQL attempts: trying to translate what you want, stated as simply as possible, into what is needed to do that efficiently).
  • thom 4y ago
    Existing query optimisers are very weak. You can write multiple semantically equivalent queries but some will be orders of magnitude slower. I would pay good money for a Postgres that I could tell to spend several minutes analysing a query before caching the query plan and reusing it for the future, instead of having to spend programmer time tweaking things.
• Tainnor 4y ago
  > There is little innovation in the database space: there are hundreds of general-purpose programming languages, but very few "database" languages. That's one way to look at it, but another way is to consider that, as a sort of assembly, SQL is as good a language to express relational algebra as any, given that many devs don't usually write a lot of raw SQL themselves. When you look at the ORM and query builder space, there are a lot of different solutions - you don't have to like them (for example, I don't generally like ORMs), but they definitely exist, and they have all sorts of capabilities for type-safety, composability, and so on.
• mike_hock 4y ago
  > There is little innovation in the database space: there are hundreds of general-purpose programming languages, but very few "database" languages. How many of those are "innovative"? How many of those have an ecosystem around them that can be taken seriously? There's maybe one innovative language per decade. Minor improvements to an existing language (with a load of unnecessary syntax changes) don't make up for programming in a barren ecosystem. And a bunch of wrappers around C libraries don't make an ecosystem, they don't allow the language to materialize any actual new paradigms and just result in a C dialect.
  • thomasmg 4y ago
    > How many of those are "innovative"? In programming languages we have imperative and functional languages, type-safe / untyped, memory managed / unmanaged, low-level vs high level, interpreted / JIT / ahead-of-time. So much innovation! And SQL is almost 50 years old, and basically still the same (plus adding features). And now maybe GraphQL. Very little innovation.
• thesnide 4y ago
  Well, I'd argue that most of the time SQL is treated as a glorified CSV. No-one really looks at it as a real language, which it actually is. As for optimization, I'd say that it might be too efficient for its own good. As now one can have its ORM generate a huge request and complain to the DB if the SQL is slow. If you write in any other language, you usually don't complain that the processor is badly optimizing the asm execution. But you blame the compiler. ORM on the other hand are mostly never blamed, just pitied if they fail. Having Wasm inside the RDBMS might be a much better approach. But then it will be up to the wasm program to be efficient.
• adamzochowski 4y ago
  > SQL has many problems: > "from" should come first to help auto-complete; Agreed > null handling is complicated; Mostly the issue is that null in dbs has different semantics than all programming languages. I wish programming languages used the db style of handling nulls. In dbs, null means that the value is unknown and can't be reasoned about. DB uses nulls in how people reason arguments: "Joe's age is unknown, Jane is 25 years old. Are they of same age? We can't say if their age are equal, or unequal, because we don't know." > the syntax in general is weird. I find syntax to be like any time switching a language. Python is weird to C++. Atleast with SQL, I find usage docs more understandable than many programming languages. > Compatibility is a problem This sounds like a complaint that two different programming languages are not providing same features. Or two operating systems have different UI toolkits (and different menu conventions, like where should the 'preferences' be under). I wouldn't expect sqlite to be Oracle / MSSQL / PGSQL compatible.
  • onlyrealcuzzo 4y ago
    > In dbs, null means that the value is unknown and can't be reasoned about. Except it can also mean it's `null` in that the field is optional and the record doesn't have it.
    • adamzochowski 4y ago
      An optional value just means that the value is unknown. For example, it is optional to enter the colour of the car I own. So my car colour is null. The database can't confirm if my car is Red or isn't Red, because it is an unknown value.
    • tremon 4y ago
      Not really. A null value in a certain column does not indicate that the field is optional; instead, the table definition having a nullable column indicates that the field is optional. A null value in a nullable column indicates only that the value is unknown, nothing else. This is database modeling 101: A table ("relation") describes one entity. The attributes of the entity are reflected in the table column set, and each table row represents a different instance of said entity. Marking a column as nullable means that not knowing that attribute is a recoverable situation (for example, because it is only stored for informational purposes and not for processing). If you're using the same table to store multiple entities, using nullable columns to indicate if a row represents entity A or entity B, you're doing it wrong.
• tonnydourado 4y ago
  > Regular programming languages don't require "query optimizers". Isn't that what llvm does? Or GCC? Or a JIT? > Possibly it would be better to send small programs written in a "better" language (something like Lua) to the server? I'd certainly like something better than SQL, but going for an imperative, Turing complete language seems like the opposite direction of where things should be going in this scenario.
  • rtpg 4y ago
    Most tools allow you to jump down to low level stuff. Isn’t it weird that I can’t just write the query plan for Postgres? My theory is people would understand indexes and the like way more if they were writing up the lookups
    • Nihilartikel 4y ago
      That's what I want too. To make a loose metaphor, if SQL is like high level opengl, then I want the low level Vulkan and shader APIs for hand optimization. I would like this especially for Apache Spark sql, since a bad plan that isn't aware of the data shape can blow gigabytes over the network unnecessarily.
      • tormeh 4y ago
        Spark has datasets, which I think lets you do this, although it’s only supported in Java and Scala, not Python.
    • tonnydourado 4y ago
      > Most tools allow you to jump down to low level stuff Eeehhh, kinda. Sure, a compiler will let you just write some assembly by hand, but it won't let you write a optimization step, or even directly configure it. You have to tweak your code or the compiler flag to give the right hints so the compiler generates the binary code you want. Doesn't look much different than a query planner.
• vbezhenar 4y ago
  Here're little things that would make my life better when I'm generating SQL queries: 1. Support trailing commas. 2. Add operator `is` which works like `==` but for nulls it returns `true`. Could also remove automatic type casting, I'd be happy about that, never liked it. 3. Support trailing `or`-s and `and`-s. So query could look like select a, b, from t where (a.x is :x and a.y is :y and) or (b.x is :x and) or
  • ailef 4y ago
    What is the reason for trailing commas and ands/ors?
    • thomasmg 4y ago
      So that you can generate SQL more easily, and (for hand-written statements) so that you can re-order lines more easily. Many programming languages support trailing commas.
  • zestyping 4y ago
    Trailing "and"s and "or"s looks so wrong to me; it reads so strongly like a syntax error. How about Boolean functions instead, like so: select a, b, from t where any( all(a.x is :x, a.y is :y,), b.x is :x, ) This way you get the uniformity you want from trailing commas, without the syntax looking quite so weird.
    • vbezhenar 4y ago
      That's even better.
  • ttfkam 4y ago
    As an aside, avoid OR as much as possible in your WHERE clauses. Adjust your relations if necessary. ORs can really blow the planner up and slow things down. ANDs are reductive wrt indexes. ORs make multiple passes on the indexes necessary. It's not always possible to avoid, and it makes perfect sense sometimes, but it'd be better if folks didn't lean into OR so much.
• bavell 4y ago
  I've been playing around with EdgeDB lately and it addresses a lot of the concerns you laid out. Might be worth a look.
• knighthack 4y ago
  > There is little innovation in the database space Have you even used Jetbrains' IDE's DataGrip?
• deleted 4y ago
  [deleted]
• databasethrow7 4y ago
  > There is little innovation in the database space: there are hundreds of general-purpose programming languages, but very few "database" languages. What we have is generally good enough for the purposes of set manipulation. SQL is not a programming language, and should not be approached as a programming language. It's a way to express relational algebra. This is similar to complaining about any other system of algebra having issues... and wanting to come up with a New Way. We don't really need a new way to describe linear algebra; what we have works. And it may seem unintelligible and a mess from the outside, but if you take the time to learn it, you'll understand why and how it's laid out the way it is, and learn to get over it. The other point being, there are a set of operations you can do on sets. SQL is as low level as you're going to get in regards to that. Any new language would either: change the labeling of the underlying operations (e.g. changing joins as a label for Cartesian products to some other label), abstract away the low-level operations into something completely else (foolish, in my view), or tweak it in minor ways to suit the preferences of the individual (again, not worth the effort in my view, just learn the syntax). All of these are inefficient; and all endeavors to make it more efficient have failed (as far as I can tell) -- primarily because the people working on these projects (such as your self) do not have a sufficient maturity in this space to understand why things are done the way they are, i.e. "Chesterton's Fence." > Many developers end up writing huge SQL statements (one statement that is hundreds of lines). You wouldn't do that in a "proper" programming language; but in languages like SQL (or maybe Excel) this will happen. Programming language and set manipulation languages are incomparable. The first is a language for describing procedural instructions and the last is a way to describe set transformations. It's not wise to apply the same set of standards to both. It's like complaining a proof is tediously long, when the actual underlying operations are simple -- you're missing the point. SQL has to be long and detailed -- when you want to be sure the data you're receiving is exactly the way you specified it to be (i.e. no compiler shenanigans where it turns your rough intent into concrete instructions). However, I will concede that I've seen some monstrous SQL in the wild... mostly written by people who don't really know SQL... and which could've been greatly shortened by knowing the little tricks that are database-specific (and similarly, I've seen enough people request a dataset to do processing on the server-side in herculean efforts, which could've been done faster and written quicker in the database). > Another problem is proper encapsulation. Views can be used, but often developers have access to all tables, and if you have many components this becomes a mess. Regular programming languages have module system and good encapsulation. I don't understand. Permissions can be tweaked however which way you want. Am I missing something? > SQL statements often don't use indexes, for one reason or the other. With regular programming languages, it is harder to end up in this position (as you have to define the API properly: encapsulation). Regular programming languages don't require "query optimizers". This is a trivial problem; but if you don't know why it happens, I can understand being befuddled by it. Query optimizers are involved so your access to the underlying data -- and the manipulations on it -- are done in the least costly fashion (i.e. in a rough sense to reduce the algorithmic complexity of your SQL to its least possible complexity) in regards to the set of hardware your cluster is running on, and the various access statistics involved. Regular programming languages do not have "query optimizers" -- but various libraries do have their own optimizations to reach the same end (as do compilers... compilers are nothing but optimizations upon optimizations in the same vein). > SQL is used to reduce network roundtrips. Clients send SQL (or GraphQL) statements to the server, which are actually small programs. Those programs are executed on the server, and the response is sent to the client. (Stored procedures and for GraphQL persisted queries can be used - but that's often happening afterwards.) Possibly it would be better to send small programs written in a "better" language (something like Lua) to the server? It would be very inefficient for general purpose. For most cases, the query optimizer can take your SQL and bring back your result sets in the most efficient way possible (in relation to the SQL you've written). If you want to do the underlying operations yourself, instead of relying on the query optimizer, then you can use a compiled language like C and write your own extensions -- though it would take much more effort to do it properly than simply learning how your database works, and how to work with the query optimizer (rather than against).