Support PRQL

[sirthias]

SQLPage is awesome!
I'm currently building larger and larger (internal) applications with it, but with increasing project size SQLPage's limitations become more and more apparent.

The main pain points for me are keeping things DRY and code organization.
While the run_sql function helps somewhat it's clunky and a bit limited as the only real vehicle for factoring out code.

Therefore I'd like to propose adding support for PRQL as a layer on top of (the query side of) SQL.

PRQL looks like a great complement/addition to SQLPage!
Apart from a more modern, concise and easier to read query syntax it also provides things like custom functions, a much richer "standard library" of predefined functions and -- hopefully soon -- a module system, which will allow importing declarations from other files with proper namespacing.

The only "problem" that SQLPage would have to work around is that PRQL is currently only focused on the query side of things and doesn’t plan on supporting writing to the database. So, we'd still need support for writing SQL directly for all the "non-SELECT" parts of the language.
I haven't really thought through how this might be achieved best.

But to me it looks like PRQL is something that SQLPage could definitely benefit from!

[lovasoa]

Hi,

When I made my first public post about sqlpage in 2023, someone working on PRQL suggested it:
https://news.ycombinator.com/item?id=36194473

At the time, the lack of support for INSERT and UPDATE made it feel awkward to require two different languages to build an app.

That said, I’m very interested in your use case and pain points. Could you share a bit more detail about what you’re building, the broader context of your project, and the main challenges you’re running into?

In my own sqlpage projects, I try to push as much logic as possible into the database itself, so that the sqlpage layer stays small and focused purely on presenting data to users. I usually factor application logic into views and stored procedures.

[sirthias]

Thank you, Ophir, for being so open about feedback! ❤️

My pain points can be summarized as a lack of composability in a few aspects of SQLPage, as it is right now.

IMHO the power of any system comes from two things:

• the utility of the atomic building blocks
• the way that smaller building blocks can be composed into larger blocks

If you have the right atomic tools and good, general ways to compose them you can build simple as well as arbitrarily complex structures.

With SQLPage I see three areas where this principle can be applied:

• the (SQL) code itself
• the organisation of the code on the file system
• the rendering components

SQL Code

While SQL defines everything required to build really complex queries it doesn't provide a vehicle for code reuse itself.
You can nest queries but cannot factor them out into sth like custom functions in SQL itself.

Views and stored procedures can be a work-around but they require storing application logic in the database.
This can be ok in simple use cases but breaks separation of concerns by conflating application data and application logic in the DB layer.
With larger systems this is decidedly not good.
I don't want to have to run db migrations every time something in my application logic changes.

Even parameterized templating already helps here.
For example, I've had some success so far with simply using gomplate to statically generate the .sql files for SQLPage.

Code Organisation

Code organisation, i.e. splitting larger code files into smaller and more manageable fragments, that can be reused and freely combined, are crucial for every larger project.
Once you have that you also want namespacing pretty quickly.
The file system is great for that but -- like functions -- SQL doesn't offer anything in that department by itself.
SQLPage does via its run_sql facility, but it's not really pretty.
And it doesn't work in migration code, which is sth I work around with gomplate so far.

Rendering Components

So far SQLPage defines a great set of building blocks through its components but for some reason doesn't appear to allow for composing these into larger pieces.
The only work-around is the html component, which is more low-level that I'd like to it be.
If there was a component that allowed for stacking nested components in a horizontal or vertical layout we could compose arbitrary complex structures from the atomic pieces.

---

So, in general there is a lot to like about SQLPage, but so far it's somewhat limited with regard to its abilities to support larger projects. Enabling composability where it's currently restricted would be a way forward here.

Incorporating PRQL looks like it would address some of this, but we'd need a smart way to work around the limitation with regard to non-SELECT statements...