Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How Postgres Triggers Can Simplify Your Back End Development

themythicalengineer.com

Post reply on HN
Print view View original

91–100 of 112 posts

Re: How Postgres Triggers Can Simplify Your Back End Development

#91

Post by OJFord »

simonw wrote: post #88
OJFord wrote: post #79

Earlier quoted context omitted.

Ok, so how do I checkout a version and view the file/schema tree? If you like, you can view my comment above as saying 'if they included that tooling, not just the similar tree of changes stored, it would be better'. How to get from one state to the next is interesting to the computer, not to me (not after I've initially written it/done it in prod anyway), I'm interested in the result, where do we stand after all of…

You checkout that version, then run the migrations from scratch against a fresh database. In Django that's "./mange.py migrate".

And then inspect the db, exactly. In other words you can't, there is no declarative 'current state' checked in or provided by the migration tooling.

Re: How Postgres Triggers Can Simplify Your Back End Development

#92

Post by leemac »

ysavir wrote: post #44
masklinn wrote: post #38

Earlier quoted context omitted.

> Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Alternatively, write all the business logic in the database. This way you can better leverage the DB features and ensure that logic only needs to be written once.

I worked for a short while at a place that tried following a similar dogma. Hiring was incredibly difficult, as was retaining people (such as myself). Writing business logic in code instead of DB functions is much more approachable than keeping it in the DB.

I spent the better half of a decade trying to rid us of the business logic in the database. At some point, the hole was too deep as we had stored procedures calling each other, such a mess. I could go on and on but also found hiring difficult, we were a small team so a database-only developer was a hard pill to swallow.

I eventually left. We pivoted to a new product and leadership agreed the old system was legacy to remain untouched. Eventually this thinking changed and the old product was to be integrated with the new. Sprocs were back baby! My battle was lost, I was done.

Re: How Postgres Triggers Can Simplify Your Back End Development

#93

Post by cgh »

throwaway173738 wrote: post #20
bongobingo1 wrote: post #8

My only hesitation with methods like this is it ends up splitting the business rules into two places, where one is sort of obscured. It's obvious to look at `add_new_payment` for the code that runs when adding a new payment, but then the code isn't there, so you have to know/ask or search in either migrations, a fresh structure dump or poke at the actual db (!). I think they're great for other, well, effects when nee…

Yeah I was expecting the post author to discuss the trade-off being made here because it’s really important to do so. The biggest complaint I have with these pithy articles is that they try to sell you on a particular trade-off without explaining what the deal is. It makes me think the author: 1. Just discovered this. 2. Implemented a bunch of them. 3. Hasn’t been maintaining this solution for more than a couple of m…

I'm not sure if the post was updated after your comment, but from the article:

"Triggers should be used with caution since they can obscure critical logic and create an illusion of automatic processes. While this can be advantageous in certain scenarios, it can also pose a challenge in terms of debugging, testing, and monitoring since they are not readily visible to developers."

Re: How Postgres Triggers Can Simplify Your Back End Development

#94

Post by citrin_ru »

bongobingo1 wrote: post #8

My only hesitation with methods like this is it ends up splitting the business rules into two places, where one is sort of obscured. It's obvious to look at `add_new_payment` for the code that runs when adding a new payment, but then the code isn't there, so you have to know/ask or search in either migrations, a fresh structure dump or poke at the actual db (!). I think they're great for other, well, effects when nee…

You have exactly the same problem in microservice architecture while calling a remote endpoint.

Re: How Postgres Triggers Can Simplify Your Back End Development

#95

Post by cgh »

whstl wrote: post #42
bongobingo1 wrote: post #8

My only hesitation with methods like this is it ends up splitting the business rules into two places, where one is sort of obscured. It's obvious to look at `add_new_payment` for the code that runs when adding a new payment, but then the code isn't there, so you have to know/ask or search in either migrations, a fresh structure dump or poke at the actual db (!). I think they're great for other, well, effects when nee…

Business rules are already often in multiple separated places in a codebase. If you mean "it should be in the same git repository", then triggers should be already in migrations. But IMO, even migrations are a stopgap solution to this problem. In some cases, such as in ORMs like Rails' ActiveRecord, even the column names aren't present in the model by default, they are only migration files. The real solution is putti…

If you are using some JPA implementation, eg Hibernate, then JPA events are more or less what you are talking about except JPA can't enforce the condition that JPA-defined events are the only triggers present. If you write custom triggers then you run the risk of stale entity data being presented to the user.

But otherwise, this fulfills the condition of triggered events residing in the codebase. Of course, this means you have to be comfortable with using an ORM in the first place.

Re: How Postgres Triggers Can Simplify Your Back End Development

#96

Post by simonw »

OJFord wrote: post #91
simonw wrote: post #88

Earlier quoted context omitted.

You checkout that version, then run the migrations from scratch against a fresh database. In Django that's "./mange.py migrate".

And then inspect the db, exactly. In other words you can't, there is no declarative 'current state' checked in or provided by the migration tooling.

I don't understand. What are you looking for here?

If you want a plain text SQL file to look at you can have that with a bit of extra automation - for example, every time you tag a release you could have a script that runs "./manage.py migrate" against that checkout and then dumps the schema out to a file somewhere (an asset attached to the release on GitHub as one example).

Re: How Postgres Triggers Can Simplify Your Back End Development

#97

Post by Kinrany »

Scubabear68 wrote: post #66
masklinn wrote: post #38

Earlier quoted context omitted.

> Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Alternatively, write all the business logic in the database. This way you can better leverage the DB features and ensure that logic only needs to be written once.

I have worked with systems with nearly 100k lines code in Oracle stored procs, and another legendary place with around 4 million. It was a nightmare. Deployments were very difficult, there was little tooling, reasoning about the system was difficult, and of course running so much code in Oracle required very expensive licenses. And it is much harder to hire hard core PL/SQL devs over Java, C#, Python or whatever. You…

This is a problem of SQL, not a database problem.

Re: How Postgres Triggers Can Simplify Your Back End Development

#98

Post by Scubabear68 »

prng2021 wrote: post #81
Scubabear68 wrote: post #66

Earlier quoted context omitted.

I have worked with systems with nearly 100k lines code in Oracle stored procs, and another legendary place with around 4 million. It was a nightmare. Deployments were very difficult, there was little tooling, reasoning about the system was difficult, and of course running so much code in Oracle required very expensive licenses. And it is much harder to hire hard core PL/SQL devs over Java, C#, Python or whatever. You…

Wow. I was going to say that no one in their right mind would even consider putting all business logic at the database layer. I’m willing to bet there were tons of Oracle-specific features being used too.

Oh, yes, absolutely.

At one point Terradata claimed they had a tool that could auto convert to something else with 99% success rate, or something like that. The reality on our samples we tried was like 40%.

Plus, Pl/SQL code is often very “chatty” with the data, if you try to port it as is you will often suddenly see performance issues as you see the amount of data that has to be sent over the wire from the DB to your code tier.

Re: How Postgres Triggers Can Simplify Your Back End Development

#99

Post by chucke »

Aqueous wrote: post #27

Why is this the top story? This is a major foot gun. Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Instead adopt a solution for structuring your business logic in a sane way, such as using a workflow engine. Your code will become simpler and well organized that way without creating a tangled web of distributed rules, as well as exist…

Workflow engines are neither simple, nor they are widely available for all programming languages.
Post reply on HN