Yeah this is it. It's all fine until a product manager asks for analytics on this via their analytics tool of choice, and you have to say "sorry can't do that", or you have to build a complex data pipeline all because you can't do an HTTP POST to an external service.
Also database migrations are notoriously hard to get right, at scale of traffic, at scale of development pace, at scale of team, and often require a bunch of tooling. This pattern pushes even more into database transactions.
I'd rather take a boring ORM plus web framework, where you need a little boilerplate, but you get a stateless handler in Python/whatever to handle this. So much more flexibility for very little extra cost.
Analytics would need to be written to a table, then you'd need a batch job to come along and read them out and send them into the analytics system of choice in the company. Now you've got extra database load, write load, you can't do it on a read replica but it needs to be on the database primary, rolling back a transaction now has bigger implications than a read-only process, and you're hosting another job to post to the analytics system. I'm assuming external analytics here because almost every product team I've seen wants them.
As for database migrations, views and procedures are easier than data migrations, sure, but I've not seen anything around progressive rollout, canarying, etc. Do you do that separately on different replicas?
All of these things are kind of solved problems in basic web apps, but all of these would require a bunch of "odd" stuff to make it work hosting in a database. Is any one part bad? Not necessarily, but I generally like to minimise things I have to apologise to new starters for, and this would cause many of those sorts of conversations.
> then you'd need a batch job to come along and read them out and send them into the analytics system of choice in the
I wouldn't need that. I would just use the logs for analytics.
> I've not seen anything around progressive rollout, canarying, etc. Do you do that separately on different replicas?
No, I would put the new feature into a new view or procedure, and use database roles to manage progressive rollout, canary releases, and feature flags.
> I generally like to minimise things I have to apologise to new starters
You do "you" as is your right. But what I hear when I read this is not that there are technical obstacles to the approach under consideration, but rather that there are social obstacles. Using the database this way "isn't how you're supposed to do it." I grant that has been the prevailing view for about thirty years or so, but to me it's unexamined received wisdom that deserves scrutiny every now and then.
Well you do you too, but in an org shipping a product I couldn't see this sort of thing working.
> I grant that has been the prevailing view for about thirty years or so
This is true, and in fact it's something I have tried to push back on. I've tried this sort of thing. I've tried to push more things into the database. I've tried things like pushing more logic into the database, or pushing more into the frontend webserver, etc, and my conclusion is that it doesn't work.
> this is not that there are technical obstacles to the approach under consideration, but rather that there are social obstacles
The way I see it is that there are no technical obstacles if you assume zero cost of development. If we value our time at zero we can make all of this work, and in some cases in nicer ways. This is how I feel as a hobbyist software developer, but I learnt early on in my career as a software engineer that this is not viable at work. Optimising for development time, and optimising for product flexibility or requirements changes, are almost always worth doing (and yes I realise those are in tension with each other).
Moving more logic to the database like this, or eschewing a logic layer entirely in favour of configuration, may feel like an elegant solution, but I've been bitten by these sorts of things too many times. I'll take a little boilerplate, something a junior engineer will understand, and a bunch of flexibility for product direction, any day.
HN guidelines urge us not to cross-examine and I wouldn't want to do that anyway, so let me just say this.
If you want to share the details of how you were bitten by these sorts of things, I'll welcome it as your "lived experience" as they say. My own experience was as a system integrator from 1997 to 1999 for a client-server CRM product called Onyx, which had a VB front-end and a MSSQL back-end with all the logic in sprocs. I didn't write it, so I don't know what that was like, but I customized it, and it worked. I was a Java developer from 2000 to 2002 building first dot-com boom internet applications this way in a small team for admittedly small clients, and it worked. I was a Java developer from 2002 to 2005 building "intranet" applications this way in a small team for a big client (Wells Fargo) with Oracle sprocs, and it worked, though already by then the industry was 5 years into the fashion of "N-tier architectures", "application servers", and "ORMs" so this way was definitely unorthodox. When I came back from grad school this way was history, with Ruby on Rails, Django, and Spring plus Hibernate being the standard. I was again a Java developer at an enterprise back-office software company from 2010 to 2016 and we definitely did not do it this way. We also had 200+ engineers and I'm quite sure it would've been chaos if we'd tried.
The way I see it is that there are few technical obstacles that aren't dwarfed by social obstacles. No software architecture or development practice will succeed whatever its merits if people don't want to use them. In my experience, what engineers have wanted is to program in anything but SQL. Building database-backed web services (for example) in the orthodox way is inefficient but it works, and more important it feels like work. It demands large teams, and then rewards large teams with forward progress and a code base that makes sense, but only in the context of a large team. My hunch is that building applications in the database with large teams would fail, but also that large teams are unnecessary when building applications in the database.
But hey. Maybe I'm wrong. You tell me. How did the projects you built in this way stuggle?
Also database migrations are notoriously hard to get right, at scale of traffic, at scale of development pace, at scale of team, and often require a bunch of tooling. This pattern pushes even more into database transactions.
I'd rather take a boring ORM plus web framework, where you need a little boilerplate, but you get a stateless handler in Python/whatever to handle this. So much more flexibility for very little extra cost.