4 ms·

I don't think that's what's meant by #2... About 12 years ago I worked at a place where all the crud operations were stored procs (calling tables/views/function

by spikej 3y ago

I don't think that's what's meant by #2... About 12 years ago I worked at a place where all the crud operations were stored procs (calling tables/views/functions as needed). The application was only granted execute on the specific schemas. That meant no direct access for crud. A number of Schema changes and query optimizations could be handled by updating the stored procs without having to recompile the application.

claytonjy 3y ago
You're right, but I think GP has a point as well. Anything you expose to the application has a potential for misuse, and a lot of "misuse" of a DB means queries with bad performance. But, I'd argue the API schema gives you better control over this by pushing what might otherwise be application-level logic into the database. I've solved a lot of bad ORM behavior this way.
- spikej 3y ago
  Both decent approaches and we reduced a lot of friction by using code generation. Unfortunately, modern development favors having all logic on the application side. There are a lot of benefits that brings including better testing (stored proc testing frameworks never really caught on) however I find now a lot of people I work with rely too heavily on the ORM and don't know how to work with the databases (writing sub optimal queries, not properly indexing, etc)
zzzeek 3y ago
joins and subqueries are part of SELECT statements, not crud. the purpose of using views is for the SELECT side of the application, not the CRUD side.
- spikej 3y ago
  You're describing the "R" in CRUD, my friend: https://en.wikipedia.org/wiki/Create,_read,_update_and_delete https://en.wikipedia.org/wiki/Create,_read,_update_and_delet... In the example above, the select statements were inside stored procedures, and applications were only granted permissions to execute those with appropriate parameters.

4 ms·

I don't think that's what's meant by #2... About 12 years ago I worked at a place where all the crud operations were stored procs (calling tables/views/function

by spikej 3y ago

claytonjy 3y ago
You're right, but I think GP has a point as well. Anything you expose to the application has a potential for misuse, and a lot of "misuse" of a DB means queries with bad performance. But, I'd argue the API schema gives you better control over this by pushing what might otherwise be application-level logic into the database. I've solved a lot of bad ORM behavior this way.
- spikej 3y ago
  Both decent approaches and we reduced a lot of friction by using code generation. Unfortunately, modern development favors having all logic on the application side. There are a lot of benefits that brings including better testing (stored proc testing frameworks never really caught on) however I find now a lot of people I work with rely too heavily on the ORM and don't know how to work with the databases (writing sub optimal queries, not properly indexing, etc)
zzzeek 3y ago
joins and subqueries are part of SELECT statements, not crud. the purpose of using views is for the SELECT side of the application, not the CRUD side.
- spikej 3y ago
  You're describing the "R" in CRUD, my friend: https://en.wikipedia.org/wiki/Create,_read,_update_and_delete https://en.wikipedia.org/wiki/Create,_read,_update_and_delet... In the example above, the select statements were inside stored procedures, and applications were only granted permissions to execute those with appropriate parameters.