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', {...});