5 ms·

We write programs with Python, Java, Rust, Javascript and so on. Yet we use a very different language, eg. SQL, to query and modify data. Why? Why don't we use

We write programs with Python, Java, Rust, Javascript and so on. Yet we use a very different language, eg. SQL, to query and modify data. Why? Why don't we use eg. Python as well? SQL is different from other languages: it is declarative, meaning it doesn't dictate how to do it, but what the response should be. Maybe that's the reason? But if that's the case, why are declarative languages not more popular? SQL and other language are not compatible, and that's often a problem. You have this hard border (related to impedance mismatch). SQL (or GraphQL) is often used as a remote API: The client sends a SQL statement, the server processes it and sends the response. SQL is very powerful, but also dangerous: the statement might be very expensive, for example because an index is missing. Sure, you can shoot yourself in the foot also with Python or Java, but I argue it's harder. SQL is one more technology in your stack, one more thing to learn. And actually, there are many many SQL dialects. I wish databases have better, faster, and standardized support for a fast procedural language. So that clients can send programs, the server processes it and sends the response. A way to access tables and indexes like a hash table or ordered map. That way, there is no border. There is no slow query due to a missing index. You have to think about how data is access, which indexes are needed. But you have everything under your control. There is no risk of a missing index, or risk of the database not picking the index it should. (I wrote 3 relational database engines and 4 SQL parsers: HypersonicSQL, H2 database, Apache Jackrabbit Oak, PointBase Micro. I also wrote a GraphQL parser and engine, and a Key-Value store. It's not that I hate SQL.)

ako 3y ago
The problem with procedural is that it doesn’t optimize very well. The fastest way to get your data for a large number of random queries often depends on the data size, the available indexes, but is also influenced by changes in data size, etc. What is fast for a small dataset might be slow for a larger dataset. The right algorithm also depends on your filters and caching. It’s almost impossible to write procedural queries that always perform, that is a really hard task. That’s why we use a declarative query language, I tell the database what data I need, and the database optimizer will determine the best performing algorithm to fetch the data based on all dynamic statistics it has. Don’t underestimate how much hard work the database optimizer takes care of, I’m glad I don’t have to program all of that myself. Better get used to this way of working, it resembles pretty much how AI assists us.
- thomasmg 3y ago
  I have written 4 query optimizers: HypersonicSQL, H2 database, PointBase Micro, and Apache Jackrabbit Oak. I know procedural language don't have such optimizers. But I argue that you don't always need them. I argue that the database engine query optimizer is often more a risk than a help. I have seen missing indexes far too often. SQL doesn't require that the programmer thinks how the data is accessed, so the result is that too many programmers don't think about it, and so don't add indexes, request too much data, and so on. The SQL statements work fine with small (development) data sets, so the same statements are used with production databases, and you run into problems too late. I don't think it's hard to write procedural queries that are always fast. You write a loop or map/filter/collect method. You just explicitly need to mention which index to use, is all.
  - ako 3y ago
    Agree on the missing indexes, was in a debugging session yesterday where missing indexes turned out to cause some issues. But I have little faith that people who forget indexes are capable of writing procedural queries that are always fast. These less experienced devs are the ones that benefit most from the query optimizer doing the hard work. I think the real solution is for database to automatically create indexes where needed, as some database already do. And some better visual query editor like ultorg.
erikpukinskis 3y ago
> But if that's the case, why are declarative languages not more popular? Declarative programming is not very popular because it’s hard to debug. You can’t step through it and inspect intermediate representations. It presents a black box to the developer. That said, they do seem to show up around certain very complex APIs sometimes. Besides SQL, CSS is the obvious one. And I think many parts of otherwise functional/imperative APIs are pseudo-declarative. For example, React is famously functional in its current form, with render functions and hooks being functions… but then common hooks take a dependency array which is declarative. And at the boundary of these hooks the code is no longer procedural, it disappears into the framework. > I wish databases have better, faster, and standardized support for a fast procedural language. That’s a fascinating idea. It would take someone with deep knowledge of a query engine to encapsulate it with a procedural API instead of a declarative one. That’s not me, but I would love to see it.
- deleted 3y ago
  [deleted]
agumonkey 3y ago
not defending sql really but sql is more stable and well defined than operational programming languages, it's somehow of an asset in times of feature / paradigm volatility to me
mappu 3y ago
SQLite somewhat works this way - the frontend compiles the query into bytecode instructions, that the data layer executes procedurally. It would be possible to expose that and write the bytecode directly (but it would take more effort to make it ergonomic.)
mbStavola 3y ago
I used to love writing SQL, but now I'm very much in the same camp as you. I don't ever want to write SQL, I want to write some straightforward code in my language of choice which allows me to interact with the underlying data structures of the DB; every lookup or aggregation I could do in SQL could definitely be written in a much more clear fashion if it was in Rust, JS, or Python. I'm hoping that as WASM games momentum and WASI becomes an actual thing, some DB is going to pop up that does just that. I'd like to try myself one day.
preseinger 3y ago
querying relational data with set theory is pretty different to expressing imperative programs in a programming language sql is not a programming language in the traditional sense, it's a mistake to try to think of it in those terms, or demand traditional programming language type stuff from it

ako 3y ago

The problem with procedural is that it doesn’t optimize very well. The fastest way to get your data for a large number of random queries often depends on the data size, the available indexes, but is also influenced by changes in data size, etc. What is fast for a small dataset might be slow for a larger dataset. The right algorithm also depends on your filters and caching. It’s almost impossible to write procedural queries that always perform, that is a really hard task. That’s why we use a declarative query language, I tell the database what data I need, and the database optimizer will determine the best performing algorithm to fetch the data based on all dynamic statistics it has. Don’t underestimate how much hard work the database optimizer takes care of, I’m glad I don’t have to program all of that myself. Better get used to this way of working, it resembles pretty much how AI assists us.

thomasmg 3y ago
I have written 4 query optimizers: HypersonicSQL, H2 database, PointBase Micro, and Apache Jackrabbit Oak. I know procedural language don't have such optimizers. But I argue that you don't always need them. I argue that the database engine query optimizer is often more a risk than a help. I have seen missing indexes far too often. SQL doesn't require that the programmer thinks how the data is accessed, so the result is that too many programmers don't think about it, and so don't add indexes, request too much data, and so on. The SQL statements work fine with small (development) data sets, so the same statements are used with production databases, and you run into problems too late. I don't think it's hard to write procedural queries that are always fast. You write a loop or map/filter/collect method. You just explicitly need to mention which index to use, is all.
- ako 3y ago
  Agree on the missing indexes, was in a debugging session yesterday where missing indexes turned out to cause some issues. But I have little faith that people who forget indexes are capable of writing procedural queries that are always fast. These less experienced devs are the ones that benefit most from the query optimizer doing the hard work. I think the real solution is for database to automatically create indexes where needed, as some database already do. And some better visual query editor like ultorg.

erikpukinskis 3y ago

> But if that's the case, why are declarative languages not more popular? Declarative programming is not very popular because it’s hard to debug. You can’t step through it and inspect intermediate representations. It presents a black box to the developer. That said, they do seem to show up around certain very complex APIs sometimes. Besides SQL, CSS is the obvious one. And I think many parts of otherwise functional/imperative APIs are pseudo-declarative. For example, React is famously functional in its current form, with render functions and hooks being functions… but then common hooks take a dependency array which is declarative. And at the boundary of these hooks the code is no longer procedural, it disappears into the framework. > I wish databases have better, faster, and standardized support for a fast procedural language. That’s a fascinating idea. It would take someone with deep knowledge of a query engine to encapsulate it with a procedural API instead of a declarative one. That’s not me, but I would love to see it.

deleted 3y ago
[deleted]

agumonkey 3y ago

not defending sql really but sql is more stable and well defined than operational programming languages, it's somehow of an asset in times of feature / paradigm volatility to me

mappu 3y ago

SQLite somewhat works this way - the frontend compiles the query into bytecode instructions, that the data layer executes procedurally. It would be possible to expose that and write the bytecode directly (but it would take more effort to make it ergonomic.)

mbStavola 3y ago

I used to love writing SQL, but now I'm very much in the same camp as you. I don't ever want to write SQL, I want to write some straightforward code in my language of choice which allows me to interact with the underlying data structures of the DB; every lookup or aggregation I could do in SQL could definitely be written in a much more clear fashion if it was in Rust, JS, or Python. I'm hoping that as WASM games momentum and WASI becomes an actual thing, some DB is going to pop up that does just that. I'd like to try myself one day.

preseinger 3y ago

querying relational data with set theory is pretty different to expressing imperative programs in a programming language sql is not a programming language in the traditional sense, it's a mistake to try to think of it in those terms, or demand traditional programming language type stuff from it