3 ms·

Here's a question for people who prefer to put their guarantees (i.e. unique/check constraints, foreign keys etc) in their database. How do you present these co

by mvc 14y ago

Here's a question for people who prefer to put their guarantees (i.e. unique/check constraints, foreign keys etc) in their database. How do you present these constraints to the user? How do you show when they've been violated? Just present them with the PG error message? Maintain a mapping of PG errors to more domain specific messages? Bite the bullet and just code them twice (once for the DB, and once for the user)?

ams6110 14y ago
Don't think if these guarantees as your primary data entry validation (though they are a stopgap for mistakes there). Your app will (ideally) never encounter violations; if it does, it means it's doing something wrong and should probably bail out with a "I am confused, please have my developers fix me" type of error.
jfb 14y ago
One could build out from the database schema -- Postgres certainly provides sufficient type information to build consistency double-checks in other layers of the application. As an added bonus, other consumers of the data won't be flying blind.
- mvc 14y ago
  Yeah, I'm trying to build something like that right now. Nice to know others have similar ideas.
jacques_chester 14y ago
Other code-generating tools build from the schema outwards. So for example, my professional life is currently about an obscure system called Oracle Application Express. You point ApEx at a schema and it will auto generate forms, reports, validations and so forth by introspecting on the database. Rails used to do something like this: you would point it at a database and it would generate code based on a superficial reading of the schema. Certainly nothing to do with validations etc.
jeffdavis 14y ago
Good question, and one that could use some discussion (e.g. in blog posts). Perhaps I'll write one. I think some redundancy is a practical necessity, regardless of your approach (using database constraints or only application code). Consider registering a unique username: even if you check for availability beforehand, you still need to handle the race condition. That's guaranteed to be a somewhat awkward user interaction because they thought it was available but you found out that it was taken by a concurrent user. So, your application always needs to have a nice way to avoid errors when they can be caught early (e.g. javascript check while filling out the form), and a less-nice way of handling errors when they can't (e.g. "sorry, that name has already been registered" after submitting the form). There are SQL-standard codes for many constraint errors, so those can be turned into exceptions. For things like CHECK constraints, think of those more like an assert: intentionally redundant.

by mvc 14y ago

ams6110 14y ago

Don't think if these guarantees as your primary data entry validation (though they are a stopgap for mistakes there). Your app will (ideally) never encounter violations; if it does, it means it's doing something wrong and should probably bail out with a "I am confused, please have my developers fix me" type of error.

jfb 14y ago

One could build out from the database schema -- Postgres certainly provides sufficient type information to build consistency double-checks in other layers of the application. As an added bonus, other consumers of the data won't be flying blind.

mvc 14y ago
Yeah, I'm trying to build something like that right now. Nice to know others have similar ideas.

jacques_chester 14y ago

Other code-generating tools build from the schema outwards. So for example, my professional life is currently about an obscure system called Oracle Application Express. You point ApEx at a schema and it will auto generate forms, reports, validations and so forth by introspecting on the database. Rails used to do something like this: you would point it at a database and it would generate code based on a superficial reading of the schema. Certainly nothing to do with validations etc.

jeffdavis 14y ago

Good question, and one that could use some discussion (e.g. in blog posts). Perhaps I'll write one. I think some redundancy is a practical necessity, regardless of your approach (using database constraints or only application code). Consider registering a unique username: even if you check for availability beforehand, you still need to handle the race condition. That's guaranteed to be a somewhat awkward user interaction because they thought it was available but you found out that it was taken by a concurrent user. So, your application always needs to have a nice way to avoid errors when they can be caught early (e.g. javascript check while filling out the form), and a less-nice way of handling errors when they can't (e.g. "sorry, that name has already been registered" after submitting the form). There are SQL-standard codes for many constraint errors, so those can be turned into exceptions. For things like CHECK constraints, think of those more like an assert: intentionally redundant.