5 ms·

Some overlap here with some of my favorite essays on why SQL is lacking: https://www.scattered-thoughts.net/writing/against-sql https://www.scattered-thoughts.

Some overlap here with some of my favorite essays on why SQL is lacking: https://www.scattered-thoughts.net/writing/against-sql https://www.scattered-thoughts.net/writing/against-sql That particular post ends with a wish-list of items so it's the most similar to the OP. But there are others on the site that I quite enjoy (click on the home icon and search "SQL" on the page). My personal take is that SQL will continue to reign for a long time because of the how monumental the task of replacing it is due to the inherent complexity of databases. LLMs make this worse because they're really good at translating prose to SQL. Now that it matters less how annoying SQL is to programmers, SQL will become more like assembly over time: something mostly computers write because it's complicated for humans to deal with directly. This is deeply ironic given that SQL was ostensibly designed to read like prose, i.e. to be easy for humans.

ljm 1mo ago
Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult. Most people reach out towards an ORM or query building engine and otherwise don't really go far beyond the basic CRUD, joins, and some simple aggregations with groups. Since they try to be DB agnostic you'll rarely get an adaptor over CTEs or window functions or partitioning. An LLM is great at exposing what a database is capable of doing with SQL and might even manage to navigate the most poorly designed of schemas. And it might even manage to design one to an acceptable standard if it has enough domain knowledge in its context.
- vjvjvjvjghv 1mo ago
  The problem in my view is that there aren't good tools to debug advanced SQL stuff within the context of the whole system which is usually written in a higher level language. I just spent a few weeks modifying some code where the original dev put a lot of logic into stored procedures. That's in principle fine but it's really hard to figure the actual business logic when it's spread out over C# and then also SQL. It doesn't help that the SQL code looks like FORTRAN code from 1985. Personally I think we need ORMs that allow expressing advanced SQL stuff with other high level languages. Or even better: The ORM detects where advanced SQL makes sense and uses it.
  - ljm 1mo ago
    If I had to pick, I'd try to make the ORM redundant by making 'lower level' SQL easier to deploy rather than depending on sending strings of SQL queries and mutations over the wire. I haven't worked in a single setup where raw SQL has been encouraged, because it always requires DB migrations and not all of them are safe. Nobody dares touch the DB server's resources by setting up stored procedures, materialised views, etc. etc. and instead people are blowing money on Redis instances and caching and shit. I don't have an answer to this but I've hit a lot of issues in my career where I think, "this could have been solved months ago by pivoting a couple of tables or creating a new function." You have been able to 'script' the DB for decades but you lose a lot of what you gain from the traditional SDLC at the app layer.
    - grebc 1mo ago
      Dapper in .net is fantastic to deal with raw sql, to the point I think I’m delusional because it’s so damn simple to send outrageous queries to the database and have those multiple mixed results turned into objects very simply. I’ve never had an issue of raw SQL requiring migrations? Unless you’re talking of changing database engine? In which case I think it’s a bit of folly to imagine changing the database engine will not mean changes to your stack higher up the chain.
    - bruce511 1mo ago
      For me, one of the primary benefits of ORMs is that they can parameterize requests which then prevents SQL injection attacks. Passing raw SQL to the database needs very careful attention to the dynamic parts, and it's too easy for user-generated data to be included. Yes, it's possible to pass user generated text through a sanitizer but now you just have an arms race between the sanitizer and "clever" users.
      - jedwards1211 1mo ago
        It’s not that hard to pass values as query parameters of a manually written query. With inferior databases that don’t support array parameters it’s a bit more work to construct the correct number of $ parameters in the query, but still not that hard
- sgarland 1mo ago
  > Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult. Isn't that true of most languages? SQL has pretty simple syntax; I think the only reason it's sometimes seen as arcane is that fewer and fewer people bother to learn it.
- catlifeonmars 1mo ago
  > Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult. This is kind of a hot take. Most devs I know know PostGreSQL well. They know how to write complex queries with CTAS, joins, etc, know how to create indexes, views, and add user defined functions.
- alliao 1mo ago
  dba here and I really don't get why SQL is so feared... I get that it requires very different way to think about data but it is quite simple in terms of you tell it what to do, and if it does it badly you probably told it wrong so just try something different...
  - mike_hearn 1mo ago
    Because: 1. SQL isn't composable (you can't assign fragments to variables except for CTEs) so you can't easily test out subparts and build them up incrementally without just copy/pasting stuff around. 2. Joins are an unnatural way to dereference pointers. 3. SQL is more than SELECT. Once you get into updates you encounter lots of scary edge cases and traps. How many engineers really understand isolation levels? Why doesn't skipping the column list in an INSERT substitute nulls for the nullable columns that aren't provided? What changes can you make to a schema that are 'safe' for your environment (won't take table locks)? What locks are being taken by the RDBMS behind your back - sometimes it matters! 4. Site outages caused by optimizer plan shifts are scary because people don't feel in control. Good databases have features to ameliorate these issues, but most people's experience is of databases that are merely OK and not good.
    - euroderf 1mo ago
      > 2. Joins are an unnatural way to dereference pointers. There's gotta be a simple & clear alternative to this obstruction. Maybe it just hasn't been invented yet.
      - mike_hearn 1mo ago
        Most query languages do fix that. GraphQL is one example.
        jedwards1211 1mo ago
        GraphQL joins are entirely up to the underlying resolvers, it has no syntax for expressing different kinds of joins or filter conditions on values from multiple joined relations
reitzensteinm 1mo ago
LLMs are also very good at writing code for newly invented languages, especially if they can execute it and iterate. I strongly believe the barrier to switch languages is lowered in a post LLM world. Ten years ago I was at a startup where we used Datomic, and it was okay, but six months in the sales team was like “ok how do I run SQL queries so I can triage leads”. We had no answer of course. Today it would simply be: type what you want in natural language and we’ll generate the query with Claude. I just tried one representative query from that startup against a hypothetical datalog query tool in Rust and it did just fine.
- scythmic_waves 1mo ago
  I agree that an LLM could also generate the code for a new query language. But my point is that fewer people will attempt to author a new query language in the first place because an LLM will be writing the queries either way. So the effort would be less impactful.
  - reitzensteinm 1mo ago
    You're right, I was talking past you. I have an implicit belief that SQL isn't the most effective low level language we could have and LLMs will free us up to explore that space, similar to asm.js -> WASM. But I'm open to being wrong about that.
    - alliao 1mo ago
      I think it strikes a good balance between still human-readable and low-ish.. any lower I suspect you'd need to dedicate a lot more documentation else where..
  - huahaiy 1mo ago
    Datalog exists and there are so many implementations.
- huahaiy 1mo ago
  Agree. Especially when Datalog queries are simpler to write and faster to run than SQL, there is a strong reason to at least try it.
Rendello 1mo ago
The end of that article ends with a pertinent quote [1] by Michael Stonebraker [2]. I've included more of the original quote here: > My biggest complaint about System R is that the team never stopped to clean up SQL. [...] All the annoying features of the language have endured to this day. SQL will be the COBOL of 2020, a language we are stuck with that everybody will complain about. > My second biggest complaint is that System R used a subroutine call interface (now ODBC) to couple a client application to the DBMS. I consider ODBC among the worst interfaces on the planet. To issue a single query, one has to open a data base, open a cursor, bind it to a query and then issue individual fetches for data records. It takes a page of fairly inscrutable code just to run one query. [...] Only recently with the advent of Linq and Ruby on Rails are we seeing a resurgence of cleaner language-specific enbeddings (sic). 1. http://www.redbook.io/ch2-importantdbms.html http://www.redbook.io/ch2-importantdbms.html 2. https://en.wikipedia.org/wiki/Michael_Stonebraker https://en.wikipedia.org/wiki/Michael_Stonebraker
- wredcoll 1mo ago
  I was with him until he mentioned Ruby on Rails, is he talking about something other than the fairly ugly activerecord pattern?
  - deleted 1mo ago
    [deleted]

ljm 1mo ago

Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult. Most people reach out towards an ORM or query building engine and otherwise don't really go far beyond the basic CRUD, joins, and some simple aggregations with groups. Since they try to be DB agnostic you'll rarely get an adaptor over CTEs or window functions or partitioning. An LLM is great at exposing what a database is capable of doing with SQL and might even manage to navigate the most poorly designed of schemas. And it might even manage to design one to an acceptable standard if it has enough domain knowledge in its context.

vjvjvjvjghv 1mo ago
The problem in my view is that there aren't good tools to debug advanced SQL stuff within the context of the whole system which is usually written in a higher level language. I just spent a few weeks modifying some code where the original dev put a lot of logic into stored procedures. That's in principle fine but it's really hard to figure the actual business logic when it's spread out over C# and then also SQL. It doesn't help that the SQL code looks like FORTRAN code from 1985. Personally I think we need ORMs that allow expressing advanced SQL stuff with other high level languages. Or even better: The ORM detects where advanced SQL makes sense and uses it.
- ljm 1mo ago
  If I had to pick, I'd try to make the ORM redundant by making 'lower level' SQL easier to deploy rather than depending on sending strings of SQL queries and mutations over the wire. I haven't worked in a single setup where raw SQL has been encouraged, because it always requires DB migrations and not all of them are safe. Nobody dares touch the DB server's resources by setting up stored procedures, materialised views, etc. etc. and instead people are blowing money on Redis instances and caching and shit. I don't have an answer to this but I've hit a lot of issues in my career where I think, "this could have been solved months ago by pivoting a couple of tables or creating a new function." You have been able to 'script' the DB for decades but you lose a lot of what you gain from the traditional SDLC at the app layer.
  - grebc 1mo ago
    Dapper in .net is fantastic to deal with raw sql, to the point I think I’m delusional because it’s so damn simple to send outrageous queries to the database and have those multiple mixed results turned into objects very simply. I’ve never had an issue of raw SQL requiring migrations? Unless you’re talking of changing database engine? In which case I think it’s a bit of folly to imagine changing the database engine will not mean changes to your stack higher up the chain.
  - bruce511 1mo ago
    For me, one of the primary benefits of ORMs is that they can parameterize requests which then prevents SQL injection attacks. Passing raw SQL to the database needs very careful attention to the dynamic parts, and it's too easy for user-generated data to be included. Yes, it's possible to pass user generated text through a sanitizer but now you just have an arms race between the sanitizer and "clever" users.
    - jedwards1211 1mo ago
      It’s not that hard to pass values as query parameters of a manually written query. With inferior databases that don’t support array parameters it’s a bit more work to construct the correct number of $ parameters in the query, but still not that hard
sgarland 1mo ago
> Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult. Isn't that true of most languages? SQL has pretty simple syntax; I think the only reason it's sometimes seen as arcane is that fewer and fewer people bother to learn it.
catlifeonmars 1mo ago
> Rawdogging SQL when you're not a seasoned DB administrator basically makes an arcane art look occult. This is kind of a hot take. Most devs I know know PostGreSQL well. They know how to write complex queries with CTAS, joins, etc, know how to create indexes, views, and add user defined functions.
alliao 1mo ago
dba here and I really don't get why SQL is so feared... I get that it requires very different way to think about data but it is quite simple in terms of you tell it what to do, and if it does it badly you probably told it wrong so just try something different...
- mike_hearn 1mo ago
  Because: 1. SQL isn't composable (you can't assign fragments to variables except for CTEs) so you can't easily test out subparts and build them up incrementally without just copy/pasting stuff around. 2. Joins are an unnatural way to dereference pointers. 3. SQL is more than SELECT. Once you get into updates you encounter lots of scary edge cases and traps. How many engineers really understand isolation levels? Why doesn't skipping the column list in an INSERT substitute nulls for the nullable columns that aren't provided? What changes can you make to a schema that are 'safe' for your environment (won't take table locks)? What locks are being taken by the RDBMS behind your back - sometimes it matters! 4. Site outages caused by optimizer plan shifts are scary because people don't feel in control. Good databases have features to ameliorate these issues, but most people's experience is of databases that are merely OK and not good.
  - euroderf 1mo ago
    > 2. Joins are an unnatural way to dereference pointers. There's gotta be a simple & clear alternative to this obstruction. Maybe it just hasn't been invented yet.
    - mike_hearn 1mo ago
      Most query languages do fix that. GraphQL is one example.
      - jedwards1211 1mo ago
        GraphQL joins are entirely up to the underlying resolvers, it has no syntax for expressing different kinds of joins or filter conditions on values from multiple joined relations

reitzensteinm 1mo ago

LLMs are also very good at writing code for newly invented languages, especially if they can execute it and iterate. I strongly believe the barrier to switch languages is lowered in a post LLM world. Ten years ago I was at a startup where we used Datomic, and it was okay, but six months in the sales team was like “ok how do I run SQL queries so I can triage leads”. We had no answer of course. Today it would simply be: type what you want in natural language and we’ll generate the query with Claude. I just tried one representative query from that startup against a hypothetical datalog query tool in Rust and it did just fine.

scythmic_waves 1mo ago
I agree that an LLM could also generate the code for a new query language. But my point is that fewer people will attempt to author a new query language in the first place because an LLM will be writing the queries either way. So the effort would be less impactful.
- reitzensteinm 1mo ago
  You're right, I was talking past you. I have an implicit belief that SQL isn't the most effective low level language we could have and LLMs will free us up to explore that space, similar to asm.js -> WASM. But I'm open to being wrong about that.
  - alliao 1mo ago
    I think it strikes a good balance between still human-readable and low-ish.. any lower I suspect you'd need to dedicate a lot more documentation else where..
- huahaiy 1mo ago
  Datalog exists and there are so many implementations.
huahaiy 1mo ago
Agree. Especially when Datalog queries are simpler to write and faster to run than SQL, there is a strong reason to at least try it.

Rendello 1mo ago

The end of that article ends with a pertinent quote [1] by Michael Stonebraker [2]. I've included more of the original quote here: > My biggest complaint about System R is that the team never stopped to clean up SQL. [...] All the annoying features of the language have endured to this day. SQL will be the COBOL of 2020, a language we are stuck with that everybody will complain about. > My second biggest complaint is that System R used a subroutine call interface (now ODBC) to couple a client application to the DBMS. I consider ODBC among the worst interfaces on the planet. To issue a single query, one has to open a data base, open a cursor, bind it to a query and then issue individual fetches for data records. It takes a page of fairly inscrutable code just to run one query. [...] Only recently with the advent of Linq and Ruby on Rails are we seeing a resurgence of cleaner language-specific enbeddings (sic). 1. http://www.redbook.io/ch2-importantdbms.html http://www.redbook.io/ch2-importantdbms.html 2. https://en.wikipedia.org/wiki/Michael_Stonebraker https://en.wikipedia.org/wiki/Michael_Stonebraker

wredcoll 1mo ago
I was with him until he mentioned Ruby on Rails, is he talking about something other than the fairly ugly activerecord pattern?
- deleted 1mo ago
  [deleted]