4 ms·

When I'm in charge of application design I always use raw SQL. It's wasted investment learning an ORM because its not knowledge that you can carry from project

When I'm in charge of application design I always use raw SQL. It's wasted investment learning an ORM because its not knowledge that you can carry from project to project and throughout your career. Any time spent problem solving or learning the weird intricacies of ANY ORM is time thrown in the garbage bin. The largest community, the widest range of problem solving minds are available to help with your database issues - if you're using raw SQL. Program the platform - use SQL directly as much as you possibly can.

gmac 4y ago
I broadly agree, although: ORMs have problems, but some are good enough and widely used enough that you might be able to take them from project to project (e.g. Rails’ ActiveRecord). SQL is good, but it can get tedious to intermingle fully ‘raw’ SQL with other languages. There are some good non-ORM database libraries whose features go beyond this (I develop one called Zapatos for Postgres/TypeScript).
listenallyall 4y ago
Obviously you should understand SQL sufficiently that you could write your query in raw SQL if necessary. For practical purposes, however, plain SQL in a software program leaves you wide open to injection, to text substitution errors, you're responsible for data type verification/formatting, and manually converting raw result sets into objects, and more. Now you have to solve these issues yourself that the ORM should handle. Some ORMs are worth it, some aren't, but raw SQL is often a poor fit within a programming project.
- est31 4y ago
  > plain SQL in a software program leaves you wide open to injection, to text substitution errors The non-ORM SQL libraries / APIs that I know offer APIs where you provide an SQL query template, where you e.g. list your arguments as \1 \2 etc, and then you also specify the values of those arguments. The API then fills in the arguments in a injection safe manner. I don't think anyone is arguing for raw string manipulation here.
  - listenallyall 4y ago
    I think it is exactly what the top poster is arguing for. He didn't say he used an API, he specifically said raw SQL. A non-ORM SQL library is just as non-portable as an ORM, his primary complaint. And each will have at least a few of their own weird intricacies, his secondary complaint (if they didn't, there wouldn't be more than one per language).
  - arkh 4y ago
    The SQL drivers offer it. Send your parameterized query to the DB engine then send the parameters. For example PostgreSQL offers the functions PQexecParams and PQprepare. https://www.postgresql.org/docs/14/libpq-exec.html https://www.postgresql.org/docs/14/libpq-exec.html > The primary advantage of PQexecParams over PQexec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping.
    - jsmith45 4y ago
      Parameterizied queries do eliminate injection in the cases that they support, but it is not uncommon to need to so some level of query building manually, since not everything you might want to do is supported by parameterized queries. For example one cannot usually do "... order by ? ?", passing in a column name and ascending/descending. And this is commonly needed for say paginated tables where the user can choose which column to sort by. Similarly, I've worked with some advanced seaarch screens that would need to dynamically add additional clauses to "WHERE", and in the end, that will come down to some form of string concatenation, even if parameters get represented as "?" and passed separately. But unfortunately doing that with string concatenation leaves more opportunity for injection errors to slip in. Its actually rather unfortunate pretty much no SQL database allows passing a query as an already parsed AST. It would be really neat if a query builder pattern in software could instead of building up a string could construct a binary representation of the AST, such that no injection was possible, and simply hand it off to the engine. The AST would dictate that this value is a column name, and because we are already an AST, there is no need to escape this value, since the engine won't need to reparse it. Or this value is a string, but once again, no reparsing from string needed, so no escaping needed. (Serialization format could have a length prefix or whatever).
- Shorel 4y ago
  This is very misleading. Every single place* you can write raw SQL, you can do it with a prepared statement, and then you don't have injection and the other nasty stuff. *At least for all the big players I have used.
est31 4y ago
> It's wasted investment learning an ORM because its not knowledge that you can carry from project to project and throughout your career. OTOH, each database implements their own set of SQL features, so has their own SQL dialect. This is visible on stack overflow where the database name is a big component of the question / answer. I still agree with you though that SQL is superior to having a separation layer between the database. SQL is already supposed to be the human readable interface to a library.
silisili 4y ago
Agreed. That said, I know SQL well enough so was open to an ORM the company was using. My problem with ORMs, at least the ones I've used, is that they seem to be kid gloves for people afraid of SQL rather than some time saving or optimizing option. Often the code was longer, harder to read and reason about, and less performant when using an ORM vs inline SQL.
- edgyquant 4y ago
  I’m having a hell of a time getting our juniors to wrap their head around even an ORM as when I got here our app used graphql which heavily incentivized using queries as the data model
hebrox 4y ago
ORMs: "Making easy things easy, and hard things harder" I do like query builders though. But I think you need to understand and have easy access to the SQL that is being generated.
- remram 4y ago
  I agree, query builders make it much easier to re-use code (e.g. putting the same CTE or expression in multiple queries without assembling strings by hand) and support multiple database backends (e.g. placeholder syntax differs). I really recommend them. ORM vs raw SQL is a false dichotomy.
nightski 4y ago
That works for a narrow set of applications. If you do any kind of dynamic SQL it can get pretty gnarly though. I think it's extremely important to learn SQL first. But I don't think it hurts to learn an ORM or two along the way. Then it's important to learn how to optimize performance. Just getting results with SQL is easy. Thinking about how to do so quickly can be more difficult. Lastly ORMs deal with the issues an application typically has to deal with anyways. Such as mapping to objects, detecting changes, caching, concurrency, etc... So by learning an ORM it might give insight into ways to handle these things even if you don't need a full blown ORM.
- BenoitP 4y ago
  > If you do any kind of dynamic SQL it can get pretty gnarly though This can be addressed with typesafe SQL-builders like jooq [1]. I got an OLAP application with plenty of gnarly SQL broken up in individual business rules. The best is that I don't even touch the SQL much. I expose it read-only to the users, hidden under an advanced mode button; and when they want to change things it often comes already written in SQL (mostly filters and projections). New queries come in requests to mix already-existing parts. And it helps technical knowledge be shared. Win-win all around. Jooq works well with CRUD/OLTP apps as well. And when you have a problem, you don't have to debug both the ORM and SQL. > Then it's important to learn how to optimize performance. Starting with IO at the db level, and execution plan in the db. And ending right there without an ORM. Neither you nor your ORM are going to be smarter than the planner. For starters do you even collect statistics about your data? And your ORM doesn't even know what the downstream processing will be. > Lastly ORMs deal with the issues an application typically has to deal with anyways. Such as mapping to objects, Hopefully included with the typesafe SQL builder, but not always needed. > detecting changes, Plenty of SQL features for an audit trail > caching, Well maybe there. But if read-availability is your problem, reading from replicas gets you very far. > concurrency Comes out of the box with MVCC RDBMS; ie good'ol postgres, mysql ---- I'm not going back to ORMs /rant [1] https://www.jooq.org/ https://www.jooq.org/
  - nightski 4y ago
    I only meant performance in context of the SQL execution, not the ORM. Agreed that is by far the most important. If one does use a query builder/ORM though it is also important to understand how that query builder/orm dsl maps to actual sql (even if it is a straightforward transformation). I love query builders! Especially type safe ones. But if you have a complex domain doing updates can get messy. You are going to write a lot of code mapping domain objects to SQL updates. It's not insurmountable, but you are basically re-inventing an ORM. Maybe find a lightweight ORM for this purpose (The command side of CQRS). But yeah if you don't need one that's great! I wasn't trying to convince anyone to use one, just that they can be worthwhile to learn. Personally I like using either direct SQL or query builder style interface for reads/queries. Then I like using an ORM with domain objects for commands/updates. But I deal with a lot of complex domains in finance, supply chain optimization, production planning, etc...
gspetr 4y ago
ORM is not ideal for every project. If we have to grind through simple calculations on millions of records, with no user experience to worry about, we gain little from expressing the data as objects for a few microseconds; the cost of transformation from raw bytes to business objects is never repaid. We're better off with the fastest, native data manipulation techniques available. An object-oriented approach is better suited to highly interactive, line-of-business applications. Data records are not nearly so numerous in such applications. They live in client space for minutes or hours, not microseconds. During their unpredictable stay, they appear in various guises on multiple screens. Users change them. They're roiled in complex business logic. The consensus of architects is that such data records are best represented as business objects. The open question is how best to shuttle data between database and business object form. ORM is one such answer. Not the answer.
heurisko 4y ago
I usually use an ORM to save data, then raw SQL to query data. The ORMs I have used, hibernate and doctrine, allow you to use raw SQL, then they do the object mapping for you. The raw SQL vs ORM is a false dichotomy.
RantyDave 4y ago
I've been using SQLAlchemy and the raw SQL/ORM dilemma is really not a thing. Alchemy does nice type checking, coercing, packing results into a dict etc. without the headache of an ORM. I'm sure there's a price to pay in terms of performance but for what I do (still in the thousands of rows per second) it really doesn't matter.
justsomeuser 4y ago
I have a similar opinion. I find that modelling SQL tables first removes the “where in the JSON-like tree should this live” question - often a single object type lives in many places in the tree. Breaking data into atomic units and then building up any JSON-like structures you require with queries is very flexible, and solves persistence first. ORMs seem to solve persistence second after modelling your data as JSON-like in-language objects. Persistence needs to be solved as disk is cheaper than RAM and your program cannot live forever with data in RAM.
SPBS 4y ago
Absolutely. People who say they want to just manipulate objects and let the ORM take care of persistence have no idea how much of a losing battle they are fighting. Sure, let me learn all about the intricacies of the Criteria API instead of just learning SQL's WHERE clause or whatever.
hnfong 4y ago
20 years ago when I was writing PHP, ORMs were not popular (if they even existed). After writing a lot of raw SQL queries, I ended up creating some kind of ORM API for my own use. I can't imagine anyone preferring to use raw SQL and hand code the mapping from SQL field results to objects. The argument that it's not transferrable knowledge isn't even ORM-specific. You can make the same argument for any hotshot $framework or even $language that you think might fade away in a couple years. There's always a trade off between adopting unproven frameworks and reinventing your own wheel (or coding at a lower abstraction level with all its drawbacks). Even proven tech can go out of fashion, as was during the "NoSQL" craze a few years ago. It's just a matter of making good predictions with industry knowledge. There's no hard law of nature that SQL is going to survive another 10 years (though admittedly that's quite likely). The number of people agreeing with your blanket, inflexible statement makes me wonder whether I'm in the wrong forum...
- andrewstuart 4y ago
  Most databases allow you to return the result as JSON. Just do that.
rvdginste 4y ago
I am surprised by all the anti-ORM comments. When you use an ORM, you need to both know SQL and the ORM. Your investment in learning an ORM is not wasted and you can carry that knowledge over from project to project. If you know both the ORM and SQL well, you can save a lot of time during development. I've been using NHibernate for over 10 years and now also getting into Entity Framework Core. I did see bad usage of ORMs and this was always caused by either a lack of SQL and database knowledge, or a lack of knowledge on how ORMs work.

gmac 4y ago

I broadly agree, although: ORMs have problems, but some are good enough and widely used enough that you might be able to take them from project to project (e.g. Rails’ ActiveRecord). SQL is good, but it can get tedious to intermingle fully ‘raw’ SQL with other languages. There are some good non-ORM database libraries whose features go beyond this (I develop one called Zapatos for Postgres/TypeScript).

listenallyall 4y ago

Obviously you should understand SQL sufficiently that you could write your query in raw SQL if necessary. For practical purposes, however, plain SQL in a software program leaves you wide open to injection, to text substitution errors, you're responsible for data type verification/formatting, and manually converting raw result sets into objects, and more. Now you have to solve these issues yourself that the ORM should handle. Some ORMs are worth it, some aren't, but raw SQL is often a poor fit within a programming project.

est31 4y ago
> plain SQL in a software program leaves you wide open to injection, to text substitution errors The non-ORM SQL libraries / APIs that I know offer APIs where you provide an SQL query template, where you e.g. list your arguments as \1 \2 etc, and then you also specify the values of those arguments. The API then fills in the arguments in a injection safe manner. I don't think anyone is arguing for raw string manipulation here.
- listenallyall 4y ago
  I think it is exactly what the top poster is arguing for. He didn't say he used an API, he specifically said raw SQL. A non-ORM SQL library is just as non-portable as an ORM, his primary complaint. And each will have at least a few of their own weird intricacies, his secondary complaint (if they didn't, there wouldn't be more than one per language).
- arkh 4y ago
  The SQL drivers offer it. Send your parameterized query to the DB engine then send the parameters. For example PostgreSQL offers the functions PQexecParams and PQprepare. https://www.postgresql.org/docs/14/libpq-exec.html https://www.postgresql.org/docs/14/libpq-exec.html > The primary advantage of PQexecParams over PQexec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping.
  - jsmith45 4y ago
    Parameterizied queries do eliminate injection in the cases that they support, but it is not uncommon to need to so some level of query building manually, since not everything you might want to do is supported by parameterized queries. For example one cannot usually do "... order by ? ?", passing in a column name and ascending/descending. And this is commonly needed for say paginated tables where the user can choose which column to sort by. Similarly, I've worked with some advanced seaarch screens that would need to dynamically add additional clauses to "WHERE", and in the end, that will come down to some form of string concatenation, even if parameters get represented as "?" and passed separately. But unfortunately doing that with string concatenation leaves more opportunity for injection errors to slip in. Its actually rather unfortunate pretty much no SQL database allows passing a query as an already parsed AST. It would be really neat if a query builder pattern in software could instead of building up a string could construct a binary representation of the AST, such that no injection was possible, and simply hand it off to the engine. The AST would dictate that this value is a column name, and because we are already an AST, there is no need to escape this value, since the engine won't need to reparse it. Or this value is a string, but once again, no reparsing from string needed, so no escaping needed. (Serialization format could have a length prefix or whatever).
Shorel 4y ago
This is very misleading. Every single place* you can write raw SQL, you can do it with a prepared statement, and then you don't have injection and the other nasty stuff. *At least for all the big players I have used.

est31 4y ago

> It's wasted investment learning an ORM because its not knowledge that you can carry from project to project and throughout your career. OTOH, each database implements their own set of SQL features, so has their own SQL dialect. This is visible on stack overflow where the database name is a big component of the question / answer. I still agree with you though that SQL is superior to having a separation layer between the database. SQL is already supposed to be the human readable interface to a library.

silisili 4y ago

Agreed. That said, I know SQL well enough so was open to an ORM the company was using. My problem with ORMs, at least the ones I've used, is that they seem to be kid gloves for people afraid of SQL rather than some time saving or optimizing option. Often the code was longer, harder to read and reason about, and less performant when using an ORM vs inline SQL.

edgyquant 4y ago
I’m having a hell of a time getting our juniors to wrap their head around even an ORM as when I got here our app used graphql which heavily incentivized using queries as the data model

hebrox 4y ago

ORMs: "Making easy things easy, and hard things harder" I do like query builders though. But I think you need to understand and have easy access to the SQL that is being generated.

remram 4y ago
I agree, query builders make it much easier to re-use code (e.g. putting the same CTE or expression in multiple queries without assembling strings by hand) and support multiple database backends (e.g. placeholder syntax differs). I really recommend them. ORM vs raw SQL is a false dichotomy.

nightski 4y ago

That works for a narrow set of applications. If you do any kind of dynamic SQL it can get pretty gnarly though. I think it's extremely important to learn SQL first. But I don't think it hurts to learn an ORM or two along the way. Then it's important to learn how to optimize performance. Just getting results with SQL is easy. Thinking about how to do so quickly can be more difficult. Lastly ORMs deal with the issues an application typically has to deal with anyways. Such as mapping to objects, detecting changes, caching, concurrency, etc... So by learning an ORM it might give insight into ways to handle these things even if you don't need a full blown ORM.

BenoitP 4y ago
> If you do any kind of dynamic SQL it can get pretty gnarly though This can be addressed with typesafe SQL-builders like jooq [1]. I got an OLAP application with plenty of gnarly SQL broken up in individual business rules. The best is that I don't even touch the SQL much. I expose it read-only to the users, hidden under an advanced mode button; and when they want to change things it often comes already written in SQL (mostly filters and projections). New queries come in requests to mix already-existing parts. And it helps technical knowledge be shared. Win-win all around. Jooq works well with CRUD/OLTP apps as well. And when you have a problem, you don't have to debug both the ORM and SQL. > Then it's important to learn how to optimize performance. Starting with IO at the db level, and execution plan in the db. And ending right there without an ORM. Neither you nor your ORM are going to be smarter than the planner. For starters do you even collect statistics about your data? And your ORM doesn't even know what the downstream processing will be. > Lastly ORMs deal with the issues an application typically has to deal with anyways. Such as mapping to objects, Hopefully included with the typesafe SQL builder, but not always needed. > detecting changes, Plenty of SQL features for an audit trail > caching, Well maybe there. But if read-availability is your problem, reading from replicas gets you very far. > concurrency Comes out of the box with MVCC RDBMS; ie good'ol postgres, mysql ---- I'm not going back to ORMs /rant [1] https://www.jooq.org/ https://www.jooq.org/
- nightski 4y ago
  I only meant performance in context of the SQL execution, not the ORM. Agreed that is by far the most important. If one does use a query builder/ORM though it is also important to understand how that query builder/orm dsl maps to actual sql (even if it is a straightforward transformation). I love query builders! Especially type safe ones. But if you have a complex domain doing updates can get messy. You are going to write a lot of code mapping domain objects to SQL updates. It's not insurmountable, but you are basically re-inventing an ORM. Maybe find a lightweight ORM for this purpose (The command side of CQRS). But yeah if you don't need one that's great! I wasn't trying to convince anyone to use one, just that they can be worthwhile to learn. Personally I like using either direct SQL or query builder style interface for reads/queries. Then I like using an ORM with domain objects for commands/updates. But I deal with a lot of complex domains in finance, supply chain optimization, production planning, etc...

gspetr 4y ago

ORM is not ideal for every project. If we have to grind through simple calculations on millions of records, with no user experience to worry about, we gain little from expressing the data as objects for a few microseconds; the cost of transformation from raw bytes to business objects is never repaid. We're better off with the fastest, native data manipulation techniques available. An object-oriented approach is better suited to highly interactive, line-of-business applications. Data records are not nearly so numerous in such applications. They live in client space for minutes or hours, not microseconds. During their unpredictable stay, they appear in various guises on multiple screens. Users change them. They're roiled in complex business logic. The consensus of architects is that such data records are best represented as business objects. The open question is how best to shuttle data between database and business object form. ORM is one such answer. Not the answer.

heurisko 4y ago

I usually use an ORM to save data, then raw SQL to query data. The ORMs I have used, hibernate and doctrine, allow you to use raw SQL, then they do the object mapping for you. The raw SQL vs ORM is a false dichotomy.

RantyDave 4y ago

I've been using SQLAlchemy and the raw SQL/ORM dilemma is really not a thing. Alchemy does nice type checking, coercing, packing results into a dict etc. without the headache of an ORM. I'm sure there's a price to pay in terms of performance but for what I do (still in the thousands of rows per second) it really doesn't matter.

justsomeuser 4y ago

I have a similar opinion. I find that modelling SQL tables first removes the “where in the JSON-like tree should this live” question - often a single object type lives in many places in the tree. Breaking data into atomic units and then building up any JSON-like structures you require with queries is very flexible, and solves persistence first. ORMs seem to solve persistence second after modelling your data as JSON-like in-language objects. Persistence needs to be solved as disk is cheaper than RAM and your program cannot live forever with data in RAM.

SPBS 4y ago

Absolutely. People who say they want to just manipulate objects and let the ORM take care of persistence have no idea how much of a losing battle they are fighting. Sure, let me learn all about the intricacies of the Criteria API instead of just learning SQL's WHERE clause or whatever.

hnfong 4y ago

20 years ago when I was writing PHP, ORMs were not popular (if they even existed). After writing a lot of raw SQL queries, I ended up creating some kind of ORM API for my own use. I can't imagine anyone preferring to use raw SQL and hand code the mapping from SQL field results to objects. The argument that it's not transferrable knowledge isn't even ORM-specific. You can make the same argument for any hotshot $framework or even $language that you think might fade away in a couple years. There's always a trade off between adopting unproven frameworks and reinventing your own wheel (or coding at a lower abstraction level with all its drawbacks). Even proven tech can go out of fashion, as was during the "NoSQL" craze a few years ago. It's just a matter of making good predictions with industry knowledge. There's no hard law of nature that SQL is going to survive another 10 years (though admittedly that's quite likely). The number of people agreeing with your blanket, inflexible statement makes me wonder whether I'm in the wrong forum...

andrewstuart 4y ago
Most databases allow you to return the result as JSON. Just do that.

rvdginste 4y ago

I am surprised by all the anti-ORM comments. When you use an ORM, you need to both know SQL and the ORM. Your investment in learning an ORM is not wasted and you can carry that knowledge over from project to project. If you know both the ORM and SQL well, you can save a lot of time during development. I've been using NHibernate for over 10 years and now also getting into Entity Framework Core. I did see bad usage of ORMs and this was always caused by either a lack of SQL and database knowledge, or a lack of knowledge on how ORMs work.