On a recent project, it was a weird inversion in terms of access... in order to keep the middle tier thin, and meet requirements that all data access happen through stored procedures... we pretty much standardized an interface with one input parameter (@json) and two output parameters (@result, @errorResult). In the end, all input/output was JSON and the database handled all data internally.
I don't really like it much, but it did make the API service super thin, and the "database guys" were happy to do it all in SQL. Of course, testing that beast isn't so fun, and there be dragons...
In the end, I tend to favor creating APIs with scripted languages that require less translation to work with the database side, and structure responses to match the expected data layouts for the API side. With node, I usually create/use a simple abstraction...
var result = await db.query`SELECT ... WHERE foo=${bar}`;
or
var result = await db.exec('sprocname', {...});
In either case, not really a need for a formal ORM here.
Yes, but I'm not sure I get the benefit of decoupling the schema, I'm still coupling to the stored procedure interfaces, and still have to deal with the shape of input and results.
If schema needs to be changed in many cases sp interface will stay the same e.g. I can do changes/optimizations to schema without changing the calling code.
But, if it's a schema change where you have to update the SP, you still have to change code... it's that the code is in (PL/T)SQL vs in another language.. You still have to update code either way.
the sprocname approach gives you an option to only grant your app user execute permissions on sps and thats it. (much tighter from security standpoint)
I don't really like it much, but it did make the API service super thin, and the "database guys" were happy to do it all in SQL. Of course, testing that beast isn't so fun, and there be dragons...
In the end, I tend to favor creating APIs with scripted languages that require less translation to work with the database side, and structure responses to match the expected data layouts for the API side. With node, I usually create/use a simple abstraction...
In either case, not really a need for a formal ORM here.