I really like what PRQL[0] is doing. Fixes a lot of the unfortunate SQL syntax. My impossible dream is that after years of language stability, Postgres could make it a first party query language.

[0] https://prql-lang.org/

Wow, this looks like it fixes a lot of the minor gripes I have with SQL, thanks for mentioning it. It feels way less cludgy and "modernized" compared to SQL.

That reminds me of the Ecto DSL.

Maybe as a baby step we could just get legal trailing commas

Or force the big RDBMS to use the same syntax like SQL was intended to be, so I don't have to reach for an ORM or maintain different flavors of the same query.

If someone knows a tool that reliably converts PostgreSQL queries to something like SQLite, please share!

Not sure how such a tool could possibly exist, given all the functionality of Postgres that SQLite simply lacks.

You could ask ChatGPT to do this sort of conversion

Still not going to have wave around things like not having transactions or proper trigger functions.

SQLite does have transactions. But I do agree with your general point that Sqlite is worlds apart from Postgre

haven't really tried for that transform but have a look at https://www.jooq.org/translate/

sqlglot might be what you are looking for.

https://github.com/tobymao/sqlglot

The syntax looks very similar to Microsoft's Kusto Query Language (KQL): https://learn.microsoft.com/en-us/azure/data-explorer/kusto/...

It's interesting to see that everyone's attempt at fixing SQL seems to converge on surprisingly similar solutions...

Microsoft did linq first which is probably the basis for the rest.

From first principles, it seems reasonable to expect transformations to go in a linear order. From there you look for ways to minimize special cases (eg why is `where` different from `having`?). Then you can just layer on some quality of life things which would appear to a designer after five minutes of real-world use (dear god, please let me have trailing commas).

I would expect there to be a lot of convergent evolution as how to go from a pile of data to narrowing on what you want.

There are some ideas based on Datalog, like this one:

https://opensource.googleblog.com/2021/04/logica-organizing-...

I've read about edgedb in the past https://www.edgedb.com/blog/we-can-do-better-than-sql where they mainly changed the query language, underlying it still uses postgres. Though not sure how successful they've been.

You mean, the ORM?

Edgedb also is nice if you want a more object like model for your db.

ORMs generally solve a different problem than the query language. In SQL, everything is a table (that's not 100% true, but it's close enough). So, joins still result in a flat list of records.

ORMs solve the problem of translating a flat list of records into objects that have arrays of other objects. Some people also use them to avoid having to write SQL, but that's honestly a secondary feature.

You can have a more modern query language that still results in tables

Spark dataframes in Scala seem to combine the best parts of relational queries with at least some typing.

https://spark.apache.org/docs/1.6.1/api/java/org/apache/spar...

KQL https://learn.microsoft.com/en-us/azure/data-explorer/kusto/...

Hers a simple example:

  StormEvents
  | where StartTime between (datetime(2007-11-01) .. datetime(2007-12-01))
  | where State == "FLORIDA"
  | count

I really like "modern SQL". Snowflake has some great functions and ideas, I really hope other databases take some of their ideas. It also has its pain points (ex Oracle.. so some notions they have about databases like NULLs is a bit meh).

Paired with dbt makes data quite fun

There are many, but most never go further than SQL (ie: Are just a "query" language).

I'm trying to do it for a "general purpose" language where array + relational + functional + imperative can work together:

https://tablam.org

---

One of the biggest damage that SQL has caused is that SQL is so bad as being a programming language, is not good enough DSL and is weirdly the only game in town, that many can't imagine that the relational model allows to do programming like every other model, just richer in "intrinsic" operators.

This is the deal with RDBMS. Is like: "Everyone implement a wild flavor of a VM, sometimes JVM, .NET VM, Web Assembly, eBPF, etc, but all use a slight variation of Java for their syntax"

Is like the reverse of how things are done!

I've written quite a bit about a new query language called Malloy: https://carlineng.com/?postid=malloy-intro#blog

It's a semantic layer with an integrated query language that makes a ton of improvements on SQL. Full disclosure, I actually joined the team a couple of weeks ago :)

Datalag is great! I use it every day in production.

https://www.learndatalogtoday.org/

An unfortunate typo.

R's dbplyr package lets you use the amazing dplyr syntax to query SQL databases.