4 ms·

SQL really sucks. In general I'm not a fan of SQL-like syntax since it makes queries with lots of joins really difficult to read -- it's hard to keep track whic

SQL really sucks. In general I'm not a fan of SQL-like syntax since it makes queries with lots of joins really difficult to read -- it's hard to keep track which value came from where. GraphQL has some neat ideas and I really love the clarity the nesting provides. But GraphQL is also too limiting: it can't express arbitrary filters, recursion, optional edges (i.e. left joins), etc. My own take on this is an embeddable GraphQL-like language with all those features and with strong guarantees about execution performance. I've been calling it Trustfall, and you can play around with it (or check out example queries over HN data) in your browser here: https://play.predr.ag/ https://play.predr.ag/ For example, here's the query "Find the GitHub or Twitter accounts listed in the bios of HN users that comment on HN stories pointing to Open AI's website": https://play.predr.ag/hackernews#?f=1&q=IyBDcm9zcyBBUEkgcXVlcnkgKEFsZ29saWEgKyBGaXJlYmFzZSk6CiMgRmluZCBjb21tZW50cyBvbiBzdG9yaWVzIGFib3V0ICJvcGVuYWkuY29tIiB3aGVyZQojIHRoZSBjb21tZW50ZXIncyBiaW8gaGFzIGF0IGxlYXN0IG9uZSBHaXRIdWIgb3IgVHdpdHRlciBsaW5rCnF1ZXJ5IHsKICAjIFRoaXMgaGl0cyB0aGUgQWxnb2xpYSBzZWFyY2ggQVBJIGZvciBIYWNrZXJOZXdzLgogICMgVGhlIHN0b3JpZXMvY29tbWVudHMvdXNlcnMgZGF0YSBpcyBmcm9tIHRoZSBGaXJlYmFzZSBITiBBUEkuCiAgIyBUaGUgdHJhbnNpdGlvbiBpcyBzZWFtbGVzcyAtLSBpdCBpc24ndCB2aXNpYmxlIGZyb20gdGhlIHF1ZXJ5LgogIFNlYXJjaEJ5RGF0ZShxdWVyeTogIm9wZW5haS5jb20iKSB7CiAgICAuLi4gb24gU3RvcnkgewogICAgICAjIEFsbCBkYXRhIGZyb20gaGVyZSBvbndhcmQgaXMgZnJvbSB0aGUgRmlyZWJhc2UgQVBJLgogICAgICBzdG9yeVRpdGxlOiB0aXRsZSBAb3V0cHV0CiAgICAgIHN0b3J5TGluazogdXJsIEBvdXRwdXQKICAgICAgc3Rvcnk6IHN1Ym1pdHRlZFVybCBAb3V0cHV0CiAgICAgICAgICAgICAgICAgICAgICAgICAgQGZpbHRlcihvcDogInJlZ2V4IiwgdmFsdWU6IFsiJHNpdGVQYXR0ZXJuIl0pCgogICAgICBjb21tZW50IHsKICAgICAgICByZXBseSBAcmVjdXJzZShkZXB0aDogNSkgewogICAgICAgICAgY29tbWVudDogdGV4dFBsYWluIEBvdXRwdXQKCiAgICAgICAgICBieVVzZXIgewogICAgICAgICAgICBjb21tZW50ZXI6IGlkIEBvdXRwdXQKICAgICAgICAgICAgY29tbWVudGVyQmlvOiBhYm91dFBsYWluIEBvdXRwdXQKCiAgICAgICAgICAgICMgVGhlIHByb2ZpbGUgbXVzdCBoYXZlIGF0IGxlYXN0IG9uZQogICAgICAgICAgICAjIGxpbmsgdGhhdCBwb2ludHMgdG8gZWl0aGVyIEdpdEh1YiBvciBUd2l0dGVyLgogICAgICAgICAgICBsaW5rCiAgICAgICAgICAgICAgQGZvbGQKICAgICAgICAgICAgICBAdHJhbnNmb3JtKG9wOiAiY291bnQiKQogICAgICAgICAgICAgIEBmaWx0ZXIob3A6ICI%2BPSIsIHZhbHVlOiBbIiRtaW5Qcm9maWxlcyJdKQogICAgICAgICAgICB7CiAgICAgICAgICAgICAgY29tbWVudGVySURzOiB1cmwgQGZpbHRlcihvcDogInJlZ2V4IiwgdmFsdWU6IFsiJHNvY2lhbFBhdHRlcm4iXSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBAb3V0cHV0CiAgICAgICAgICAgIH0KICAgICAgICAgIH0KICAgICAgICB9CiAgICAgIH0KICAgIH0KICB9Cn0%3D&v=ewogICJzaXRlUGF0dGVybiI6ICJodHRwW3NdOi8vKFteLl0qXFwuKSpvcGVuYWkuY29tLy4qIiwKICAibWluUHJvZmlsZXMiOiAxLAogICJzb2NpYWxQYXR0ZXJuIjogIihnaXRodWJ8dHdpdHRlcilcXC5jb20vIgp9 https://play.predr.ag/hackernews#?f=1&q=IyBDcm9zcyBBUEkgcXVl... The repo is here: https://github.com/obi1kenobi/trustfall https://github.com/obi1kenobi/trustfall It's not just a toy implementation -- it has real-world uses too: - I gave a conference talk on how to use Trustfall to query any kind(s) of data sources, from APIs to DBs to raw files: https://www.hytradboi.com/2022/how-to-query-almost-everything https://www.hytradboi.com/2022/how-to-query-almost-everythin... - cargo-semver-checks, a semver linter for Rust crates, uses Trustfall to define its lint rules: https://github.com/obi1kenobi/cargo-semver-checks https://github.com/obi1kenobi/cargo-semver-checks

croes 4y ago
>it's hard to keep track which value came from where Isn't that dependent on how you write the query? If you put the table name or it's alias before the column it's quite easy to keep track of the data source.
- obi1kenobi 4y ago
  Try it with a query that has 40 joins with multiple joins to the same table from different places, and let me know if you still think it's easy :) At work I've seen SQL queries that are thousands of lines long that would make that 40-join query look like a toy example.
  - fknhellmyas 4y ago
    That's what I always tell people advocating for plastic straws. They think plastic straws are great, they drink their cocktails with them and everything. Then I tell them how when I try to wash my cars by assembling 50 straws into a water hose, the whole plastic straw concept quickly breaks down. In my opinion you should never use plastic straws.
  - croes 4y ago
    That's exactly what I do and why aliases exist. It's manual effort, but that's documentation always.
    - Izkata 4y ago
      I mean, they're also talking about using the same table multiple times - aliases aren't just useful, they're required in that case so the engine knows which one you mean when using a column from one of them. Best guess without actually seeing these queries, whoever wrote them is using bad aliases (like "foo1", "foo2" and so on) instead of something more reasonable.
  - ako 4y ago
    Best not to have a single query, but to break it down into a number of separate views and join those views for your final query.

croes 4y ago

>it's hard to keep track which value came from where Isn't that dependent on how you write the query? If you put the table name or it's alias before the column it's quite easy to keep track of the data source.

obi1kenobi 4y ago
Try it with a query that has 40 joins with multiple joins to the same table from different places, and let me know if you still think it's easy :) At work I've seen SQL queries that are thousands of lines long that would make that 40-join query look like a toy example.
- fknhellmyas 4y ago
  That's what I always tell people advocating for plastic straws. They think plastic straws are great, they drink their cocktails with them and everything. Then I tell them how when I try to wash my cars by assembling 50 straws into a water hose, the whole plastic straw concept quickly breaks down. In my opinion you should never use plastic straws.
- croes 4y ago
  That's exactly what I do and why aliases exist. It's manual effort, but that's documentation always.
  - Izkata 4y ago
    I mean, they're also talking about using the same table multiple times - aliases aren't just useful, they're required in that case so the engine knows which one you mean when using a column from one of them. Best guess without actually seeing these queries, whoever wrote them is using bad aliases (like "foo1", "foo2" and so on) instead of something more reasonable.
- ako 4y ago
  Best not to have a single query, but to break it down into a number of separate views and join those views for your final query.