SQLPage and S3 file storage

[a-gonzalo]

Hello everyone,

I'm nowadays trying to develop a clinical documentation management system (some sort of repository with audit/access about clinical documents).

For the storage part I was first considering storing files on the server with the upload functionality, but I'm scared of high time responses on fetching documents if customers upload a lot of data.

For this scenario, I was considering using some sort of local S3 like MinIO.

I think the best possible fit for this is using a PostgreSQL extension like aws_s3 and interacting with the MinIO service with SQL statements.

The question are: Do you ever used this with SQLpage?
Also, is considered for SQLPage any method to interact with file storage services at any nearly future? Would you think it will be a good idea?

Thanks!

[lovasoa]

The latest versions of SQLPage contain an odbc driver manager. Why not query the files on s3 directly using for instance the duckdb odbc driver:

• https://duckdb.org/docs/stable/clients/odbc/overview
• https://duckdb.org/docs/stable/core_extensions/httpfs/s3api

[a-gonzalo]

Well, I think it's the same idea behind using PostgreSQL via extension (as the S3 client is a DuckDB extension).

My idea on top of that is something more database agnostic, and also integrated within SQLPage

[lovasoa]

Reading again, I think I misunderstood your initial post. You meant you wanted sqlpage to be able to upload arbitrary files to s3, not querying structured tables stored on s3, right ?

That would indeed be a nice useful feature ! Would you be interested in working on it ? Maybe something like sqlpage.upload_to_s3(file_path, target_name) and a new configuration key to store the s3 endpoint region and signing info ?

[a-gonzalo]

Yeah! I was thinking on something like that.
I would like to have 2 functions:

• sqlpage.upload_to_s3 -> I don't really know how this will be better: receiving as args a file path from local file storage and a target bucket, or recieving the file as base64 (so then we can avoid the upload to host step)
• sqlpage.retrieve_from_s3 -> args: file_name, bucket. So we can retrieve the files and display them into the frontend. But as the upload function, I don't know if this will be better to retrieve it into the file system then accessing them with the read file function, or return it as a BLOB or something like that.

What do you think? I'll enjoy trying to implement this in SQLPage, but I appreciate some guidelines (not for the codebase itself, but more on which crate would be feasible to use)

[a-gonzalo]

I've found this. I think is a cool place to start