Support for inserting uploaded file content in db as bytes for streaming upload/download

[djyotta]

What are you building with SQLPage ?

A tool to share files.

What is your problem ? A description of the problem, not the solution you are proposing.

Upload: I can't do streaming upload into the db because the data_uri is manifested as a string in memory first.

Download: I use an external service to provide a streaming download of the data. But this is not as ideal as it could be because I have to decode the data_uri from the db first (buffer in memory before download starts). Theoretically, one could decode a data_uri as a stream, but ideally I could just stream the bytes of the blob as is.

What are you currently doing ? Since your solution is not implemented in SQLPage currently, what are you doing instead ?

I'm storing uploaded files as data uri in db and using an external service to provide the download.

Describe the solution you'd like

I'd like to store the bytes alone as blob in db. Ideally this could be inserted into db as a streaming upload to avoid buffering entire file in memory.

SQLite buffers blobs in memory anyway, but other dbs may not. Even sqlite supports streaming blobs https://www.sqlite.org/c3ref/blob_open.html

Actually, streaming download could be a great featur for SQLPage (a new component maybe like the JSON component...?).

[lovasoa]

Hi! Streaming blobs in and out of databases is not supported in SQL. Databases have their own interfaces, but that would require some non-sql syntax. Storing large files in databases is usually not recommended anyway, so I'm not sure there would be enough use of such a feature to justify its development...

For a file sharing service, what I would recommend is storing the files on a separate blob storage service like minio or just local disk, and store only file paths in the database

[djyotta]

Yeah, I was prior to SQLPage storing everything on disk. I rolled my own content addressable storage. But most these files are tiny so the db makes more sense.

Regarding large files, in db - I think it really depends on the db whether it's good or bad. Personally, I'd rather store files in a db than on disk. In my case, I have a SBC so storage is limited. With a db connection, the storage could be anywhere. Yes, I could also use a NAS, but a db seems way more portable.

My vision for such a SQLPage feature is like this:

-- index.sql

-- download (probably has to be first component like for JSON component
SET ":key" = $download;
SELECT 'streaming-download' AS component, (SELECT sqlpage.streaming_download('large_files', :key)) AS content
WHERE $download IS NOT NULL;

-- upload form
SELECT 'form' AS component;
SELECT 'file' AS type, 'upload' AS name, 'Upload' AS label;

-- upload
SET ":key" = sqlpage.uploaded_file_name();
INSERT INTO large_files (key, content)
VALUES (:key, sqlpage.empty_blob());
-- stream the bytes into the zero-sized blob
SELECT sqlpage.stream_uploaded_file_to_db(:key, sqlpage.uploaded_file_path('upload'))
WHERE :upload IS NOT NULL;

-- display a button to retrieve the just uploaded content
SELECT 'button' AS component, '/index.sql?download='||:key AS link;

So still SQL, but of course those stream_uploaded_file_to_db, empty_blob, sreaming_download functions need to be implemented - and likely need to be specifically implemented per db...

But yeah, I get this is likely not within scope of SQLPage target feature set.