3 ms·

This doesn't seem like a good idea when it comes to consistently hardening an access layer. You mean I get to double the ops maintenance cost of my existing ser

This doesn't seem like a good idea when it comes to consistently hardening an access layer. You mean I get to double the ops maintenance cost of my existing service by adding a new one? You mean I need to figure out how to set up RBAC, rate limiting, logging, and error handling in two places instead of just one? By and large, opinions in API design has suggest against directly mirroring table structure for quite some time. The reasons are many, but they include things like migrating data sources, avoiding tight coupling with the database schema, and maintaining ultimate control over what the payload looks like at the API layer. Just in case you want to do something dynamic, or hydrate data from a secondary service. And if you still want to generate you response payloads like they came straight from the database, there are plenty of code generation or metaprogramming solutions that make providing access via an existing API layer quite simple. This solution seems simpler only because it ignores the problems of most practical service-oriented API architectures. If end users need a direct line to the database, then get your RBAC nailed down and open the DB's port up to the end user.

delusional 2y ago
> If end users need a direct line to the database, then get your RBAC nailed down and open the DB's port up to the end user. I'd really consider creating a new table just for this "database interface" That'll let you keep evolving your internal architecture while preserving backwards compatibility for the client. That won't work in all cases obviously, but I think it's suitable for most of the "sensible" ones.
- everforward 2y ago
  I thought the prevailing advice was to expose views (materialized or not) rather than tables directly, but I could be wrong or outdated. I still probably wouldn’t do it, though. My “lazy” solution is usually OpenAPI code generation so I basically only have to fill in the SQL queries. Not having any code gives me the nagging feeling that very soon I will hit a problem that’s trivial to fix in code but impossible or very stupid to do via a SQL query.
dventimi 2y ago
> If end users need a direct line to the database, then get your RBAC nailed down and open the DB's port up to the end user I do, when users are willing to speak libpq. When they insist on speaking HTTP, then I use PostgREST.
fzeindl 2y ago
> You mean I get to double the ops maintenance cost of my existing service by adding a new one? You mean I need to figure out how to set up RBAC, rate limiting, logging, and error handling in two places instead of just one? PostgREST has a 10 line config file, so it's trivial to setup. Monitoring, rate limiting, logging and error handling could be done by central tools like API-gateways or on the reverse proxy.

delusional 2y ago

> If end users need a direct line to the database, then get your RBAC nailed down and open the DB's port up to the end user. I'd really consider creating a new table just for this "database interface" That'll let you keep evolving your internal architecture while preserving backwards compatibility for the client. That won't work in all cases obviously, but I think it's suitable for most of the "sensible" ones.

everforward 2y ago
I thought the prevailing advice was to expose views (materialized or not) rather than tables directly, but I could be wrong or outdated. I still probably wouldn’t do it, though. My “lazy” solution is usually OpenAPI code generation so I basically only have to fill in the SQL queries. Not having any code gives me the nagging feeling that very soon I will hit a problem that’s trivial to fix in code but impossible or very stupid to do via a SQL query.

dventimi 2y ago

> If end users need a direct line to the database, then get your RBAC nailed down and open the DB's port up to the end user I do, when users are willing to speak libpq. When they insist on speaking HTTP, then I use PostgREST.

fzeindl 2y ago

> You mean I get to double the ops maintenance cost of my existing service by adding a new one? You mean I need to figure out how to set up RBAC, rate limiting, logging, and error handling in two places instead of just one? PostgREST has a 10 line config file, so it's trivial to setup. Monitoring, rate limiting, logging and error handling could be done by central tools like API-gateways or on the reverse proxy.