5 ms·

Sounds like most/all of these issues would be solved by validation at the app layer.

ivandenysov 2y ago
True, but for example with db-level validations I don’t have to worry that a new developer ships a batch job that runs raw inserts for performance reasons and bypasses app layer validations.
- zepolen 2y ago
  What? You should be exposing your data access layer as a microservice and all your db level validations should be done there... You can even write a small DSL syntax language to make it easier to use for developers, and perhaps an Obviously Reduntant Middleware that sits between them to convert their programming language objects to the DSL. Add some batch support, perhaps transactional locks (using mongo, we want to be webscale after all) and perhaps a small terminal based client and voila, no one should ever need to deal with petty integrity in the db again.
  - deleted 2y ago
    [deleted]
  - eropple 2y ago
    This post is a fantastic example of Poe's Law. You had me for a second.
- goosejuice 2y ago
  I'm not advocating for no database level validations. One can and should have both in my opinion. Postgres supports constraints on jsonb as well. I would generally advocate for no write access outside of the app as well. Certainly for new developers. Get some tests on that batch job.
  - corytheboyd 2y ago
    > I would generally advocate for no write access outside of the app as well. FWIW I think OP was referring to app code still, but code opting in to skipping app data validations. Rails for example makes this very easy to do, and there are tons and tons of people out there selling this as a performance improvement (this one trick speeds up bulk operations!!!!). There are times where it’s useful, but it’s a very sharp knife that people can easily misuse. So yeah, anyway, have both db constraints and app validations.
    - goosejuice 2y ago
      Thanks for pointing that out, I did misread that and have seen/written such things in rails land. We used active record import which gives the option of running validations. But yeah, layering your safety nets is generally wise. I would include testing and code review in that as well.
pennomi 2y ago
App layer validation is for the user’s sanity. DB layer validation is for the developer’s sanity.
- papichulo2023 2y ago
  Ehhh, most dbs are managed by migrations in the application, so essentially is duplicated logic
  - eropple 2y ago
    Most databases eventually have a human typing an INSERT statement or the moral equivalent. It's not duplication to have your correctness functions protect that, too.
- goosejuice 2y ago
  I like this as a general rule, but I almost always have more refined validation at the app layer -- particularly when using jsonb. Tool of choice is ecto which has excellent support for maintaining jsonb structure.
- evanelias 2y ago
  I'd assume this might be solvable in the DB layer using CHECK constraints, possibly combined with JSON Schema validation? From quick glance, JSON Schema validation isn't built-in to Postgres, but there are third-party extensions, such as https://github.com/supabase/pg_jsonschema https://github.com/supabase/pg_jsonschema or https://github.com/gavinwahl/postgres-json-schema https://github.com/gavinwahl/postgres-json-schema. I'm more familiar with MySQL and MariaDB, which offer it built-in: https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html https://dev.mysql.com/doc/refman/8.0/en/json-validation-func... and https://mariadb.com/kb/en/json_schema_valid/ https://mariadb.com/kb/en/json_schema_valid/

True, but for example with db-level validations I don’t have to worry that a new developer ships a batch job that runs raw inserts for performance reasons and bypasses app layer validations.

zepolen 2y ago
What? You should be exposing your data access layer as a microservice and all your db level validations should be done there... You can even write a small DSL syntax language to make it easier to use for developers, and perhaps an Obviously Reduntant Middleware that sits between them to convert their programming language objects to the DSL. Add some batch support, perhaps transactional locks (using mongo, we want to be webscale after all) and perhaps a small terminal based client and voila, no one should ever need to deal with petty integrity in the db again.
- deleted 2y ago
  [deleted]
- eropple 2y ago
  This post is a fantastic example of Poe's Law. You had me for a second.
goosejuice 2y ago
I'm not advocating for no database level validations. One can and should have both in my opinion. Postgres supports constraints on jsonb as well. I would generally advocate for no write access outside of the app as well. Certainly for new developers. Get some tests on that batch job.
- corytheboyd 2y ago
  > I would generally advocate for no write access outside of the app as well. FWIW I think OP was referring to app code still, but code opting in to skipping app data validations. Rails for example makes this very easy to do, and there are tons and tons of people out there selling this as a performance improvement (this one trick speeds up bulk operations!!!!). There are times where it’s useful, but it’s a very sharp knife that people can easily misuse. So yeah, anyway, have both db constraints and app validations.
  - goosejuice 2y ago
    Thanks for pointing that out, I did misread that and have seen/written such things in rails land. We used active record import which gives the option of running validations. But yeah, layering your safety nets is generally wise. I would include testing and code review in that as well.

pennomi 2y ago

App layer validation is for the user’s sanity. DB layer validation is for the developer’s sanity.

papichulo2023 2y ago
Ehhh, most dbs are managed by migrations in the application, so essentially is duplicated logic
- eropple 2y ago
  Most databases eventually have a human typing an INSERT statement or the moral equivalent. It's not duplication to have your correctness functions protect that, too.
goosejuice 2y ago
I like this as a general rule, but I almost always have more refined validation at the app layer -- particularly when using jsonb. Tool of choice is ecto which has excellent support for maintaining jsonb structure.
evanelias 2y ago
I'd assume this might be solvable in the DB layer using CHECK constraints, possibly combined with JSON Schema validation? From quick glance, JSON Schema validation isn't built-in to Postgres, but there are third-party extensions, such as https://github.com/supabase/pg_jsonschema https://github.com/supabase/pg_jsonschema or https://github.com/gavinwahl/postgres-json-schema https://github.com/gavinwahl/postgres-json-schema. I'm more familiar with MySQL and MariaDB, which offer it built-in: https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html https://dev.mysql.com/doc/refman/8.0/en/json-validation-func... and https://mariadb.com/kb/en/json_schema_valid/ https://mariadb.com/kb/en/json_schema_valid/