11 comments

  • brudgers 297 days ago
    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 296 days ago
      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 296 days ago
        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 296 days ago
          I'll try to include more of the technical contents from github on the landing page, without making it too obscure for neophytes.
  • buremba 296 days ago
    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 296 days ago
      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 296 days ago
      Wow this is dope! Been looking to build an API on top of our warehouse.

      Any instructions for prod deployment?

  • leoedin 296 days ago
    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 296 days ago
      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 296 days ago
      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 296 days ago
    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 296 days ago
      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 296 days ago
    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 296 days ago
      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 296 days ago
    Been working on something similar based on DDL but this seems better, I think I'll use this instead!
    • lovasoa 296 days ago
      I'd love to get your feedback if you use it ! Both positive and negative.
  • snthpy 295 days ago
    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 295 days ago
      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 295 days ago
        > 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 295 days ago
          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 296 days ago
    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 296 days ago
      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 296 days ago
    Fantastic idea and excellent execution! I look forward to using what you’ve created. Is there a way to support you with $$$?
  • pratio 296 days ago
    Fantastic.
    • lovasoa 296 days ago
      Thank you ! If you've built something with it, I'd love to get your feedback, and see your pain points.
  • dawsongzhao 292 days ago
    [dead]