5 ms·

What would you replace them with in statically-typed languages? You see, in database you've got tuples and nothing else, and in program, you've got objects and

by ilyak 17y ago

What would you replace them with in statically-typed languages? You see, in database you've got tuples and nothing else, and in program, you've got objects and nothing else. You have to convert one to another this way or that way. Talking about dynamically-typed languages, you can treat data as key-value pairs (where values can be arrays or their own key-value pairs) and thus skip this problem entirely. But yeah, Hibernate is painful enough to make one want to escape conventional ORMs. What we really need is something like XStream of SQL: i.e. just take the result of query (set of tuples) and populate objects using reflection.

thwarted 17y ago
The populating objects part doesn't seem to be the hardest part. The impedance mismatch with ORM is trying to merge the concepts of "result sets" that are not "table rows" to objects in such a way that you can interact with them on a per-table row basis. It's easy to say that each row in the users table represents a user so each row is a distinct object. It's harder when you want to get data out of the database using a single query that hits multiple tables and returns a joined result set that doesn't map to the already established object hierarchy, an object hierarchy that is based on the normalized schema. Denormalizing only helps with some parts of this. Writing the naive ORM implementation that tries to model a relational schema as a sets of nested objects is doable, but it becomes difficult to get decent performance out of it, since the kinds of queries that map rows to objects are not usually the kinds of queries that are performant in SQL.
- ilyak 17y ago
  Populating objects isn't the hardest part, but it's the most useful part. Writing object populating code is tedious, copy-pasty and uncool. And having that, you don't really need ORM, naive or not.
  - bokonist 17y ago
    If you're in Java, just use BeanResultHandler. We use helper functions that simply wrap the low level queries using beanhandler. So all my queries end up looking like: List<MyObject> myobjects = dbUtils.select("SELECT name,age FROM (some complex query ... )", params, MyObject.class) BeanHandler will automatically fill any property in MyObject with a column of a matching name from the select statement.
    - ilyak 17y ago
      This is not enough: you should also set up references to other objects (by id) and deal with underscore_database_names vs camelCaseJavaNames.
      - randallsquared 17y ago
        ...via setting. But that last is trivial. Just add it.
      - thwarted 17y ago
        And include a singular<->plural mapping for column and table names.
jerf 17y ago
My suggestion, which I've only used a little so far, is that if you want to get "objects" back out of the database, instead of trying to map "tables" to objects, map queries to objects. Use standard code techniques to limit the amount of SQL you have to write; a "DSL" that works in $YOUR_FAVORITE_LANGUAGE but directly maps to SQL can be very helpful here. (One of the ways that SQL shows its age is that it isn't very composable, which is a fatal flaw. This can at least be papered over in better languages, though it would be better if the actual DB language wasn't so... 70s.) This avoids many problems with DSLs: Aggregate SQL functions work again and aren't that special. If you don't actually want a 1-to-1 mapping of rows to objects, you have the opportunity to do something more clever. The downside is that you need to think more about your interaction with the DB and that it takes a fairly mature programmer to avoid getting tightly coupled with the DB, not to mention a language with powerful enough abstractions to let you not be tightly coupled with the DB. I suspect there's a good replacement library waiting in here. SQLAlchemy in Python has some aspects of this, I think, though the ORM heritage still shines through. It will never be as easy as ORMs promised to be, but it could very well end up being easier than ORMs actually are. Since ORMs have never delivered on their promise, I think we can stop holding things to that entirely-theoretical standard.
- rikthevik 17y ago
  "...instead of trying to map "tables" to objects, map queries to objects..." Brilliant! My solution to this problem using Propel was to make fake objects that map to views rather than tables. It's worked out splendidly so far. I'm not sure what performance is going to be like in the long term, but it sure beats the O(n) queries that were written before I got there.
- olliesaunders 17y ago
  This is a very long comment for me to agree with everything, well done.
- reynolds 17y ago
  Mapping queries to objects is something I do with my datastore layer. Models that are inserted are first pickled and zlib compressed. When objects are pulled from the table they are returned as python dicts. To map them back into an object state I just pass the dict as kwargs and initialize the model.
jlouis 17y ago
Please read up on your type theory. A tuple is easily typed as a product type. Most functional languages have these, so the impedance mismatch is greatest when you consider a language which has no product types, as in e.g. Java. Ocaml has PG'Ocaml for instance: "PG'OCaml provides an interface to PostgreSQL databases for Ocaml applications. It extends the Ocaml syntax, enabling the embedding of SQL statements inside the Ocaml code, and checking at compile-time the consistency between the programme and the DB." You still need to work with the concept of the database returning a set of data rather than a single row, but that can be done with a little code as well.

by ilyak 17y ago

thwarted 17y ago

The populating objects part doesn't seem to be the hardest part. The impedance mismatch with ORM is trying to merge the concepts of "result sets" that are not "table rows" to objects in such a way that you can interact with them on a per-table row basis. It's easy to say that each row in the users table represents a user so each row is a distinct object. It's harder when you want to get data out of the database using a single query that hits multiple tables and returns a joined result set that doesn't map to the already established object hierarchy, an object hierarchy that is based on the normalized schema. Denormalizing only helps with some parts of this. Writing the naive ORM implementation that tries to model a relational schema as a sets of nested objects is doable, but it becomes difficult to get decent performance out of it, since the kinds of queries that map rows to objects are not usually the kinds of queries that are performant in SQL.

ilyak 17y ago
Populating objects isn't the hardest part, but it's the most useful part. Writing object populating code is tedious, copy-pasty and uncool. And having that, you don't really need ORM, naive or not.
- bokonist 17y ago
  If you're in Java, just use BeanResultHandler. We use helper functions that simply wrap the low level queries using beanhandler. So all my queries end up looking like: List<MyObject> myobjects = dbUtils.select("SELECT name,age FROM (some complex query ... )", params, MyObject.class) BeanHandler will automatically fill any property in MyObject with a column of a matching name from the select statement.
  - ilyak 17y ago
    This is not enough: you should also set up references to other objects (by id) and deal with underscore_database_names vs camelCaseJavaNames.
    - randallsquared 17y ago
      ...via setting. But that last is trivial. Just add it.
    - thwarted 17y ago
      And include a singular<->plural mapping for column and table names.

jerf 17y ago

My suggestion, which I've only used a little so far, is that if you want to get "objects" back out of the database, instead of trying to map "tables" to objects, map queries to objects. Use standard code techniques to limit the amount of SQL you have to write; a "DSL" that works in $YOUR_FAVORITE_LANGUAGE but directly maps to SQL can be very helpful here. (One of the ways that SQL shows its age is that it isn't very composable, which is a fatal flaw. This can at least be papered over in better languages, though it would be better if the actual DB language wasn't so... 70s.) This avoids many problems with DSLs: Aggregate SQL functions work again and aren't that special. If you don't actually want a 1-to-1 mapping of rows to objects, you have the opportunity to do something more clever. The downside is that you need to think more about your interaction with the DB and that it takes a fairly mature programmer to avoid getting tightly coupled with the DB, not to mention a language with powerful enough abstractions to let you not be tightly coupled with the DB. I suspect there's a good replacement library waiting in here. SQLAlchemy in Python has some aspects of this, I think, though the ORM heritage still shines through. It will never be as easy as ORMs promised to be, but it could very well end up being easier than ORMs actually are. Since ORMs have never delivered on their promise, I think we can stop holding things to that entirely-theoretical standard.

rikthevik 17y ago
"...instead of trying to map "tables" to objects, map queries to objects..." Brilliant! My solution to this problem using Propel was to make fake objects that map to views rather than tables. It's worked out splendidly so far. I'm not sure what performance is going to be like in the long term, but it sure beats the O(n) queries that were written before I got there.
olliesaunders 17y ago
This is a very long comment for me to agree with everything, well done.
reynolds 17y ago
Mapping queries to objects is something I do with my datastore layer. Models that are inserted are first pickled and zlib compressed. When objects are pulled from the table they are returned as python dicts. To map them back into an object state I just pass the dict as kwargs and initialize the model.

jlouis 17y ago

Please read up on your type theory. A tuple is easily typed as a product type. Most functional languages have these, so the impedance mismatch is greatest when you consider a language which has no product types, as in e.g. Java. Ocaml has PG'Ocaml for instance: "PG'OCaml provides an interface to PostgreSQL databases for Ocaml applications. It extends the Ocaml syntax, enabling the embedding of SQL statements inside the Ocaml code, and checking at compile-time the consistency between the programme and the DB." You still need to work with the concept of the database returning a set of data rather than a single row, but that can be done with a little code as well.