3 ms·

You should be careful with how much you lean into “doing it in the database” as well with how you implement it. Lest, you get the situation where your applicati

by hk1337 1y ago

You should be careful with how much you lean into “doing it in the database” as well with how you implement it. Lest, you get the situation where your application inserts as one value and it gets saved completely different.

mattmanser 1y ago
I'm not sure if this is what you mean, but I think a big thing missing from the article is how you should isolate you business logic. A great software design will separate all business logic into its own layer. That might be a distinct project, module, or namespace, depending on what your language supports. Keep business logic out of SQL and out of web server code (controllers, web helpers, middleware, etc.). Then you're treating SQL as the data store it is designed to be. When you embed application logic in SQL, you're hiding core functionality in a place where most developers won't expect to find it. This approach also creates tight coupling between your application and your database provider, making it hard to switch as needs change/the application grows.
- dondraper36 1y ago
  That also depends on what you would consider "business logic in the database". What would you say about CHECK constraints, though? I don't think it's something few developers expect to see, and having these checks is very convenient. I know that there are even opponents of foreign keys (which makes sense sometimes), but in general, I don't understand why I would ever throw away the nice features of Postgres that can enforce correctness.
- hk1337 1y ago
  Pretty much yeah. I think you need to stay somewhat vigilant because it can creep in under the guise of validating data. Triggers is something I was thinking about that is grossly misused.
  - porridgeraisin 1y ago
    I like using triggers for validation important for maintaining data integrity. Example: let's say you have a parent table P whose rows are soft deleted (update P set _deleted=1 where id=?) and a child table C (foreign key on P.id) On delete cascade isn't gonna work here obviously. So I use a trigger to emulate on delete cascade. Also, I've seen triggers to cancel transactions raise abort(..) if they violate some property that can't be retrofitted into a proper db constraint due to team politics at a previous workplace.

by hk1337 1y ago

mattmanser 1y ago

I'm not sure if this is what you mean, but I think a big thing missing from the article is how you should isolate you business logic. A great software design will separate all business logic into its own layer. That might be a distinct project, module, or namespace, depending on what your language supports. Keep business logic out of SQL and out of web server code (controllers, web helpers, middleware, etc.). Then you're treating SQL as the data store it is designed to be. When you embed application logic in SQL, you're hiding core functionality in a place where most developers won't expect to find it. This approach also creates tight coupling between your application and your database provider, making it hard to switch as needs change/the application grows.

dondraper36 1y ago
That also depends on what you would consider "business logic in the database". What would you say about CHECK constraints, though? I don't think it's something few developers expect to see, and having these checks is very convenient. I know that there are even opponents of foreign keys (which makes sense sometimes), but in general, I don't understand why I would ever throw away the nice features of Postgres that can enforce correctness.
hk1337 1y ago
Pretty much yeah. I think you need to stay somewhat vigilant because it can creep in under the guise of validating data. Triggers is something I was thinking about that is grossly misused.
- porridgeraisin 1y ago
  I like using triggers for validation important for maintaining data integrity. Example: let's say you have a parent table P whose rows are soft deleted (update P set _deleted=1 where id=?) and a child table C (foreign key on P.id) On delete cascade isn't gonna work here obviously. So I use a trigger to emulate on delete cascade. Also, I've seen triggers to cancel transactions raise abort(..) if they violate some property that can't be retrofitted into a proper db constraint due to team politics at a previous workplace.