4 ms·

> ORMs don't have to be a problem but they nudge people into doing very sub optimal things. I don't think good ORMs do any nudging. The issue arises when peopl

by shkkmo 7y ago

> ORMs don't have to be a problem but they nudge people into doing very sub optimal things. I don't think good ORMs do any nudging. The issue arises when people assume that because they are using an ORM they don't have to learn the underlying DB. ORMs should be treated as tools that sit on top of your SQL knowledge and allow you to do certain types of things easier. Like any tool, there are inappropriate uses cases. On one side you have people using an ORM to implement a document store, on the other side you have people that end up hand-rolling a crappy ORM because they thought they didn't need one.

jillesvangurp 7y ago
I don't do object relational mappings generally. I instead query by id or a few other columns and construct objects from json documents stored in text columns. Frameworks for that are awesome and a lot easier to deal with and serializing/deserializing overhead is typically minimal. Columns in databases only have two purposes: indexed columns for querying (ids, dates, names, categories, etc.) with or without some constraints, and raw data (json or for simple structures some primitive values. Some databases even allow you to query the json directly but in my experience this is kind of fiddly to set up and not really worth the trouble. The nice thing is that most domain model changes don't require database schema changes this way because the only thing affected is your json schema. This makes iterating on your domain model a lot easier. You still have to worry about migrations of course. The added value of using a database is being able to manipulate them safely with transactions and query them efficiently. Bad ORM ends up conflicting with both goals and the added value of well implemented ORM is usually fairly limited. At best you end up with a lot of tables and columns you did not really need mapped to your objects and classes. A good table structure often makes for a poor domain model and vice versa. The friction you get from the object relational impedance mismatch is best avoided by treating them as two things instead of one. Bad ORM shoves this under the carpet and in my experience does not address this (other than by providing the illusion this is not a problem).
- shkkmo 7y ago
  > I instead query by id or a few other columns and construct objects from json documents stored in text columns. > Columns in databases only have two purposes: indexed columns for querying (ids, dates, names, categories, etc.) with or without some constraints, and raw data (json or for simple structures some primitive values. You are basically describing a sort of ad-hoc document store with potentially limited ability to query. You've lost many of the benefits provided by a relational DB. Your ability to do run large update queries or reports will be limited (unless your DB provides native json support, which as you said, is fiddly). If you are going to do this, why not use a NoSQL document store with support for transactions? Then you will get a tool that is designed to work with your use case. Edit: If you use an ORM, a hybrid approach is possible. Where you store some properties as separate columns and then store the less frequently accessed (or more dynamically structure) data in a json field (which you can deserialize on hydration or on request). The main downside of this hybrid approach is that moving a propery out of the json field into a normal column would require using that fiddly native json support or a fairly slow migration that would need to go through and serialize each json field. > A good table structure often makes for a poor domain model and vice versa. Can you clarify what you mean? This has not at all been my experience so I am curious and would love to see some examples.
  - jillesvangurp 7y ago
    Transactional semantics are problematic with a lot of nosql databases but I've used a few and you can work around this if you have some kind of consistency checks using content hashes. Postgres is pretty nice these days for a wide variety of use cases; including nosql ones. And it does transactions pretty nicely. Regarding the object relational impedance mismatch, check here: https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch https://en.wikipedia.org/wiki/Object-relational_impedance_mi... In short, there are lots of things you'd do different in an OO domain model vs. properly normalized tables. A lot of what ORMs do is about taking object relations and mapping those to some kind of table structure. You either end up making compromises on your OO design to reduce the number of tables or on the database side to end up with way too many tables and joins (basically most uses of ORM I've encountered in the wild). For reporting, you can of course choose to go for a hybrid document/column based approach. I've done that. In a pinch you can even extract some data from the json in an sql query using whatever built in functions the database provides. Kind of tedious and ugly but I've done it. Or you can use something that actually was built to do reporting properly. I do a lot of stuff in Elasticsearch with aggregations and it kind of blows most sql databases out of the water for this kind of stuff if you know what you are doing. In a pinch, I can do some sql queries and I've also used things like amazon athena (against json or csv in s3 buckets) as well. Awesome stuff but limited. Either way, if that's a requirement, I'd optimize the database schema for it. But for the kind of stuff people end up doing where they have an employee and customer class that are both persons that have addresses and a lot of stuff that is basically only ever going to be fetched by person id and never queried on, I'll take a document approach every time vs. doing joins between a dozen tables. I also like to denormalize things into documents. Having a category table and then linking categories by id is a common pattern in relational databases. Or you can just decide that the category id is a string that contains some kind of urn or string representation of the category and put those directly in in a column or in the json. You lose the referential integrity check on the foreign key of course; but then you should not rely on your database to do input validation so that check would be kind of redundant.

by shkkmo 7y ago

jillesvangurp 7y ago

I don't do object relational mappings generally. I instead query by id or a few other columns and construct objects from json documents stored in text columns. Frameworks for that are awesome and a lot easier to deal with and serializing/deserializing overhead is typically minimal. Columns in databases only have two purposes: indexed columns for querying (ids, dates, names, categories, etc.) with or without some constraints, and raw data (json or for simple structures some primitive values. Some databases even allow you to query the json directly but in my experience this is kind of fiddly to set up and not really worth the trouble. The nice thing is that most domain model changes don't require database schema changes this way because the only thing affected is your json schema. This makes iterating on your domain model a lot easier. You still have to worry about migrations of course. The added value of using a database is being able to manipulate them safely with transactions and query them efficiently. Bad ORM ends up conflicting with both goals and the added value of well implemented ORM is usually fairly limited. At best you end up with a lot of tables and columns you did not really need mapped to your objects and classes. A good table structure often makes for a poor domain model and vice versa. The friction you get from the object relational impedance mismatch is best avoided by treating them as two things instead of one. Bad ORM shoves this under the carpet and in my experience does not address this (other than by providing the illusion this is not a problem).

shkkmo 7y ago
> I instead query by id or a few other columns and construct objects from json documents stored in text columns. > Columns in databases only have two purposes: indexed columns for querying (ids, dates, names, categories, etc.) with or without some constraints, and raw data (json or for simple structures some primitive values. You are basically describing a sort of ad-hoc document store with potentially limited ability to query. You've lost many of the benefits provided by a relational DB. Your ability to do run large update queries or reports will be limited (unless your DB provides native json support, which as you said, is fiddly). If you are going to do this, why not use a NoSQL document store with support for transactions? Then you will get a tool that is designed to work with your use case. Edit: If you use an ORM, a hybrid approach is possible. Where you store some properties as separate columns and then store the less frequently accessed (or more dynamically structure) data in a json field (which you can deserialize on hydration or on request). The main downside of this hybrid approach is that moving a propery out of the json field into a normal column would require using that fiddly native json support or a fairly slow migration that would need to go through and serialize each json field. > A good table structure often makes for a poor domain model and vice versa. Can you clarify what you mean? This has not at all been my experience so I am curious and would love to see some examples.
- jillesvangurp 7y ago
  Transactional semantics are problematic with a lot of nosql databases but I've used a few and you can work around this if you have some kind of consistency checks using content hashes. Postgres is pretty nice these days for a wide variety of use cases; including nosql ones. And it does transactions pretty nicely. Regarding the object relational impedance mismatch, check here: https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch https://en.wikipedia.org/wiki/Object-relational_impedance_mi... In short, there are lots of things you'd do different in an OO domain model vs. properly normalized tables. A lot of what ORMs do is about taking object relations and mapping those to some kind of table structure. You either end up making compromises on your OO design to reduce the number of tables or on the database side to end up with way too many tables and joins (basically most uses of ORM I've encountered in the wild). For reporting, you can of course choose to go for a hybrid document/column based approach. I've done that. In a pinch you can even extract some data from the json in an sql query using whatever built in functions the database provides. Kind of tedious and ugly but I've done it. Or you can use something that actually was built to do reporting properly. I do a lot of stuff in Elasticsearch with aggregations and it kind of blows most sql databases out of the water for this kind of stuff if you know what you are doing. In a pinch, I can do some sql queries and I've also used things like amazon athena (against json or csv in s3 buckets) as well. Awesome stuff but limited. Either way, if that's a requirement, I'd optimize the database schema for it. But for the kind of stuff people end up doing where they have an employee and customer class that are both persons that have addresses and a lot of stuff that is basically only ever going to be fetched by person id and never queried on, I'll take a document approach every time vs. doing joins between a dozen tables. I also like to denormalize things into documents. Having a category table and then linking categories by id is a common pattern in relational databases. Or you can just decide that the category id is a string that contains some kind of urn or string representation of the category and put those directly in in a column or in the json. You lose the referential integrity check on the foreign key of course; but then you should not rely on your database to do input validation so that check would be kind of redundant.