Background

Frappe has a very simple and easy-to-understand ORM layer that has two components:

• Documents - create (doc.insert), read (get_doc), update (doc.save), delete (doc.delete) a record.
• Lists - Read (get_list) or update (set_value) multiple documents matching specified filters.

These abstractions were written a long time ago and have evolved over the years. While 97% of the data manipulation work can be done by just composing these building blocks of ORMs, the last 3% complex use cases require bypassing the ORM layer and writing raw SQL queries yourself.

Like any ORM, complex operations that require multiple ORM calls result in:

• Inefficient code because of the N+1 query problem.
• Degraded developer experience because of stringed-together parts of the SQL query.
• More opportunities for SQL injection if user input is passed incorrectly.
• Reimplementation of Frappe's non-trivial permission model to avoid leaking unauthorised data.

ORM's internal implementation itself used strings to stitch together the final query. We have gotten several reports of SQL injections over the years, and we've fixed them one at a time. However, there was always a deeper design problem that caused these SQLi vulnerabilities in the first place. The central flaw in design was validating and sanitising bad inputs instead of explicitly allowing known good inputs.

Enter query builder

Knowing these limitations of ORM, Gavin started working on a new query builder project to provide a more Pythonic way of building complex queries. This project was executed by interns from the GitHub Externship program. We tried a couple of proof of concepts for integrating widely used ORMs like SQLAlchemy, but eventually settled on a thin query builder pypika that provided the nicest Pythonic API and allowed us to customise it for Frappe's needs.

Queries looked like Python code and can be modified like Python objects instead of doing string manipulation. We released this to the public four years ago.

customer = frappe.qb.DocType('Customer')
filtered_data = (
    frappe.qb.from_(customer)
        .select(customer.name, customer.fname, customer.lname)
        .where((customer.fname == 'Max') | customer.id.like('RA%'))
        .where(customer.lname == 'Mustermann')
).run(as_dict=True)

As the query builder (QB) started taking shape, it made more and more sense to use it internally in our ORM's implementation to replace string-based query generation. This allowed us to build a solid layer of extremely flexible query building and wrap it inside our ORM, where users can specify filters using simple dictionaries.

We migrated most of the popular utils like db.get_value, db.set_value and db.delete to internally use the new query builder. This required building a layer to map Frappe filters to their SQL analogue, and this is how qb.Engine.get_query was born - a spiritual successor to our old ORM's get_list.

Second systems effect

Query builder kept evolving over the next year... while trying to mimic the complexity of SQL, support for multiple databases and parity with our own old ORM, the code became messier and messier over time. The progress stalled, and people who worked on it originally were no longer at Frappe, which added to the slowdown.

I believe large changes like these require significant and continuous conviction in the idea from one or more individuals to see it through. Thankfully, Faris Ansari had this conviction. Faris was hellbent on a nicer experience for extending ORM for the new apps he was working on. Faris started by refactoring the code so that future changes are easier. After this cleanup, the code was much more stable and readable.

A couple of years passed by, and still, we weren't close to achieving parity with our old ORM. Permissions still lacked in the new query builder abstraction. This was visible in poorly implemented permission models in some new apps that relied exclusively on the query builder. Faris once again took it on himself to add support for the entire permission model of Frappe in the new query builder. This change pushed feature parity with the old ORM beyond 90%. We were finally ready to swap the internals of the old ORM with the query builder.

Biting the bullet

v16 release was looming. Major releases are a perfect time to introduce breaking changes, as missing that means waiting 1-2 more years before we can make those hard calls. We don't love to break things, but a few breaking changes are essential for improving security, and this query builder change is one of those.

Akhil finally picks up the task of doing the last 10% of work to swap the internals of our old ORM. We have a funny saying at Frappe: "In every software project, there's 90% of work and then there's the remaining 90% of work". Most engineers at Frappe are good at either the first 90% or last 90% of the work, with a few exceptions who can do both.

Understandably, it took several weeks to make the switch. We had to decide on several obscure usages of ORM, test with multiple apps to ensure that our breaking changes aren't "too breaking".

A few years ago, we put significant effort into increasing server-side test coverage. Now that 80%+ of our code is covered by tests in our mature projects, we were quickly able to test breakages and fix the code to adapt to new requirements for query building. Large changes like this would've been impossible without a good automated test suite.

Outcomes and takeaways

v16 will finally have a unified internal query builder engine, and we are sure it will improve developer experience for those who are just starting with the Frappe framework. Query building flexibility will drastically improve, as you'll now be able to modify partial queries generated by Frappe's ORM and then execute them. You'll rarely have to implement permission checks yourself or fallback to raw SQL.

query = frappe.get_list("User", run=0)
results = query.select("enabled").run(as_dict=1)

Secure internals of the new implementation also fixed design flaws in the previous implementation. We asked our trusted old pen-testing partner, Resilient Tech, to audit query-building changes, and this was their response:

I'm really pleased to report no working SQLi so far.

-- Sagar Vora (Resilient Tech)

In my opinion, this four-year-long project is a perfect example of how large brownfield projects that have 100s of dependent stakeholders are significantly harder to push to the finish line. There's a constant threat of breaking something or ending up with a worse outcome than what we started with. This project was only successful because...

1. We always had someone with conviction in the idea. They also happen to have strong taste in what a good developer experience looks like.
2. We developed and rolled out changes in milestones. We also continuously evaluated the "developer experience" in our own apps.
3. We improved test coverage so we could push major breaking changes with confidence.

---

Thanks to everyone who made it happen. Gavin D'Souza for starting the query builder project, Abhishek Saxena and Aradhya Tripathi for executing the original version and refactoring it. Aradhya and Ritwik Puri for the 2nd round of changes, Faris Ansari for code refactors and adding permissions support, Akhil Narang for "final 90%" of the work migrating the ORM, Sagar Vora for auditing and the entire community for feedback and code fixes.