How to serve a binary file with mime type

[mnesarco]

Hello Friends,
I want to download a generated file. I can get the generated file as bytes from a postgresql function. But how can I dump it to the browser as a download.

Something like this would be great:

SELECT 
  'download' as component,  
  'application/vnd.ms-excel' as content_type,
  my_func(...) as content

[lovasoa]

Hello! This is an interesting feature request, and would definitely be useful. This is currently not possible to do directly, since SQLPage currently retrieves each row from the database as a json object, without the ability to efficiently store binary data.
You can have a look at the csv component which may partially cover your needs.
Otherwise you can use sqlpage.exec to create the local file, then redirect the user to it.

[lovasoa]

Another solution is to generate a data url, and then use a simple link to it.

select 
    'button' as component;
select 
    binary_to_data_url(
        my_func(...), 
        'application/vnd.ms-excel'
    ) as link,
    'Download'            as title;

With the following small helper function:

CREATE OR REPLACE FUNCTION (input_data BYTEA, mime_type TEXT)
RETURNS TEXT AS $$
DECLARE
    base64_data TEXT;
BEGIN
    -- Convert binary data to base64
    base64_data := encode(input_data, 'base64');

    -- Construct data URL with provided MIME type
    RETURN 'data:' || mime_type || ';base64,' || base64_data;
END;
$$ LANGUAGE plpgsql;

[mnesarco]

Thank you @lovasoa
I have explored those options but i think the existing json component is more similar to what i want. It dumps the response along with a content-type header. Maybe it is easy to create a similar download component with similar approach but dumping the returned value from the query without interpreting it as json. I am not a rust developer but looking at the source code it sounds feasible.

[mnesarco]

I see. In that case i think the best solution is to delegate the file generation to a different microservice and just redirect. But it only supports GET as redirect is done by location header. I wonder if is it possible to have a forward component that instead of redirect by location header, call the url and dump the response? something like:

SELECT 'forward' as component,
  'https://....' as url,
  'post' as method,
  '{...}'::json as data,
  '{...}'::json as headers;

In this scenario you can call the external service and just dump whatever it returns, copying also the returned headers.

[lovasoa]

Why proxying the entire request? You can make it a simple redirection, and pass the data as url parameters.

[mnesarco]

Yes but it will only work for GET requests. If I need to pass complex and probably not too small data to the external service plus some special headers i require POST call with a json body.

[lovasoa]

If the user is simply redirected to the external service, then the service itself can define the response headers. And if the data you transmit is multiple megabytes, then it probably shouldn't transit through SQLPage in the first place, the external service should get it directly from whatever the original source is. If it's smaller than that, then you should be fine passing it as get parameters.

[mnesarco]

Sure, there are many possible solutions. I was just wondering for possible built in options, but not a direct requirement of course. btw, SQLPage is great, thank you for sharing it, i have really enjoyed a lot making internal qnd tools.

[twoxfh]

Hello Friends, I want to download a generated file. I can get the generated file as bytes from a postgresql function. But how can I dump it to the browser as a download.

Something like this would be great:

SELECT 
  'download' as component,  
  'application/vnd.ms-excel' as content_type,
  my_func(...) as content

You might want to consider the Tabulator poc I wrote about here. Tabulator has the download as excel and record paging functionality to the web browser. Depending the amount of records it may work for your usecase.

[mnesarco]

tabulator component is excellent for its purpose. But what i need is something different. I have an existing service to generate pdf files from json objects. So the service receives a json payload by http-post and generates a pdf file. I want to call it internally from SQLPage so the PDF can be generated without exposing the service endpoint outside of the internal subnet.

Of course there are several possibilities to solve this outside SQLPage, I was just wondering if there was an easy way.

[vtzi]

Thumbs up for this request.

My use case is simpler - just want to serve small static files directly from sqlite. Usually this is faster vs serving from the file system and the added advantage is that all the app data is in a single file (really nice for small apps/prototypes).

[lovasoa]

This use case is already covered by the sqlpage_files table, isn't it ?

[vtzi]

sqlpage_files will work but enforces a particular db schema design.

BTW, while reading the docs I missed that you can host any files in the sqlpage_files - have been left with the impression it's only for sql files.