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.