4 ms·
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 thr
by tracker1 7y ago
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.
- Nican 7y agoIt was not the first time I heard the requirement about "all data access happen through stored procedures", and I find it ludicrous. Does anyone know how such a paradigm came to exist? What problem is this solving?
- qaq 7y agoSecurity, decoupling calling code from db schema
- tracker1 7y agoDon't API services do that?
- qaq 7y agoHow do they do that in the context of ORM vs sps :)?
- tracker1 7y agoCalling code is the front end... API services separate that calling code from the backend.
- qaq 7y agoyour backend is still storing data somewhere right?
- tracker1 7y agoYes, 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.
- qaq 7y agoIf 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.
- tracker1 7y agoBut, 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.
- ratww 7y agoI remember it being common in the 90s and 2000s, when DBAs were primarily developers. It's absurdly overkill if you're just doing CRUD, but if the DB uses some crazy schema that doesn't match the usage it keeps you sane. One such project I worked in had a primitive form of event sourcing built in. Everything was logged and could be replayed. It was kinda neat.
- burpsnard 7y agoSame as a rest api
- qaq 7y agothe sprocname approach gives you an option to only grant your app user execute permissions on sps and thats it. (much tighter from security standpoint)
- taffer 7y agoI always like to link these articles when the topic comes up: https://sivers.org/pg https://sivers.org/pg https://www.vertabelo.com/blog/business-logic-in-the-database-yes-or-no-it-depends/ https://www.vertabelo.com/blog/business-logic-in-the-databas... https://www.martinfowler.com/articles/dblogic.html https://www.martinfowler.com/articles/dblogic.html There is also this video on how to properly architect these kinds of applications: https://youtu.be/PMPW024NIDE?t=1052 https://youtu.be/PMPW024NIDE?t=1052