6 ms·

Raw SQL is always better, IMHO. There are no new abstractions (for example extra session/trransaction-like behaviour). You can write good efficient SQL and no

Raw SQL is always better, IMHO. There are no new abstractions (for example extra session/trransaction-like behaviour). You can write good efficient SQL and not use an iteration-protocol. ORMs tend to make slow and clumsy SQL. It might not matter if you only update one record, but if you need to fix something in 1 billion records, you are going to feel the pain.. If you need to learn real sql (and not clumsy beginner-sql), look at use-the-index-luke.com and modern-sql.com (both from the same, fantastic knowlegdeable guy) and read Joe Celkos "smarties" books... If needed, a small library can help write CRUD style SQL. I have implemented them myself multiple times in an afternoon. Only tricky thing.. Handling of strings so that you have no SQL-injection.. (for that, serverside bind-parameters are a god-given).

pschlump 9y ago
I have had a number of contracts where my entire job was to fix the performance problems introduced by using a ORM. ORMs add a layer of complexity to your code and a huge risk of really bad SQL.
- giancarlostoro 9y ago
  ORMs can be used poorly sure, but anything I can design using raw SQL I can typically do with an ORM. The question is, are people using ORMs as a way to avoid having to figure out proper database design? If so, they should not be using an ORM and should really learn how the database they will be using works. Heck! There's micro ORMs like Dapper which let you run raw SQL and match it to an actual object for you, to be fair almost all ORM's support doing raw SQL and getting back an object which can be handy for performance bottlenecks. Badly coded software will usually have performance issues, whether using raw SQL or not. Update: Fixed typos.
- dagw 9y ago
  Do you think the performance would have been better if the same programmers who introduced them using the ORM had been forced to use raw SQL? I've seen far too many cases where programmers get around their lack of SQL knowledge by writing a bunch of basic queries and then post-processing the results client side.
acutesoftware 9y ago
Yes, if you already know SQL and by using bind-parameters, then I [personally] can't see a tangible benefit for using an ORM. They can certainly make for cleaner looking code, and are useful for people who don't really understand the database schema, but not worth the added complexity.
- timrichard 9y ago
  Just to mention another alternative, I've been using a QueryBuilder library on the last two projects. The code's prettier than it would be with raw SQL, plus there aren't ORM downsides.
  - pserwylo 9y ago
    Good point. We had great success doing this with our PHP code base. For us it wasn't about pretty or not-pretty code, but rather about type safety. The idea that you can have a method which accepts "WhereClause $constraints" instead of "string $constraints" means you can be much more confident that the code is correct. We actually wrote a custom query builder because none of the available ones (e.g. Doctrine) had the type safety that we desired. A direct parallel to what we used is the Java library JOOQ, in terms of its querybuilder API [0]. [0] https://www.jooq.org/doc/3.10/manual/sql-building/sql-statements/dsl-and-non-dsl/ https://www.jooq.org/doc/3.10/manual/sql-building/sql-statem...
  - Allstar 9y ago
    Same here, we use Knex.js Querybuilder to build queries and fallback to `knex.raw()` for very specific SQL queries.
    - timrichard 9y ago
      Ah, coincidence... Knex is the one I've been using too :-)

pschlump 9y ago

I have had a number of contracts where my entire job was to fix the performance problems introduced by using a ORM. ORMs add a layer of complexity to your code and a huge risk of really bad SQL.

giancarlostoro 9y ago
ORMs can be used poorly sure, but anything I can design using raw SQL I can typically do with an ORM. The question is, are people using ORMs as a way to avoid having to figure out proper database design? If so, they should not be using an ORM and should really learn how the database they will be using works. Heck! There's micro ORMs like Dapper which let you run raw SQL and match it to an actual object for you, to be fair almost all ORM's support doing raw SQL and getting back an object which can be handy for performance bottlenecks. Badly coded software will usually have performance issues, whether using raw SQL or not. Update: Fixed typos.
dagw 9y ago
Do you think the performance would have been better if the same programmers who introduced them using the ORM had been forced to use raw SQL? I've seen far too many cases where programmers get around their lack of SQL knowledge by writing a bunch of basic queries and then post-processing the results client side.

acutesoftware 9y ago

Yes, if you already know SQL and by using bind-parameters, then I [personally] can't see a tangible benefit for using an ORM. They can certainly make for cleaner looking code, and are useful for people who don't really understand the database schema, but not worth the added complexity.

timrichard 9y ago
Just to mention another alternative, I've been using a QueryBuilder library on the last two projects. The code's prettier than it would be with raw SQL, plus there aren't ORM downsides.
- pserwylo 9y ago
  Good point. We had great success doing this with our PHP code base. For us it wasn't about pretty or not-pretty code, but rather about type safety. The idea that you can have a method which accepts "WhereClause $constraints" instead of "string $constraints" means you can be much more confident that the code is correct. We actually wrote a custom query builder because none of the available ones (e.g. Doctrine) had the type safety that we desired. A direct parallel to what we used is the Java library JOOQ, in terms of its querybuilder API [0]. [0] https://www.jooq.org/doc/3.10/manual/sql-building/sql-statements/dsl-and-non-dsl/ https://www.jooq.org/doc/3.10/manual/sql-building/sql-statem...
- Allstar 9y ago
  Same here, we use Knex.js Querybuilder to build queries and fallback to `knex.raw()` for very specific SQL queries.
  - timrichard 9y ago
    Ah, coincidence... Knex is the one I've been using too :-)