5 ms·

Show HN: Trilogy – A Reusable, Composable SQL Experiment

78 points by efromvt 2y ago

Recipe: Add a semantic layer to SQL; use it drop the requirement for joins/group_by; add in type-checking and a lightweight python-esque import syntax to enable reuse and hierarchical querying. Trilogy is intended to provide an accessible but deep alternative to raw SQL. It offers a new-but-inspired-by-SQL syntax that compiles to various dialects of SQL (with DuckDB as the default). The target audience is people that really like SQL for analytics and data engineering, but want less boilerplate and sharp edges and looser coupling to the DB. Semantic models can be easily shared, composed and iterated on in an interactive session, preserving the adhoc workflows that make SQL so powerful. The "higher level" of the language vis-a-vis SQL makes it straightforward to extend into ETL (an experimental basic DBT integration is available), offering potential to optimize a processing graph across intermediate staging nodes automatically. This higher level of abstraction also offers some nice opportunities for more reliable text to SQL for LLMs. A similarly basic integration is available to demonstrate this, as is a very basic VsCode extension and electron-based IDE. Tech stack is primarily Python. Open source, MIT license. Github is linked from demo page. Thoughts, feedback, contributions all welcome! Note: renamed from PreQL (see prior show https://news.ycombinator.com/item?id=40728938 https://news.ycombinator.com/item?id=40728938) to avoid confusion with the many PreQLs of the world. The `SQL pun` naming space is unfortunately well-explored. Other SQL replacements (all great, all worth a look!): PRQL (pipelined SQL alternative, all new syntax) https://news.ycombinator.com/item?id=36866861 https://news.ycombinator.com/item?id=36866861 Malloy (all new syntax, semantic focus) https://news.ycombinator.com/item?id=30053860 https://news.ycombinator.com/item?id=30053860 preql (much more ambitious, all new syntax) https://news.ycombinator.com/item?id=26447070 https://news.ycombinator.com/item?id=26447070

snthpy 2y ago
Cool project! Will take a proper look when I get a chance. In the meantime, I just wanted to say: nice name! ;-)
- efromvt 2y ago
  Thanks on the name, hah. Been fun to see the progress PRQL has made going mainstream!
totalhack 2y ago
Congrats on the launch. I made a tool that has some similar objectives but doesn't present as SQL itself like Trilogy seems to. I'll take a deeper look at Trilogy soon, always interested to see the variety of approaches to this. https://github.com/totalhack/zillion https://github.com/totalhack/zillion
- efromvt 2y ago
  Oh wow yeah, a lot of parallels - thanks for sharing, I'll take a deeper dive in a bit. I think there's a lot of demand and a lot of space for different solutions; Trilogy definitely aspires to hew closer to standard SQL. (I actually really like SQL for the most part!)
  - totalhack 2y ago
    Thanks for taking a look! Happy to chat, DM me here: https://bsky.app/profile/totalhack.bsky.social https://bsky.app/profile/totalhack.bsky.social I have nothing against SQL of course. The simplified approach of a UI built on top of zillion or tools like it really enables a whole next level of productivity for business users that are never going to learn SQL, but also need more query flexibility than just "dashboards" without having to wait on a BI team for answers -- I die inside a little bit every time I hear of a company doing this. And as you have noted, I also think text-to-semantic-layer is an interesting approach for involving AI/NLP. I've been pulled away from this project for some time due to an acquisition at my day job but hoping to get back into it soon!
    - efromvt 2y ago
      Also full agreement! In an optimistic view, the SQL layer (at a slightly higher level) unifies the top level accessibility tools (NLP, drag/drop chart result builders, etc) with the more tech-familiar level of analysts/engineers, and provides progressive disclosure as you go down the stack and a path to promote the adhoc/SQL level work up to reporting easily.
- efromvt 2y ago
  Had some more time to drill into this and we've ended up with a very similar approach to a lot of the metadata definition and resolution - I'd love to chat sometime about how you've solved some of the common problems (table selection w/ multiple sources, the constraint vs output projection, aggregation level, etc, etc)
emmanueloga_ 2y ago
I might be off here, but this seems like the right place to ask: don't most SQL replacements focus heavily on querying while largely overlooking insertion and updating? I get why querying gets more attention, insertions are usually straightforward and don’t need much simplification. Updates, on the other hand, can be a bit trickier since they often involve modifying data derived from complex queries. These tools seem geared toward data analysis and not data generation, which is ok: is nice focusing on a single problem and solving it "right". But! for projects where a single person handles data creation, analysis, and management, it feels cumbersome to use one set of tools for querying ("R" in CRUD) and another for creation, updates, and deletions ("C," "U," and "D"). I think a "SQL replacement" or approach covering all of CRUD could be interesting for projects of any scale. Something that I could pick instead of shopping for ORMs and/or lightweight query generators.
- efromvt 2y ago
  More effort has definitely been focused on the 'select' aspect, since you often select more than you mutate - and even in the future state, for data warehousing cases updates can be relatively rare. I definitely don't see it targeting core OLTP CRUD work, where abstractions can sometimes cause more problems then they solve - but I hope for projects that involve bulk data creation, analysis, and management - analytics like - it can be a complete solution once the 'persist'/ 'export' queries are developed further.
tluyben2 2y ago
This is great; I have been thinking about this for a long time. I like reading about past and current implementations that try to better sql; from a programming and a data science and performance perspective. I am aware of the ones you linked and some others like 'Real' (shakti.com) sql and some enhancements from papers. Anyway; nice one! Will try.
mritchie712 2y ago
Not a SQL replacement, but if you're looking for an open source semantic layer, Cube is the way to go [0] 0 - https://github.com/cube-js/cube https://github.com/cube-js/cube
- deleted 2y ago
  [deleted]
- knowitnone 2y ago
  always appreciate links to good tools
hipadev23 2y ago
[flagged]
- d-lisp 2y ago
  I was reading this comment and I have no expertise in sql; would you like to explain why do exemples "look like junior-level bloat" to you ?
  - hipadev23 2y ago
    Their examples showing why Trilogy is so good are comparing it against poorly written SQL with poorly designed schemas. It reduces my confidence that their tool is actually solving real problems but instead was borne out of frustration in learning SQL and databases.
    - efromvt 2y ago
      Any particular examples you have in mind? The demo is just referencing https://github.com/duckdb/duckdb/tree/main/extension/tpcds/dsdgen/queries https://github.com/duckdb/duckdb/tree/main/extension/tpcds/d... which I wouldn't regard as a standard of good SQL; (implicit joins, yikes!) - but is a useful capability reference (as is tpc-ds in general). As I tried to convey, I like SQL a lot - my frustration is more around the lifecycle and maintainability. Happy to add more ergonomic references in other places, if you have some good examples to reference against?
- deleted 2y ago
  [deleted]
hobofan 2y ago
One thing that I am always looking for in a new "reusable", "composable" SQL tool is reuse of the same analytical queries across different source tables. My litmus test: I have a table "people" with the columns "people.firstname", "people.lastname", and a table "persons" with the columns "persons.firstname", "persons.lastname". I now want to create a query that gives me the "fullname" (".firstname" + " " + ".lastname") of all rows of both tables. If I have to spell out the logic for how to calculate the fullname in the query twice, the test is failed. (Taking the shortcut of creating the union of both tables first is not allowed, but I can't think of a simple example that enforces that restriction). For some reason, all of the solutions (PRQL, Malloy, dbt) that try to make SQL more reusable don't really consider this kind of reuse, and with that ultimately fall flat for the use-cases I would typically have for them. Sadly, Trilogy doesn't seem to be any better on that front.
- friendly_deer 2y ago
  This is something I've never thought about before, and haven't had a use case for, so I'm genuinely interested in learning a little more about your use cases if you can elaborate a little futher.
  - default-kramer 2y ago
    Sometimes you want to be able to do something like "run some SQL, but instead of using the normal tables use these temp tables I just created." In particular, I wanted to do this in SQLite recently. I wanted to have one write process which would always remain unblocked. And I also wanted to be able to run certain tasks which would do some temporary/discardable DB manipulations as part of producing an output file. These tasks could open the SQLite DB in read-only mode; load relevant data into temp tables, manipulate that data, and write the output file. Everything would have worked great if only SQL were a more composable language.
- hn_throwaway_99 2y ago
  TBH, I don't think your test is very useful in real world environments. That is, you have 2 independent tables, and you're wanting the solution to depend on the fact that there are columns that are named the same across both tables. IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road. For example, I've been bitten in the past by "natural joins" when we've wanted to refactor something later. I definitely agree that I don't want to have to repeat logic within a single table, but the kind of syntactic sugar that is your litmus test is a big foot gun IMO.
deleted 2y ago
[deleted]

78 points by efromvt 2y ago

snthpy 2y ago

Cool project! Will take a proper look when I get a chance. In the meantime, I just wanted to say: nice name! ;-)

efromvt 2y ago
Thanks on the name, hah. Been fun to see the progress PRQL has made going mainstream!

totalhack 2y ago

Congrats on the launch. I made a tool that has some similar objectives but doesn't present as SQL itself like Trilogy seems to. I'll take a deeper look at Trilogy soon, always interested to see the variety of approaches to this. https://github.com/totalhack/zillion https://github.com/totalhack/zillion

efromvt 2y ago
Oh wow yeah, a lot of parallels - thanks for sharing, I'll take a deeper dive in a bit. I think there's a lot of demand and a lot of space for different solutions; Trilogy definitely aspires to hew closer to standard SQL. (I actually really like SQL for the most part!)
- totalhack 2y ago
  Thanks for taking a look! Happy to chat, DM me here: https://bsky.app/profile/totalhack.bsky.social https://bsky.app/profile/totalhack.bsky.social I have nothing against SQL of course. The simplified approach of a UI built on top of zillion or tools like it really enables a whole next level of productivity for business users that are never going to learn SQL, but also need more query flexibility than just "dashboards" without having to wait on a BI team for answers -- I die inside a little bit every time I hear of a company doing this. And as you have noted, I also think text-to-semantic-layer is an interesting approach for involving AI/NLP. I've been pulled away from this project for some time due to an acquisition at my day job but hoping to get back into it soon!
  - efromvt 2y ago
    Also full agreement! In an optimistic view, the SQL layer (at a slightly higher level) unifies the top level accessibility tools (NLP, drag/drop chart result builders, etc) with the more tech-familiar level of analysts/engineers, and provides progressive disclosure as you go down the stack and a path to promote the adhoc/SQL level work up to reporting easily.
efromvt 2y ago
Had some more time to drill into this and we've ended up with a very similar approach to a lot of the metadata definition and resolution - I'd love to chat sometime about how you've solved some of the common problems (table selection w/ multiple sources, the constraint vs output projection, aggregation level, etc, etc)

emmanueloga_ 2y ago

I might be off here, but this seems like the right place to ask: don't most SQL replacements focus heavily on querying while largely overlooking insertion and updating? I get why querying gets more attention, insertions are usually straightforward and don’t need much simplification. Updates, on the other hand, can be a bit trickier since they often involve modifying data derived from complex queries. These tools seem geared toward data analysis and not data generation, which is ok: is nice focusing on a single problem and solving it "right". But! for projects where a single person handles data creation, analysis, and management, it feels cumbersome to use one set of tools for querying ("R" in CRUD) and another for creation, updates, and deletions ("C," "U," and "D"). I think a "SQL replacement" or approach covering all of CRUD could be interesting for projects of any scale. Something that I could pick instead of shopping for ORMs and/or lightweight query generators.

efromvt 2y ago
More effort has definitely been focused on the 'select' aspect, since you often select more than you mutate - and even in the future state, for data warehousing cases updates can be relatively rare. I definitely don't see it targeting core OLTP CRUD work, where abstractions can sometimes cause more problems then they solve - but I hope for projects that involve bulk data creation, analysis, and management - analytics like - it can be a complete solution once the 'persist'/ 'export' queries are developed further.

tluyben2 2y ago

This is great; I have been thinking about this for a long time. I like reading about past and current implementations that try to better sql; from a programming and a data science and performance perspective. I am aware of the ones you linked and some others like 'Real' (shakti.com) sql and some enhancements from papers. Anyway; nice one! Will try.

mritchie712 2y ago

Not a SQL replacement, but if you're looking for an open source semantic layer, Cube is the way to go [0] 0 - https://github.com/cube-js/cube https://github.com/cube-js/cube

deleted 2y ago
[deleted]
knowitnone 2y ago
always appreciate links to good tools

hipadev23 2y ago

[flagged]

d-lisp 2y ago
I was reading this comment and I have no expertise in sql; would you like to explain why do exemples "look like junior-level bloat" to you ?
- hipadev23 2y ago
  Their examples showing why Trilogy is so good are comparing it against poorly written SQL with poorly designed schemas. It reduces my confidence that their tool is actually solving real problems but instead was borne out of frustration in learning SQL and databases.
  - efromvt 2y ago
    Any particular examples you have in mind? The demo is just referencing https://github.com/duckdb/duckdb/tree/main/extension/tpcds/dsdgen/queries https://github.com/duckdb/duckdb/tree/main/extension/tpcds/d... which I wouldn't regard as a standard of good SQL; (implicit joins, yikes!) - but is a useful capability reference (as is tpc-ds in general). As I tried to convey, I like SQL a lot - my frustration is more around the lifecycle and maintainability. Happy to add more ergonomic references in other places, if you have some good examples to reference against?
deleted 2y ago
[deleted]

hobofan 2y ago

One thing that I am always looking for in a new "reusable", "composable" SQL tool is reuse of the same analytical queries across different source tables. My litmus test: I have a table "people" with the columns "people.firstname", "people.lastname", and a table "persons" with the columns "persons.firstname", "persons.lastname". I now want to create a query that gives me the "fullname" (".firstname" + " " + ".lastname") of all rows of both tables. If I have to spell out the logic for how to calculate the fullname in the query twice, the test is failed. (Taking the shortcut of creating the union of both tables first is not allowed, but I can't think of a simple example that enforces that restriction). For some reason, all of the solutions (PRQL, Malloy, dbt) that try to make SQL more reusable don't really consider this kind of reuse, and with that ultimately fall flat for the use-cases I would typically have for them. Sadly, Trilogy doesn't seem to be any better on that front.

friendly_deer 2y ago
This is something I've never thought about before, and haven't had a use case for, so I'm genuinely interested in learning a little more about your use cases if you can elaborate a little futher.
- default-kramer 2y ago
  Sometimes you want to be able to do something like "run some SQL, but instead of using the normal tables use these temp tables I just created." In particular, I wanted to do this in SQLite recently. I wanted to have one write process which would always remain unblocked. And I also wanted to be able to run certain tasks which would do some temporary/discardable DB manipulations as part of producing an output file. These tasks could open the SQLite DB in read-only mode; load relevant data into temp tables, manipulate that data, and write the output file. Everything would have worked great if only SQL were a more composable language.
hn_throwaway_99 2y ago
TBH, I don't think your test is very useful in real world environments. That is, you have 2 independent tables, and you're wanting the solution to depend on the fact that there are columns that are named the same across both tables. IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road. For example, I've been bitten in the past by "natural joins" when we've wanted to refactor something later. I definitely agree that I don't want to have to repeat logic within a single table, but the kind of syntactic sugar that is your litmus test is a big foot gun IMO.

deleted 2y ago

[deleted]