host: localhostport: Wayfair Blog Post: ORM Bankruptcy | Robby Grodin Skip to content

Wayfair Blog Post: ORM Bankruptcy

This post was originally a release note I sent while working at Wayfair, and is now a blog post on their Engineering blog. I’d be remiss to republish without noting that while this perspective piece sounds fairly dogmatic, it shouldn’t be taken as such. ORMs are useful tools in any engineer’s toolkit, as is the ability to write clean and optimized SQL queries. What works for Wayfair won’t necessarily work for everyone.

ORM Bankruptcy: Why We Ditched Our ORM


This post describes a recent architectural change made on my team at Wayfair. The Intelligent Systems Team, as we are called, is responsible for creating a fully personalized experience for millions of customers every day. One of the core products we own is the Recommendations Service. At its core, the service is a simple Python/Flask app that provides recommendations used on the site, in emails, to power our proprietary display retargeting platform, and throughout various other applications.

An Object Relational Mapping (ORM) is a technology that translates relationship database schemas to an object-oriented model. The Recommendations Service utilized one to retrieve information from MSSQL server before storing that data in Redis. This blog post explains why we’ve discarded this approach in favor of directly dispatching SQL queries using SQLAlchemy Core.

The last few months have seen the Recommendations Service increase in both functional and architectural complexity. The core of this service is the ability to ingest a 32 character alphanumeric GUID per request which corresponds to a predetermined configuration. Having to adhere to a strict SLA, we try to hit the DB at most once per request, and only if the cache is empty.

With the most recent feature update, Configuration Sequences, our configuration query needed to model a One-To-Many-To-Many relationship. Executing this DB interaction through the existing SQLAlchemy ORM became too unwieldy, for multiple reasons. Others have written fairly extensively about their distaste for ORMs. Here are the main reasons why we’ve deprecated our ORM code.

Code Maintainability– ORM code can appear elegant when working with simple models. When that model contains multiple join tables and picklists, things get fairly complex. We ended up with over 100 lines of code that were tightly coupled to the database design, single line functions with half a dozen operators, and lack of clarity about how often the DB is queried. Not only did someone need to learn a new technology to be effective, but their success was often hindered by a lack of documentation. Directly dispatching SQL queries in Flask is fairly straightforward.

Do less work– It’s much more efficient for an engineer to build a SQL query once than to have the ORM do that work for us every time a request is made. Additionally, the more complex the query, the more difficult it is to translate into ORM code. By altering this approach, we are enabling the service to scale more smoothly.

Don’t hide the good stuff– Data Literacy is one of our main tenets at Wayfair. If you’re an engineer, you should be able to “speak SQL”. The idea behind an ORM is to hide the SQL code, but it doesn’t- it just makes it more unintelligible. The most important parts of the query are abstracted away and hidden behind a foreign syntax. This is very bad- especially when you realize that your vital WITH (NOLOCK) hint doesn’t actually work. It’s very difficult to optimize a SQL query you can’t see, so why not just put it directly in your code?

In general, the service is now more pleasant to work with and gives our engineers an increased sense of confidence when pushing code. This is due not only to the legibility of the code but also the increased testability. Just another way we’re trying to shake the anti-patterns from our codebase.

Also published on Medium.


  1. Ramon Leon Ramon Leon

    > The idea behind an ORM is to hide the SQL code

    Nonsense; the idea behind an ORM is to eliminate repetitive hand written SQL and mapping code to simplify and speed up development. It isn’t and never was about hiding SQL from the developer.

    Your justification are little more than post-hoc rationalizations that don’t make any sense. You could have hidden the complex and efficient SQL in a view and used the ORM to map the view and had the best of both worlds; instead you choose to blame the ORM because you use the ORM badly, blaming your tools is amateurish.

    • Yawar Yawar

      If I can just wrap all my SQL in views and stored procs and call them, why should I use an ORM in the first place?

  2. yohan yohan

    How do you guys test your SQL queries in CI?

    • Yawar Yawar

      Arrange-act-assert pattern works fine in integration tests. Arrange: set up a transaction and execute some SQL that sets up data. Act: execute SQL that performs the task to be tested. Assert: verify the SQL results are what you expect.

  3. Hey,

    What I found works best for me is to use actually a mix of both. When relations/queries can be simple use ORM and I if I need to build complex sql queries I just use raw sql via `text` in sqlalchemy. I agree that sometime is much harder to create/debug complex queries with ORM(alchemy, django etc.)

Leave a Reply

Your email address will not be published. Required fields are marked *