Ask HN: Raw SQL vs. ORM?

Higher-level: Dependent on the quality of the "bridge", 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.

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.

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).

A good ORM that abstracts away simple access, querying and database creation can be a godsend during many early iterations of a project.

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.

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 industry-standard ORM -- Hibernate, ActiveRecord, SQLAlchemy, whatever is the done thing in your language.

There'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't know of a single ORM that doesn'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.

Yes, there exist counterexamples where you're doing very cutting-edge stuff with your DB that most ORMs can't handle. If you're doing that you aren't asking "should I use an ORM," you've already made that call and skipped this thread.

SQL isn't hard to write, unless the model is poorly conceived, in which case throwing an ORM at it will only makes things worse.

It really depends on how many tables you have, and how you generate their definition.

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 / joins / schema changes etc...

This is not to say ORMs are to throw in the bin, I'm sure they solve some problems, but I've never fund myself in a situation where they would help me more than slow me down.

What's your preferred approach to store table definitions and migrations? Raw SQL queries there too? Doesn't it make them more susceptible to mistakes?

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.

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.

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.

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.

Dapper (https://github.com/StackExchange/Dapper) is a great one for .NET.

For simple CRUD (Create, Read, Update, Delete) operations Hibernate is excellent and does the job damn well!

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/parent nodes. This can only be done with CTE(Common Table Expressions) https://www.postgresql.org/docs/10/static/queries-with.html

Hibernate and "all" other ORM's out there doesn't support CTE's and this is where you want to use raw SQL's instead. There are also other examples where I want to use specific function, for example the array_to_json function in PostgreSQL.

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.

OTOH, I tend to encapsulate the direct interaction with the database into a separate class/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.

Full disclosure: All the projects I have worked on were fairly modest in size and complexity. The most "out there" thing I have done was to write and/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. ;-)

2. Performance is good enough, but with caveats. Some ORMs (especially newer ones) don'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.

3. Always watch the actual queries with a database tool.

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 "creditCard" (resulting in a join) to avoid 1+100 queries.

5. You probably don't need an ORM with NoSQL.

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'll get cleaner tables - but poor performance because of joins. Add: generally avoid mapping inheritance via ORMs.

7. Built-in caching, which you can find in some ORMs is probably not worth it.

8. ORMs need not replace 100% of your queries. Some functionality will work better with SQL or even Stored Procedures - let it be.

9. ORMs let you compose queries. I'll not go into details, but you could compose getCustomersByCountry() and getCustomersByAgeGroup() to get getCustomersByCountry_and_Age().

10. Depending on the size of your project, see if patterns like Repository make sense (even if you're using ORMs).

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.

For instance, if you'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.

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'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't know problems until you vet issues at run-time. You're not entirely on your own with syntax checking, though-- there are sql syntax verification libraries that can help vet raw sql for you.

Raw SQL for simple stuff, of course - easier to debug, transportable to multiple languages.

For non-trivial stuff...

For read-write access, no choice but ORM imho - are you really gonna create stored procedures for every type of update?

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.

(experience from trying to avoid ORMs in 3 startups... someday I'd love to add SELECT * MINUS <columns> to PostgreSQL to make VIEW authoring more scalable...)

I do NOT use ORMs as a replacement for knowing SQL or using SQL when it'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.

The one thing you probably should never do is use an ORM because you don't want to learn SQL or you just can't be bothered to care.

It's the perfect balance of getting out of your way for trivial things, and letting you write your own SQL where it's required.

https://docs.djangoproject.com/en/2.0/ref/models/querysets/

Examples: https://elixirschool.com/en/lessons/specifics/ecto/#querying

For really simple stuff, CRUD application, an ORM usually fine though.

For complicated queries involving joins and/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.

ORM for OLTP

SQL for OLAP

Expressed nearby to each other when there is shared business rules, for maintenance reasons:

    public static String PREDICATE_NO_GUMMY_BEARS = "user_preferred_candy <> 'gummy bears'";
    public static Predicate noGummyBears(Root<User> user, CriteriaBuilder cb) {
        return cb.notEqual(user.get("preferredCandy"), "gummy bears");
    }

2. For dynamic SQL, use jOOQ or something similar in your language.

3. For the rare case of really needing 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't want to do that with SQL.

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'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't like that and thus prefer an ORM.

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 "maybe." But I'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'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.

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/has been done).

Once you start growing and you can'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.

For scaling to millions, start strategically using raw SQL for alot of things. You'll save alot of money on processing power and infrastructure costs.

My product have done all the above, at http://shujutech.mywire.org/corporation?goto=ormj

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're more to goes still............