7 ms·

Well the problem is, if you don't use an ORM, you'll invent one yourself...only poorly. And new hires will end up taking a ton of time to learn this "custom" or

by esaym 9y ago

Well the problem is, if you don't use an ORM, you'll invent one yourself...only poorly. And new hires will end up taking a ton of time to learn this "custom" orm framework of yours. And there are many bad, or simply, "too simple" ORMs out there that really don't help you much. I haven't really found one better than Perl's DBIx::Class (though outside of Java, Python, Ruby, I have haven't really looked). Case in point, I recently found this little gem[0] allowing easy correlated subqueries. I basically took a webpage that was loading in over 2 minutes, and reduced it to about 100ms and the outputted SQL was about 2 pages long (from about half a page of custom resultset orm code). I used several correlated subqueries to sort of pivot part of a table (well several tables actually). The original author of the code I was working on was fetching entire tables of data, with each row fetching (joining) to another entire table (it did this in several levels really) just to sum some values. This was all valid ORM code (even with handy 'if' statements checking every row id to do in ORM join (yes he didn't even use a dang where clause!)) but it showed a true lack of knowledge of the ORM at hand and even SQL in general. Nonetheless, I saved the day :) [0] https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-helper-resultset-correlaterelationship/ https://blog.afoolishmanifesto.com/posts/introducing-dbix-cl...

joncrocks 9y ago
ORMs try and solve a hard problem, and the devil is in the detail. https://martinfowler.com/bliki/OrmHate.html https://martinfowler.com/bliki/OrmHate.html
- marcosdumay 9y ago
  It surely is hard. But is it worth solving? The price you pay for a one-size-fits-all solution is that you lose the flexibility of arranging your queries the way you want. You'll automatically lose a lot of performance (that is not always important), but you also lose a kind of code clarity. If you don't get a one-size-fits-all solution, you will have to write the translations by yourself. That leads to more code, but in a better structure because you don't need to beat your code until it fits the ORM's model. All said, I do think an ORM is not something useful by itself, but may be part of a very useful solution. Python Django's CRUD generation and Haskell Persistent (not really ORM, but very similar) type checking are examples of that.
joshribakoff 9y ago
> if you don't use an ORM, you'll invent one yourself Not true. You may use a graph database, or write SQL statements & apply functional transformations. Or you might be using a platform like smalltalk & just save your image. There are other paradigms besides OOP & relational databases. > new hires will end up taking a ton of time to learn this "custom" orm framework of yours. Or your new hire might really know how to leverage the particular database you're using, but you force him to throw away a lot of his experience & to start over from scratch & instrument the database through an ORM layer. There are a lot of ORMs, their APIs may even differ, so someone who knows Propel still has to stop & read the docs if you're using Doctrine, for example. > it showed a true lack of knowledge of the ORM at hand and even SQL in general. Quite the opposite, anyone who can craft a query that complex obviously knows the tool well. Their real problem is knowing when to not use that tool. Or they just write bad code, regardless of what tool/langauge they're using, for whatever reason.
- hyperpape 9y ago
  I think the scope of the discussion is whether to use an ORM when using a database that supports SQL.
  - chiph 9y ago
    Or businesses that write their own database wrappers "because we might switch to Oracle one day and this will isolate the code from that change"
- mandeepj 9y ago
  > You may use a graph database There you go. I need 6 more months to deliver now.
deleted 9y ago
[deleted]
toast0 9y ago
You can absolutely build a product on top of an SQL database, by just writing the SQL queries you need. As a bonus, if you're thinking ahead, you can avoid writing queries that will perform poorly. If you're not thinking ahead, at least you can more easily fix the ones that do. I've never really understood why people want to interact with their database outside of its native query language. There's just so much mismatch between an ORM and the underlying interface, it feels like fighting all the time. And I don't feel like it's really saving any time either. The one thing that's sort of nice is it's easier to write a general filtering function, but SQL filtering lets you write so many things that won't perform well, making that easier isn't really helping anybody.
- ykler 9y ago
  I don't want a full-fledged ORM, and I sort of like SQL, but just normalizing nested objects to put them in the database and then unnormalizing them on the way out is really annoying to do in raw SQL in my experience. Every time you want to store a new object in the database, it feels like so much effort to write a bunch of boilerplate functions. Or do you have a solution to this?
  - toast0 9y ago
    I enjoy using SQL in an environment with first class hash/map/ whatevers; I don't like to build up an object. SELECT X, Y, Z from .... gets you an array of things with X, Y, and Z keys. Turing your hash into parameters for an insert isn't going to be great fun, although perl hash slices aren't bad. I think I've seen something about named SQL variables instead of just ?, that might be usable with a hash directly?
  - stuartaxelowen 9y ago
    If you're using python, you can use named tuples for everything. It's really nice! Check out the psycopg2 namedtuple connection.
    - ykler 9y ago
      I just glanced at this, and maybe I don't understand, but it sounds like psycopg2 namedtuples only work on one table at a time; is this right?
lkrubner 9y ago
I think you might be confusing 2 different ideas. An ORM is an Object Relational Mapper. Lots of companies build very big software without using an ORM. And the very concept of an ORM becomes meaningless if you use a language that rejects Object Oriented Programming. For instance, when using Clojure, some teams use Korma: http://sqlkorma.com/ http://sqlkorma.com/ Korma is a DSL. And I think you are confusing the concepts of ORM and DSL. I'd agree that most teams, over time, tend to develop (or borrow) their own DSLs for interacting with the database. And I've seen teams that use the term DSL loosely to refer to any code they write to interact with the database, so long as they are doing more than concatenating strings of SQL. But they don't necessarily use ORMs.
mehorms 9y ago
While I agree with this, the quality of ORMs vastly differs. For instance, the ORM style that wraps sql in a dsl is strictly better than one that encourages model lifecycle callback hell, as activerecord does. The latter is never necessary to work with a database and is a terrible pattern long-term for composing database interactions: it is extremely difficult to reason about what will actually happen inside the transaction (or immediately after). Tl;dr you aren’t wrong, but many home-rolled ORMs are simpler and much easier to use than, say, activerecord.
- kpil 9y ago
  I think that QueryDSL on top of JPA on Scala is just about as good as it gets. It is also complex and requires that you really master all the ingredients... If SQL is a mystery to you, it will just be a painful experience.
EpicEng 9y ago
>And new hires will end up taking a ton of time to learn this "custom" orm framework of yours Or your new hire actually learns how to use the tech that makes their code work. I could use fewer devs who have no idea how the code they write actually works.
noncoml 9y ago
> Well the problem is, if you don't use an ORM, you'll invent one yourself...only poorly There is always a trade-off. The code you wrote, even if it is poorly re-implentation, you know it inside out, since you wrote it. The third party code, may be better, but people usually make a lot of implicit assumptions and end up not using it correctly, so you end up in situations like this. For DB layer, I usually prefer custom code so I know exactly what is going on under the hood.
- jaggederest 9y ago
  The best of both worlds is just to... actually understand the ORM you choose to use. I'm always confused by how reluctant folks are, in general, to read underlying libraries.
  - Can_Not 9y ago
    You don't even have to read the library source, just verify the SQL statements generated. Most ORMs let you drop into raw SQL for advanced clauses and sub-statements. For me there value of an ORM is composable business logic. For example, if whereHasLotsOfTrees is a complicated SQL subquery and has it's business logic definition changed, I can edit only one function and have the new logic work everywhere.
dkersten 9y ago
> if you don't use an ORM, you'll invent one yourself...only poorly. My favourite SQL library is Clojure's HugSQL, which avoids this issue by letting you write SQL in .sql files and then generating functions for you that execute these queries. https://www.hugsql.org/ https://www.hugsql.org/

by esaym 9y ago

joncrocks 9y ago

ORMs try and solve a hard problem, and the devil is in the detail. https://martinfowler.com/bliki/OrmHate.html https://martinfowler.com/bliki/OrmHate.html

marcosdumay 9y ago
It surely is hard. But is it worth solving? The price you pay for a one-size-fits-all solution is that you lose the flexibility of arranging your queries the way you want. You'll automatically lose a lot of performance (that is not always important), but you also lose a kind of code clarity. If you don't get a one-size-fits-all solution, you will have to write the translations by yourself. That leads to more code, but in a better structure because you don't need to beat your code until it fits the ORM's model. All said, I do think an ORM is not something useful by itself, but may be part of a very useful solution. Python Django's CRUD generation and Haskell Persistent (not really ORM, but very similar) type checking are examples of that.

joshribakoff 9y ago

> if you don't use an ORM, you'll invent one yourself Not true. You may use a graph database, or write SQL statements & apply functional transformations. Or you might be using a platform like smalltalk & just save your image. There are other paradigms besides OOP & relational databases. > new hires will end up taking a ton of time to learn this "custom" orm framework of yours. Or your new hire might really know how to leverage the particular database you're using, but you force him to throw away a lot of his experience & to start over from scratch & instrument the database through an ORM layer. There are a lot of ORMs, their APIs may even differ, so someone who knows Propel still has to stop & read the docs if you're using Doctrine, for example. > it showed a true lack of knowledge of the ORM at hand and even SQL in general. Quite the opposite, anyone who can craft a query that complex obviously knows the tool well. Their real problem is knowing when to not use that tool. Or they just write bad code, regardless of what tool/langauge they're using, for whatever reason.

hyperpape 9y ago
I think the scope of the discussion is whether to use an ORM when using a database that supports SQL.
- chiph 9y ago
  Or businesses that write their own database wrappers "because we might switch to Oracle one day and this will isolate the code from that change"
mandeepj 9y ago
> You may use a graph database There you go. I need 6 more months to deliver now.

deleted 9y ago

[deleted]

toast0 9y ago

You can absolutely build a product on top of an SQL database, by just writing the SQL queries you need. As a bonus, if you're thinking ahead, you can avoid writing queries that will perform poorly. If you're not thinking ahead, at least you can more easily fix the ones that do. I've never really understood why people want to interact with their database outside of its native query language. There's just so much mismatch between an ORM and the underlying interface, it feels like fighting all the time. And I don't feel like it's really saving any time either. The one thing that's sort of nice is it's easier to write a general filtering function, but SQL filtering lets you write so many things that won't perform well, making that easier isn't really helping anybody.

ykler 9y ago
I don't want a full-fledged ORM, and I sort of like SQL, but just normalizing nested objects to put them in the database and then unnormalizing them on the way out is really annoying to do in raw SQL in my experience. Every time you want to store a new object in the database, it feels like so much effort to write a bunch of boilerplate functions. Or do you have a solution to this?
- toast0 9y ago
  I enjoy using SQL in an environment with first class hash/map/ whatevers; I don't like to build up an object. SELECT X, Y, Z from .... gets you an array of things with X, Y, and Z keys. Turing your hash into parameters for an insert isn't going to be great fun, although perl hash slices aren't bad. I think I've seen something about named SQL variables instead of just ?, that might be usable with a hash directly?
- stuartaxelowen 9y ago
  If you're using python, you can use named tuples for everything. It's really nice! Check out the psycopg2 namedtuple connection.
  - ykler 9y ago
    I just glanced at this, and maybe I don't understand, but it sounds like psycopg2 namedtuples only work on one table at a time; is this right?

lkrubner 9y ago

I think you might be confusing 2 different ideas. An ORM is an Object Relational Mapper. Lots of companies build very big software without using an ORM. And the very concept of an ORM becomes meaningless if you use a language that rejects Object Oriented Programming. For instance, when using Clojure, some teams use Korma: http://sqlkorma.com/ http://sqlkorma.com/ Korma is a DSL. And I think you are confusing the concepts of ORM and DSL. I'd agree that most teams, over time, tend to develop (or borrow) their own DSLs for interacting with the database. And I've seen teams that use the term DSL loosely to refer to any code they write to interact with the database, so long as they are doing more than concatenating strings of SQL. But they don't necessarily use ORMs.

mehorms 9y ago

While I agree with this, the quality of ORMs vastly differs. For instance, the ORM style that wraps sql in a dsl is strictly better than one that encourages model lifecycle callback hell, as activerecord does. The latter is never necessary to work with a database and is a terrible pattern long-term for composing database interactions: it is extremely difficult to reason about what will actually happen inside the transaction (or immediately after). Tl;dr you aren’t wrong, but many home-rolled ORMs are simpler and much easier to use than, say, activerecord.

kpil 9y ago
I think that QueryDSL on top of JPA on Scala is just about as good as it gets. It is also complex and requires that you really master all the ingredients... If SQL is a mystery to you, it will just be a painful experience.

EpicEng 9y ago

>And new hires will end up taking a ton of time to learn this "custom" orm framework of yours Or your new hire actually learns how to use the tech that makes their code work. I could use fewer devs who have no idea how the code they write actually works.

noncoml 9y ago

> Well the problem is, if you don't use an ORM, you'll invent one yourself...only poorly There is always a trade-off. The code you wrote, even if it is poorly re-implentation, you know it inside out, since you wrote it. The third party code, may be better, but people usually make a lot of implicit assumptions and end up not using it correctly, so you end up in situations like this. For DB layer, I usually prefer custom code so I know exactly what is going on under the hood.

jaggederest 9y ago
The best of both worlds is just to... actually understand the ORM you choose to use. I'm always confused by how reluctant folks are, in general, to read underlying libraries.
- Can_Not 9y ago
  You don't even have to read the library source, just verify the SQL statements generated. Most ORMs let you drop into raw SQL for advanced clauses and sub-statements. For me there value of an ORM is composable business logic. For example, if whereHasLotsOfTrees is a complicated SQL subquery and has it's business logic definition changed, I can edit only one function and have the new logic work everywhere.

dkersten 9y ago

> if you don't use an ORM, you'll invent one yourself...only poorly. My favourite SQL library is Clojure's HugSQL, which avoids this issue by letting you write SQL in .sql files and then generating functions for you that execute these queries. https://www.hugsql.org/ https://www.hugsql.org/