Feature request: Editable table & combo box

[leoedin]

Hi,

I left a comment on HN - but I thought maybe an issue would be easier for you!

Docs

• 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")
• More examples! Maybe a straightforward CRUD type example would be good - it took me a while to work out how to create a row.
• 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?

Features

• 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.

• The form should have an input type "combo" or similar, 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. eg https://mui.com/material-ui/react-autocomplete/. Bonus if the displayed name and value are different - that way I can make the value the id and show a nice name to the user.

• 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?

• (I just added this one) - a JSON API component - you've got a CSV component, but it would be great if I could make a page which just renders JSON of my query, so my app can have an API as well as GUI.

[leoedin]

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 ?

Yeah, exactly that. The first problem was that the "shell" component was missing (but that sounds like a bug). Once I had that, the GUI components I included started appearing - but I had to copy the first few lines of the example on github to make it work.

I was playing around with making a simple inventory tracking system - it's basically a bunch of relational tables defining products, suppliers, customers, orders, shipments etc - with the idea that the total inventory can be summarised by outbound shipments - inbound shipments. I need interfaces to insert things, and then a bunch of pages for reporting the state of the database - basically rending a few different SQL queries to show total stock, lists of orders etc. It's mostly a toy project to replace a few excel spreadsheets that I've been trying over the past few months in various no-code tools, but always running up against limitations due to their implementation details. Most no-code tools do very well with simple mappings between a database table and what you see on screen, but fall apart with relational data (like having many-to-many relations defined by a separate table). I can handle all of that in SQL, but most no-code tools don't let you edit the SQL.

The most important thing for me is that the underlying database structure is tool-agnostic - if I start hitting limitations with whatever low-code tool makes the CRUD part (and the database is useful enough to invest time in) I can port it to a "real" framework.

[lovasoa]

Thank you for opening this, and for all the feedback !

• About queries not working when not explicitly selecting the shell component: that should be fixed. Can you confirm v0.6.8 works with the first SQL you tried, without a shell ?
• About examples, we currently have https://github.com/lovasoa/SQLpage/tree/main/examples . I'll add a link to it higher on the front page, and in the technical instructions. Is the inventory tracking system you were working on proprietary ? If not, maybe you could contribute it to the examples folder on this repo ?
• About examples with hardcoded data, you are talking about the examples in the documentation page on the official site, right ? Currently, they are rendered dynamically, and help ensure they are always up to date and work. But I could add another type of examples, that are not dynamic, but demonstrate dynamic data querying. I think that would be useful in particular for the form component.

Features

• searchable table of database items with edition : if the information is tabular, there is no great way to do that today, agreed. What I would use today is a list or card component, with clickable items. To do that with a table, I was thinking about the following two ideas (that may not be incompatible between themselves):
  • add a row-level property named link to the "table" component. The entire row would become clickable. You would to something like select 'table' as component; select *, 'details.sql?id=' || id as link from my_items;.
  • add support for markdown to the table component. When a column name ends in _md, interpret the column contents as markdown. You would use it like so:

  • select 'table' as component;
    select *, '[edit](details.sql?id=' || id || ')' as actions_md from my_items;

    • Problem is, depending on where the value comes from, it may lead to a markdown injection from a malicious user.
    • (both solution have the downside that usage may become unintuitive to an user who wants a column in their table named "link" or "_md".
• select from a predefined set of values that come from the db : What would you think about something like

  •  SELECT 'form' as component;
     SELECT 'Friend' as name, 'select' as type, group_concat(family_name, '\n') AS options FROM people;

• checkbox value not sent when not checked : I haven't checked, but I think it's the way the browser works. It doesn't send the value. I'm not a super fan of abstracting that away. But I could definitely add examples to make it more obvious how to work with them.
• JSON API I've been thinking about it. I'll make a separate issue to track that. It's definitely something I'd love to have, but haven't figured all the technical details about it yet.

[leoedin]

select from a predefined set of values that come from the db : What would you think about something like

SELECT 'form' as component;
SELECT 'Friend' as name, 'select' as type, group_concat(family_name, '\n') AS options FROM people;

That could work - although to be useful it'd need to preserve the primary key of each row in people - so you can create a relationship with the subsequent information. You really need 2 lists for the options - the displayed string, and the underlying HTML value element.

[leoedin]

(both solution have the downside that usage may become unintuitive to an user who wants a column in their table named "link" or "_md".
Yeah, that's tricky. _md is probably less likely to clash with someones columns than link. It's also more extensible.

Could you pass a list of markdown-rendered rows in the first SELECT? Something like

select 
	'table' as component,
	1 as search,
	'actions' as markdown;
SELECT 
 *, '[edit](details.sql?id=' || id || ')' as actions from my_items;

Maybe you'd want the _md to be mandatory, I don't know. Still possible to render markdown from the database, but at least it'd have to be explicit.

[lovasoa]

That could work - although to be useful it'd need to preserve the primary key of each row in people - so you can create a relationship with the subsequent information. You really need 2 lists for the options - the displayed string, and the underlying HTML value element.

SELECT 'form' as component;
SELECT 
    'Friend' as name, 
    'select' as type, 
    group_concat(family_name, '\n') AS options
    group_concat(id, '\n') AS option_values
FROM people;

?

[lovasoa]

  select 
  'table' as component,
  1 as search,
  'actions' as markdown;
SELECT 
*, '[edit](details.sql?id=' || id || ')' as actions from my_items;

Yes, that sounds better ! I'll see how feasible it is

[lovasoa]

I rewrote the landing page and the getting started instructions on the official website to try and make them easier to understand. @leoedin , can you tell me if the new version is better ?

https://sql.ophir.dev/

[lovasoa]

Now that I've opened separate issues for the feature requests mentioned above, I'll convert this thread to a github discussion.

[leoedin]

Much clearer! I didn't realise the difference between : and $ parameters before!

One thought - your instructions get the user to download the sqlpage binary into their website folder - but then they have to make a sqlpage/ directory to hold the migrations and config. That clashes with the binary file!

[lovasoa]

Oh, yes you're right ! I changed the binary names to sqlpage.bin for the next version.

[ToniJ123]

Is there possibility that table component would have in future checkboxes for rows, for multiselect to do something via external button or something, and could there be dropdown menus and editable text fields in table rows? This would be awesome and multiple the use cases where you can use table, now it's kind of stiff when you have to transfer row data to form for editing etc.