Ask HN: Raw SQL vs. ORM?

The trade-off is that of every higher-level VS lower-level anything when it comes to computers:<p>Higher-level: Dependent on the quality of the &quot;bridge&quot;, but generally you will benefit from good-practice security measures, less repetition and more idiomatic code with regard to your language of choice. What you lose is the cost of the bridge and the fact that the bridge itself has to be maintained over time, requiring you to keep up with any API changes.<p>Lower-level: Generally less approachable for beginners, highly dependent on your capacity to understand all the implications of the choices you will make, will yield more performance if used correctly because the layer between you and the system is smaller but with power comes responsability.

Raw SQL is always better, IMHO.<p>There are no new abstractions (for example extra session&#x2F;trransaction-like behaviour).<p>You can write good efficient SQL and not use an iteration-protocol.<p>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..<p>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 &quot;smarties&quot; books...<p>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).

Short answer: First one, then the other.<p>A good ORM that abstracts away simple access, querying and database creation can be a godsend during many early iterations of a project.<p>Eventually however you may start to hit boundary cases where an ORM is unsuited or generates slow queries. Once you identify slow queries, then drop down to raw SQL to optimize only when you know where to optimize.

Raw SQL queries will perform fast if you write correctly. No ORM will stand close. Stored procedures help a lot. It&#x27;s close to C&#x2F;C++ in a sense that is easy to shoot yourself in the foot.<p>On other hand, ORM helps to write code that can be unit tested and you can test that some data retrieval conforms to certain principles.

Use an ORM.<p>Use an industry-standard ORM -- Hibernate, ActiveRecord, SQLAlchemy, whatever is the done thing in your language.<p>There&#x27;s just no reason not to. Onboarding new developers becomes easier. Your common queries (fetch a record by PK, fetch some associated records by their FK, etc) require little to no thought, and complex joins between multiple tables become relatively simple to represent. I don&#x27;t know of a single ORM that doesn&#x27;t also allow you to execute raw SQL and get back objects, so in the case where you really _do_ need to do that, you can.<p>Yes, there exist counterexamples where you&#x27;re doing very cutting-edge stuff with your DB that most ORMs can&#x27;t handle. If you&#x27;re doing that you aren&#x27;t asking &quot;should I use an ORM,&quot; you&#x27;ve already made that call and skipped this thread.

I&#x27;ve only had bad experiences with ORMs. You get implicit behaviours and performance nightmares.<p>SQL isn&#x27;t hard to write, unless the model is poorly conceived, in which case throwing an ORM at it will only makes things worse.<p>It really depends on how many tables you have, and how you generate their definition.<p>Personally I once had to start a fresh project with a large amount of tables, I generated the few CRUD requests I needed on all the tables, and commit them in my VCS. Then when iterating I would update and tweak them manually, either for performance tuning or new features &#x2F; joins &#x2F; schema changes etc...<p>This is not to say ORMs are to throw in the bin, I&#x27;m sure they solve some problems, but I&#x27;ve never fund myself in a situation where they would help me more than slow me down.

For those who don&#x27;t use ORMs in large projects:<p>What&#x27;s your preferred approach to store table definitions and migrations? Raw SQL queries there too? Doesn&#x27;t it make them more susceptible to mistakes?

I prefer a hybrid approach. I like to use an ORM for simple object manipulation and access. So for creating&#x2F;updating basic models and simple object retrieval. When I say simple I mean queries like “where id =“ and other very basic where statements. Anything more advanced I prefer to use raw SQL.<p>SQL is a language unto itself and it doesn’t really need a layer of abstraction. I’ve found a lot of people use an ORM as a crutch instead of really learning the true power of SQL. I’ve spent too much time fighting against the ORM trying to get it to replicate what I want to do with SQL.<p>I find it much harder to maintain complex queries that are in an ORM. My personal workflow is to develop queries directly interfacing with the database and then more or less copy and paste the solution into code. If you use the query builder in the ORM you have to make the conversion from SQL to code, which I find to be pointless. SQL is very expressive, easy to read (once you learn it), and portable. Every ORM is unique and has its own syntax and style.<p>My one exception to where I prefer the ORM query builder over raw sql is when I’m building SQL that is heavily machine manipulated and has a lot of logic paths. Like for example a custom csv generator or an advanced query form. In those cases I find wrapping code logic around raw SQL to be quite messy and error prone. It flows much nicer when the builder can generate the SQL around your logic.<p>In either case people should learn SQL. Even when you use an ORM the end result is SQL. Understanding SQL makes it’s easier to debug problems, improve performance, and answer the hard questions. I also think it just makes someone a more well rounded developer.

I like using what is sometimes called Micro-ORM. Basically it does the mapping of query results to objects and sometimes has a basic query builder but you mostly use raw sql.<p>Dapper (<a href="https:&#x2F;&#x2F;github.com&#x2F;StackExchange&#x2F;Dapper" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;StackExchange&#x2F;Dapper</a>) is a great one for .NET.

I use both raw SQL and Hibernate in my applications.<p>For simple CRUD (Create, Read, Update, Delete) operations Hibernate is excellent and does the job damn well!<p>But as your data model gets more complex, you need to write more complex queries. For example if you need to build a tree with child&#x2F;parent nodes. This can only be done with CTE(Common Table Expressions) <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;10&#x2F;static&#x2F;queries-with.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;10&#x2F;static&#x2F;queries-with.html</a><p>Hibernate and &quot;all&quot; other ORM&#x27;s out there doesn&#x27;t support CTE&#x27;s and this is where you want to use raw SQL&#x27;s instead. There are also other examples where I want to use specific function, for example the array_to_json function in PostgreSQL.

Depends the quality of the ORM really. For simple queries (basic insert, basic select), pretty much every ORM will do the job and in a more readable way than SQL. For something more complex, if the ORM isn&#x27;t great, I prefer writing SQL directly.

Clojure has several libraries (such as <a href="https:&#x2F;&#x2F;github.com&#x2F;jkk&#x2F;honeysql" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;jkk&#x2F;honeysql</a>) which basically let you build a representation of an SQL query as a Clojure data structure, and then programatically transform it. It is great when you need to do things like add joins and subselects programatically to a query based on selections from a UI, and is extensible enough to support many advanced SQL features.

ORM, mapped to views for more complex stuff.<p>That said, mostly I use Clojure and there’s no actual mapping to do. I use a library to create dynamic, composable SQL queries, but mostly have macros to generate crud statements. Everything gets returned in namespace qualified, idiomatic Clojure maps. It’s all then covered by Clojure Spec so you know you’re forming and passing everything around correctly if you need to.

Personally, I prefer raw SQL, because I like SQL. It is one of the few languages where you can be <i>really</i> clever without making maintenance a nightmare, IMHO.<p>OTOH, I tend to encapsulate the direct interaction with the database into a separate class&#x2F;data type in my applications, that offers an interface to the rest of the code that is more in line with the application domain. So one could argue that I tend to write special-purpose ORMs over and over again. ;-) It has the advantage, though, that switching out the underlying DBMS is less of a pain, when it is required.<p>Full disclosure: All the projects I have worked on were fairly modest in size and complexity. The most &quot;out there&quot; thing I have done was to write and&#x2F;or maintain a couple of SQL views and triggers to hook up our ERP system to other applications. Because our ERP system sucks. But on the plus side, I learnt a lot about SQL, which tends to feed back into my preference of raw SQL over ORMs. ;-)

1. The Programming Language is factor in how well ORMs integrate into your app. If you&#x27;re using a language which is expressive enough or has native support for querying, ORMs are an excellent fit. An example of a language in which ORMs work well is C#, on account of LINQ. But if you&#x27;re on Java, it isn&#x27;t as appealing. Hibernate still has value and is a very mature solution, but is nothing close to the expressiveness you get in LINQ.<p>2. Performance is good enough, but with caveats. Some ORMs (especially newer ones) don&#x27;t handle entity relationships very well, and sometimes do 1+N queries to fetch a single object. For eg, if you have a customer with an orders property, a naive ORM might SELECT the customer first, and issue separate SELECTs for loading each of the Orders. Entity Framework had this problem earlier, which they resolved later.<p>3. Always watch the actual queries with a database tool.<p>4. Be careful about Lazy Loading. Lazy loading defers the actual load until you use it. If the code always uses a property (that needs to be loaded from the DB), always eager load. eg: if you have 100 customers, and you usually need customer.creditCard, eager load &quot;creditCard&quot; (resulting in a join) to avoid 1+100 queries.<p>5. You probably don&#x27;t need an ORM with NoSQL.<p>6. Inheritance relationships can be tricky, and have performance consequences. You can choose to have a (1) Table for the entire class hierarchy, or (2) a table for each Class. With (1), you get an ugly wide table with lots of fields and faster performance. With (2), if you were to select a list of Animals, and have Cat, Dog, Rabbit tables, you&#x27;ll get cleaner tables - but poor performance because of joins. Add: generally avoid mapping inheritance via ORMs.<p>7. Built-in caching, which you can find in some ORMs is probably not worth it.<p>8. ORMs need not replace 100% of your queries. Some functionality will work better with SQL or even Stored Procedures - let it be.<p>9. ORMs let you compose queries. I&#x27;ll not go into details, but you could compose getCustomersByCountry() and getCustomersByAgeGroup() to get getCustomersByCountry_and_Age().<p>10. Depending on the size of your project, see if patterns like Repository make sense (even if you&#x27;re using ORMs).<p>11. Last, the most important detail. It is not actually about saving lines of code - as much as it is about the ability to refactor. The biggest win from ORMs (in a statically typed language) is that if you edit a property, it changes the property across all files including your queries. Without an ORM, the code degrades quicker - because developers are reluctant to change.

Use the best tools available to you for the job at hand.<p>For instance, if you&#x27;re using Python, there is SQL Alchemy, which gives you everything you will ever need in any situation, from ORM to parameter-bound raw sql, from a very feature-rich library.<p>Parameter-bound raw SQL is a fine option as long as you are comfortable with taking responsibility for testing and auditing for risks of sql injection. Don&#x27;t use this approach unless you understand what the risks are and know how to manage them. Further, raw SQL is more challenging to debug in that you don&#x27;t know problems until you vet issues at run-time. You&#x27;re not entirely on your own with syntax checking, though-- there are sql syntax verification libraries that can help vet raw sql for you.

(in between compiles...)<p>Raw SQL for simple stuff, of course - easier to debug, transportable to multiple languages.<p>For non-trivial stuff...<p>For read-write access, no choice but ORM imho - are you really gonna create stored procedures for every type of update?<p>For read-only access, raw SQL is an option, but it gets tricky with layers of VIEWs. Must be at least as powerful as Postgres e.g. partial and function indices to hide the underlying physical structure without paying some horrendous performance penalty.<p>(experience from trying to avoid ORMs in 3 startups... someday I&#x27;d love to add SELECT * MINUS &lt;columns&gt; to PostgreSQL to make VIEW authoring more scalable...)

I use ORMs as a programmatic way to generate SQL for me.<p>I do NOT use ORMs as a replacement for knowing SQL or using SQL when it&#x27;s appropriate. I think this is where a lot people get into trouble. They assume they can use the ORM and not need to know SQL.

If you do use an ORM, you should still know SQL and know what raw queries it actually generates, and you should make sure it doesn&#x27;t do anything cute like falling back to concatenating strings or some other unsafe practice for the sake of having a convenient API. Everyone assumes that using an ORM means better security than raw SQL, and maybe in the general case that&#x27;s true, but the more abstract and complex a framework is, the more room there is for unknown and unwanted behavior.<p>The one thing you probably should <i>never</i> do is use an ORM because you don&#x27;t want to learn SQL or you just can&#x27;t be bothered to care.

As a Django developer, I wouldn&#x27;t give up the Django ORM for the World. It&#x27;s very expressive (albeit a bit verbose if you want to do non-trivial things — but you can do them), extensible, and all around a complete joy to work with.<p>It&#x27;s the perfect balance of getting out of your way for trivial things, and letting you write your own SQL where it&#x27;s required.<p><a href="https:&#x2F;&#x2F;docs.djangoproject.com&#x2F;en&#x2F;2.0&#x2F;ref&#x2F;models&#x2F;querysets&#x2F;" rel="nofollow">https:&#x2F;&#x2F;docs.djangoproject.com&#x2F;en&#x2F;2.0&#x2F;ref&#x2F;models&#x2F;querysets&#x2F;</a>

Uh, neither? A good query builder library, though not one that tries to excuse you from knowing actual SQL. If you try writing SQL by hand, you _will_ eventually mess up and introduce SQL injection somewhere.

The main advantage for ORM is security. The main disadvantage is that they often abstract away things that can impact performance heavely even if you are careful. I love Ecto (for Elixir) as a middle ground: closer to SQL, safe, still able to leverage your client models and well integrated into the language.<p>Examples: <a href="https:&#x2F;&#x2F;elixirschool.com&#x2F;en&#x2F;lessons&#x2F;specifics&#x2F;ecto&#x2F;#querying" rel="nofollow">https:&#x2F;&#x2F;elixirschool.com&#x2F;en&#x2F;lessons&#x2F;specifics&#x2F;ecto&#x2F;#querying</a>

I really like the concept of sqlx for Go. You write the SQL, but sqlx returns populates structs for you.<p>For really simple stuff, CRUD application, an ORM usually fine though.

I got bitten by ORMs in my current toy project, <a href="https:&#x2F;&#x2F;github.com&#x2F;voltagex&#x2F;YAMS" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;voltagex&#x2F;YAMS</a>. For the life of me, I couldn&#x27;t get Dapper to join correctly. Now I&#x27;m running into trouble keeping the POCOs and SQL in sync. I may try to use some kind of code generation based on schema - I think there&#x27;s a middle ground.

For simple queries, I totally prefer ORM because the code that converts the results to objects is more work than learning&#x2F;writing the ORM API.<p>For complicated queries involving joins and&#x2F;or complicated conditions, in my experience writing the raw SQLs directly is simpler and more readable than ORM. However most ORMs let you use raw queries and you still get the benefit of not having to write the conversion code.

There was a weirdly named thing called &quot;mybatis&quot; around awhile ago, that had a nice balance where the SQL was contained in one place and communication into and out of it was objects, but it was actual SQL (amenable to DBA optimization) and not some library doing things for you cleverly. Not sure if it&#x27;s still a thing but I&#x27;m surprised that model isn&#x27;t more common.

Both<p>ORM for OLTP<p>SQL for OLAP<p>Expressed nearby to each other when there is shared business rules, for maintenance reasons:<p><pre><code> public static String PREDICATE_NO_GUMMY_BEARS = &quot;user_preferred_candy &lt;&gt; &#x27;gummy bears&#x27;&quot;; public static Predicate noGummyBears(Root&lt;User&gt; user, CriteriaBuilder cb) { return cb.notEqual(user.get(&quot;preferredCandy&quot;), &quot;gummy bears&quot;); }</code></pre>

1. For static SQL, write views or procedures (i.e. raw SQL).<p>2. For dynamic SQL, use jOOQ or something similar in your language.<p>3. For the rare case of really <i>needing</i> object graph persistence (loading a graph of entities, manipulating it, and storing the changes back to the database), or the less rare case of doing boring single-record CRUD, use an ORM. You don&#x27;t want to do that with SQL.

I&#x27;m firmly on the side of raw queries with binding params to avoid SQL injection. This is doubly true with complex queries. I can&#x27;t count how many times I&#x27;ve crafted my SQL query very quickly, and then fought the ORM query builder.<p>Often, ORM proponents will say that is why any true Scotsman, erm, I mean ORM, will let you drop into raw mode. But that can have problems with testing. If you test against your ORM one way, it might just not work to test it against raw mode. And, thinking of testing, I&#x27;m also a fan of testing your SQL or ORM against a real DB (one that is set up and tore down per test). I know there are some unit testing purests that don&#x27;t like that and thus prefer an ORM.<p>Many who like ORMs claim that they are so much faster for the programmer. Maybe for basic CRUD and composing simple conditional WHERE statements. And I contend that really is a &quot;maybe.&quot; But I&#x27;ve been bit by poorly formed ORM queries (either does not do what you planned or is too slow, or one of my favorites: it returns ALL records and the ORM filters that app side to give you the ONE matching result) and I&#x27;ve lost enough time trying to force the ORM to make the query the way I want it, that I opt to skip that whole class of problem whenever I can.

One thing I have found out useful is having a setup where you can easily write test cases for ORM queries using a real database and see a log file of generated SQL. Most of the &quot;strange ORM errors&quot; are much easier to figure out when you can see the DDL (like foreign keys) and the queries&#x2F;updates that ORM is generating.

ORMs are fun to play with, and are probably better for productivity in small apps.<p>However, for anything more significant than such, I find that writing SQL is not only more performant but also allows for greater productivity (do exactly what you want the way you want it without excessive digging around to see if it can been done&#x2F;has been done).

The biggest advantage of using RAW SQL is that you learn it &#x27;quicker&#x27;. You will make mistakes, but hopefully those will be found before they make it to production. If you get used to ORMs, you probably won&#x27;t know how to optimize your queries..

Start with an ORM, for 99% of projects that is the good first choice.<p>Once you start growing and you can&#x27;t solve your problems with the ORM or performance on the database starts to suck, start handcrafting where it hurts and gradually move to Raw SQL.

RAW SQL of course. * Create the database for the queries you will use, then, for the admin panel, you can use an ORM (but why would you mix the 2?), but for front-end I always use RAW SQL (PDO, to be exact).

For an MVP, use an ORM. Faster development.<p>For scaling to millions, start strategically using raw SQL for alot of things. You&#x27;ll save alot of money on processing power and infrastructure costs.

MicroORMS are a good trade off, they do the tedious mapping between results and tables but you write the sql.

if you mean embedding actual SELECTs in your code then no. Calling stores procedures is a much better more maintainable solution. Your app shouldn’t have to know anything about the schema just from a separation of concerns view.

People are so afraid of SQL they end up putting in 10x the effort trying to do it with an ORM. You just don’t need it, just a day of studying SQL is an investment that will pay off 100-fold.

Most current ORM out in the market do not make things easier but instead otherwise. We need ORM to make it easy to build application that all agrees on OO principal meaning inheritance, one or more instant variable, polymorphism and etc.<p>My product have done all the above, at <a href="http:&#x2F;&#x2F;shujutech.mywire.org&#x2F;corporation?goto=ormj" rel="nofollow">http:&#x2F;&#x2F;shujutech.mywire.org&#x2F;corporation?goto=ormj</a><p>and me personally have felt the abstraction have been successfully achieved ever since because I never need to switch back to the ORM layer to build my applications. But, there&#x27;re more to goes still............