4 ms·

I agree with most of what you said, but one thing bothers me: > And yet [SQL]'s the first thing that developers abstract away with an Object Relational Mapping

I agree with most of what you said, but one thing bothers me: > And yet [SQL]'s the first thing that developers abstract away with an Object Relational Mapping, much to their loss. I see that sentiment repeated often, but I really don't understand it. A decent ORM does two things: 1) It makes writing simple queries better – more maintainable, easier to compose, easier to secure against injection, smoother to write without constantly translating between your domain language and SQL. 2) It gets out of your way when you need to take the reins. If it is difficult to make your ORM run raw SQL queries and map the results back into domain models and primitives like array, hash, whatevs – it's time to find a better ORM. For me and many decent devs I've worked with, an ORM is not about hiding SQL. It's about automating the simple, routine stuff and letting me focus on the important, complicated parts – including highly-optimized SQL queries, at times.

barrkel 10y ago
If someone's primary perspective of the database is through the lens of an OO graph, they will write code that performs abysmally. Graph navigation from row to row is antithetical to all the power relational algebra gives you. That's the biggest risk: viewing the DB primarily as an object store. For the app I work on, the DB is only viewed as an object store for very small sets of data - configuration, specifically. It's impossible to map it to objects in any realistically performant way for our user's data, not least because that would require the data round-tripping through the application. It's much, much more efficient to execute my multi-million row updates and inserts on the database server directly rather than drag the data down to the app, tweak it slightly, then persist it back.
- ak39 10y ago
  This is true. ORMs force the thinking "objects first" (ie. work with objects, the SQL will be handled for you). This translates into a design of "per object" architecture. Business logic is abstracted for handling single rows only - one at a time - encapsulation being the design idiom. However, this is a trap that immediately becomes apparent when you realise that you've forgone the powerful set-based capabilities of SQL. SQL doesn't "need to see" a single row of a table as anything special to warrant ring-fencing in the name of encapsulation. The whole (related) data model is a single algebraic representation of state.
- chris_wot 10y ago
  I've never quite worked out why graphs and relations can't be unified in some way. They both rely on first order logic, after all.
  - DougBTX 10y ago
    The select N+1 problem is a problem because of the overhead on each select, if that was free the graph navigation and relational set operations would match. Reminds me of, "in theory, there's no difference between theory and practice, in practice there is".
- icebraining 10y ago
  On the other hand, isn't that just a sign of a weak ORM? For example, in the platform I use, when I "fetch" some records by ID, it doesn't actually issue a SELECT; it just collects the ID and returns a proxy object. Only when I access an attribute of that object, it'll issue a SELECT for all the IDs in question. One could extend that for more complex operations - instead of immediately executing the operations, it'd collect the operations and convert them into efficient queries. In a way, this is already done in "fluent interfaces" like LINQ, but with a more flexible language (Lisp?), I'm sure one could use standard control flow instead of a custom layer.
  - fdgdasfadsf 10y ago
    This is what the Django ORM does when used with some degree of care.
    - icebraining 10y ago
      I know it uses lazy results, but can you actually loop over the results without triggering a query? Could you write something like: records = ORM.search(type='X') for record in records: record.a = record.b + 30 and only trigger a single UPDATE query?
      - collyw 10y ago
        There is an update method that can be used with F expressions to achieve this. This is the example in their docs: Entry.objects.all().update(n_pingbacks=F('n_pingbacks') + 1)
      - combatentropy 10y ago
        To write it in pure SQL would ensure the single query you're looking for and be less to type: update records set a = b + 30 where type = 'X'
  - GrinningFool 10y ago
    > For example, in the platform I use, when I "fetch" some records by ID, it doesn't actually issue a SELECT; it just collects the ID and returns a proxy object. Only when I access an attribute of that object, it'll issue a SELECT for all the IDs in question. I'm my opinion this is the least desirable behavior. It abstracts the data layer away to the point where you don't think about what's happening under the hood when you type foo = Klazz.new("id"); foo.bar; foo.baz; Was that one, two or three separate queries ? Did a query run at all? Does it matter, after all you have an object to work on, right in your language of choice. Sure the ORM may be smart about it and do the right thing - I have no doubt that most decent ones day. But that misses the point - as the person using this interface you get divorced from the direct awareness that this data has an origin, and there is a cost to access it. After all, you're just instantiating an object. Patterns that use interfaces that make it so simple to do a thing that you don't need awareness that you're doing it tend to degrade quickly after the original engineers who did have that understanding leave a project.
sanderjd 10y ago
People tend to talk around each other when discussing "ORM"s because the term conflates a few different things. The most fundamental thing that is sometimes included as part of the "ORM" definitions is translating the data received from a query into objects the host programming environment understands, eg. a PostgreSQL varchar into a Ruby String. This is basically what you're talking about in (2). Everyone (I think?) agrees this is useful. Next up the chain of usefulness is providing a version of the query interface that uses concepts native to the host environment, like chainable methods. This is what you're talking about in (1). Some people debate the usefulness of this over SQL strings, but most will agree that it is compelling because it lets you build queries out of composable parts, and to separate the parts of the final SQL string that are part of the query syntax vs. part of the query data (ie. for protection against injection). Past this, there are a bunch of features like mapping to a domain model and validation and lifecycle management and on and on and on that are very debatable. If we would separate those first two things ("data translation" and "query composition" maybe?) from this other grab-bag of things ("ORM stuff"?) when talking about this, I think the discussions would be more enlightening.
combatentropy 10y ago
The way you're using an ORM is fine. Do you think, though, that ORMs encourage people to delay learning the highly optimized SQL that you acknowledge is sometimes needed? I myself have yet to embrace anything beyond what they call a database-abstraction layer, where instead of pg_query and mysql_query, you just have db_query, but you still have to write the SQL. So: db_query('select id, name from table where foo = :bar').values({ foo: 'bar' }); Is that so much worse than the more involved ORMs that let you write: db_select([ 'id', 'name' ]).from('table').where({ foo: 'bar' });
- int_19h 10y ago
  Compared to the example that you gave, it's not. Where the latter becomes more interesting is when the identifiers are no longer string literals, but actual identifiers (i.e. once SQL records are mapped to some kind of tuple, structure or object type in the language). Then you get things such as static type verification, code completion, refactoring etc. Note that this doesn't require a full fledged ORM. True object-relational mapping requires dealing with things such as object identity.
- greenleafjacob 10y ago
  I think SQLAlchemy is the only ORM I've used that has support for most/all of: - Subqueries - HAVING clause - USE index hint - WHERE ... IN - SELECT DISTINCT - ON DUPLICATE KEY UPDATE - SELECT ... FOR UPDATE The trouble comes when you're using an ORM that has some but not all of the features, then the abstraction falls apart and you need to go down to using SQL anyway.
int_19h 10y ago
A lot of the time, when people speak of "ORM", what they actually mean is a "strongly typed DSL for SQL, using DTOs". They are not actually the same thing. What you describe seems to be more of the latter.
- Roboprog 10y ago
  Thus my fear and loathing of "ORM". Service gets rows from table - essentially a set of name-value pairs or N sets of similar name-value pairs. Service writes data to consumer as JSON - also name-value pairs. We could just (usually) run a prepared statement that safely inserts any search criteria into the query, then convert the DB name-values (possibly with column aliases) directly to the JSON. In some cases, we might need to validate, or run server side business logic on some parts of a tuple, so, under duress, we could write a wrapper class that manages the name-values of a tuple, which are actually used - and not just shoveled over the fence, for business logic. Or, we could define a layer of junk throw-away classes, or hell, maybe even 2 layers of junk throw-away classes, and maybe some other classes specific to each query or CRUD access type. Then we can shovel stuff into and back out of them, and immediately throw them away. Lazy loading apparently doesn't apply to all the gratuitous value mapping and copying that is always done, even if not looked at, etc. The length of the code bloat, and the depth of hiding the database, in this style of code is appalling. (Not that you shouldn't have an access layer, but you can keep your HQL and rubbish like that, thank you very much) IDE users like languages and tools that like IDEs :-( Contrariwise, I have worked (sometimes) with languages since the mid 80s that don't insist on every identifier binding specified at compile time (vs mandatory early-as-possible - compile time - binding, so the IDE can autocomplete and rename, er, "refactor" everything for baby, regardless of how much repetitive drivel has to be shoveled into the project) THAT'S what I think about, when somebody says "ORM" :-( (sorry, I guess it's been a long week)

barrkel 10y ago

If someone's primary perspective of the database is through the lens of an OO graph, they will write code that performs abysmally. Graph navigation from row to row is antithetical to all the power relational algebra gives you. That's the biggest risk: viewing the DB primarily as an object store. For the app I work on, the DB is only viewed as an object store for very small sets of data - configuration, specifically. It's impossible to map it to objects in any realistically performant way for our user's data, not least because that would require the data round-tripping through the application. It's much, much more efficient to execute my multi-million row updates and inserts on the database server directly rather than drag the data down to the app, tweak it slightly, then persist it back.

ak39 10y ago
This is true. ORMs force the thinking "objects first" (ie. work with objects, the SQL will be handled for you). This translates into a design of "per object" architecture. Business logic is abstracted for handling single rows only - one at a time - encapsulation being the design idiom. However, this is a trap that immediately becomes apparent when you realise that you've forgone the powerful set-based capabilities of SQL. SQL doesn't "need to see" a single row of a table as anything special to warrant ring-fencing in the name of encapsulation. The whole (related) data model is a single algebraic representation of state.
chris_wot 10y ago
I've never quite worked out why graphs and relations can't be unified in some way. They both rely on first order logic, after all.
- DougBTX 10y ago
  The select N+1 problem is a problem because of the overhead on each select, if that was free the graph navigation and relational set operations would match. Reminds me of, "in theory, there's no difference between theory and practice, in practice there is".
icebraining 10y ago
On the other hand, isn't that just a sign of a weak ORM? For example, in the platform I use, when I "fetch" some records by ID, it doesn't actually issue a SELECT; it just collects the ID and returns a proxy object. Only when I access an attribute of that object, it'll issue a SELECT for all the IDs in question. One could extend that for more complex operations - instead of immediately executing the operations, it'd collect the operations and convert them into efficient queries. In a way, this is already done in "fluent interfaces" like LINQ, but with a more flexible language (Lisp?), I'm sure one could use standard control flow instead of a custom layer.
- fdgdasfadsf 10y ago
  This is what the Django ORM does when used with some degree of care.
  - icebraining 10y ago
    I know it uses lazy results, but can you actually loop over the results without triggering a query? Could you write something like: records = ORM.search(type='X') for record in records: record.a = record.b + 30 and only trigger a single UPDATE query?
    - collyw 10y ago
      There is an update method that can be used with F expressions to achieve this. This is the example in their docs: Entry.objects.all().update(n_pingbacks=F('n_pingbacks') + 1)
    - combatentropy 10y ago
      To write it in pure SQL would ensure the single query you're looking for and be less to type: update records set a = b + 30 where type = 'X'
- GrinningFool 10y ago
  > For example, in the platform I use, when I "fetch" some records by ID, it doesn't actually issue a SELECT; it just collects the ID and returns a proxy object. Only when I access an attribute of that object, it'll issue a SELECT for all the IDs in question. I'm my opinion this is the least desirable behavior. It abstracts the data layer away to the point where you don't think about what's happening under the hood when you type foo = Klazz.new("id"); foo.bar; foo.baz; Was that one, two or three separate queries ? Did a query run at all? Does it matter, after all you have an object to work on, right in your language of choice. Sure the ORM may be smart about it and do the right thing - I have no doubt that most decent ones day. But that misses the point - as the person using this interface you get divorced from the direct awareness that this data has an origin, and there is a cost to access it. After all, you're just instantiating an object. Patterns that use interfaces that make it so simple to do a thing that you don't need awareness that you're doing it tend to degrade quickly after the original engineers who did have that understanding leave a project.

sanderjd 10y ago

People tend to talk around each other when discussing "ORM"s because the term conflates a few different things. The most fundamental thing that is sometimes included as part of the "ORM" definitions is translating the data received from a query into objects the host programming environment understands, eg. a PostgreSQL varchar into a Ruby String. This is basically what you're talking about in (2). Everyone (I think?) agrees this is useful. Next up the chain of usefulness is providing a version of the query interface that uses concepts native to the host environment, like chainable methods. This is what you're talking about in (1). Some people debate the usefulness of this over SQL strings, but most will agree that it is compelling because it lets you build queries out of composable parts, and to separate the parts of the final SQL string that are part of the query syntax vs. part of the query data (ie. for protection against injection). Past this, there are a bunch of features like mapping to a domain model and validation and lifecycle management and on and on and on that are very debatable. If we would separate those first two things ("data translation" and "query composition" maybe?) from this other grab-bag of things ("ORM stuff"?) when talking about this, I think the discussions would be more enlightening.

combatentropy 10y ago

The way you're using an ORM is fine. Do you think, though, that ORMs encourage people to delay learning the highly optimized SQL that you acknowledge is sometimes needed? I myself have yet to embrace anything beyond what they call a database-abstraction layer, where instead of pg_query and mysql_query, you just have db_query, but you still have to write the SQL. So: db_query('select id, name from table where foo = :bar').values({ foo: 'bar' }); Is that so much worse than the more involved ORMs that let you write: db_select([ 'id', 'name' ]).from('table').where({ foo: 'bar' });

int_19h 10y ago
Compared to the example that you gave, it's not. Where the latter becomes more interesting is when the identifiers are no longer string literals, but actual identifiers (i.e. once SQL records are mapped to some kind of tuple, structure or object type in the language). Then you get things such as static type verification, code completion, refactoring etc. Note that this doesn't require a full fledged ORM. True object-relational mapping requires dealing with things such as object identity.
greenleafjacob 10y ago
I think SQLAlchemy is the only ORM I've used that has support for most/all of: - Subqueries - HAVING clause - USE index hint - WHERE ... IN - SELECT DISTINCT - ON DUPLICATE KEY UPDATE - SELECT ... FOR UPDATE The trouble comes when you're using an ORM that has some but not all of the features, then the abstraction falls apart and you need to go down to using SQL anyway.

int_19h 10y ago

A lot of the time, when people speak of "ORM", what they actually mean is a "strongly typed DSL for SQL, using DTOs". They are not actually the same thing. What you describe seems to be more of the latter.

Roboprog 10y ago
Thus my fear and loathing of "ORM". Service gets rows from table - essentially a set of name-value pairs or N sets of similar name-value pairs. Service writes data to consumer as JSON - also name-value pairs. We could just (usually) run a prepared statement that safely inserts any search criteria into the query, then convert the DB name-values (possibly with column aliases) directly to the JSON. In some cases, we might need to validate, or run server side business logic on some parts of a tuple, so, under duress, we could write a wrapper class that manages the name-values of a tuple, which are actually used - and not just shoveled over the fence, for business logic. Or, we could define a layer of junk throw-away classes, or hell, maybe even 2 layers of junk throw-away classes, and maybe some other classes specific to each query or CRUD access type. Then we can shovel stuff into and back out of them, and immediately throw them away. Lazy loading apparently doesn't apply to all the gratuitous value mapping and copying that is always done, even if not looked at, etc. The length of the code bloat, and the depth of hiding the database, in this style of code is appalling. (Not that you shouldn't have an access layer, but you can keep your HQL and rubbish like that, thank you very much) IDE users like languages and tools that like IDEs :-( Contrariwise, I have worked (sometimes) with languages since the mid 80s that don't insist on every identifier binding specified at compile time (vs mandatory early-as-possible - compile time - binding, so the IDE can autocomplete and rename, er, "refactor" everything for baby, regardless of how much repetitive drivel has to be shoveled into the project) THAT'S what I think about, when somebody says "ORM" :-( (sorry, I guess it's been a long week)