4 ms·

It's funny, 10min ago, I had some comment on a 10 years old question of mine, and my question linked to another opinionated answer on stackoverflow. which still

by jokoon 3y ago

It's funny, 10min ago, I had some comment on a 10 years old question of mine, and my question linked to another opinionated answer on stackoverflow. which still has 136 upvotes: https://stackoverflow.com/questions/1643365/why-no-love-for-sql/1643440#1643440 https://stackoverflow.com/questions/1643365/why-no-love-for-... I mean I was never crazy, RDBMS are useful, but SQL languages feel awkward not just to me. I'm curious about an alternative to the SQL language or API for a database system, and I guess that sort of RDBMS would also have a very different design. BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. Of course a DB system is not designed to be used like this, but to me, it should, or at least there should be better alternative to SQL. For some reason, python list comprehension/dict comprehension and sets somehow feel like a weird, light alternative to a database system. I just get the raw data with SQL and do data things I should do with SQL, and even if it's ill-advised, it feels so much better, and it works. Not to dismiss sqlite though, it's a wonderful software.

sedatk 3y ago
You might like EdgeQL https://www.edgedb.com/docs/edgeql/index https://www.edgedb.com/docs/edgeql/index
winrid 3y ago
The problem is this exposes you directly to storage mechanism internals. A good example of this is Redis. Redis is exactly what you described. It's just data structures as a service. For Redis - this works. It's its purpose. It's a tool. Same with KV stores. Again, you are working directly with the storage mechanism. However, now you're limited to that implementation detail. With SQL, or a traditional DB, your SELECT x by y could be served by a hashmap today, and maybe a btree tomorrow, and you don't need to change your app. Whether this makes sense for you depends entirely on your application. For loops in Python are okay for some apps. It won't work when you have a billion records.
summarity 3y ago
I use KQL (inside ADX/Kusto) at work, it's awesome. Not just saying that bc I work for an MSFT company, I legit enjoy it more than SQL. And with Kusto one can Python inside of KQL as well, which sounds bonkers but is actually pretty neat. I found through teaching people KQL that the learning curve (until they can do medium-complexity analysis) is also much shorter.
chrysoprace 3y ago
I think part of the reason that SQL is so prevalent without real alternatives is two-fold: - It's backed by a mathematical theory (relational algebra). - SQL has an international standard (although no implementation really follows this correctly if I'm not mistaken). Lots of other languages have their own take on lists/arrays/dicts, but SQL is (generally) universal and often backwards compatible due to its extremely simple syntax.
- bhewes 3y ago
  And relational algebra is based on set theory with constraints. Cantor the OG DB
- hakfoo 3y ago
  I'd argue that the other aspect is that it abstracts out serialization. I have a small hobby project (a frontend for a TV tuner device) that originally held channel and EPG data in a few python dicts. This was clunky because I had to manually JSON-encode them and write them out, and saying "rewrite an entire 500kb config file when just 10 bytes had changed" always felt wrong. (Although, as I gather it, on an SSD, rewriting one byte may as well be the same as the whole file). I replaced the guts with SQLite in part for that. I figured it's used enough for "we changed one row of 5,000" use cases that it would be optimized for that case. It also made cross-references simpler-- multiple table scans can be chained in a single statement, rather than having to nest queries and pass intermediary results from scan to scan. (For example, some program-guide data is more sensibly cross-referenced by physical channel number, and some by "display" channels, so being able to join in a table that maps the two streamlines stuff)
bigbillheck 3y ago
> tapping directly into the data. I can't imagine this being particularly pleasant if your data is too large or you want to do anything more complicated than a single SELECT.
pravus 3y ago
> BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. A lot of databases are just data structures written to disk in an efficient manner. There's nothing really stopping anyone from doing the same and implementing the query language as a direct API to these on-disk structures. In my mind this all just falls under the general category of random access files. This can be extremely performant if you are very knowledgeable of your use case and storage characteristics. SQL on the other hand is a query layer on top of these data structure engines which gives you a more general purpose language to work with. It doesn't map directly to the underlying data structures so you can model data differently at a more abstract level. Ultimately it will be compiled down into some sort of routine that attempts to perform data access to those underlying data structures in as optimized a manner as it can figure out. It's all about trade-offs. There are also plenty of storage engines that already give you an on-disk data structure in a box (e.g. RocksDB).
agumonkey 3y ago
Maybe this https://prql-lang.org/ https://prql-lang.org/ ?
deleted 3y ago
[deleted]
mamcx 3y ago
> I asked was about querying data without ever using a SQL language, like tapping directly into the data. I agree (making https://tablam.org https://tablam.org to try a fix & working on https://github.com/clockworklabs/SpacetimeDB https://github.com/clockworklabs/SpacetimeDB in the SQL conformance). Before I think SQL was bad. *Now I'm certain*. SQL is absurdly massive for things that could have collapse all the syntax 10x or more with more principled approach (and ACTUALLY doing relational model). However, working in an RDBM now I also understand why is not desirable to make "raw" calls to the DB: The engine MUST mediate all the calls to make things works (from query optimization, execution, iteration, lock management, transaction management, etc). Is incredible how much sophistication is in a simple `SELECT * FROM table`. What I wish is to build a `Wasm-like` IR so that is what anybody target, and `SQL` is not the mediator.
- cmrdporcupine 3y ago
  > What I wish is to build a `Wasm-like` IR so that is what anybody target, and `SQL` is not the mediator. Hey Mario, I believe I referenced this when we were working together, but one attempt at this was/is MonetDB's "MAL" VM: https://www.monetdb.org/documentation-Jun2023/dev-guide/monetdb-internals/mal_reference/ https://www.monetdb.org/documentation-Jun2023/dev-guide/mone... It's a fairly minimalistic somewhat assembly-like ("Monet Assembly Language", MAL) virtual machine that executes both program flow and relational algebraic operations (against purely binary relations, from which they are able to compose higher arity relations): https://www.monetdb.org/documentation-Jun2023/dev-guide/monetdb-internals/mal-modules/algebra/ https://www.monetdb.org/documentation-Jun2023/dev-guide/mone... It's not a bad place to start from as an idea. SQLite itself has a virtual machine, but it's just too highly specific for its own storage engine and transaction model Another approach is what Umbra does, which is (in some circumstances) to generate a query plan in the form of LLVM intermediate representation and then compile it down to native code (one could also compile to web assembly, I suppose, if you love the crappy performance :-) ). That works best for longer-running analytical queries, and they have other approaches for shorter, ad-hoc transactional ones https://link.springer.com/article/10.1007/s00778-020-00643-4 https://link.springer.com/article/10.1007/s00778-020-00643-4 gets into it.
  - 3y ago
nine_k 3y ago
SQL the language has many drawbacks. My weapon of choice when using Python is SQLAlchemy; it gives you composability. Also, there's https://github.com/PRQL/prql https://github.com/PRQL/prql that compiles to SQL.
jiehong 3y ago
I think that’s what MongoDB API does, albeit it’s not a relational DB [0]. [0]: https://www.mongodb.com/basics/examples https://www.mongodb.com/basics/examples
SubjectToChange 3y ago
I mean I was never crazy, RDBMS are useful, but SQL languages feel awkward not just to me. Calling RDBMSs “useful” is quite the understatement. They are among the most commercially successful software products of all time, and the theory backing them has endured with us for over 50 years now. BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. Of course a DB system is not designed to be used like this, but to me, it should, or at least there should be better alternative to SQL. This approach has been tried over and over again with little success to show for it. Considering that a good number of working programmers would be hard pressed to recall what ACID even stands for, much less with any rigor, you have to ask yourself if you really want to entrust valuable data to them. Furthermore, mucking around with low level storage and access patterns adds tremendous amount of work to development, it is very unpleasant.

by jokoon 3y ago

sedatk 3y ago

You might like EdgeQL https://www.edgedb.com/docs/edgeql/index https://www.edgedb.com/docs/edgeql/index

winrid 3y ago

The problem is this exposes you directly to storage mechanism internals. A good example of this is Redis. Redis is exactly what you described. It's just data structures as a service. For Redis - this works. It's its purpose. It's a tool. Same with KV stores. Again, you are working directly with the storage mechanism. However, now you're limited to that implementation detail. With SQL, or a traditional DB, your SELECT x by y could be served by a hashmap today, and maybe a btree tomorrow, and you don't need to change your app. Whether this makes sense for you depends entirely on your application. For loops in Python are okay for some apps. It won't work when you have a billion records.

summarity 3y ago

I use KQL (inside ADX/Kusto) at work, it's awesome. Not just saying that bc I work for an MSFT company, I legit enjoy it more than SQL. And with Kusto one can Python inside of KQL as well, which sounds bonkers but is actually pretty neat. I found through teaching people KQL that the learning curve (until they can do medium-complexity analysis) is also much shorter.

chrysoprace 3y ago

I think part of the reason that SQL is so prevalent without real alternatives is two-fold: - It's backed by a mathematical theory (relational algebra). - SQL has an international standard (although no implementation really follows this correctly if I'm not mistaken). Lots of other languages have their own take on lists/arrays/dicts, but SQL is (generally) universal and often backwards compatible due to its extremely simple syntax.

bhewes 3y ago
And relational algebra is based on set theory with constraints. Cantor the OG DB
hakfoo 3y ago
I'd argue that the other aspect is that it abstracts out serialization. I have a small hobby project (a frontend for a TV tuner device) that originally held channel and EPG data in a few python dicts. This was clunky because I had to manually JSON-encode them and write them out, and saying "rewrite an entire 500kb config file when just 10 bytes had changed" always felt wrong. (Although, as I gather it, on an SSD, rewriting one byte may as well be the same as the whole file). I replaced the guts with SQLite in part for that. I figured it's used enough for "we changed one row of 5,000" use cases that it would be optimized for that case. It also made cross-references simpler-- multiple table scans can be chained in a single statement, rather than having to nest queries and pass intermediary results from scan to scan. (For example, some program-guide data is more sensibly cross-referenced by physical channel number, and some by "display" channels, so being able to join in a table that maps the two streamlines stuff)

bigbillheck 3y ago

> tapping directly into the data. I can't imagine this being particularly pleasant if your data is too large or you want to do anything more complicated than a single SELECT.

pravus 3y ago

> BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. A lot of databases are just data structures written to disk in an efficient manner. There's nothing really stopping anyone from doing the same and implementing the query language as a direct API to these on-disk structures. In my mind this all just falls under the general category of random access files. This can be extremely performant if you are very knowledgeable of your use case and storage characteristics. SQL on the other hand is a query layer on top of these data structure engines which gives you a more general purpose language to work with. It doesn't map directly to the underlying data structures so you can model data differently at a more abstract level. Ultimately it will be compiled down into some sort of routine that attempts to perform data access to those underlying data structures in as optimized a manner as it can figure out. It's all about trade-offs. There are also plenty of storage engines that already give you an on-disk data structure in a box (e.g. RocksDB).

agumonkey 3y ago

Maybe this https://prql-lang.org/ https://prql-lang.org/ ?

deleted 3y ago

[deleted]

mamcx 3y ago

> I asked was about querying data without ever using a SQL language, like tapping directly into the data. I agree (making https://tablam.org https://tablam.org to try a fix & working on https://github.com/clockworklabs/SpacetimeDB https://github.com/clockworklabs/SpacetimeDB in the SQL conformance). Before I think SQL was bad. *Now I'm certain*. SQL is absurdly massive for things that could have collapse all the syntax 10x or more with more principled approach (and ACTUALLY doing relational model). However, working in an RDBM now I also understand why is not desirable to make "raw" calls to the DB: The engine MUST mediate all the calls to make things works (from query optimization, execution, iteration, lock management, transaction management, etc). Is incredible how much sophistication is in a simple `SELECT * FROM table`. What I wish is to build a `Wasm-like` IR so that is what anybody target, and `SQL` is not the mediator.

cmrdporcupine 3y ago
> What I wish is to build a `Wasm-like` IR so that is what anybody target, and `SQL` is not the mediator. Hey Mario, I believe I referenced this when we were working together, but one attempt at this was/is MonetDB's "MAL" VM: https://www.monetdb.org/documentation-Jun2023/dev-guide/monetdb-internals/mal_reference/ https://www.monetdb.org/documentation-Jun2023/dev-guide/mone... It's a fairly minimalistic somewhat assembly-like ("Monet Assembly Language", MAL) virtual machine that executes both program flow and relational algebraic operations (against purely binary relations, from which they are able to compose higher arity relations): https://www.monetdb.org/documentation-Jun2023/dev-guide/monetdb-internals/mal-modules/algebra/ https://www.monetdb.org/documentation-Jun2023/dev-guide/mone... It's not a bad place to start from as an idea. SQLite itself has a virtual machine, but it's just too highly specific for its own storage engine and transaction model Another approach is what Umbra does, which is (in some circumstances) to generate a query plan in the form of LLVM intermediate representation and then compile it down to native code (one could also compile to web assembly, I suppose, if you love the crappy performance :-) ). That works best for longer-running analytical queries, and they have other approaches for shorter, ad-hoc transactional ones https://link.springer.com/article/10.1007/s00778-020-00643-4 https://link.springer.com/article/10.1007/s00778-020-00643-4 gets into it.
- 3y ago

nine_k 3y ago

SQL the language has many drawbacks. My weapon of choice when using Python is SQLAlchemy; it gives you composability. Also, there's https://github.com/PRQL/prql https://github.com/PRQL/prql that compiles to SQL.

jiehong 3y ago

I think that’s what MongoDB API does, albeit it’s not a relational DB [0]. [0]: https://www.mongodb.com/basics/examples https://www.mongodb.com/basics/examples

SubjectToChange 3y ago

I mean I was never crazy, RDBMS are useful, but SQL languages feel awkward not just to me. Calling RDBMSs “useful” is quite the understatement. They are among the most commercially successful software products of all time, and the theory backing them has endured with us for over 50 years now. BTW the question I asked was about querying data without ever using a SQL language, like tapping directly into the data. Of course a DB system is not designed to be used like this, but to me, it should, or at least there should be better alternative to SQL. This approach has been tried over and over again with little success to show for it. Considering that a good number of working programmers would be hard pressed to recall what ACID even stands for, much less with any rigor, you have to ask yourself if you really want to entrust valuable data to them. Furthermore, mucking around with low level storage and access patterns adds tremendous amount of work to development, it is very unpleasant.