5 ms·
For your next app backend, you should try SQLPage [1][2] It makes creating a small web form to feed your database as easy as SELECT 'form' AS component;
by lovasoa 3y ago
For your next app backend, you should try SQLPage [1][2]
It makes creating a small web form to feed your database as easy as
SELECT 'form' AS component;
SELECT name, label, type FROM form_fields;
INSERT INTO parties(name, date) VALUES(:name, :date);
And creating an api on top of that db as simple as:
SELECT 'json' AS component,
(SELECT JSON_GROUP_ARRAY(JSON_OBJECT('name', name, 'date', date)) FROM parties) AS contents;
[1] https://sql.ophir.dev/ https://sql.ophir.dev/
[2] https://github.com/lovasoa/SQLPage https://github.com/lovasoa/SQLPage
- raybb 3y agoI like the idea of this a lot, but does it provide any way to edit the data?
- lovasoa 3y agoYou write the SQL yourself, so you can do anything you want, including UPDATE parties SET name=:name, date=:date WHERE id=:id;
- wruza 3y agoI can’t tell if that’s a sarcasm or “dropbox is just ftp” level of professional deformation.
- lovasoa 3y agoI'm not sure I understand this comment. The parent asked whether SQLPage could be used to update data in the database after inserting it. I answered that yes, it could. The update form would look something like this: select 'form' as component; select 'event_name' as name, (select name from events where id=$id) as value; select 'event_date' as name, 'date' as type, (select date from events where id=$id) as value; select 'event_id' as name, $id as value, 'hidden' as type; and the page processing the form would look like this: update events set name=:event_name, date=:event_date where id=:event_id; If what you are saying is that it's more complicated than a google sheet, then yes, there's no denying that. But it's also an order of magnitude less complicated than traditional web tech, and the result is a real website, not a google sheet. It's a little bit more complicated to set up than a google sheet, but it's less complicated to use afterwards.
- wruza 3y agoI didn’t realize it’s your project, so probably it hit personal, which wasn’t my intent. And I should have posted under the first comment to avoid making it even more confusing. So I beg your pardon. But what it really meant is that this sort of a technical solution may appeal to a developer, but not to regular users, not the ones who just updated spreadsheets for their website yesterday. Looking from that “mere mortal” point of view, and when it’s suggested by a person who isn’t yet known to be an author, in that context it feels like a 50% sarcasm and 50% our common geek stereotypes. I understand what SQLPage provides for a technical person, so please treat my comment as a joke which it is.
- raybb 3y agoThanks for the response, I was looking at your site from mobile earlier and it didn't jump out at me that it could also be used to edit data (tho I now see it mentioned clearly). Might be worth calling it out in a section as easy editing. Btw how would you compare this to https://datasette.io/ https://datasette.io/ which is the first thing that came to mind? Is yours a bit more developer oriented?
- lovasoa 3y agoHey, that's a good question, and the tools do have some overlap. Here is my (totally biased) comparison: - datasette builds one single website and API, which you cannot customize, on top of a SQLite database. It is a little bit ugly, but very efficient to help you discover your own dataset, and make some quick data analysis. If you're concerned about restricting public access to your database, sharing it may not be ideal. - SQLPage does not build anything for you by default. You need to write at least two or three SQL queries to make a useful website on top of your database. This requires you to already know the schema of the data you are working with. But this allows you to build visualisations of your data that look the way you want. While Datasette mainly offers tables, SQLPage provides forms, graphs, lists, tables, data cards, and more. The results tend to have a more polished and professional appearance, and your database remains private. Only the (parameterized) queries you create yourself can be executed by website visitors. Furthermore, a significant distinction is that Datasette exclusively works with SQLite, while SQLPage can connect to SQLite, Postgres, MySQL, or SQL Server databases.
- raybb 3y agoI haven't quite had a chance to set it up and play with it yet but one thing that I think is missing is that there is no clear way to write to it via api. I know it's not exactly the main purpose but I'd really like to be able to shoot data over from wherever and then let my friends/family easily see and edit it in the GUI. Anyway it's a great looking tool and wishing you the best with it!
- alin23 3y agoThanks for building this, and for the sample code :) Although what sheets excels at is batch editing and collaboration, which is really hard to get in an SQL frontend. My brother usually runs a Raycast workflow I wrote for him to extract a small number of party data as TSV which gets copied in clipboard. Then in sheets he can just paste that to get full rows and columns populated. After that he and his friends start proof reading and cross referencing social media data for each row, and Sheets makes this super easy as they can see each row highlighted with each user’s color so they don’t step on each other’s toes. Your solution is still very good for other use cases and I think I already have one in mind. My father always wanted to maintain a list of up-to-date prices for the vegetables that sell in my hometown’s large wholesale market.
- vivekd 3y agoDjango provides an admin page that makes it easy for non programmers to update SQL tables so thats worth looking into as well
- jason-phillips 3y ago> Although what sheets excels at well played
- stavros 3y agoThis looks really good, but I had to pass on it because I couldn't find screenshots or, even better, a demo site. I think that would really help sell this.
- lovasoa 3y agoThe official website for SQLPage (https://sql.ophir.dev/ https://sql.ophir.dev/) is written in SQLPage. The sql source code for it is here: https://github.com/lovasoa/SQLpage/tree/main/examples/official-site https://github.com/lovasoa/SQLpage/tree/main/examples/offici... The site also links to this little collaborative game written in SQLPage: https://conundrum.ophir.dev/ https://conundrum.ophir.dev/ The github README has code snippets and associated screenshots: https://github.com/lovasoa/SQLpage#examples https://github.com/lovasoa/SQLpage#examples There is also an official repl.it that you can fork to quickly try it online without having to download anything: https://replit.com/@pimaj62145/SQLPage https://replit.com/@pimaj62145/SQLPage And SQLPage cloud is coming: https://sql.ophir.dev/your-first-sql-website/hosted.sql https://sql.ophir.dev/your-first-sql-website/hosted.sql
- stavros 3y agoThis is all good, but you're making a mistake I see many people make: If one person has this experience/questions (me), then many of your other visitors will. Answering here will answer my questions, but it won't answer everyone else's questions. My recommendation would be to have a clear "see a demo" button, which leads to a website that showcases the most common elements I might want to have on such a page (a form, some graphs, etc), maybe with source code close by.