Rendered at 07:45:02 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
slowcache 12 hours ago [-]
I think an important benefit of a good ORM is to reduce the translations that you have to do between your mental model of the data and what you are trying to do with the data.
Before I started working a lot with SQL, ORMs fit my mental model better since I was more used to imperative programming languages and I thought they were easier to work with.
Now that I am very comfortable with SQL, I have to translate an ORM into the SQL that it would produce. So now they just add another step in between me and the data
kamma4434 2 hours ago [-]
There is no good ORM -like it says here https://github.com/l3nz/ObjectiveSync “Bad practice - if you hide the database, you may get something done quickly, but it's a bad idea. If yor Java code expects to have a collection of one million objects as an array, it does not matter if they are lazily loaded or not - some code somewhere might want to iterate over them, and this will kill the process. You cannot really forget that there is a database somewhere, and you should not do it.”
remywang 12 hours ago [-]
The point of Prela is exactly to remove that step of indirection, it gives you ORM ergonomics but compiles directly to operations on the physical columns, skipping SQL. At least for me I find it easier to think in Prela than to think in SQL, especially for complex queries, and I believe you’ll feel the same with some practice.
remywang 11 hours ago [-]
Author here, I will be at VLDB in Boston this coming week and will be very happy to chat about Prela.
You’ve got do a better job selling the title sorry.
I feel like the separation between a query & the query execution plan is one of the benefits of SQL. I trust the database system to do the right thing 99% of the time, and I don’t want to think about that either really.
remywang 8 hours ago [-]
The remaining 1% is usually uncomfortable if not down right painful.
But yes, I agree a query optimizer is valuable. Luckily there’s nothing stopping us from implementing one, as Prela is algebraic and all optimization techniques for SQL carry over.
grebc 6 hours ago [-]
Linq, in dot net, is a real life working example of what you’re talking about and it’s agnostic of how the storage is structured.
That said I still rarely use the but the basics of Linq. I just don’t see the upgrade of what you’re solving here.
wavemode 9 hours ago [-]
I feel the opposite way. I very rarely trust the database system to do the right thing. Any query more complex than a basic lookup by primary key requires me to look at query plans and validate that indexes are in place and are being used. Otherwise we risk the production server grinding to a halt.
Personally I'd love a more explicit form of SQL that allowed specifying things like "select via scan" or "select via index lookup". (I don't think this HN submission is that - I'm just saying generally.)
grebc 6 hours ago [-]
You should investigate how you’re storing & retrieving data. A database system is going to abstract away various things.
If you need direct control of primitives then there’s always fopen as SQLite says.
megagpt1 8 hours ago [-]
Most databases that aren't postgres have some sort of query hinting.
petilon 9 hours ago [-]
The example is not particularly impressive. The SQL equivalent is much easier to understand, which means it is easier to maintain. Number of lines is not an interesting metric; understandability and maintainability are more important.
kamma4434 2 hours ago [-]
I’m not sure how they come up with the 20-line example anyway - it’s one join.
Traditionally SQL uses a lot of lines because you put one thing per line, but so what?
Planktonne 12 hours ago [-]
This seems harder to read than SQL, and only less verbose if you assume that an SQL database would be built with Prela's limitations in mind, which doesn't feel like a reasonable assumption.
remywang 11 hours ago [-]
With some syntax sugar it looks almost exactly like SQL [1]. Here I’m showing the unsweetened edition for didactic purposes.
Thanks, I had not spotted that. I guess "better SQL" claim makes it seem like it is something more novel.
prathje 13 hours ago [-]
Interesting concept which reminds of the operations available in pandas.
I disagree though with the statement of SQL needing 20 lines. The given query feels verbose and has lots of redundant conditions. Not saying that it is short but a better analogy could look like this:
SELECT DISTINCT an.name, t.title
FROM keyword k
JOIN movie_keyword mk ON mk.keyword_id = k.id
JOIN title t ON t.id = mk.movie_id
JOIN movie_companies mc ON mc.movie_id = t.id
JOIN company_name cn ON cn.id = mc.company_id
JOIN cast_info ci ON ci.movie_id = t.id
JOIN aka_name an ON an.person_id = ci.person_id
WHERE k.keyword = 'character-name-in-title' AND cn.country_code = '[us]';
amluto 11 hours ago [-]
I would go one step farther: the SQL is awkward and long because the SQL language not at all optimized for data that is normalized all the way to binary relations.
And if you’re trying to benchmark one of these binary relationship query tools against DuckDB, keep in mind that DuckDB is heavily optimized for wide tables and is really not heavily optimized for point queries.
(Also, I, personally, would be a bit unhappy with a DBMS that cannot express, as part of the schema, that a movie has at most one or exactly one title.)
andai 12 hours ago [-]
At the bottom is the actual code for the "language", which is only 79 lines.
I found it helpful to read it first and then go back to the article. (On my initial reading I was like, "okay, but what is a Rel?")
FTA: “The motivation for focusing on binary relations is that they generalize functions. Functions are powerful because they compose, making them the building blocks of programs. A function maps every input to a unique output, where as a relation can map an input to multiple different outputs. In a sense, a relation can be viewed as a nondeterministic function”
If “A function maps every input to a unique output, where as a relation can map an input to multiple different outputs”, wouldn’t a binary relation have the same problem? I know they mean to say a binary relation isn’t a relation in that sense, but that text could do with better terminology.
Also, and more importantly, I don’t see how “binary” is essential here. What is essential is the uniqueness constraint. Compare Relational Algebra (https://en.wikipedia.org/wiki/Relational_algebra) with SQL.
remywang 11 hours ago [-]
Ah, that's not what I meant to say. You're talking about bag vs set semantics. Prela implements bag semantics just like SQL.
That sentence should say "a binary relation can map an input to multiple different outputs", and that's not a bad thing. It's exactly how binary relations generalize functions, and we want that because that lets us compose binary relations like how we compose functions!
thrance 11 hours ago [-]
"Binary" applied to a relation just means it links pairs of elements, (left, right) for example. Functions are special cases of binary relations, in that each "left" element is linked to at most one "right" element. But the general case of a relation can have multiple right elements for a single left element. So TFA's correct.
remywang 11 hours ago [-]
Yes this is correct, thank you.
wbadart 10 hours ago [-]
The core relation composition operator reminds me of Alloy's dot-join operator [1]. Wondering if anyone can comment on the differences, theoretical or practical?
Cool! That's both unsurprising, given the apparent similarities, but also a little surprising, since Alloy is built on relational algebra, which you're very careful to distinguish from TAR in your paper. (Great read, btw!)
remywang 8 hours ago [-]
Thanks! Alloy is also based on TAR, they just call it the more common name of relation algebra (not relational).
kscarlet 13 hours ago [-]
Cool language! I thought dplyr and datalog are both local optima (forget about the three-letter abomination) but I now declare this language the global optimum of query language.
> In contrast, Prela can be implemented extremely close to the metal. The Rust implementation inlines operators and compiles them into tight fused loops over raw arrays, running several times faster than DuckDB even without a query optimizer.
This will be true in Common Lisp as well. Now someone just have to implement it.
Or maybe I should steal the syntax and compile to SQL first, just so people can use existing DBMS.
kscarlet 12 hours ago [-]
On second thought, some skepticism on performance comparison:
1. do both systems access everything from memory?
2. do both systems have the same kind of indices?
3. do either system tradeoff scan performance for faster/acceptably fast updates?
remywang 12 hours ago [-]
1. Yes
2. No. Prela’s speedup is largely due to indexing. We tried to port the same indexing tricks back to duckdb but it wouldn’t let us. See the paper [1] for details
3. Prela focuses on analytical queries at least for now
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM aka_name AS an,
cast_info AS ci,
company_name AS cn,
keyword AS k,
movie_companies AS mc,
movie_keyword AS mk,
name AS n,
title AS t
WHERE cn.country_code ='[us]'
AND k.keyword ='character-name-in-title'
AND an.person_id = n.id
AND n.id = ci.person_id
AND ci.movie_id = t.id
AND t.id = mk.movie_id
AND mk.keyword_id = k.id
AND t.id = mc.movie_id
AND mc.company_id = cn.id
AND an.person_id = ci.person_id
AND ci.movie_id = mc.movie_id
AND ci.movie_id = mk.movie_id
AND mc.movie_id = mk.movie_id;
Cleaned up written as ON joins eliminating redundant quals:
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM cast_info AS ci
JOIN name AS n ON n.id = ci.person_id
JOIN title AS t ON t.id = ci.movie_id
JOIN aka_name AS an ON an.person_id = n.id
JOIN movie_keyword AS mk ON mk.movie_id = t.id
JOIN keyword AS k ON k.id = mk.keyword_id
JOIN movie_companies AS mc ON mc.movie_id = t.id
JOIN company_name AS cn ON cn.id = mc.company_id
WHERE cn.country_code = '[us]'
AND k.keyword = 'character-name-in-title';
The keyword and company branches only control existence though; their row multiplicities cannot affect MIN. We can therefore optimize this using EXISTS:
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM cast_info AS ci
JOIN title AS t ON t.id = ci.movie_id
JOIN aka_name AS an ON an.person_id = ci.person_id
WHERE EXISTS
(
SELECT 1
FROM movie_keyword AS mk
JOIN keyword AS k ON k.id = mk.keyword_id
WHERE mk.movie_id = t.id
AND k.keyword = 'character-name-in-title'
)
AND EXISTS
(
SELECT 1
FROM movie_companies AS mc
JOIN company_name AS cn ON cn.id = mc.company_id
WHERE mc.movie_id = t.id
AND cn.country_code = '[us]'
);
Shameless plug: We're working on a new proposed SQL feature to add explicit syntax for key joins: https://keyjoin.org
Here is how the query could then be rewritten further:
SELECT MIN(an.name) AS cool_actor_pseudonym,
MIN(t.title) AS series_named_after_char
FROM cast_info AS ci
JOIN title AS t FOR KEY (id) <- ci (movie_id)
JOIN aka_name AS an ON an.person_id = ci.person_id
WHERE EXISTS
(
SELECT 1
FROM movie_keyword AS mk
JOIN keyword AS k FOR KEY (id) <- mk (keyword_id)
WHERE mk.movie_id = t.id
AND k.keyword = 'character-name-in-title'
)
AND EXISTS
(
SELECT 1
FROM movie_companies AS mc
JOIN company_name AS cn FOR KEY (id) <- mc (company_id)
WHERE mc.movie_id = t.id
AND cn.country_code = '[us]'
);
Note: for this to work, I had to add referential constraints (aka "foreign keys") to the join-order-benchmark, which only had PRIMARY KEYs declared.
bvrmn 12 hours ago [-]
Examples don't show much more composability comparing to SQL. Even more Prela is heavily based on tuples and has same operation semantics as SQL.
I'm afraid I'm in the "uses column store" and not "understands the actual storage mechanisms", but this feels like something that's essentially the same thing?
Yes, I could ask my local AI, I'm just curious if anyone here's wondering the same thing.
andai 12 hours ago [-]
Very interesting. I'm not very fluent in SQL, so it would have been helpful to see some more side by side examples. (Since Prela seems a lot more ergonomic!)
Though maybe a reader fluent in SQL can compare them mentally on the fly?
This is in rust and we’re still tweaking the language, so the syntax is slightly different from the post.
trueno 12 hours ago [-]
am i the only one who's not afraid of sql taking up lines? sql thats formatted well is beautiful to read my brain enjoys it. it's way easier to read sql in terms of "what resultset is this trying to build" then it is to pick apart some fluent api lookin orm on top of sql
somat 12 hours ago [-]
I am in that club. As someone who quite enjoys writing sql but does not like the big sql strings intermingled in the rest of the code I even wrote a clever little python library that loads the queries from files as a function call, that is, you have a file with a pure sql query with parameterized variables and you call it like "for row in sql.video_search(title_like='bridge', date_after='1964-1-1', date_before='1975-1-1')" Nowhere near an orm, everything just produces a result set.
I am sure there are many projects like it, I suspect it is like static site generators and notekeeping apps, easy enough that everybody just makes their own. But this one is mine, and I have grown quite fond of it and use it in all my scripts. It is a little more magic than I am normally comfortable with. dynamic function generation is a bit of a black art, but having each query as it's own callable unit is super handy.
trueno 8 hours ago [-]
and i think this is somewhere in the ballpark of a p good approach, treat it as a dataset that has some predefined queries to get what's needed if you're OCD about seeing it in the code
sql is an interface query way to talk to databases, and treating things as datasets where their initialization query lives in a certain spot that flows into something generic/typed or whatever so that it can be wrangled elsewhere in the code with its own bespoke guarantees and handling behaviors is fine i think
i think i just don't think seeing sql in code or near code is a bad thing at all, to me it just means this code talks to a database and its using the database dialect/language to query the data. maybe people see string replacements over the query to apply variables or whatever as a bad thing i dunno, I think it's a relatively simple way to look directly at how a query might be dynamically adjusted on the fly. i dont have to like step through the lineage of methods and whatever other abstractions to formulate _how_ it put a query together. it's definitely annoying that there's some wild west feel to having so many dialects of sql, but it's a hard bet to make that you're going to make a better sql in 11 lines of code. SQL looks different than code because it is different, it's entire function is different, different paradigms are at play it's specifically purposed for querying and it excels at that on every front.
mamcx 12 hours ago [-]
I also work on this area (https://tablam.org) and have used languages where this weird, poorly developed language SQL was not the main interface (FoxPro).
Think on this: You imagine yourself writing a regular website with ONLy sql? no, because SQL is not a "programming language" for developers.
Is possible you could think in various ideas about why is "nonsensical" to make an app with a relational language (that SQL clearly is not) but is the same as with OOP or functional: there is not reason to be a problem, and there is a lot of things that will be far easier if a proper relational language is used, like for example, is unnecessary and ORM and/or is not complicated and confusing to make one.
slowcache 12 hours ago [-]
I'm in this boat, especially if you're language supports multi-line strings
drob518 11 hours ago [-]
Seems to be sort of triple store / datalog-ish.
Archelaos 9 hours ago [-]
How does it compare to Linq?
scotty79 10 hours ago [-]
The entire point of databases are indexes. Without indexes there is no point to keeping data in tables with rows and columns and having a special language (or even interface) for querying.
kurtis_reed 12 hours ago [-]
People don't use SQL because it's a good language
tabith 12 hours ago [-]
this is utterly fascinating.
thinking of LLM usage... it's so close to how LLMs think anyway, vector similarity also being a binary relation. LLM stops blindly guessing SQL and instead starts navigating data straight away.
Before I started working a lot with SQL, ORMs fit my mental model better since I was more used to imperative programming languages and I thought they were easier to work with.
Now that I am very comfortable with SQL, I have to translate an ORM into the SQL that it would produce. So now they just add another step in between me and the data
Unrelated, we also have a tutorial on instance-optimal join algorithms: https://www.vldb.org/2026/program.html#tut-2
I feel like the separation between a query & the query execution plan is one of the benefits of SQL. I trust the database system to do the right thing 99% of the time, and I don’t want to think about that either really.
But yes, I agree a query optimizer is valuable. Luckily there’s nothing stopping us from implementing one, as Prela is algebraic and all optimization techniques for SQL carry over.
That said I still rarely use the but the basics of Linq. I just don’t see the upgrade of what you’re solving here.
Personally I'd love a more explicit form of SQL that allowed specifying things like "select via scan" or "select via index lookup". (I don't think this HN submission is that - I'm just saying generally.)
If you need direct control of primitives then there’s always fopen as SQLite says.
Traditionally SQL uses a lot of lines because you put one thing per line, but so what?
[1]: https://remy.wang/blog/prela.html
I disagree though with the statement of SQL needing 20 lines. The given query feels verbose and has lots of redundant conditions. Not saying that it is short but a better analogy could look like this:
SELECT DISTINCT an.name, t.title
FROM keyword k
JOIN movie_keyword mk ON mk.keyword_id = k.id
JOIN title t ON t.id = mk.movie_id
JOIN movie_companies mc ON mc.movie_id = t.id
JOIN company_name cn ON cn.id = mc.company_id
JOIN cast_info ci ON ci.movie_id = t.id
JOIN aka_name an ON an.person_id = ci.person_id
WHERE k.keyword = 'character-name-in-title' AND cn.country_code = '[us]';
And if you’re trying to benchmark one of these binary relationship query tools against DuckDB, keep in mind that DuckDB is heavily optimized for wide tables and is really not heavily optimized for point queries.
(Also, I, personally, would be a bit unhappy with a DBMS that cannot express, as part of the schema, that a movie has at most one or exactly one title.)
I found it helpful to read it first and then go back to the article. (On my initial reading I was like, "okay, but what is a Rel?")
https://github.com/remysucre/prela/blob/main/tutorial/prela....
If “A function maps every input to a unique output, where as a relation can map an input to multiple different outputs”, wouldn’t a binary relation have the same problem? I know they mean to say a binary relation isn’t a relation in that sense, but that text could do with better terminology.
Also, and more importantly, I don’t see how “binary” is essential here. What is essential is the uniqueness constraint. Compare Relational Algebra (https://en.wikipedia.org/wiki/Relational_algebra) with SQL.
That sentence should say "a binary relation can map an input to multiple different outputs", and that's not a bad thing. It's exactly how binary relations generalize functions, and we want that because that lets us compose binary relations like how we compose functions!
[1]: https://practicalalloy.github.io/chapters/structural-topics/...
> In contrast, Prela can be implemented extremely close to the metal. The Rust implementation inlines operators and compiles them into tight fused loops over raw arrays, running several times faster than DuckDB even without a query optimizer.
This will be true in Common Lisp as well. Now someone just have to implement it.
Or maybe I should steal the syntax and compile to SQL first, just so people can use existing DBMS.
1. do both systems access everything from memory?
2. do both systems have the same kind of indices?
3. do either system tradeoff scan performance for faster/acceptably fast updates?
2. No. Prela’s speedup is largely due to indexing. We tried to port the same indexing tricks back to duckdb but it wouldn’t let us. See the paper [1] for details
3. Prela focuses on analytical queries at least for now
[1]: https://arxiv.org/abs/2607.26356
Shameless plug: https://github.com/baverman/sqlbind-t
If anyone can point me to a huge SQL query, I’ll take it up as a challenge to rewrite in Prela!
Prela’s semantics is based on an algebra of binary relations (unfortunately called relation algebra [1]), not the standard relational algebra.
[1]: https://arxiv.org/abs/2607.26356
Yes, I could ask my local AI, I'm just curious if anyone here's wondering the same thing.
Though maybe a reader fluent in SQL can compare them mentally on the fly?
This is in rust and we’re still tweaking the language, so the syntax is slightly different from the post.
I am sure there are many projects like it, I suspect it is like static site generators and notekeeping apps, easy enough that everybody just makes their own. But this one is mine, and I have grown quite fond of it and use it in all my scripts. It is a little more magic than I am normally comfortable with. dynamic function generation is a bit of a black art, but having each query as it's own callable unit is super handy.
sql is an interface query way to talk to databases, and treating things as datasets where their initialization query lives in a certain spot that flows into something generic/typed or whatever so that it can be wrangled elsewhere in the code with its own bespoke guarantees and handling behaviors is fine i think
i think i just don't think seeing sql in code or near code is a bad thing at all, to me it just means this code talks to a database and its using the database dialect/language to query the data. maybe people see string replacements over the query to apply variables or whatever as a bad thing i dunno, I think it's a relatively simple way to look directly at how a query might be dynamically adjusted on the fly. i dont have to like step through the lineage of methods and whatever other abstractions to formulate _how_ it put a query together. it's definitely annoying that there's some wild west feel to having so many dialects of sql, but it's a hard bet to make that you're going to make a better sql in 11 lines of code. SQL looks different than code because it is different, it's entire function is different, different paradigms are at play it's specifically purposed for querying and it excels at that on every front.
Think on this: You imagine yourself writing a regular website with ONLy sql? no, because SQL is not a "programming language" for developers.
Is possible you could think in various ideas about why is "nonsensical" to make an app with a relational language (that SQL clearly is not) but is the same as with OOP or functional: there is not reason to be a problem, and there is a lot of things that will be far easier if a proper relational language is used, like for example, is unnecessary and ORM and/or is not complicated and confusing to make one.
thinking of LLM usage... it's so close to how LLMs think anyway, vector similarity also being a binary relation. LLM stops blindly guessing SQL and instead starts navigating data straight away.