I think you're right about number 3. I have a friend who likes to do as much as possible with pgsql and I'm often surprised at how well it performs for the things he uses it for.
I'm not sure if it's fair to read your parent as putting business logic in the db: it may just use Postgres whenever a datastore is required and Postgres is not a bad fit.
Deciding where business/application logic should reside is an important decision, and treating PostgreSQL as an application server in addition to a database does require a different way of approaching the tool. Of course, this is no different than any of the other application architecture choices that need to be made.
I am not sure i understand your comment also :)
My point was that people (read web devs) treat databases (postgres) as only data stores, put in data, get data out, and they are so much more. Sure not anything goes into the db, but if it's a bit of logic that relates only to the data (who can access it, who can change it and in what way), most likely it belongs in the database.
PS: but let's not derail this thread with debates on where business logic should go :)
I think I agree with you that there are some things that belong in the database. For example, anything I can do in the database to ensure data integrity (without unduly affecting performance), I'll put there (data types, constraints, triggers, functions which encapsulate transactions). Similarly with access privileges.
My initial comment was in response to these statements:
I have a friend who likes to do as much as possible with pgsql and I'm often surprised at how well it performs for the things he uses it for.
I understood you to read this as putting business logic in the database (indicated by the And yet) in your comment:
And yet i hear day after day, don't put business logic in the db
I read the initial comment by 'fapjacks as meaning using Postgres whenever appropriate, as opposed to doing everything one could with Postgres in Postgres.
Not a big deal at all. I saw a potential misunderstanding and attempted to clarify.
PS: Of course, business logic should go where I put it, right? ;)
You are right in both cases, yes i read that comment like you described and yes that is probably not what fapjacks meant.
As for the beginning of your comment, i am 100% with you. The problem is that the cases you describe (integrity,access,reports on data) are most of the time what the entire application does (we are all just doing CRUD and all that ...) and instead seeing that for what it is (let's call it DataLogic) people are all to often calling it Business logic, and the moment they put that label on it their brain automatically (after years of conditioning) does not even begin to consider the database as the appropriate place for that type of logic.
Isn't it harder to create well designed abstractions for business logic in SQL compared to a full-fledged backend application server?
I know SQL has stored procedures and the like, but is it correct that SQL is limited in how it can express domain logic, compared to languages that backend application servers are generally written in?
The point i was making was more in the direction of logic in the database, not specifically in pure SQL, it can be PgSql, Python ... does not matter. For the tasks that database side code should be used for (data consistency/access rights/reports on data) i would say they can express the domain logic for those tasks quite nicely, even better then in other languages.
For example i can say "grant select on invoices to accountant" or i can add a constraint on a table like "CHECK (price > discounted_price)" or "CHECK (price > 0)". I do that in one place and i no longer have to worry about checking this (simple example of) domain logic anywhere else.
Most of the time this is what people call domain logic (consistency/access/reports) although i would think a better name for it would be "data logic", heard that somewhere and in a lot of the cases this is almost everything the application does
> I know SQL has stored procedures and the like, but is it correct that SQL is limited in how it can express domain logic, compared to languages that backend application servers are generally written in?
Procedural extensions aren't, really. I mean you can literally just use Python (pg/python) or Javascript (plv8) to write postgres functions.
At one dev position I had, the DBA wrote stored procedures for everything I could possibly want to do with the database, and that was the only way I interacted with it period. I wrote a small wrapper around these stored procedures and the records they returned, and used it exclusively.
It was a much more pleasant experience than having no stored procedures and a full blown ORM, which IMO inevitably leads to sillyness like doing inner joins at the UI level.
We store settings based on a hierarchy in the database using JSONB. Basically:
global -> country -> account -> user
PostgreSQL doesn't support deep merging of json docs, so I just wrote an aggregate function using plv8 to merge them. (the result from the query always results in ~10 records which is then grouped and merged so perf is always fast)
I also had to write JSONB deep merging as part of this RethinkDB --> PostgreSQL rewrite. Here's the code I wrote
https://github.com/sagemathinc/smc/blob/ce594ff0574ce781bf78...
That code constructs a single complicated query; I wonder whether I should write it as a stored function in the database instead. (I'm the author of the blog post.)
I've deliberately avoided deep arrays for settings, so my jsonb settings merge is simply:
SELECT COALESCE(companies.options, '{}'::jsonb) || COALESCE(groups.options, '{}'::jsonb) || COALESCE(users.options, '{}'::jsonb)
FROM users
JOIN companies ON users.company_id = companies.id
LEFT JOIN groups ON users.group_id = groups.id
WHERE users.id = ?
Where I need a hierarchy for key names I'm just prefixing.
I sit right on the knife edge of tossing all the jsonb options and replacing with an old-school normalized EAV model.
reply