3 ms·

I think #2 is a bit dated approach by now. Certainly there's a place for this approach, when you have a DBA who is acting as gatekeeper to a large DB and the ap

I think #2 is a bit dated approach by now. Certainly there's a place for this approach, when you have a DBA who is acting as gatekeeper to a large DB and the application is stable and is probably a monolith, but this adds friction and comes at a cost. It also tends to encourage a style of design where more business logic goes into the DB instead of stateless services, which eventually bottlenecks your ability to scale.

claytonjy 3y ago
I last applied this approach a few years ago at an IIOT startup, where we had billions of rows of sensor data. We needed it all in one place, but with a fairly varied set of users, like an application team showing customers various slices of their data and a data science team applying all kinds of analytics to the data. When I showed up we had a single Postgres database with a half-dozen different clients. One managed the migrations in addition to doing real work, and the others were a mix of "direct" users and API layers used elsewhere. Tons of overlap, tons of repeated logic, tons of inconsistency. I used this approach to first stem the plague of direct DB access, and later to consolidate our APIs. It worked pretty well, and the folks I left it to seemed to appreciate my approach. We did have a lot of debates about what is or isn't "business logic", what belongs in this DB or not. We tried to keep it pretty light; our raw data was many independent tables, and our API schema had functions for insertions and views to make that data usable in ways that didn't feel very application-specific. I was pushing to replace our handwritten API service(s) with something like Hasura, though that didnt happen before I left. I think that's where this approach gets more powerful: manage your data with your database, get a web API for free. Then your data store is encapsulated behind a singular service with a well-defined API. You're right that it requires some serious attention to the DB. We wrote all migrations in PL/pgSQL (managed with sqitch), giving us a level of control that is hard to get from a language-specific migration framework. That required a lot of upskilling, but I think it was worth it for us.
- throw__away7391 3y ago
  Yeah, definitely a case-by-case basis situation, but I think with "modern" apps this is not a great approach. The situation you describe walking into is definitely horrible and adding some discipline and especially a solid DBA or lead can mitigate a lot of damage. I've seen plenty of outages caused by some dev not understanding the query their ORM was generating taking down production at the worst possible time, and this approach can help. This once was the "correct" way to do things, period. At least that's how I learned it originally. If you go a back a couple of decades, "users" in a RDBMS meant literally end users, using a client application to directly query the DB from their desktop machine and whatever permissions they had was what they had. In this environment locking down everything except stored procedures and views was the only way to go. (Interestingly, we've sort of seen a resurgence of this design recently with some of these PaaS based web applications where all the logic is living client-side somewhere in a React app.) After that indoctrination I remember feeling very resistant initially to a lot of this and in particular the practice of just granting full read/write access to a service account and letting developers write whatever they wanted, but times change. We were working under different constraints at the time. If you have services scoped to a specific responsibility and they completely own their own data and don't have the situation you describe of many clients touching the same DB, a lot of the problems that the proc+views only pattern were intended to solve just go away on their own.

claytonjy 3y ago

I last applied this approach a few years ago at an IIOT startup, where we had billions of rows of sensor data. We needed it all in one place, but with a fairly varied set of users, like an application team showing customers various slices of their data and a data science team applying all kinds of analytics to the data. When I showed up we had a single Postgres database with a half-dozen different clients. One managed the migrations in addition to doing real work, and the others were a mix of "direct" users and API layers used elsewhere. Tons of overlap, tons of repeated logic, tons of inconsistency. I used this approach to first stem the plague of direct DB access, and later to consolidate our APIs. It worked pretty well, and the folks I left it to seemed to appreciate my approach. We did have a lot of debates about what is or isn't "business logic", what belongs in this DB or not. We tried to keep it pretty light; our raw data was many independent tables, and our API schema had functions for insertions and views to make that data usable in ways that didn't feel very application-specific. I was pushing to replace our handwritten API service(s) with something like Hasura, though that didnt happen before I left. I think that's where this approach gets more powerful: manage your data with your database, get a web API for free. Then your data store is encapsulated behind a singular service with a well-defined API. You're right that it requires some serious attention to the DB. We wrote all migrations in PL/pgSQL (managed with sqitch), giving us a level of control that is hard to get from a language-specific migration framework. That required a lot of upskilling, but I think it was worth it for us.

throw__away7391 3y ago
Yeah, definitely a case-by-case basis situation, but I think with "modern" apps this is not a great approach. The situation you describe walking into is definitely horrible and adding some discipline and especially a solid DBA or lead can mitigate a lot of damage. I've seen plenty of outages caused by some dev not understanding the query their ORM was generating taking down production at the worst possible time, and this approach can help. This once was the "correct" way to do things, period. At least that's how I learned it originally. If you go a back a couple of decades, "users" in a RDBMS meant literally end users, using a client application to directly query the DB from their desktop machine and whatever permissions they had was what they had. In this environment locking down everything except stored procedures and views was the only way to go. (Interestingly, we've sort of seen a resurgence of this design recently with some of these PaaS based web applications where all the logic is living client-side somewhere in a React app.) After that indoctrination I remember feeling very resistant initially to a lot of this and in particular the practice of just granting full read/write access to a service account and letting developers write whatever they wanted, but times change. We were working under different constraints at the time. If you have services scoped to a specific responsibility and they completely own their own data and don't have the situation you describe of many clients touching the same DB, a lot of the problems that the proc+views only pattern were intended to solve just go away on their own.