github.com
We're excited to be releasing 0.2[1], the first version of PRQL you can use in your own projects. It wouldn't exist without the feedback we got from HackerNews when we originally posted the proposal.
from employees
join side:left positions [id==employee_id]
SELECT
employees.*,
positions.*
FROM
employees
LEFT JOIN positions ON id = employee_id
I would love to see Datalog/SPARQL-style implicit joins to make graph traversals like "which users have edited documents I own?" less verbose.
I don't think we do joins that much better than SQL does. We're thinking whether there's potential there, maybe through understanding foreign keys — but we're being conservative about introducing change without value.
And maybe a shorter alternative might be tie:arm/leg.
This part of the join operation should be an after thought - just a flag after the central argument of the transform which should be the condition you join over.
Not my initial idea though: I was just looking at short words that might hold the analogy need, from "relationship" you easily come to "tie", and then "arm/leg" for "anterior/posterior" seems pretty straight forward and analogous to "antecedent/(consequent|postcedent|succedent)".
from employees join optional positions [id==employee_id] --> LEFT JOIN
from employees join positions [id==employee_id] --> JOIN
Then you'd use "optional right" or something similar for the "right join" case
Thank you!
It is the central part of RM which is difficult to model using other methods and which requires high expertise in non-trivial use cases. One alternative to how multiple tables can be analyzed without joins is proposed in the concept-oriented model [1] which relies on two equal modeling constructs: sets (like RM) and functions. In particular, it is implemented in the Prosto data processing toolkit [2] and its Column-SQL language [3]. The idea is that links between tables are used instead of joins. A link is formally a function from one set to another set.
[1] Joins vs. Links or Relational Join Considered Harmful https://www.researchgate.net/publication/301764816_Joins_vs_...
[2] https://github.com/asavinov/prosto data processing toolkit radically changing how data is processed by heavily relying on functions and operations with functions - an alternative to map-reduce and join-groupby
[3] Column-SQL https://prosto.readthedocs.io/en/latest/text/column-sql.html
E.g. a parameterised aggregate query that retrieves the name and average rating of a film starring cast members whose names match the input names:
[:find ?name (avg ?rating)
:in $ [?name ...] [[?title ?rating]]
:where
[?p :person/name ?name]
[?m :movie/cast ?p]
[?m :movie/title ?title]]
I wrote a toy Datalog -> SQLite compiler: https://percival.jake.tl/
Other Datalog -> SQL compilers I know of:
- Originally from Mozilla, now independent: https://github.com/qpdb/mentat
- From Google: https://logica.dev/
enum SchoolType {
College {
degrees: Vec<Degree>
},
HighSchool
}
Part of me has wondered if a language is the solution. Maybe just a better query builder with support for sum types is necessary. But I suppose there's something useful about having a consistent model based around a language, even if people aren't writing the language directly.
CREATE TABLE schools (id SERIAL PRIMARY KEY);
CREATE TABLE colleges (id INTEGER NOT NULL REFERENCES schools (id));
CREATE TABLE high_schools (id INTEGER NOT NULL REFERENCES schools (id));
Another alternative is to make one big table with check constraints but that's also hairy in its own right:
CREATE TYPE school_type AS ENUM ('college', 'high_school');
CREATE TABLE schools (
id SERIAL PRIMARY KEY,
type school_type,
/* college columns */,
/* high school columns */,
CHECK (type = 'college' AND /* college column constraints */),
CHECK (type = 'high_school' AND /* high school column constraints */)
);
CREATE TYPE school_type AS ENUM ('college', 'high_school');
CREATE TABLE schools (
id SERIAL PRIMARY KEY,
type school_type,
unique (id, type)
);
CREATE TABLE colleges (
id INTEGER NOT NULL,
type school_type default 'college',
check (type='college'),
foreign key (id, type) references school(id, type)
);
foreign key (id, 'college') references school(id, type)Do you have any real world scenarios where you've faced this problem?
In your example, you wouldn't model it like that. A school just needs an attribute that identifies the type of school (high school or college), and other attributes that would be common to both.
I'm sure there's lots of examples but it's late and I'm struggling to think of one that a good normalized data model couldn't handle.
The latter makes little sense. What constraints are you placing on a college that do not also apply to a highschool, given they're both schools?
> The other thing in the grandparent's comment that's a constant pain in SQL is representing an ordered list: how do you insert items into the middle of the list? Depending on your database, it can also be painful to renumber the other items.
I'm unclear on what you mean by this. If you want a list of records ordered in a certain fashion, there's an entire "ORDER BY" clause for that express purpose. If you're trying to add "extra" data into the middle of some list that is not otherwise represented in the data in the database, that's essentially business logic and you should be using some kind of custom view or procedure to do that or doing it inside your application code.
If it's just a question of how you add data into the middle of a resultset from actual data in a table based on some arbitrary ordering, you can do that too, people solved that problem ages ago by simply having an ORDER column or similar that's just an int with whatever likeliest precision you get, e.g.: default might be 1000 and then if need be you can insert 999 items between two others before needing to do a re-numbering on the column. These are dumb tricks but needing to "insert a record between two other records" is often also a dumb trick someone is trying to do in the database because they haven't designed things well elsewhere.
I'd venture the 99.9% case is querying real live data in the database and ordering it by factual things like record names, dates of update or creation, status, etc.
EDIT: To expand further, I would generally model a playlist as its own tables anyways. Something like:
CREATE TABLE playlist (
id ID_TYPE_FOR_DB PRIMARY KEY,
name varchar(4096), /* Or whatever storage size makes sense */
)
CREATE TABLE playlist_entry (
id ID_TYPE_FOR_DB PRIMARY KEY,
song_ref ID_TYPE_FOR_DB FOREIGN KEY REFERENCES songs(id),
order INT, /* Or bigint or whatever you want */
)If the DBMS supports it, you can add check constraints that query the other tables. See for example here: https://stackoverflow.com/a/2588427
That sounds like a nitpick, but man is it useful when you need it.
Notice how the first thing in PRQL is the table declaration.
The fact that UPDATE and INSERT have different syntaxes for basically specifying the same mutation operation is pretty dumb.
(No foreign key constraints, but those are falling out of use in some cases due to inability to online migrate mysql schemas anyways.)
CREATE TABLE dirents (
parent bigint NOT NULL,
child_dir bigint,
child_file bigint,
child_symlink bigint,
basename text NOT NULL,
-- Ensure exactly one type of child is set
CHECK (num_nonnulls(child_dir, child_file, child_symlink) = 1),
CONSTRAINT dirents_child_dir_fkey FOREIGN KEY (child_dir) REFERENCES dirs (id),
CONSTRAINT dirents_child_file_fkey FOREIGN KEY (child_file) REFERENCES files (id),
CONSTRAINT dirents_child_symlink_fkey FOREIGN KEY (child_symlink) REFERENCES symlinks (id),
PRIMARY KEY (parent, basename)
);I'm not sure if it's just the query language though - the definition language needs to make creating columns that are sum types trivial. For one-to-many data this might be a slight generalization of foreign key (compound of table tag + foreign key for that table). This can work for one-to-one data too, but can be a bit annoying having lots of tables compared to doing adding a couple nullable columns (plus there's also data locality differences). I suppose a wrapper language that covers both DDL and DML could work.
They're incomplete (e.g. don't work with foreign keys) and are essentially unmaintained.
I noticed one issue though... please don't copy the prefix of f-strings! That only exists because Python boxed itself in and it was literally the only ascii syntax left that could be used for string interpolation. It's mildly ugly but the best that could be done given those requirements. Not so here.
The way shells do it with single quotes producing literal strings and double quotes available for interpolation has not been topped imho. Triple quotes are a nice extension as well, not sure if that made it in.
And would you rather see "My {name}" or "My ${name}"? I personally dislike the $ prefix for all variables and interpolations...
https://infinum.com/blog/multiline-strings-ruby-2-3-0-the-sq...
CTEs provide this functionality already, don't they?
SELECT cols from table where <ANY_COLUMN> like '%foobar%'"
select col1 from table where col1 like '%foobar%' union select col2 from table where col2 like '%foobar%' union ...
Or did you mean like using the same CTE across multiple queries? Views / materialized views are good for that.
Need to delete from multiple tables with foreign keys back to a single primary table. This before deleting from the primary table, due to consistency.
We often get a "list" of pks, then use it in multiple "delete key in" statements. A kludge, but these are for one-off tests on a dev database.
from a
join b
It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.
Compilation does have to be stateless (for performance reasons), but we are planning to add some kind of schema definitions which could also specify foreign keys.
So joins without conditions would be possible, we'll look into it!
What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?
If it's not ambiguous, then let me do it. If I rely on ambiguity then throw an exception. In the case of multiple foreign keys, throw an exception, as there's no way to know which one I mean. It'd be nice if I could disambiguate the situation though. Normal SQL allows the `on` clause.
from TableA
inner join TableB on <expression>
from TableA
inner join TableB by ConstraintC
The same goes for the many to many relationship with an intermediate table. It could look something like this...
from TableA
inner join TableB through TableC
As applications grow, and initially simple lookup table semantics get more nuanced, it might be nice to be able to constrain the join on the lookup table like this...
from TableA
inner join TableB through TableC where <expression>
This is where many conveniences that use implicit data run into problems. A small convenience now for the possibility of accidentally breaking because of mostly unrelated changes later is a poor trade off for anyone that wants to have stable and consistent software.
This is likely one of those cases where you're better off with tooling to help make writing the correct unambiguous code easier (or automated away) than introducing a feature which leads to less stable systems in some cases.
Edit: Along the lines of what you note at the end, I would rather see joins able to use named relations as defined in the schema. Of there's a relation from table movie to table actor specifically names roles in the schema, I would rather be able to join movie on roles and have actors joined correctly using that relation, and aliases to roles which I could then use. Then you're using features that are designed and stable and not implicit and subject to changing how or whether they function based on semi-unrelated changes.
That might look like: "from movie relate roles" which is equivalent to "from movie join actor roles on movie.id = roles.movie_id", but because actor.movie_id has a constraint in the schema named roles which restricts it to a movie.id already.
I don't have a specific syntax in mind yet; for illustrative purposes:
defjoin r,m,a = %prejoin_roles() -> { # define a common join path between three relations r,m,a:
from r=ROLES # can hard-code table names or use parameters (which may refer to other parameters)
join m=MOVIES [r.movie_id = m.movie_id]
join a=ACTORS [r.actor_id = a.actor_id]
}
from r,m,a = %prejoin_roles()
select m.title, a.character_name
create table users (id int primary key, name text);
create table things (id int primary key, creator int references users);
from things select [id, creator.name];Compilation should fail and require you to explicitly specify what key to use. Please don’t do anything magic.
Probably the biggest constraint SQL language design has is that its on a live system — things are not compiled at the same time.
from a
join b on b.a_id = a.a_id
An unreasonable way to achieve that is to put the table name in every column. A more palatable way is to write some clever functions in your schema to scan the information table look for column name clashes (you essentially write a tiny "linter" inside your schema).
Select * from a join b using (a_id)
from a
join b [a_id]
It’s annoying adding another foreign key later and then having previously working queries fail at runtime due to an ambiguous join condition.
select * from a natural join b
(not based on fk constraints though, it will join on all attributes with the same name in the relations)
But I think that's a shortcoming of the client tool, rather than the language.
If SQL tools auto completed the join conditions as best as they could it would probably be a great help.
SQL addresses this via the natural join keyword `using`, where you enumerate the common columns between the two tables being joined. It isn't too convenient for your example unless your pk naming pattern happens to be `<entity>_id` instead of just `id` (note: this naming pattern has all sorts of other adverse consequences though). But it does provide convenience in some cases without introducing backwards compatibility risks as the schema evolves.
Of course, if you allow naming the relation when you create a foreign key, then you could use the source table-qualified relationship name for joins rather than the target table name, which would be unambiguous (and more communicative of intent).
E.g., for a hypothetical table with two self-fks:
FROM employees ee
INNER JOIN ee.manager mgr
INNER JOIN ee.team_lead leadI was looking through the documentation for conditional logic, control flow, IF(), and the CASE...WHEN...THEN operator. It seems like the ternary operator is the single way to implement conditionality?
At https://prql-lang.org/book/examples/functions.html I found the example `func if_valid x`, which is then used in the `derive` expression `prices_adj | ret | if_valid`. This usage of `if_valid` at the end of the pipeline seems a bit awkward: if I want to do the whole calculation only if some condition is met, I'd like to write it at the front of the pipeline. Can I nest the `ret` function in the if-function like so?: `func ret_if_valid x -> is_valid_price ? (ret x) : null` Then I guess I'd have to do:
return_total = prices_adj | ret_if_valid
SELECT CASE category WHEN 'a' THEN 'x' AS newCategory
...
SELECT SUM( if(newCategory = 'a', revenue, 0) ) AS aRevenue, ...
aggregate [
for x in ['a', 'b', 'c']:
(concat x 'Revenue') = sum ( [new_category == x] ? revenue : 0 ),
...]
I'd be interested in contributing to this, guess I'll take a look at the code and any community chat you've set up!
I not mature enough to fully appreciate the technical potential of the project, but the good ambient, the kindness and the growth potential is for sure worthwhile. I truly encourage everyone to contribute!
What about crazy operations like calculating percentile_cont? [2]
Or just in general, how would "implementation specific" queries end up looking?
[1] https://www.postgresql.org/docs/current/tutorial-window.html
[2] https://docs.microsoft.com/en-us/sql/t-sql/functions/percent...
Window functions are here [1]. (We should add these to the homepage too)
Implementation specific queries can be handled by the Dialect parameter [2], though there's still lots of work to do to build that out.
[1]: https://prql-lang.org/book/transforms/window.html
[2]: https://prql-lang.org/book/queries/dialect_and_version.html
I'd absolutely love to see the next level of this pipeline be continued where something like Observable Plot or ggplot2 like functionality where you can take your pipeline data analysis and directly plot it to visualize it.
(I'm a co-founder)
Possibly our focus is a bit different — I see EdgeDB as primarily focused on transactional queries, whereas PRQL is very focused on analytical queries. PRQL doesn't do quite as much — e.g. we don't model the relationships between entities, which is less functional but more compatible.
Feel free to reach out on Twitter if you think there's some way of us collaborating, or if you have any feedback or guidance for us.
> I see EdgeDB as primarily focused on transactional queries, whereas PRQL is very focused on analytical queries.
That's true to an extent currently, but we actually envisioned EdgeQL to be a capable analytical query language too. We'll release EdgeDB 2.0 in a couple of weeks and it will feature a powerful GROUP BY statement (read more about it here [1]) and in 3.0 we might ship window functions (or some equivalent).
With all that said PRQL looks cool!
[1] https://github.com/edgedb/rfcs/blob/master/text/1009-group.r...
> pronounced "Prequel".
- and I burst out laughing. Very good.
from my_table
group column_a (
aggregate (max column_b)
)
from my_table
group column_a (
sort [-column_b]
take 1
)
(We need better tests against real DBs, which is very much on our roadmap)
create function foo() returns bar as language prql $$<prql code here>$$;
It has a typescript definition file, but it looks like it's autogenerated and a bit clunky. You get back a CompileResult and have to call free() explicitly, it seems? That doesn't seem very idiomatic for JavaScript.
Also, the links to the documentation and examples in the README are broken.
We are currently working on compiling it for both Node.js and the browser target, and would be happy to see some advice if you are familiar with WASM!
export function compile(s: string): CompileResult;
export class CompileResult { free(): void;
readonly error: CompileError | undefined;
readonly sql: string | undefined;What is the purpose of the free() method?
Somewhat — you can use it in Jupyter now[1]; e.g.:
%%prql
from p = products.csv
group categoryID (
aggregate [average unitPrice]
)
There's also a cool TUI in PyPrql[2]
[1]: https://pyprql.readthedocs.io/en/latest/magic_readme.html
For example
{% prql %}
from source = {{ source('salesforce', 'in_process') }}
derive expected_sales = probability * value
join {{ ref('team', 'team_sales') }} [name]
group name (
aggregate (sum expected_sales)
)
{% endprql %}
SELECT
name,
SUM(source.probability * source.value) AS expected_sales
FROM
{{ source('salesforce', 'in_process') }} AS source
JOIN {{ ref('team', 'team_sales') }} USING(name)
GROUP BY
name
With your contribution we can get there faster!
The README states that "PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement. Like SQL, it's readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it transpiles to SQL."
What that means to me is that PRQL more naturally maps onto how I think about and work with data.
Say I have some dataset, `employees`, and I want to answer some questions about it like, for US employees, what is the maximum and minimum salary and how many employees are there:
from employees
filter country == "USA" # Each line transforms the previous result.
aggregate [ # `aggregate` reduces column to a value.
max salary,
min salary,
count, # Closing commas are allowed :)
]
If you find that it doesn't map well onto how you think about data pipelines then please let us know as we're constantly looking for more real world examples to help us iterate on the language!
Do you think the SQL complied by PRQL could be as effective and optimized by database engine as the direct-written SQL?
I currently have no reason to believe that the PRQL generated SQL would be any worse than hand written SQL. That said, I don't think we've currently looked at any ways of passing hints to the query planner. We're always open to suggestions!
In the worst case, you have full access to the generated SQL, and for absolutely crucial queries you can hand modify that SQL. At least PRQL might have saved you the trouble of writing a cumbersome window function or something like that (see for example the example of picking the top row by some GROUP BY expression).
The PRQL pipeline syntax would make for a much better experience for lnav since you're able to progressively refine a query without having to jump around. (You've probably noticed that many log services, like Sumologic, already provide a pipeline-style syntax instead of something SQL-like.) The nice thing is that you can simply keep typing to get the results you want and get a preview at each stage. For example, entering "from" and then pressing <TAB> would make it clear to the program that table-names should be suggested. The program could then show the first few lines of the table. Typing "from syslog_log | filter " and then pressing <TAB> would make it clear that columns from the syslog_log table should be suggested (along with some other expression stuff). And, then, the preview of the filtered output could be shown.
In the current implementation, pressing <TAB> just suggests every possible thing in the universe, whether it's appropriate or not. This leaves the poor with not much help after they've typed "SELECT". I find myself having to lookup docs/source to figure out column names or whatever and I wrote the darn thing. Ultimately, I think the analysis functionality just doesn't get used because interactively writing SQL is so user-hostile. So, I'm looking forward to seeing this succeed so that I can integrate it and still be able to use SQLite in the backend.
Are you able to add in the examples the following:
1) Use of delimiters for names that include space, etc. I don't know if PRQL uses double quote or square brackets.
2) Use of two/three/four-naming convention to refer to servers, databases, tables and columns.There are a lot of rough edges when building a string representing an SQL query in the programming language that you're using. You have to be careful to avoid SQL injections, for starters. Do the bindings for PRQL innovate at this level?
I'm used to SQL syntax, but this has definite appeal. As a small example, I like that it starts with the "from" clause, so autocomplete is more viable.
But we also have plans for doing things that some SQL databases may not support, such as pivot (rows to columns).
EDIT: I'm sorry, I didn't realize that even if something transpiles from one language to another it does not guarantee that one language can generate all strings of another language. But taking a look at the abstractions PRQL offers I would be very surprised to find it not capable of it.
I can transpile a pure language exposing only `if`, `while`, and `for` with no standard library and no interop to C - that definitely does not make it "trivial" that it can do everything SQL can do.
So I was hoping PRQL might have some sugar like that.
It'd be nicer for my brain if I could treat any select like a function that transforms a set. Instead, even with PRQL, I'm stuck examining source tables and fully understanding them before being able to understand the query.
This seems to have resolved a problem with SQL, you can't read it in a linear fashion.
When we start getting query optimizers for Pandas, much of the benefit of SQL will go away.
but wish this was somehow encoded as JSON, so you could easily build pipeline UI for complex SQL Generation.
> 0.2
No it ain't (in production).
Anyway, this looks great. I LOVE the fact that you've provided a book too. Consider me a fan!
But we are ready for people to start using it in their development work. Lmk if there's a better way of describing that.
Would I use this instead of proper SQL in a data warehouse / large app? Maybe not.
Would I use it to manually query DBs when I need some ad hoc info? For sure.
There's zero thought to any kind of ergonomics, there's no way to say "join table Y but prefix all its columns with employee_", it's expressed backwards ffs (instead of starting with FROM), results of queries with joins are forced to be flat tables and there's no way to get trees as you need 99% in app code - all the repetitve app code to "nest" entities in results that also needs to make brittles assumptions about ordering and uniqueness because people couldn't standardize on a "RESULT AS TREE [NESTING <Y> INTO <X>]" clause or smth. equivalent etc. etc.
PRQL though seems to also lack all the essetial features you would expect around joins.
Suff like Arrango DB's AQL seems to be a nice example of adding the missing feature to SQL, probably more of the need to accomodate graph data too, but it actually solves SQLs problems even in relational contexts - see https://www.arangodb.com/docs/stable/aql/tutorial-join.html#... .