I’ve been doing this in Postgres and it’s worked out well. We had a source of JSON events and wanted to store them for analysis, but didn’t know ahead of time what indices we’d need. We just knew we’d probably want the ability to do that analysis in the future.
So we ingested everything as jsonb and now I can add generated columns and indices retroactively where needed for query performance. So far, no regrets.
Indeed, the fact that both sqlite and postgres support virtual columns is great that it allows a path for minimal scaling up from the former to the later if desired.
zie’s answer is good. Another perspective: single responsibility for microservices.
You don’t reimplement logic in multiple apps writing to the same store. So either you have a microservice dedicated to providing the API needed while remaining the single-source-of-truth, or you have business logic in the RDBMS so that the RDBMS effectively embeds that microservice.
And then it’s a question of available skillsets of your staff, and how many people are good at debugging stored procedures vs issuing distributed traces, etc.
so that the RDBMS effectively embeds that microservice
That’s an awesome way to describe the approach that I haven’t heard before. It sounds like it could also be used to make some db engineers twitch when I refer to their stored procedures repo as a microservice. Great! :-)
It’s not business logic, it’s pretty much like a database view, which certainly belongs into a database. I think abstractions for data belong close to the data. That also means that you can change, replace, the thing that interacts with the data, the actual business logic.
One can be cut by this easily, when designing database schemas to close to a framework, ORM, etc. only for things to change and having a horrible migration path, potentially having to replicate very unidiomatic behavior.
So I’d argue, data and data representation doesn’t belong in your business logic.
Or at least for keeping logic and data separate, which you don’t do if you essentially build parts of the schema or views in your business logic.
…unless you can put all the logic in there.
…or, you need to do selects on that data.
…or, other business units have views into your schema
…or, you have compliance requirements to do so
I use this a lot in conjunction with a json blob. This allows for document storage without super strict structure enforcement:
Something like:
I’ve been doing this in Postgres and it’s worked out well. We had a source of JSON events and wanted to store them for analysis, but didn’t know ahead of time what indices we’d need. We just knew we’d probably want the ability to do that analysis in the future.
So we ingested everything as
jsonband now I can add generated columns and indices retroactively where needed for query performance. So far, no regrets.Indeed, the fact that both sqlite and postgres support virtual columns is great that it allows a path for minimal scaling up from the former to the later if desired.
What is stored if the field is missing?
NULLis stored:Do not embed your business logic in the database.
Only a Sith deals in absolutes. (:
What about tools like PostgREST that allow you to embed all the business logic in the database?
Why not?
There are conflicting opinions on this.
Some people think they should push everything into the DB and the UI basically equates to pretty eye candy and user experience(UX).
Some people prefer a middle ground, and some people think all the business logic should live outside the DB.
I personally don’t think there is any one right answer. I think it depends on where the API boundary is, which mostly depends on the application.
If you have a need/desire to give end users DB access, then you almost certainly want a lot(if not all) business logic in the DB.
If you treat the DB as nothing more than a convenient place to store some data, then putting business logic there is stupid.
Most databases can support any/all of these options.
zie’s answer is good. Another perspective: single responsibility for microservices.
You don’t reimplement logic in multiple apps writing to the same store. So either you have a microservice dedicated to providing the API needed while remaining the single-source-of-truth, or you have business logic in the RDBMS so that the RDBMS effectively embeds that microservice.
And then it’s a question of available skillsets of your staff, and how many people are good at debugging stored procedures vs issuing distributed traces, etc.
It’s all trade-offs.
That’s an awesome way to describe the approach that I haven’t heard before. It sounds like it could also be used to make some db engineers twitch when I refer to their stored procedures repo as a microservice. Great! :-)
With a custom wire RPC format no less.
I agree with this as a valid perspective.
The example given wasn’t really business logic, right? Just an abstraction over normalization?
It’s not business logic, it’s pretty much like a database view, which certainly belongs into a database. I think abstractions for data belong close to the data. That also means that you can change, replace, the thing that interacts with the data, the actual business logic.
One can be cut by this easily, when designing database schemas to close to a framework, ORM, etc. only for things to change and having a horrible migration path, potentially having to replicate very unidiomatic behavior.
So I’d argue, data and data representation doesn’t belong in your business logic.
Or at least for keeping logic and data separate, which you don’t do if you essentially build parts of the schema or views in your business logic.
…unless you can put all the logic in there. …or, you need to do selects on that data. …or, other business units have views into your schema …or, you have compliance requirements to do so
etc