A better SQL in 11 lines of code

rremywang about 4 hours ago 24 commentsRead Article on prela-lang.org

DE version is available. Content is displayed in original English for accuracy.

⚡ Community Insights

Discussion Sentiment

89% Positive

Analyzed from 961 words in the discussion.

Discussion (24 Comments)Read Original on HackerNews

slowcache•about 1 hour 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

remywang•about 1 hour 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.

Planktonne•33 minutes 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•22 minutes ago

With some syntax sugar it looks almost exactly like SQL [1]. Here I’m showing the unsweetened edition for didactic purposes.

[1]: https://remy.wang/blog/prela.html

andai•about 1 hour 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?")

https://github.com/remysucre/prela/blob/main/tutorial/prela....

andai•about 1 hour 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?

remywang•26 minutes ago

Here are some SQL queries from standard benchmarks rewritten in Prela: https://github.com/remysucre/prela/tree/cidr#queries

This is in rust and we’re still tweaking the language, so the syntax is slightly different from the post.

bvrmn•about 1 hour 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.

Shameless plug: https://github.com/baverman/sqlbind-t

remywang•36 minutes ago

Compositionality is hard to show with a small example because it really only comes through at scale.

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

mwcremer•about 1 hour ago

Looks a lot like 6NF (https://en.wikipedia.org/wiki/Sixth_normal_form)

frizlab•about 1 hour ago

See first footnote

mwcremer•17 minutes ago

Thanks, I had not spotted that. I guess "better SQL" claim makes it seem like it is something more novel.

kscarlet•about 2 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•about 1 hour 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•about 1 hour 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

[1]: https://arxiv.org/abs/2607.26356

kscarlet•40 minutes ago

Thanks! Kudos for the great work!

prathje•about 2 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•2 minutes 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

bradleyy•about 1 hour ago

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.

trueno•about 1 hour 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•about 1 hour 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.

mamcx•about 1 hour ago

I also work on this are (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•about 1 hour ago

I'm in this boat, especially if you're language supports multi-line strings

kurtis_reed•about 1 hour ago

People don't use SQL because it's a good language

tabith•about 1 hour 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.