Show HN: SQLPage – Build Dynamic Websites with Just SQL Queries

[lovasoa]

https://sql.ophir.dev/

Hey Hacker News!

I wanted to share the open source project I have been working on during the last year: SQLPage, a tool to build small web applications entirely in SQL.

Building web applications with just SQL isn't as crazy as it seems. Most simple applications can be expressed declaratively as just data queries that fill pre-defined web components.

I'd love to hear your feedback and thoughts on it. Would you potentially use it? How can it be improved?

Website: https://sql.ophir.dev/ Github: https://github.com/lovasoa/SQLpage Example app: https://conundrum.ophir.dev/

[brudgers]

On the landing page, I think a simple hero diagram would be helpful because this appears to be an uncommon approach that mashes-up familiar terms.

I mean I have some familiarity with "dynamic websites" and "just SQL Queries" but no clear picture of the mechanics.

When I hit a landing page, my first questions are:

  What is it? 
  What does it do?
  Should I care about it enough to invest more time?

I clicked "try it out" but it didn't start to answer my questions...instead it asked me questions.

Anyway, Congratulations on launching. I know that's hard.

[lovasoa → brudgers]

This is a very good point, and I think I have a hard time explaining how it works technically without it sounding more complicated than it actually is.

The diagram is a good idea, I'll do it. For a simple explanation, what do you think about:

    SQLPage is a web server that runs .sql files to answer web requests. For every request, it runs the SQL statements in the requested file one by one, passing the HTTP request parameters as SQL statement parameters. Then, it passes the results returned by the database to pre-defined HTML components to create a good-looking webpage that is streamed back to the user's web browser.

[zigh → lovasoa]

That explanation made it easier to understand, but I found the GitHub Readme much better explains everything. The little video and example helped a lot.

[lovasoa → zigh]

I'll try to include more of the technical contents from github on the landing page, without making it too obscure for neophytes.

[buremba]

This is cool! I have been working on something similar as a side project as I believe that we need something similar to Streamlit but for SQL. I use dbt as the application framework so the approach is a combination of Jinja + SQL + YML files.

Shameless plug: https://jinj.at

[lovasoa → buremba]

Cool project ! It seems to target a more technical audience than SQLPage though, with a bit more required to build an app than just SQL. It takes more time and skills to build something, but as a result, you get more control and customization abilities.

[arberx → buremba]

Wow this is dope! Been looking to build an API on top of our warehouse.

Any instructions for prod deployment?

[buremba → arberx]

It's not production ready yet but here is the instructions: https://jinjat.com/docs/deploy

Also an example project with Fly.io integration: https://github.com/jinjat-data/jaffle_shop_metrics

I'm actively working on the project so any feedback is welcome!

[leoedin]

This is a really great idea! I spent a few hours playing around with it today, as I've been looking for a low-boilerplate way to quickly interact with a relational database. I've got a whole load of tables with relationships defined, and I want a nice way to render that.

I really love the idea of having "virgin" SQL statements to get and set the data - doing all the JOINs etc in SQL so I can pull together the view I want from a few interlinked tables. The low effort declarative GUI which interacts with that is nice. It's super-powerful to be able to describe a CRUD app as a set of relational tables, and a set of SQL insert/select statements, and then a very lightweight declarative GUI to start modifying and reading data from it. Then if the app becomes important you can take the same underlying data structure and wrap it in a more established framework with a "real" front end. And if it doesn't, you only spent a few hours on it!

A few thoughts:

1. It took me a while to work out how to render anything. Maybe make the first example you hit in the "Your website's first SQL file" section actually have everything needed to render a page (including "select 'shell' as component")

2. More examples! Maybe a straightforward CRUD type example would be good - it took me a while to work out how to create a row.

3. I want a searchable table of database items with a way to edit each one - maybe an extension to the table view? Either a way to add a custom link to each row in the table, or an extension to it that allows editing of data. That's where a CRUD example would be good.

4. The form should have an input type "multi" which is constrained to the options I've queried from the database. That way I can use the form to insert relational data, or limit users to a set of selections.

5. The "checkbox" type is either whatever it's value is, or NULL. If I've got an integer not null type in the database, I have to coalesce($myCheckbox, 0) when saving it - can you make it have a default unchecked value?

6. The examples are all hard coded data - can you have more examples that use the result of database queries to get the data that's rendered?

It's a fun project though! I like how lightweight it is as well.

[lovasoa → leoedin]

This feedback is gold to me. Is there any way I can get in touch with you when I've made changes to see how that would work for you ?

Can you list the step you took, starting from opening the website, and where the first pain points were ? Did you go sql.ophir.dev -> Get started -> technical, then downloaded the binary, wrote the first select, and hit a wall ? What did you do then ? What is the app you were trying to build ?

[lovasoa → leoedin]

Re-reading your comment, I realized what you seem to have hit was a bug in SQLPage !

Simple pages without an explicit 'shell' component were always supposed to work, but they were not tested ! I fixed that in v0.6.8

https://github.com/lovasoa/SQLpage/commit/f91f0d57c39360e560...

Thank you !

[felipemesquita]

I love this idea and will try to create apps with it. I’ve been thinking about creating something similar in ruby/rails for a while. It is currently a proof of concept with dbt like functionality using erb templates for sql. I would love to chat about it with interested rubyists, reach me via email on my username at hey dot com.

[lovasoa → felipemesquita]

If you do create an app with SQLPage, I would love to get your feedback, here on contact at ophir dot dev. And if you do not manage to create what you wanted, I would love to get that feedback too !

[jagged-chisel]

In the examples, the queries are returning ‘component’ and ‘contents.’ Presumably, that’s rendered into something like:

  <component>content</component>

Neat idea, but putting all the app’s logic into SQL queries is not how I would choose to design a production app.

[lovasoa → jagged-chisel]

Yes, the idea is that you first make a query that returns a single row with a "component" column, and potential other top-level parameters for the component. This is translated to some HTML that displays a piece of user interface on the page.

Then, you can make subsequent queries that can return multiple rows, and fill the component with data, displayed inside the piece of UI created by the component.

For instance, you can run

     SELECT 'list' AS component, 'Popular websites' AS title;

This will open a list component. Then

    SELECT
        topic AS title,
        'https://en.wikipedia.org/wiki/' || topic AS link
    FROM interesting_topics;

will populate the list with entries from your interesting_topics database table.

About putting all the app logic in SQL queries: yes, there are some apps that would be very hard, or impossible to build in SQLPage. But for most simple Create-Read-Update-Delete apps, you don't really have any complicated business logic, and not having ANY boilerplate to get started is I think what makes SQLPage compelling. And when your app later grows to have more complex business logic, you just move from SQLPage to a full-fledged backend framework, but you don't lose the work you have already done structuring your database schema and the core queries that your app will need.

The goal of SQLPage is not to replace something like Django, for instance. It is for django developers to be able to quickly test out their app idea, and for people who have no idea what "backend development" even means to still be able to create simple apps.

[gxt]

Been working on something similar based on DDL but this seems better, I think I'll use this instead!

[lovasoa → gxt]

I'd love to get your feedback if you use it ! Both positive and negative.

[snthpy]

Congratulations on the launch! This looks awesome!

Would you be interested in supporting PRQL (prql-lang.org)? The compiler is also written in rust so integration should be straightforward.

Disclaimer: I'm a contributor.

Github: github.com/PRQL/prql

[lovasoa → snthpy]

Hi, and thank you! I could see it happening. Of course it would be in addition to sql, not to replace it. First, prql would need to support insert and update statements, though.

[snthpy → lovasoa]

> Of course it would be in addition to sql, not to replace it.

Yes, absolutely! PRQL supports having a version and target header in the first line so you could make that mandatory for PRQL queries and only if it finds that then it routes it to the prql-compiler. Here's an example header:

    prql version:"0.8.1" target:sql.postgres

> First, prql would need to support insert and update statements, though.

Ah, I didn't spot that in your README examples. We don't have any plans to support those at the moment. PRQL aims to be an excellent query language and to focus on that solely (unix philosophy and all that).

What do you need inserts and updates for? To make a full CRUD app?

It might be convenient just to support PRQL for the query and Read parts and leave the CUD to SQL, if you felt so inclined of course.

Anyway, thanks for the reply. I'll play around with SQLPage for a bit and then we can chat again.

[lovasoa → snthpy]

I will add an example to the README with an INSERT statement to make it more obvious what SQLPage can do.

For the header, I don't think that would be necessary. SQLPage would route queries to .sql pages to the SQL parser and queries to .prql files to the prql parser.

If you want to play around with integrating prql in sqlpage, you can have a look at https://github.com/lovasoa/SQLpage/blob/main/src/webserver/d... This is a short self-contained rust file, and think you would not need to change anything more to interface with a prql parser. If you want to give it a stab, feel free to make a POC and get in touch :)

[lintim]

Nice, it looks like a great idea. To be honest, I didn't click through all the forms following the landing. It would be great to see right away how SQLPage lets me build dynamic websites with just SQL queries.

[lovasoa → lintim]

Thank you ! That's the main feedback I've had about the landing page. I wanted to make it clear, but as non-technical as possible to convey the value proposition and avoid scaring decision-makers away. But I missed my target: reading it now, I realise that it is too abstract, and it makes it unclear what exactly SQLPage does.

I'll work on it.

[RandomUser4976]

Fantastic idea and excellent execution! I look forward to using what you’ve created. Is there a way to support you with $$$?

[lovasoa → RandomUser4976]

Thank you for this heartwarming comment !

My github sponsors page is opened at https://github.com/sponsors/lovasoa

You can also leave your email on https://docs.google.com/forms/d/1t91PI0vF5HyghshkGBONbdk6BvM... to get updates about the project, and the coming hosted SQLPage service.

[pratio]

Fantastic.

[lovasoa → pratio]

Thank you ! If you've built something with it, I'd love to get your feedback, and see your pain points.