4 ms·

Versioned views, materialized views or procedures are the solution to this. It is frequent that even internally, companies don't give access to their raw data b

Versioned views, materialized views or procedures are the solution to this. It is frequent that even internally, companies don't give access to their raw data but rather to a restricted schema containing a formated subset of it.

vmfunction 3y ago
In addition if you are using postgres, then there is postgresRest to make api really quick and nice.
tacker2000 3y ago
Of course it’s possible, but now you need more people with DB and SQL knowledge. Also, using views and stored procedures with source control is a pain. Deploying these into prod is also much more cumbersome than just normal backend code. Accessing a view will also be slower than accessing an “original” table since the view needs to be aggregated.
- _a_a_a_ 3y ago
  > Accessing a view will also be slower than accessing an “original” table since the view needs to be aggregated. Where does it say anything needs aggregating. You can have a view that exists just for security. > Also, using views and stored procedures with source control is a pain. Deploying these into prod is also much more cumbersome than just normal backend code. Uh? This is normal backend code.
- Shorel 3y ago
  I don't see the problem here. Are modern developers allergic to SQL or what is the issue?
  - tacker2000 3y ago
    Not all devs are proficient in SQL. Its another skill that is required.
madsbuch 3y ago
why would you want to develop your api in sql over a traditional language? versioned views and materialized views are essentially api endpoints in this context. just developed in sql instead of some sane language.
- dvdkon 3y ago
  You can let your API's users do arbitrary queries on any sets of data you expose if your API exposes SQL views directly. Plenty of apps don't need anything beyond basic "get by id" and "search by name" endpoints, but plenty others do. At that point, with traditional backends, you're either reimplementing SQL in your API layer, or creating a new endpoint for each specific usecase.
- camgunz 3y ago
  A lot of my backend career has been essentially Greenspun's 10th Law: Any sufficiently complicated REST or GraphQL API contains an ad-hoc, informally-specified bug-ridden slow implementation of half of SQL. SQL is a legendary language, it's powerful enough to build entire APIs out of (check out PostGraphile, PostgREST, and Hasura) but also somehow simple enough that non-technical business analysts can use it. It's definitely worth spending time on.
rewmie 3y ago
> Versioned views, materialized views or procedures are the solution to this. Wouldn't it be far simpler to just create a service providing access to those views with something like OData?
vbezhenar 3y ago
Views will severely restrict the kinds of changes you might want to do in the future. For example now you can't just move some data from your database into S3 or REST service. Stored procedures technically can do anything, I guess, but at that point you would be better with traditional services which will give you more flexibility.
- indigo945 3y ago
  A view can also do anything - it could query a REST service, for example. (Not saying that this is necessarily a good idea, though...)
  - falcor84 3y ago
    Is that a real thing? What DBMSs support such views?
    - jakewins 3y ago
      Most of the heavy artillery RDMSes at least, eg Postgres let’s you mount arbitrary HTTP resources as tables, which you then can put views over: https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
      - remus 3y ago
        This sounds like a total minefield. You might get a response in the same format, but I imagine it'd be very easy to break an API user who accidentally depends on performance characteristics (for example).
      - ttfkam 3y ago
        AWS RDS and Aurora both support synchronous and asynchronous lambda invocations from the database. Should be used very carefully, but when you want/need a fully event-driven architecture, it's wonderful. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...

vmfunction 3y ago

In addition if you are using postgres, then there is postgresRest to make api really quick and nice.

tacker2000 3y ago

Of course it’s possible, but now you need more people with DB and SQL knowledge. Also, using views and stored procedures with source control is a pain. Deploying these into prod is also much more cumbersome than just normal backend code. Accessing a view will also be slower than accessing an “original” table since the view needs to be aggregated.

_a_a_a_ 3y ago
> Accessing a view will also be slower than accessing an “original” table since the view needs to be aggregated. Where does it say anything needs aggregating. You can have a view that exists just for security. > Also, using views and stored procedures with source control is a pain. Deploying these into prod is also much more cumbersome than just normal backend code. Uh? This is normal backend code.
Shorel 3y ago
I don't see the problem here. Are modern developers allergic to SQL or what is the issue?
- tacker2000 3y ago
  Not all devs are proficient in SQL. Its another skill that is required.

madsbuch 3y ago

why would you want to develop your api in sql over a traditional language? versioned views and materialized views are essentially api endpoints in this context. just developed in sql instead of some sane language.

dvdkon 3y ago
You can let your API's users do arbitrary queries on any sets of data you expose if your API exposes SQL views directly. Plenty of apps don't need anything beyond basic "get by id" and "search by name" endpoints, but plenty others do. At that point, with traditional backends, you're either reimplementing SQL in your API layer, or creating a new endpoint for each specific usecase.
camgunz 3y ago
A lot of my backend career has been essentially Greenspun's 10th Law: Any sufficiently complicated REST or GraphQL API contains an ad-hoc, informally-specified bug-ridden slow implementation of half of SQL. SQL is a legendary language, it's powerful enough to build entire APIs out of (check out PostGraphile, PostgREST, and Hasura) but also somehow simple enough that non-technical business analysts can use it. It's definitely worth spending time on.

rewmie 3y ago

> Versioned views, materialized views or procedures are the solution to this. Wouldn't it be far simpler to just create a service providing access to those views with something like OData?

vbezhenar 3y ago

Views will severely restrict the kinds of changes you might want to do in the future. For example now you can't just move some data from your database into S3 or REST service. Stored procedures technically can do anything, I guess, but at that point you would be better with traditional services which will give you more flexibility.

indigo945 3y ago
A view can also do anything - it could query a REST service, for example. (Not saying that this is necessarily a good idea, though...)
- falcor84 3y ago
  Is that a real thing? What DBMSs support such views?
  - jakewins 3y ago
    Most of the heavy artillery RDMSes at least, eg Postgres let’s you mount arbitrary HTTP resources as tables, which you then can put views over: https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
    - remus 3y ago
      This sounds like a total minefield. You might get a response in the same format, but I imagine it'd be very easy to break an API user who accidentally depends on performance characteristics (for example).
    - ttfkam 3y ago
      AWS RDS and Aurora both support synchronous and asynchronous lambda invocations from the database. Should be used very carefully, but when you want/need a fully event-driven architecture, it's wonderful. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL-Lambda.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...