@jadudm and I chatted on 2024-10-10 on the use of various DB tools on another TTS project.
He noted that when you expose a DB over an API, people will want to D/L the entire database, so be prepared for that.
They've been using PostgREST successfully, but have paired with it SlingData to make copies of useful data from the canonical database to another database for read access. This allows for better data partitioning, and you can also move a lot of SQL query logic from the application side, to the Sling YAML configuration, which is highly readable and maintainable.
This has a real upside compared to a ReadReplica, since Sling could de-identify PII and reduce exposure errors downstream.
It can also write to filesystems and S3, and to different formats (excel?, CSV). Also - SQLite is pretty awesome.
The use of postgREST with API keys has been working out well. Excel can read directly from an API, for example.
The TTS project has ~5Gb of data and the largest tables has 5M rows.
The pREST should also be considered as an alternative to PostgREST.
@jadudm and I chatted on 2024-10-10 on the use of various DB tools on another TTS project.
He noted that when you expose a DB over an API, people will want to D/L the entire database, so be prepared for that.
They've been using PostgREST successfully, but have paired with it SlingData to make copies of useful data from the canonical database to another database for read access. This allows for better data partitioning, and you can also move a lot of SQL query logic from the application side, to the Sling YAML configuration, which is highly readable and maintainable.
This has a real upside compared to a ReadReplica, since Sling could de-identify PII and reduce exposure errors downstream.
It can also write to filesystems and S3, and to different formats (excel?, CSV). Also - SQLite is pretty awesome.
The use of postgREST with API keys has been working out well. Excel can read directly from an API, for example.
The TTS project has ~5Gb of data and the largest tables has 5M rows.
The pREST should also be considered as an alternative to PostgREST.