improve json component DX

[mtt-artis]

Hi 👋,

I like the json component but the forced use of json(b) functions add complexity not only because it requires extra knowledge but also because there are named differently across databases.

I'd like to propose something like this:

select 'json' as component;
select * from todos;

and

select 'json' as component, true as first;
select * from todos where id = $id;

PS: love the project

[lovasoa]

Hi ! This would indeed be a very useful addition ! Would you be interested in implementing it ?

The json component is defined here:

https://github.com/lovasoa/SQLpage/blob/0a9390fd64e04b532f95c7ac2d36a3e8ca4a7664/src/render.rs#L189-L202

[lovasoa]

Hi @mtt-artis ! I implemented multiple improvements to the json component, including things you were suggesting above 😃

Would you be interested in beta-testing it before we release v0.30 ? Development builds are available on docker as lovasoa/sqlpage:main

[mtt-artis]

Hello.

SSE (Server-Sent Events) is a unidirectional communication protocol that allows the server to push updates to the client, unlike WebSockets, which support bidirectional communication (allowing the client to send messages to the server as well).

A potential use case for SSE in a Single Page Application (SPA) is streaming data, such as table rows, to be stored in the app's state or signals as they arrive. This enables the view to update incrementally, allowing users to see new rows in real-time without waiting for the entire payload to load before rendering.

[lovasoa]

Still I don't get what would be the cases in which 'sse' would be useful. If it sends the result of a query to the client only once, isn't it the same as "select 'json' as component" (without 'sse' as type)?

SSE (Server-Sent Events) is useful when you want to send data from the server to the client progressively, as the data becomes available, instead of waiting for the entire query to finish.

Without SSE, if your query takes a long time to fetch all rows, the user will experience a delay (blank screen) until the entire dataset is retrieved and sent in one go (like your example with "select 'json' as component").

With SSE, as soon as the server has the first row, it can be pushed to the client immediately. The user can start seeing data even if the query is still running, improving perceived performance.

However, the current SSE implementation is not for "watching" ongoing changes to the data after the query is done (like subscribing to future changes). It’s just about delivering the results as soon as they are ready. That would be a great and useful feature, but it's not implemented yet.

[ToniJ123]

Hi, this sounds very interesting upgrade to SQLpage, is this possible to impelent in (near) future?

We have to limit at the moment very much amount of loaded data by filtering it because otherwise the page would be stuck for too long.

[lovasoa]

@ToniJ123 : Are you using the json component? I'm not sure how query watching in the json component relates to your problem of loading large amounts of data.

[ToniJ123]

@ToniJ123 : Are you using the json component? I'm not sure how query watching in the json component relates to your problem of loading large amounts of data.

Yeah, sorry for misundestooding and confusion in this case, should have read better what we were talking about here, this isn't important section in our case, maybe some others benefit more from this 👍

[mtt-artis]

Hello @lovasoa
Super hype about this feature.
Is the development build ready yet ?

[lovasoa]

Now they are!

[mtt-artis]

Love it !

Did some test on postgres with all the postgres type and it's working perfectly for select insert delete and update.
SSE is a nice feature for future use and more complex components.

Doing multiple query like the other components is a really cool trick.

select 'json' as component;
select id, name from cats;
select id, name from dogs;
select id, name from pokemon;

Having the db error in the json response is nice too.

await fetch("/single.sql?id=no-one").then(r => r.json())
// {error: 'error returned from database: invalid input syntax for type integer: "no-one"'}

Quetions:

• In the documentation under the chapter "Send a single JSON object", should we warn users that both 'jsonlines' AS type AND LIMIT 1 are needed for correct behavior?

• For jsonlines that return no rows, an empty response body isn't valid JSON, leading to errors like below:

await fetch("/single.sql?id=999").then(r => r.json())
// Uncaught SyntaxError: Unexpected end of JSON input

Should SQLPage provide a solution or pattern for this case?
Like a fallbackStatus and fallbackBody option in the json component to handle cases where no rows are returned?
I'm unsure about this, as I don't want to overcomplicate things and some libraries like already Redaxios handle this issue Redaxios Source Code.

• Other edge cases I'm not certain sqlpage should handle as it's already the case for html pages
  • Should the status still return 200 even if there’s an error?
  • Should errors be stored in an array?

await fetch("/all.sql?id=no-one").then(r => r.json())
// [{ "error": "error returned from database: invalid input syntax for type integer: \"no-one\"" }]

I'm really happy with the current implentation as it cover all my use cases (and more). Thank you !