Maybe you'd like to check FunSQL.jl, my library for compositional construction of SQL queries. It also follows algebraic approach and covers many analytical features of SQL including aggregates/window functions, recursive queries and correlated subqueries/lateral joins. One thing where it differs from dlpyr and similar packages is how it separates aggregation from grouping (by modeling GROUP BY with a universal aggregate function).
Writing an alternative syntax is straight forward. Perhaps prototype PRQL using xi's excellent FunSQL backend? This way it's working out of the gate. Once syntax+semantics are pinned, writing another backend in the language of your choice would then be easier. Getting the backend correct is non-trivial work, and xi has done this already. Besides, we need a sandbox syntax anyway, so it might be fun to collaborate.
FunSQL.jl requires Julia to run (obviously as it is a Julia library) but it
produces standard SQL so Julia in this case is just an implementation language.
I have re-implemented parts of FunSQL in Python and OCaml (the one I have ended
up using) and have added a concrete syntax similar to what you have in PRQL.
from employees
define
salary + payroll_tax as gross_salary,
gross_salary + benefits_cost as gross_cost
where gross_cost > 0 and country = 'usa'
group by title, country
select
title,
country,
avg(salary) as average_salary,
sum(salary) as sum_salary,
avg(gross_salary) as average_gross_salary,
sum(gross_salary) as sum_gross_salary,
avg(gross_cost) as average_gross_cost,
sum(gross_cost) as sum_gross_cost,
count() as count
order by sum_gross_cost
where count > 200
limit 20
But, in my mind, the biggest difference between PRQL and FunSQL is the way
FunSQL treats relations with `GROUP BY` - as just another kind of namespaces,
allowing to defer specifying aggregates. A basic example:
from users as u
join (from comments group by user_id) as c on c.user_id = u.id
select
u.username,
c.count() as comment_count,
c.max(created_date) as comment_last_created_date
The `c` subrelation is grouped by `user_id` but it doesn't specify any
aggregates - they are specified in the `select` below so you have all selection
logic co-located in a single place.
I think this approach is very powerful as it allows you to build reusable query
fragments in isolation but then combine them into a single query which fully
specifies what's being selected.
I like the explicit pipelining idea, seems much easier to reason about. Some comments:
I found the "# `|` can be used rather than newlines." a bit odd. So when using let, you're only transforming one column? I think the example would look weird with returns instead of |.
Depending on your intended target, it might help adoption if you stay closer to the naming conventions of that target. If you're targeting mainstream Java/Python/C#/Javascript etc. then functions need parentheses, "take 20" may be worse than slice, etc.
I think annotating microversions would get tiresome fast. I think the right way to think of this is that you put in a single version number like 1, and then only ever change that if you need to do backwards-compatible changes that cannot be handled by clever hacks in the runtime.
Also I think you should try writing one or more native wrappers in your intended target languages to make sure it's easy to interface between the two, even if it means you'd have to use dots in that language.
I could imagine an end game where the ergonomics were so good that a database like Postgres ends up with a native PRQL frontend. Not sure you're there yet, though. IMHO SQL as a query language suffers from a) sometimes really bad ergonomics, b) it's hard to wrap in another programming language (also ergonomics), c) it has far too many concepts - it's not orthogonal.
> I think annotating microversions would get tiresome fast. I think the right way to think of this is that you put in a single version number like 1, and then only ever change that if you need to do backwards-compatible changes that cannot be handled by clever hacks in the runtime.
Thanks good idea, I just changed this to remove the microversions. If we use SemVer, then before `1`, we'd hold versions compatible to the 0.X, and then to the X.
IMO you are at the forefront of where query languages need to and will go.
Some programmers like you see that SQL ordering is backwards to human thinking, except in the simplest cases. But many people with practice and sunk costs in their SQL expertise will be resistant. The resistance usually wins the day.
But sometimes, a useful tool gets created by one person, and a rift is created in that resistance. Think John Resig creating jQuery, leading to LINQ and many other similar patterns. You could be that person for database query languages, but how do you ensure that?
Maybe imagine what made jQuery easy to adopt and indispensable for programmers: easy availability as a simple .js download; solved the problem of DOM differences between browsers. Good luck to you, and thanks for sharing.
There was this professor of language who would say "Do you think the question ('are carpets furniture?') tells you something about the ambiguity of the word carpet, or do you think it tells you something about the ambiguity in the world?"
Similarly, I think joins are "tough" not because of the way SQL expresses them but because the logical possibilities of merging data from multiple tables are varied.
There is no such thing as a domain-agnostic SQL database that holds up under this kind of semantic scrutiny. I don't think that there ever could be.
If you are rolling a SQL schema for a home improvement contractor, it is extraordinarily unlikely that their specific business would expect any scenarios in which carpets are sometimes known as furniture.
Having a bounded context to operate within is what makes SQL magical for me. When people don't understand the business or simply the game around how you talk about the business, things start getting messy wrt joins.
The carpet discussion was simply to say that you can't take out all the complexity of a language if the domain it is meant to describe is complex. The language has a limit to how simple it can be.
I was not proposing a SQL database of carpets, or furniture, as a thought experiment.
SQL has effectively failed, as a standard, despite it's ubiquity. It's literally being aged out, which makes for opportunities for PRQL, etc to fill pragmatic gaps.
eg the lack of default column aliasing from joins
SELECT
A.id AS A__id,
A.name AS A__name,
B.id AS B__id,
B.name AS B__name
FROM A
LEFT JOIN B
ON A.other_id = B.other_id
When you could have:
SELECT
A.*,
B.*
FORMAT (TABLE__)
FROM A
LEFT JOIN B
ON A.other_id = B.other_id
IMO, this appears not to be something that solves the SQL learning curve but rather the usability of a query language with tooling.
I don't think there is much that could be done to address left, right, inner, outer join semantics. It's just something you have to learn if you want to do a lot of SQL (though, you are likely only ever going to use left and inner joins).
I'm really excited about languages that build on or are compiled to SQL, in the long-term (because I think it will take a very long time to build adoption).
The ones that particularly excite me are shorthands for SQL, even though their heavy use of symbols may be a detriment. One particular use case is in easily defining static authorization policy-queries that are backed by database data plus and have request variables injected during evaluation.
I am not very excited by datalog/prolog-based languages because I think logic languages are too unnatural to ever go mainstream. But I'd be excited to be wrong or for logic languages to become more friendly.
in a way SQL is Prolog and all reasoning for improvement of SQL should start from Prolog, because is where SQL started from. the expressive power of both languages is theoretically the same, even though SQL is much more comprehensible. but then again - certain complex task turn SQL in difficult-to-comprehend series of nested declarative operations on algebraic sets.
The link refers to dplyr not being able to use databases but actually there is a database backend for it in package dbplyr. See https://dbplyr.tidyverse.org/
It would be definitely interesting to have a TypeScript of some sort but for SQL.
So a more practical and prettier syntaxe like what I'm seeing here that compiles to SQL queries.
TypeScript is more verbose than JavaScript. While I love to use TypeScript I don't think I'd categorize it as prettier than JavaScript. And practical... well if you mean it is more maintainable then yes but if you mean faster to write then no.
I don't want a more verbose SQL I want a less verbose SQL!
I meant more the aspect that with TypeScript people would prefer to write in it and then compile to JavaScript because there is a benefit. With a PRQL with an SQL compilation target we would reap the benefit of a more practical and better syntax. In both cases they bring benefits but not in the same way.
Go to https://sqlframes.com/demo and in the code editor enter the following and execute (this example is taken from the first example on PRQL github page). It generates SQL, but it also computes and displays the results within the browser (though the data set below gives no results).
First, kudos because it takes courage to take on SQL in this way.
Second, this kind of reversed SQL (filter-first, select-last) is much easier to reason about than the original and keep in mind that I prefer to code complex queries in SQL than to build or translate them in the ORM of the project I'm working on.
Maybe a transpiler is an inevitable first step but I think that any SQL replacement should be itself the target of ORMs and run directly in the database CLI tools (psql / mysql ...) or IDEs (pgAdmin, MySQLAdmin, ...). What's the long term plan of the project?
I agree that integrating with the DB would allow much more from a lang. But PRQL is a bet that languages which start there (e.g Kusto) get lost because it requires changing DB, which is really hard. I worry EdgeDB may hit this issue too (but I'm really hoping it works, and they have an excellent team).
As I think you're suggesting — you could imagine a language starting out as a transpiler, and then over time DBs working with it directly, cutting out some of the impediment mismatch.
Malloy [1] is another point in space — it targets existing DBs through SQL queries but can also ask for schemas etc while developing.
> Second, this kind of reversed SQL (filter-first, select-last) is much easier to reason about than the original
Given that SQL clauses tend to be unambiguously terminated by the start of the next clause or the end of the statement, it surprises me that no engine has gone to accepting otherwise standard(-ish, as much as real DB vendor dialects are) SQL but without a mandated order of clauses.
And then combine that with dev tools that allow easy rearrangement of clauses, perhaps based on configured preferences so that you don’t even see the original if its not your preferred order, so that “Bob likes old-school SELECT FROM WHERE GROUP BY and Alice likes FROM WHERE GROUP BY SELECT” isn’t a problem.
Very cool! A couple questions/suggestions off the top of my head:
1. Did you consider using a keyword like `let` for column declarations, e.g. `let gross_salary = salary + payroll_tax` instead of just `gross_salary = salary + payroll_tax`? It's nice to be able to scan for keywords along the left side of the window, even if it's a bit more verbose.
2. How does it handle the pattern where you create two moderately complex CTEs or subqueries (maybe aggregated to different levels of granularity) and then join them to each other? I always found that pattern awkward to deal with in dplyr - you have to either assign one of the "subquery" results to a separate dataframe or parenthesize that logic in the middle of a bigger pipeline. Maybe table-returning functions would be a clean way to handle this?
> Did you consider using a keyword like `let` for column declarations
Yeah, the current design for that is not nice. Good point re the keyword scanning. I actually listed `let` as an option in the notes section. Kusto uses `extend`; dplyr uses `mutate`; pandas uses `assign`.
Awesome that you're responding to feedback like this!
Another suggestion around `let`: consider splitting it into two operations, for creating a new column and for modifying an existing one. E.g. called `let` and `set`. Those are in effect pretty different operations: you need to know which one is happening to know how many columns the table will have, and renaming a table column can with your current system change which operation is happening.
Splitting them into separate operations would make things easier on the reader: they can tell what's happening without having to know all the column names of the table. And it shouldn't really be harder for the writer, who ought to already know which they're doing.
I encountered something like this at my previous job. We had a DSL with an operation that could either create or modify a value. This made the code harder to read, because you had to have extra state in your head to know what the code was doing. When I rewrote the DSL (the rewrite was sorely needed for other reasons), I split the operation in two. I was worried people would have been too used to the old language, but in practice everyone was happy with it.
> Another suggestion around `let`: consider splitting it into two operations, for creating a new column and for modifying an existing one. E.g. called `let` and `set`.
Couldn’t we just not allow modifying an existing column? Ie. we would not allow
count = count + 1
But force the use of a new variable name:
new_count = count + 1
I think this makes for much more readable code, since the value of a variable does not depend on line number.
> 2. How does it handle the pattern where you create two moderately complex CTEs or subqueries (maybe aggregated to different levels of granularity) and then join them to each other? I always found that pattern awkward to deal with in dplyr - you have to either assign one of the "subquery" results to a separate dataframe or parenthesize that logic in the middle of a bigger pipeline. Maybe table-returning functions would be a clean way to handle this?
I don't have an example on the Readme, but I was thinking of something like (toy example):
table newest_employees = (
from employees
sort tenure
take 50
)
from newest_employees
join salary [id]
select [name, salary]
Or were you thinking something more sophisticated? I'm keen to get difficult examples!
There, each variable can be referenced by downstream steps. Generally, the prior step is referenced. Without table variables, your language implicitly pipes the most recent one. With table references, you can explicitly pipe any prior one. That way, you can reference multiple prior steps for a join step.
I haven't thought through that fully, so there may be gotchas in compiling such an approach down to SQL, but you can already do something similar in SQL CTEs anyway, so it should probably work.
SPARQL. Representing human information in relational tables goes against how people actually think and use information. We humans think in tremendous numbers of nested hierarchies, and recursive hierarchy traversal is a nightmare in relational databases. A graph is the structure for data that works best, is most efficient, and actually reflects how things are connected in our brains.
I'm a big fan of SPARQL, but the one thing that would concern me about trying to use it outside of the SemWeb context is simply that it assumes data is stored in <S,P,O> triples. Legacy databases by and large are not, so you need an adapter to bridge the representations. And while I know some exist, I haven't really used them and am not sure about the performance impact.
You can get quite far mapping the triple concept to (PK, column, value) or (PK, FK, related-row) and transpiling from there.
(I played around with this some years back, not to the point where anything came out of it worthy of publishing, but enough to be pleasantly surprised how far 'quite far' turned out to be in practice)
This actually looks like an improvement (and I like SQL). This feels closer to non-programmers, contrary to some other SQL "competitors" like that query language from InfluxDB.
Absolutely. If I use a SQL db for my applications (I'm a software dev for context), I generally write raw SQL vs using an ORM. I find the long term issues of an ORM to not be worth investing and understanding SQL.
I'm also not having to learn a new library, in addition to the standard DB connection libraries, ~if~ when I switch a language or platform for some project.
Yes, quite a lot I would imagine. I use it extensively at work and similarly sql heavy software companies in the past. That being said, I’ve also worked at places where they’ve avoided it like the plague – largely because few people were competent at it – and were moving away from relational DBs due to scale.
I prefer to write SQL as most alternatives ether have runtime surprises or require more roundtrips to the database. I mostly work on line-of-business software, so if I was doing simple CRUD apps I might have a different opinion.
Of course. Large companies like Pepsi have teams of analysts that only write SQL. I applied for a programming job there a long time ago and didn’t follow up when they explained in the interview that’s the only language they used.
You kinda have to. Assuming that you're using an ORM, you still need to understand how it translate to SQL, and help it do the translation correctly.
Personally I've seen developer use the Django ORM, and create application with terrible performance. Tweaking the queries, you can help guide the ORM to generate better SQL, which in turn will affect your performance greatly.
We're currently facing a problem with a custom who have an application with terrible performance/scaling issues. The entire thing is very database heavy, but interaction is done solely via Hibernate. I have nothing against Hibernate, it's a fine ORM, but you need to understand it well enough that you can guide it towards better queries (Which sometimes involve actually writing SQL). At some point you need to decide if your time isn't better spend learning SQL directly, as that via always provide you with better access to the functionality provided by the database.
I think it would be worthwhile to develop a shorthand of the same thing, suitable for use on the command prompt. Something using symbols as synonyms for keywords. Less eligble but more useful in a future when shell tools understand this syntax.
wr to linq to sql: the difference is linq works by making objects to tables and dotnet primitives to sql types, often producing really poor queries as a result
For any who want this kind of pipelining way-of-writing-SQL that has the benefit of existing in live production databases today, I highly, highly recommend looking into Postgres' and Snowflake's LATERAL JOIN keyword.
The TL;DR is that they allow you to reuse annotated and aggregated columns in an incredibly elegant way. Compared to OP's proposal, you still do need to start the query with what columns you want to come out at the end, and normal SQL weirdnesses still apply - but it's far, far, easier when writing massive analytics queries to see the flow of variables from one stage to another.
Awesome! Would love to see an implementation. I worked on something similar over the Summer. It’s just relational algebra with pipes for composition. If you are interested, we could get an antlr grammar going and plug it into this basic execution engine to get a feel for the language.
Yup, I posted that yesterday too. I think Malloy is really interesting — compile to SQL but give more integrations to the DB, like schema-during-development. It has a proper team, led by Lloyd Tabb.
I see EdgeQL as an excellent replacement for SQL in OLTP settings — it has great language integration and a unified relational & typing approach. (Please correct me if this is mistaken though).
I wrote the PRQL proposal for analytical / OLAP queries, where the pipeline of transformations are more important, and relations and typing are relatively less important.
EdgeQL is getting support for generic partitioning/aggregating `GROUP` very soon [1], so we are giving some love to the analytical side of things too :-)
We definitely need more collective effort put into "Better SQL", so PRQL is a welcome sight!
I've always wondered why there aren't query languages that embrace algebraic data types and pattern matching. Seems like an obvious fit to me. There's many times where you'd want to model a table that has either this scheme or that schema.
They can work well. In the project I'm working on the database uses algebraic datatype keys (i.e. tags and tag-dependent columns) to make the database faster and smaller than an equivalent relational schema, but the database is used via API rather than via a query language.
301 comments
[ 4.2 ms ] story [ 274 ms ] threadLet me know any feedback — as you can see it's still at the proposal stage. If it gains some traction I'll write an implementation.
I guess the biggest difference between FunSQL (and similarly dbplyr) and PRQL is that the former needs a Julia (or R) runtime to run.
I really respect the library and keen to see how it develops.
I have re-implemented parts of FunSQL in Python and OCaml (the one I have ended up using) and have added a concrete syntax similar to what you have in PRQL.
But, in my mind, the biggest difference between PRQL and FunSQL is the way FunSQL treats relations with `GROUP BY` - as just another kind of namespaces, allowing to defer specifying aggregates. A basic example: The `c` subrelation is grouped by `user_id` but it doesn't specify any aggregates - they are specified in the `select` below so you have all selection logic co-located in a single place.I think this approach is very powerful as it allows you to build reusable query fragments in isolation but then combine them into a single query which fully specifies what's being selected.
I found the "# `|` can be used rather than newlines." a bit odd. So when using let, you're only transforming one column? I think the example would look weird with returns instead of |.
Depending on your intended target, it might help adoption if you stay closer to the naming conventions of that target. If you're targeting mainstream Java/Python/C#/Javascript etc. then functions need parentheses, "take 20" may be worse than slice, etc.
I think annotating microversions would get tiresome fast. I think the right way to think of this is that you put in a single version number like 1, and then only ever change that if you need to do backwards-compatible changes that cannot be handled by clever hacks in the runtime.
Also I think you should try writing one or more native wrappers in your intended target languages to make sure it's easy to interface between the two, even if it means you'd have to use dots in that language.
I could imagine an end game where the ergonomics were so good that a database like Postgres ends up with a native PRQL frontend. Not sure you're there yet, though. IMHO SQL as a query language suffers from a) sometimes really bad ergonomics, b) it's hard to wrap in another programming language (also ergonomics), c) it has far too many concepts - it's not orthogonal.
Also want "OFFSET 20" from PG.
Thanks good idea, I just changed this to remove the microversions. If we use SemVer, then before `1`, we'd hold versions compatible to the 0.X, and then to the X.
Some programmers like you see that SQL ordering is backwards to human thinking, except in the simplest cases. But many people with practice and sunk costs in their SQL expertise will be resistant. The resistance usually wins the day.
But sometimes, a useful tool gets created by one person, and a rift is created in that resistance. Think John Resig creating jQuery, leading to LINQ and many other similar patterns. You could be that person for database query languages, but how do you ensure that?
Maybe imagine what made jQuery easy to adopt and indispensable for programmers: easy availability as a simple .js download; solved the problem of DOM differences between browsers. Good luck to you, and thanks for sharing.
Similarly, I think joins are "tough" not because of the way SQL expresses them but because the logical possibilities of merging data from multiple tables are varied.
If you are rolling a SQL schema for a home improvement contractor, it is extraordinarily unlikely that their specific business would expect any scenarios in which carpets are sometimes known as furniture.
Having a bounded context to operate within is what makes SQL magical for me. When people don't understand the business or simply the game around how you talk about the business, things start getting messy wrt joins.
I was not proposing a SQL database of carpets, or furniture, as a thought experiment.
eg the lack of default column aliasing from joins
When you could have:I don't think there is much that could be done to address left, right, inner, outer join semantics. It's just something you have to learn if you want to do a lot of SQL (though, you are likely only ever going to use left and inner joins).
The ones that particularly excite me are shorthands for SQL, even though their heavy use of symbols may be a detriment. One particular use case is in easily defining static authorization policy-queries that are backed by database data plus and have request variables injected during evaluation.
I am not very excited by datalog/prolog-based languages because I think logic languages are too unnatural to ever go mainstream. But I'd be excited to be wrong or for logic languages to become more friendly.
Here are some others I'm watching.
I don't want a more verbose SQL I want a less verbose SQL!
A little off topic, but I have created many databases by writing and converting simple text to database scripts
Try this: https://text2db.com/
const employees = SQL.values([{ title: 'Developer', country: 'USA', salary: 120, payroll_tax: 20, healthcare_cost: 6 }]); employees.schemaName = 'employees'; const { groupBy, where: { gt, eq, and }, agg: { count, sum, avg } } = SQL; return employees.pdf(SQL.script('[salary]+[payroll_tax]').as('gross_salary'),SQL.script('[gross_salary]+[healthcare_cost]').as('gross_cost')) .fdf(and(gt('gross_cost',0),eq('country','USA'))) .gdf(groupBy('title','country') ,avg('salary').as('average_salary') ,sum('salary').as('sum_salary') ,avg('gross_salary').as('average_gross_salary') ,sum('gross_salary').as('sum_gross_salary') ,avg('gross_cost').as('average_gross_cost') ,sum('gross_cost').as('sum_gross_cost') ,count().as('count')) .having(gt('count',200)) .orderBy('sum_gross_cost');
Second, this kind of reversed SQL (filter-first, select-last) is much easier to reason about than the original and keep in mind that I prefer to code complex queries in SQL than to build or translate them in the ORM of the project I'm working on.
Maybe a transpiler is an inevitable first step but I think that any SQL replacement should be itself the target of ORMs and run directly in the database CLI tools (psql / mysql ...) or IDEs (pgAdmin, MySQLAdmin, ...). What's the long term plan of the project?
I agree that integrating with the DB would allow much more from a lang. But PRQL is a bet that languages which start there (e.g Kusto) get lost because it requires changing DB, which is really hard. I worry EdgeDB may hit this issue too (but I'm really hoping it works, and they have an excellent team).
As I think you're suggesting — you could imagine a language starting out as a transpiler, and then over time DBs working with it directly, cutting out some of the impediment mismatch.
Malloy [1] is another point in space — it targets existing DBs through SQL queries but can also ask for schemas etc while developing.
[1] https://github.com/looker-open-source/malloy
(I noticed this on the github readme too)
Given that SQL clauses tend to be unambiguously terminated by the start of the next clause or the end of the statement, it surprises me that no engine has gone to accepting otherwise standard(-ish, as much as real DB vendor dialects are) SQL but without a mandated order of clauses.
And then combine that with dev tools that allow easy rearrangement of clauses, perhaps based on configured preferences so that you don’t even see the original if its not your preferred order, so that “Bob likes old-school SELECT FROM WHERE GROUP BY and Alice likes FROM WHERE GROUP BY SELECT” isn’t a problem.
1. Did you consider using a keyword like `let` for column declarations, e.g. `let gross_salary = salary + payroll_tax` instead of just `gross_salary = salary + payroll_tax`? It's nice to be able to scan for keywords along the left side of the window, even if it's a bit more verbose.
2. How does it handle the pattern where you create two moderately complex CTEs or subqueries (maybe aggregated to different levels of granularity) and then join them to each other? I always found that pattern awkward to deal with in dplyr - you have to either assign one of the "subquery" results to a separate dataframe or parenthesize that logic in the middle of a bigger pipeline. Maybe table-returning functions would be a clean way to handle this?
> Did you consider using a keyword like `let` for column declarations
Yeah, the current design for that is not nice. Good point re the keyword scanning. I actually listed `let` as an option in the notes section. Kusto uses `extend`; dplyr uses `mutate`; pandas uses `assign`.
I opened an issue here: https://github.com/max-sixty/prql/issues/2
Another suggestion around `let`: consider splitting it into two operations, for creating a new column and for modifying an existing one. E.g. called `let` and `set`. Those are in effect pretty different operations: you need to know which one is happening to know how many columns the table will have, and renaming a table column can with your current system change which operation is happening.
Splitting them into separate operations would make things easier on the reader: they can tell what's happening without having to know all the column names of the table. And it shouldn't really be harder for the writer, who ought to already know which they're doing.
I encountered something like this at my previous job. We had a DSL with an operation that could either create or modify a value. This made the code harder to read, because you had to have extra state in your head to know what the code was doing. When I rewrote the DSL (the rewrite was sorely needed for other reasons), I split the operation in two. I was worried people would have been too used to the old language, but in practice everyone was happy with it.
This could _mostly_ be enforced by PRQL. There's a case where we transpile to:
...where we don't know whether or not we're overwriting an existing column. But it's a minority of cases, and the contract could stand within PRQL.I opened an issue here: https://github.com/max-sixty/prql/issues/6
Couldn’t we just not allow modifying an existing column? Ie. we would not allow
But force the use of a new variable name: I think this makes for much more readable code, since the value of a variable does not depend on line number.I don't have an example on the Readme, but I was thinking of something like (toy example):
Or were you thinking something more sophisticated? I'm keen to get difficult examples!Edit: formatting
There, each variable can be referenced by downstream steps. Generally, the prior step is referenced. Without table variables, your language implicitly pipes the most recent one. With table references, you can explicitly pipe any prior one. That way, you can reference multiple prior steps for a join step.
I haven't thought through that fully, so there may be gotchas in compiling such an approach down to SQL, but you can already do something similar in SQL CTEs anyway, so it should probably work.
(I played around with this some years back, not to the point where anything came out of it worthy of publishing, but enough to be pleasantly surprised how far 'quite far' turned out to be in practice)
One thing I like about Flux is the ability to split streams and return multiple distinct aggregations. Very handy in Grafana dashboards!
I'm also not having to learn a new library, in addition to the standard DB connection libraries, ~if~ when I switch a language or platform for some project.
Personally I've seen developer use the Django ORM, and create application with terrible performance. Tweaking the queries, you can help guide the ORM to generate better SQL, which in turn will affect your performance greatly.
We're currently facing a problem with a custom who have an application with terrible performance/scaling issues. The entire thing is very database heavy, but interaction is done solely via Hibernate. I have nothing against Hibernate, it's a fine ORM, but you need to understand it well enough that you can guide it towards better queries (Which sometimes involve actually writing SQL). At some point you need to decide if your time isn't better spend learning SQL directly, as that via always provide you with better access to the functionality provided by the database.
And then dump the queries via https://stackoverflow.com/questions/1412863/how-do-i-view-th... or https://www.linqpad.net/ ?
[0] - https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL...
https://heap.io/blog/postgresqls-powerful-new-join-type-late...
https://stackoverflow.com/questions/28550679/what-is-the-dif...
https://docs.snowflake.com/en/sql-reference/constructs/join-...
The TL;DR is that they allow you to reuse annotated and aggregated columns in an incredibly elegant way. Compared to OP's proposal, you still do need to start the query with what columns you want to come out at the end, and normal SQL weirdnesses still apply - but it's far, far, easier when writing massive analytics queries to see the flow of variables from one stage to another.
- https://github.com/RCHowell/Sift - https://github.com/RCHowell/Sift/blob/main/src/main/kotlin/c...
This feels like it could cause compatibility issues in the future.
Offhand i thought PRQL seemed easier to reason about, but something about EdgeQL seems better to me.. though i can't describe it.
I wrote the PRQL proposal for analytical / OLAP queries, where the pipeline of transformations are more important, and relations and typing are relatively less important.
We definitely need more collective effort put into "Better SQL", so PRQL is a welcome sight!
[1] https://github.com/edgedb/rfcs/blob/21e581a188715c6ff82944b6...