Idea: A pattern for abstracting file storage using PL/Python

[desmoteo]

Hi PostgREST team,

I've been exploring a pattern to handle file uploads that keeps all metadata and security inside PostgreSQL while offloading the binary storage to an abstract backend, allowing users to adopt local filesystem or cloud storage transparently. This avoids the downsides of storing BLOBs directly in the database.

The core of my solution is a pattern that leverages the PL/Python procedural language within PostgreSQL, combined with the versatile pyfilesystem library. This approach effectively turns the database into a smart orchestration layer for file management.

It allows a simple SQL function to securely write binary data to dozens of different storage backends (like the local filesystem, Amazon S3, or an SFTP server) through a single, abstract interface. The storage backend can be switched with a single environment variable, requiring no changes to the database schema or API code.

This keeps all file metadata, security policies, and transactional integrity securely within the database—where they belong—while delegating the heavy lifting of binary storage to a more suitable external system. PostgREST continues to do what it does best: provide a secure and instant RESTful API for the database functions, which now include robust file handling capabilities.

I've written a full article on Medium that walks through a proof-of-concept, complete with the Docker setup and commented code. You can find it here: Beyond BLOBs: A Flexible, Database-Integrated Approach to File Storage

You can find the corresponding code here: postgrest_fs on GitHub

I wanted to share this with the community and the core team to see what you think of the approach. Do you see this as a valuable pattern for PostgREST users? I'm open to any feedback or suggestions.

[steve-chavez]

Very interesting, I see the plpython code is small too (read file, write file).

Also great to see media type handlers being useful: https://github.com/desmoteo/postgrest_fs/blob/main/schema.pgsql#L330

Would you like to add postgrest_fs to https://docs.postgrest.org/en/v13/ecosystem.html?

[desmoteo]

Of course, I would be happy if this will be listed in the ecosystem page.