5 ms·

>My opinion is that SQL should be for computers to write. This would put it firmly in the same category as LLVM IR. This is nuts. SQL is a high level language

>My opinion is that SQL should be for computers to write. This would put it firmly in the same category as LLVM IR. This is nuts. SQL is a high level language. It's higher level than python or rust. It's basically a declarative statement that's almost english-like and it's specifically DESIGNED to be more readable and easier to use by humans. It compiles down into many procedures that don't easily map to the SQL statement itself. You can't get any higher level than SQL. The issue here is that the database exists at the bottleneck of web dev. It's where state mutation happens and it's essentially usually the slowest part of the pipeline in a computer. Yet instead of having fine grained low level control over this part of the pipeline, we have a high level language on top of it. So if we want to optimize this part of the pipeline we have to HACK the query. We have to make the abstraction layer leaky in the API itself with EXPLAIN. We can't do direct optimizations because SQL is so high level. It is a bit of a problem but the industry is so entrenched in SQL that it's actually 10x more efficient to just use it then to develop an API that's more appropriate for this level. SQL is tech debt we are sort of stuck with. The ideal API would be one that is both high level but allows fine grained control... but we don't have one yet. To use machine learning analogies. SQL is a local optima. There's a much more optimal language somewhere in this space but we are stuck in the local optima and it's likely we will never end up finding the actual optimal api. In short SQL is the furthest thing from LLVM IR. It's crazy. You cannot treat it as the same thing. If you do there are huge problems. The problem with rust query and the problem with ORMs in general is that the API for these libraries are in itself high level. They are HIGH level abstractions ON TOP of high level abstractions. You want to optimize a query now? Well you need to hack the first high level abstraction in such a way that it hacks the second high level abstraction such that it produces optimized compiled procedures. That's the problem here. All this ORM stuff is just programmer OCD. We don't want to do meta programming where we have another language living as a string in our web app. We want everything fully integrated so we create an abstraction in attempt to get rid of an abstraction that was intended to be an abstraction in itself. It's aesthetics and the aesthetics actually makes life harder.

vlovich123 2y ago
> You can't get any higher level than SQL. Can’t you? Wouldn’t higher level be a goal oriented natural language like “what’s the most popular music file in the database” and an AI agent just figured out how to give you the answer?
- ninetyninenine 2y ago
  Well. You know what I mean. AI is also unreliable. Place chatGPT over a database and it's going to start lying about what is in it eventually. That being said my statement likely isn't strictly true even when you account for ML. I'm sure there are even higher level languages that are not probability based and thus deterministic with expected results. But in terms of a software developers average experience, SQL is the highest level you can get without getting into the minefield that is LLMs.
  - threeseed 2y ago
    > SQL is the highest level you can get without getting into the minefield that is LLMs You can use NLPs to safely write SQL in more business language. And of course there are GUIs.
  - kelnos 2y ago
    > SQL is the highest level you can get without getting into the minefield that is LLMs That's trivially false. I'm currently playing with sea-orm[0], and I can write things like: let db = Database::connect(...).await?; let yesterday: chrono::DateTime<chrono::Utc> = ...; let foos: Vec<foos::Model> = Foos::find() .filter(foos::Column::Status.eq(FooStatus::Frobbed)) .filter(foos::Column::UpdatedAt.gt(yesterday)) .left_join(Bars) .also_select(Bars) .all(&db) .await?; How is that not higher-level than SQL? [0] https://crates.io/crates/sea-orm https://crates.io/crates/sea-orm
    - cwbriscoe 2y ago
      Why not just write SQL, it would be much more readable.
Yoric 2y ago
That's a good point. It would be very nice to have a LLVM IR-style language for database queries, though. As a language, SQL is... well, a little behind times. ORMs explore lots of interesting design choices, but as you mention, by piling up high-level decisions on top of something that, by many aspects, are even higher decisions. I wonder what an LLVM IR-like would look like in this space.
- ninetyninenine 2y ago
  I agree there should be a common api. The sheer number of dialects with SQL contributes to a lot of issues with ORMs. But SQL is definitely not something designed for computers to only write or compile another language into (which is what the OP is promoting).
  - Yoric 2y ago
    Yeah. I wonder if something like GlueSQL or the IndexedDB layer of most browsers could be used as such an API.
josephg 2y ago
I hear you and I 70% agree. I’ve also been using Prisma for a project recently and I’ve been finding it quite good. It layers several useful features on top of sql, like schema management (checked in to git), code gen (incl typescript types), db connection management, serialization / deserialization from JavaScript objects to sql rows and back, and it lets you follow foreign keys. If you want, you can also easily just make raw sql queries. That works fine too. Sql is a very old programming language, and it’s showing its age. The design is wacky - does GROUP BY go before or after ORDER BY in the query? Every command has custom, seemingly random words which are actually parameters. They are placed at totally arbitrary, hard to remember spots in the command. And what, we make our program output a string that gets immediately parsed by the sql server? What a horrible, stupid way to do RPC. It also gives terrible error messages if you get it wrong. And it’s awful to learn and debug. All of that is a huge pity, because modern databases can do so, so much. Doing logic inside the database can often be an order of magnitude faster than doing it in client code. SQL is a fantastic way to represent your data. One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql.
- ninetyninenine 2y ago
  >One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql. The problem here is these low level optimizations that the database is doing is freaking complicated. There's a lot of intelligence going on in the background and it's different depending on the database. We can solve it with a low level common IR but that IR will be extremely complex. And that IR will likely give different performance profiles for the same code on different databases. I can see this being solved for one database. But not across all databases and a common api. We sort of do have a common api and we paid for it with the trade off of it being a easier high level language on top of the part of the web that needs to be most optimized.
9rx 2y ago
> We don't want to do meta programming where we have another language living as a string in our web app. Strings are fine in theory, but the trouble with SQL is that, while too high-level in all the wrong places, it is also too low-level in all the wrong places. Developers want to be able to do things like define the shape of the data beyond tables and compose queries, which SQL fails hard at. In order to accomplish what developers need, you either end up extending SQL into a new language, or you can use the programming constructs you already have, treating SQL as a compiler target, where necessary. The latter is considerably easier to implement.
jimbokun 2y ago
I think the sweet spot is a query builder that just ensures you emit syntactically valid SQL to send to the database. So there are no surprises about what the database is executing. But protects against typos and security issues from building SQL through string concatenation.
- mplanchard 2y ago
  sqlx (the rust library) does this, while also validating your sql statement, input types, and output types against your local db. For me it doesn’t get much better.
bob1029 2y ago
Every time I've found myself frustrated with a SQL query, there was some horrible misstep in how the underlying schema was designed. Having the wisdom to craft a high quality schema can be the biggest unlock in the whole enterprise. If the tables, columns & relations are ~1:1 with the actual business (i.e., you went and talked to real, live stakeholders at design time), then it should be deeply intuitive for a non-technical domain expert to interact with. Often, you don't get a chance to redo the whole thing, but that doesn't mean you can't still design an ideal schema and then shim it with views, CTEs, replicas, ETL, etc. SQL is a domain specific language. It would be like complaining about how horrible it is to write Lua scripts. The criticism is baseless without understanding the underlying schema/bindings.
habitue 2y ago
I see your point for one part, which is that `select([id]).from(Users)` is maybe in the best case as readable as `select id from users`, and in many common cases it'll be less readable than sql. Plus, it's not quite sql, you'll have to look up how to do things a lot of times. But we aren't just talking about SQL the high level language that you might execute when you are poking the data live. In a web app dynamically constructing sql, we have multiple issues: 1. Deserialization: we need to know what the query returns and plug it into data structures we want to use in our program 2. SQL is dynamically typed, and if you construct queries dynamically you're even more dynamically up a creek. Maybe even "sql injection vulnerability" up a creek. 1 and 2 are solved by strongly typed query builders like this: the type system has a proof that the ways you will construct queries in your program actually result in valid sql queries and that they return the right data shape. It's actually solving a real problem
- cryptonector 2y ago
  (1) is premised on (2), which is not right. Some SQL RDBMSes are statically typed (e.g., PostgreSQL) while others are dynamically typed (e.g., SQLite3). If you're building on SQLite3 then you're going to have to do run-time type checks anyways (even if you somehow disallow access to the DB that bypasses your application). So maybe don't build on SQLite3. This is a shame. We really do need a SQLite3-like DB that is statically typed.
  - Hytak 2y ago
    sqlite has `STRICT` tables that are statically typed
zamalek 2y ago
I call it "SQL fear", and I agree: people need to get over it. We used EFCore at my previous job, and I don't know how much time figuring out how poorly performing queries are happening. The abstraction leaks like a sieve and isn't worth it. Now, if we could replace SQL with a purpose-built language, that I would be interested in. I have to concede that SQL doesn't have great ergonomics (e.g. if FROM was first then auto-complete would be better, trailing commas would eliminate much formatting bike shedding, why are INSERT and UPDATE so different).
threeseed 2y ago
> It's basically a declarative statement that's almost english-like and it's specifically DESIGNED to be more readable and easier to use by humans It has categorically failed at this task. That is what we have an entire ecosystem of tools to allow people to query and interact with databases without using SQL. Developers need to understand that SQL is an abstraction for developers, analysts, data engineers etc not end users. And only basic SQL statements are English like.
Animats 2y ago
I tend to agree. The main trouble with SQL is that the query parameters are mixed in with the query, which results in string escape problems. That dates from when people were expected to query a database by hand from SQL, which is sometimes useful. For machine generated use, SQL queries should consist of a well defined fill-in-the-blanks system, with a text SQL query and a machine data structure. Something like this: Query in SQL text as a constant string: "SELECT foo FROM BAR WHERE name=NAME1" Parameters: key/value pairs in a safe native format for the language in use. {"NAME1" : "Smith"} That gets you out of the curse of character escaping and SQL injection, the only real problem. It avoids trying to rewrite SQL in yet another ORM. Microsoft has something which works this way, but it's unnecessarily clunky.[1] [1] https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-queries/using-statement-parameters-binding-parameters?view=sql-server-ver15 https://learn.microsoft.com/en-us/sql/relational-databases/n...
- Animats 2y ago
  This suggests a simple API using a Rust macro: fill_in_sql!(query_string, query_params) Where query_params is a structure containing the parameters: struct some query { name: &str, id: u32, } The macro would parse the query string, find the variables to be filled in, match them to fields in the "params" structure, and generate the code to safely escape and fill in the params. Any matching failures are found at compile time. This is similar to what the usual "derive" macros do - examine a data structure at compile time and generate appropriate code.
  - kelnos 2y ago
    I've used systems like this in Scala. It's actually pretty magical to write something like: val foos = sql"SELECT * from foos WHERE status = 'frobbed'".as[List[Foo]] ... and have the macro system parse the query at compile time and verify that the query will indeed return a `List[Foo]`. I think this is a nice middle ground, but constructing dynamic queries often can't be checked at compile-time. (I'm probably getting the syntax somewhat wrong; I haven't done Scala in quite a few years at this point.)
- 2y ago
Seb-C 2y ago
> The ideal API would be one that is both high level but allows fine grained control... but we don't have one yet. What kind of fine grained control are you talking about here? I never felt like I was missing anything when doing even complex SQL (unless I am forced to use a crappy MySQL, which problems are not inherent to SQL itself). Quite the contrary actually: despite having this amazing high level of abstaction, most developers are ignorant or don't care to properly index the data, add integrity constraints, manage the locks or think about transactions. In 99% of the non-SQL code that I see, developers don't even go further than nested loops containing IOs, so I don't think that letting them decide which tree/trie is appropriate for a user case (for example) would help in any way.
swaits 2y ago
> almost english-like Real world SQL from the analytics, DE, BIE side of things is _extremely far_ from readable. It's a form of actual hell.
cryptonector 2y ago
> >My opinion is that SQL should be for computers to write. This would put it firmly in the same category as LLVM IR. > This is nuts. > SQL is a high level language. [...] It's... not completely nuts. It's the reason that LinkQ exists, and that's not bad. The main reason this approach is good is that too many programmers accidentally create SQL injection vulnerabilities. Another way to prevent SQL injection vulnerabilities would be to disallow all literal values in SQL queries (making them all parameterized, though there could be compile-time and run-time parameters). But for the SQL injection issue I think this would be... not quite nuts, but IMO unwise, because now I'd have N relational query languages instead of 1 (SQL). Sure, SQL means N variations due to differences among N RDBMSes, but mostly much smaller than the variations one would expect in different SQL-free query libraries. One thing is clear: this sort of scheme does not make it any easier to reason about what the query engine does under the covers since expressing a query as an AST does not preclude the query engine performing algebra on your queries to optimize (or pessimize) them.

vlovich123 2y ago

> You can't get any higher level than SQL. Can’t you? Wouldn’t higher level be a goal oriented natural language like “what’s the most popular music file in the database” and an AI agent just figured out how to give you the answer?

ninetyninenine 2y ago
Well. You know what I mean. AI is also unreliable. Place chatGPT over a database and it's going to start lying about what is in it eventually. That being said my statement likely isn't strictly true even when you account for ML. I'm sure there are even higher level languages that are not probability based and thus deterministic with expected results. But in terms of a software developers average experience, SQL is the highest level you can get without getting into the minefield that is LLMs.
- threeseed 2y ago
  > SQL is the highest level you can get without getting into the minefield that is LLMs You can use NLPs to safely write SQL in more business language. And of course there are GUIs.
- kelnos 2y ago
  > SQL is the highest level you can get without getting into the minefield that is LLMs That's trivially false. I'm currently playing with sea-orm[0], and I can write things like: let db = Database::connect(...).await?; let yesterday: chrono::DateTime<chrono::Utc> = ...; let foos: Vec<foos::Model> = Foos::find() .filter(foos::Column::Status.eq(FooStatus::Frobbed)) .filter(foos::Column::UpdatedAt.gt(yesterday)) .left_join(Bars) .also_select(Bars) .all(&db) .await?; How is that not higher-level than SQL? [0] https://crates.io/crates/sea-orm https://crates.io/crates/sea-orm
  - cwbriscoe 2y ago
    Why not just write SQL, it would be much more readable.

Yoric 2y ago

That's a good point. It would be very nice to have a LLVM IR-style language for database queries, though. As a language, SQL is... well, a little behind times. ORMs explore lots of interesting design choices, but as you mention, by piling up high-level decisions on top of something that, by many aspects, are even higher decisions. I wonder what an LLVM IR-like would look like in this space.

ninetyninenine 2y ago
I agree there should be a common api. The sheer number of dialects with SQL contributes to a lot of issues with ORMs. But SQL is definitely not something designed for computers to only write or compile another language into (which is what the OP is promoting).
- Yoric 2y ago
  Yeah. I wonder if something like GlueSQL or the IndexedDB layer of most browsers could be used as such an API.

josephg 2y ago

I hear you and I 70% agree. I’ve also been using Prisma for a project recently and I’ve been finding it quite good. It layers several useful features on top of sql, like schema management (checked in to git), code gen (incl typescript types), db connection management, serialization / deserialization from JavaScript objects to sql rows and back, and it lets you follow foreign keys. If you want, you can also easily just make raw sql queries. That works fine too. Sql is a very old programming language, and it’s showing its age. The design is wacky - does GROUP BY go before or after ORDER BY in the query? Every command has custom, seemingly random words which are actually parameters. They are placed at totally arbitrary, hard to remember spots in the command. And what, we make our program output a string that gets immediately parsed by the sql server? What a horrible, stupid way to do RPC. It also gives terrible error messages if you get it wrong. And it’s awful to learn and debug. All of that is a huge pity, because modern databases can do so, so much. Doing logic inside the database can often be an order of magnitude faster than doing it in client code. SQL is a fantastic way to represent your data. One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql.

ninetyninenine 2y ago
>One path for fixing this would be to move the sql query parser inside the client library. Then internally, have the client library send optimized RPC instructions to the database. This would take cpu load off the database (usually a good move - databases are harder to scale). And it would open the door for the client library to provide other better, lighter and faster ways to programmatically construct database queries. Ideally without the totally unnecessary loop of constructing then parsing sql. The problem here is these low level optimizations that the database is doing is freaking complicated. There's a lot of intelligence going on in the background and it's different depending on the database. We can solve it with a low level common IR but that IR will be extremely complex. And that IR will likely give different performance profiles for the same code on different databases. I can see this being solved for one database. But not across all databases and a common api. We sort of do have a common api and we paid for it with the trade off of it being a easier high level language on top of the part of the web that needs to be most optimized.

9rx 2y ago

> We don't want to do meta programming where we have another language living as a string in our web app. Strings are fine in theory, but the trouble with SQL is that, while too high-level in all the wrong places, it is also too low-level in all the wrong places. Developers want to be able to do things like define the shape of the data beyond tables and compose queries, which SQL fails hard at. In order to accomplish what developers need, you either end up extending SQL into a new language, or you can use the programming constructs you already have, treating SQL as a compiler target, where necessary. The latter is considerably easier to implement.

jimbokun 2y ago

I think the sweet spot is a query builder that just ensures you emit syntactically valid SQL to send to the database. So there are no surprises about what the database is executing. But protects against typos and security issues from building SQL through string concatenation.

mplanchard 2y ago
sqlx (the rust library) does this, while also validating your sql statement, input types, and output types against your local db. For me it doesn’t get much better.

bob1029 2y ago

Every time I've found myself frustrated with a SQL query, there was some horrible misstep in how the underlying schema was designed. Having the wisdom to craft a high quality schema can be the biggest unlock in the whole enterprise. If the tables, columns & relations are ~1:1 with the actual business (i.e., you went and talked to real, live stakeholders at design time), then it should be deeply intuitive for a non-technical domain expert to interact with. Often, you don't get a chance to redo the whole thing, but that doesn't mean you can't still design an ideal schema and then shim it with views, CTEs, replicas, ETL, etc. SQL is a domain specific language. It would be like complaining about how horrible it is to write Lua scripts. The criticism is baseless without understanding the underlying schema/bindings.

habitue 2y ago

I see your point for one part, which is that `select([id]).from(Users)` is maybe in the best case as readable as `select id from users`, and in many common cases it'll be less readable than sql. Plus, it's not quite sql, you'll have to look up how to do things a lot of times. But we aren't just talking about SQL the high level language that you might execute when you are poking the data live. In a web app dynamically constructing sql, we have multiple issues: 1. Deserialization: we need to know what the query returns and plug it into data structures we want to use in our program 2. SQL is dynamically typed, and if you construct queries dynamically you're even more dynamically up a creek. Maybe even "sql injection vulnerability" up a creek. 1 and 2 are solved by strongly typed query builders like this: the type system has a proof that the ways you will construct queries in your program actually result in valid sql queries and that they return the right data shape. It's actually solving a real problem

cryptonector 2y ago
(1) is premised on (2), which is not right. Some SQL RDBMSes are statically typed (e.g., PostgreSQL) while others are dynamically typed (e.g., SQLite3). If you're building on SQLite3 then you're going to have to do run-time type checks anyways (even if you somehow disallow access to the DB that bypasses your application). So maybe don't build on SQLite3. This is a shame. We really do need a SQLite3-like DB that is statically typed.
- Hytak 2y ago
  sqlite has `STRICT` tables that are statically typed

zamalek 2y ago

I call it "SQL fear", and I agree: people need to get over it. We used EFCore at my previous job, and I don't know how much time figuring out how poorly performing queries are happening. The abstraction leaks like a sieve and isn't worth it. Now, if we could replace SQL with a purpose-built language, that I would be interested in. I have to concede that SQL doesn't have great ergonomics (e.g. if FROM was first then auto-complete would be better, trailing commas would eliminate much formatting bike shedding, why are INSERT and UPDATE so different).

threeseed 2y ago

> It's basically a declarative statement that's almost english-like and it's specifically DESIGNED to be more readable and easier to use by humans It has categorically failed at this task. That is what we have an entire ecosystem of tools to allow people to query and interact with databases without using SQL. Developers need to understand that SQL is an abstraction for developers, analysts, data engineers etc not end users. And only basic SQL statements are English like.

Animats 2y ago

I tend to agree. The main trouble with SQL is that the query parameters are mixed in with the query, which results in string escape problems. That dates from when people were expected to query a database by hand from SQL, which is sometimes useful. For machine generated use, SQL queries should consist of a well defined fill-in-the-blanks system, with a text SQL query and a machine data structure. Something like this: Query in SQL text as a constant string: "SELECT foo FROM BAR WHERE name=NAME1" Parameters: key/value pairs in a safe native format for the language in use. {"NAME1" : "Smith"} That gets you out of the curse of character escaping and SQL injection, the only real problem. It avoids trying to rewrite SQL in yet another ORM. Microsoft has something which works this way, but it's unnecessarily clunky.[1] [1] https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-queries/using-statement-parameters-binding-parameters?view=sql-server-ver15 https://learn.microsoft.com/en-us/sql/relational-databases/n...

Animats 2y ago
This suggests a simple API using a Rust macro: fill_in_sql!(query_string, query_params) Where query_params is a structure containing the parameters: struct some query { name: &str, id: u32, } The macro would parse the query string, find the variables to be filled in, match them to fields in the "params" structure, and generate the code to safely escape and fill in the params. Any matching failures are found at compile time. This is similar to what the usual "derive" macros do - examine a data structure at compile time and generate appropriate code.
- kelnos 2y ago
  I've used systems like this in Scala. It's actually pretty magical to write something like: val foos = sql"SELECT * from foos WHERE status = 'frobbed'".as[List[Foo]] ... and have the macro system parse the query at compile time and verify that the query will indeed return a `List[Foo]`. I think this is a nice middle ground, but constructing dynamic queries often can't be checked at compile-time. (I'm probably getting the syntax somewhat wrong; I haven't done Scala in quite a few years at this point.)
2y ago

Seb-C 2y ago

> The ideal API would be one that is both high level but allows fine grained control... but we don't have one yet. What kind of fine grained control are you talking about here? I never felt like I was missing anything when doing even complex SQL (unless I am forced to use a crappy MySQL, which problems are not inherent to SQL itself). Quite the contrary actually: despite having this amazing high level of abstaction, most developers are ignorant or don't care to properly index the data, add integrity constraints, manage the locks or think about transactions. In 99% of the non-SQL code that I see, developers don't even go further than nested loops containing IOs, so I don't think that letting them decide which tree/trie is appropriate for a user case (for example) would help in any way.

swaits 2y ago

> almost english-like Real world SQL from the analytics, DE, BIE side of things is _extremely far_ from readable. It's a form of actual hell.

cryptonector 2y ago

> >My opinion is that SQL should be for computers to write. This would put it firmly in the same category as LLVM IR. > This is nuts. > SQL is a high level language. [...] It's... not completely nuts. It's the reason that LinkQ exists, and that's not bad. The main reason this approach is good is that too many programmers accidentally create SQL injection vulnerabilities. Another way to prevent SQL injection vulnerabilities would be to disallow all literal values in SQL queries (making them all parameterized, though there could be compile-time and run-time parameters). But for the SQL injection issue I think this would be... not quite nuts, but IMO unwise, because now I'd have N relational query languages instead of 1 (SQL). Sure, SQL means N variations due to differences among N RDBMSes, but mostly much smaller than the variations one would expect in different SQL-free query libraries. One thing is clear: this sort of scheme does not make it any easier to reason about what the query engine does under the covers since expressing a query as an AST does not preclude the query engine performing algebra on your queries to optimize (or pessimize) them.