[Announcement] Introducing Frappe Query Builder

Community
, ,
1

We’ve wanted to add a more Pythonic way to manage queries in Frappe for a while. So, we finally got around and did it this month :smile: .

So you want to fetch some data from your database, but you need something more powerful than frappe.get_all? This is when most resort to using frappe.db.sql, and the brave-hearts use multiple frappe.get_all calls along with pandas, along with a for loop or two. With our query builder, we aim to ease this pain a bit. Here’s how it’d look like to run a select with or, and and filters:

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

Okay, this is a relatively simpler query. But, you could write much complex queries; subqueries, CTEs, recursive, joins too. I didn’t add an example for those here because that’d get a bit too much for this light-hearted post.

One particularly neat thing about our query builder is that it evaluates your queries to whatever DBMS your site’s running on. Yup, you can write cross-database apps without having to use frappe.db.multisql. In fact, we tried to get rid of all the multisql calls in Frappe itself…there’s still a couple left though.

The above query object, evaluates differently on Postgres and MariaDB as you can see here:

# Postgres query
SELECT "name","fname","lname","phone" FROM "tabCustomer" WHERE ("fname"='Max' OR "id" LIKE 'RA%') AND "lname"='Mustermann'

# MariaDB query
SELECT `name`,`fname`,`lname`,`phone` FROM `tabCustomer` WHERE (`fname`='Max' OR `id` LIKE 'RA%') AND `lname`='Mustermann'

We’ve documented the available APIs so that you can get started writing queries with frappe.qb. You can read further about it here:

https://frappeframework.com/docs/user/en/api/query-builder

Once you’ve familiarised yourself with what Frappe sugar is available, you can find comprehensive docs about all possible usages, check out PyPika’s official docs and their even more comprehensive README:

Here’s the PRs that introduced this feature in Frappe. It’s currently on develop and will be a part of Frappe Version 14. Meanwhile, we’re going to try and convert as many raw queries to this notation.

https://github.com/frappe/frappe/pull/13705

https://github.com/frappe/frappe/pull/13841

Apart from PyPika, we considered a few other query builders and ORMs. SQLAlchemy seemed to be a strong contender in the early stages. We soon faced a barrage of issues as we started implementing it. Some of our work on it is documented in this PR:

https://github.com/frappe/frappe/pull/13732

Apart from these, we’ve been making active efforts in extending the existing Database APIs to make them more powerful. We aim to make it easier for everyone to write Frappe Apps, and reduce the interfaces for bugs while we’re at it :wink: .

We converted close to 80 raw queries to use the Database APIs. This enabled us to further improve on the low level frappe.db.delete API and introduce a new frappe.db.truncate too.

https://github.com/frappe/frappe/pull/13753

We’re also trying to expand the feature set in DatabaseQuery it’s the engine that supports frappe.get_list, frappe.get_all, list views, etc. Support for pure column or data comparisons, and more Pythonic APIs for using Pseudo columns and better handling for SQL functions is coming soon…

https://github.com/frappe/frappe/pull/13845

Feel free to drop your suggestions or ideas for improvements :slight_smile:

49 Likes
2

Exciting stuff! Will this be available on ERPNext.com and Frappe.cloud?

2 Likes
4

This is an enormously huge step for Frappe as a data framework. Please allow Pseudo-columns (virtual fields?) to initiate joins and simple computations. ERPNext frequently struggles with data normalization, and this kind of change will make life so much better for developers and users both!

4 Likes
6

This is so impressive. It is going to help people build awesome customized reports with ease. Why don’t we merge this to v13 as well?

3 Likes
7

Thanks for your effort and contribution towards ERPNext. This will save lot of time those who struggling to achieve MIS Report.

1 Like
8

Will this frappe query builder also consider data restrictions that set from user permissions, field permissions, etc. ?

8 Likes
9

Syntactically previous ORM was better to use but if a new one provides better performance and complex queries then its good to have it :slightly_smiling_face:

2 Likes
10

It’s currently only on develop, the PR for the v13 backport has been raised and will likely be in the next (v13.10.0) of Frappe. So, both should be available as they’re deployed on the platforms. Contact support directly to get a clearer deployment plan for ERPNext.com and frappe.cloud teams.

So, there’s nothing restricting us from using pseudo columns in the current implementation. You can import pseudo columns from the pypika.pseudocolumns module and use them freely with the query builder attached to frappe.qb. Checkout the examples under the Pypika tests for them via the link below. The only difference is Query is replaced with frappe.qb. Also, we’re planning on making all the imports relative from pypika to frappe.query_builder, but it’s not implemented yet. The general idea is that from pypika.pseudocolumns import SysDate would be the same as from frappe.query_builder.pseudocolumns import SysDate.

A PR is open for the same. It would likely be introduced in the v13.10.0 release, if all goes well :sweat_smile:

Looking forward to see all the ways this query builder gets used in the future :slight_smile:

Not currently. At current implementation, it works purely as a more Pythonic frappe.db.sql and frappe.db.multisql replacement. But, it’s something that did come up while developing this. At this point, I’m looking into the possibilities of permission management through the query builder. Would be able to say something solid about this after the upcoming DatabaseQuery updates.

Being the raw sql replacement it is, I doubt you’d see any performance improvement. The motivations for this were to ease development and extend Frappe’s capabilities to run complex queries without resorting to frappe.db.sql.

6 Likes
11

This is great stuff! Thank you!

1 Like
12

Looking forward to see it implemented.

13

I’m not familiar enough with pypika yet, so I’m not quite sure what these demos are doing. Would the pseudo columns described here allow for table joins?

I.e., on sales invoice, I’d like to be able to create a virtual field that links the phone number field in a linked customer document. This is similar to what the “Fetch From” property does in form customization, but taking place at the database rather than UI level. Is this going to be possible?

14

I’ve got mixed feelings on this.

Pros:

  • ERPNext has needed an ORM-type feature for a very long time. This was definitely a missing piece that separated ERPNext from well-known, commercial products.

  • Instead of reinventing the wheel, it will use PyPika. Over 1,300 GitHub stars is quite a lot. Which hopefully means the package has some longevity and support.

Cons:

  • Again, the ERPNext learning curve increases. We’re now forced to learn another vendor-specific syntax. At a glance, it does not appear “too” difficult. Still, that’s one more thing to master. 1

  • Limited Added Value : I can name a lot of features that would bring immediate help or improvement to ERPNext. Introducing a new ORM…is not one. Will it help new development? Probably. But given everything else that -could- be fixed/achieved with similar effort, I question the priority.

  • Surprise, Surprise: Once again, the community is surprised to hear about the latest invention. Introducing an ORM was not on the publicly shared Roadmap. And to my knowledge, it’s been implemented without any community feedback.

    Here in the USA, I would use the sports phrase ‘par for the course’.

1
Q: Okay Brian, you’re not being fair. SQL has a learning curve too!
A: For sure. But here’s the thing. Does learning PyPika replace our need to learn SQL? No. It’s an additional tool. Not a replacement for SQL.

Many report authors, developers, and business analysts will still need SQL language skills in their toolbelt. Sooner or later, you want to query the database with a client tool. Debug issues. Perform ad-hoc data exploration. Or write queries that are too-complex for an ORM to handle. Tools like PyPika supplement SQL; they never supplant it. So my point is now you must learn SQL and PyPika (2 things instead of 1)

5 Likes
15

I disagree. The importance of this change is not syntax; it’s programmability. With frappe.db.sql, query composition has always been done via string concatenation. This is an absolute anti-pattern, prone to error and very difficult to scale in complexity. By giving queries an object structure, much more complex queries become possible.

The implications for this are important. At the moment, doctypes and sql tables are more or less one-to-one mapped. More complex data schema, I’d argue, really require a proper ORM. This has important implications for how data is normalized in the system between doctypes and in one-to-many relations. If you’re frustrated by how limited child docs currently are, for example, this is the necessary backend work to a more flexible system.

16

If I knew for certain that future, important improvements (like first-class Child Docs) were the -reason- Frappe Query Builder was introduced in the first place? Then I might have a different opinion.

But there’s no Road Map. No discussions and community collaboration. Everything happens behind-closed-doors.

So until then. Until ORM yields Indirect Added Value? By itself, I don’t see Direct Added Value. I cannot think of anything it can accomplish, that I cannot already via ordinary SQL. There’s no limit to the quantity or complexity of my statements. Even more-advanced syntax like CTEs work just fine, with a tiny tweak to 'database.sql'.

I admire your optimism and faith, Peter. I really do. I think I’ve just reached a breaking point, in terms of the lack of transparency and collaboration in this project. I’m already scrambling to deal with the latest catastrophe with Web Forms and Easy Install. Then I read this, and I’m like “great…will this be another half-implemented feature that I’m fixing for the next 6 months?”

Probably not a great attitude on my part. But hard to argue it’s not justified, given 5 years of history.

2 Likes
17

I think we have very different understandings of how open source can and should work.

The kind of centralized planning you’re describing certainly exists in the open source world. Apart from ERPNext, I have some minimal understanding of two other projects, Drupal and Moodle. Both are similar to ERPNext in scale and complexity, but unlike ERPNext those two projects have very formal development cycles. Roadmaps go out two, three, four years into the future, and decisions are made through painstaking negotiations among a wide range of boards and stakeholders.

There are advantages to that approach, to be sure, but there are disadvantages too. For starters, development is slooooooow. If you compare Drupal or Moodle to what they looked like 5 years ago, really very little has changed. And those stakeholder negotiations? Unless your organization can commit 1000 hours+ of professional developer time annually, you simply don’t have a seat at the table.

With Frappe/ERPNext, things are very different. There’s no roadmap, I strongly suspect, because there’s no central plan. There’s no corporate overseer deciding who should do what next as part of a long-term strategy, but rather there are a bunch of talented and generous individuals chasing after whatever next thing seems interesting or important. Things get built because somebody with the skills to make it happen decided that it should be built. Nothing more, and nothing less. You can see that as a bad thing if you want, but I’d argue it’s the project’s central strength.

3 Likes
18

If the talented contributors were a diverse group of individuals, operating independently, and volunteering their time? I’d probably be more sympathetic. However, the vast majority are in-fact employees of the very corporate overseer you described. Given that we’re dealing with a corporation, I naturally expect things like roadmaps, strategy, and plans to exist.

I’m certain they do. However, that information isn’t shared. Which is quite fine! It’s their prerogative. Private corporation. No one is obligated or required to do anything. It is, as you say, an open source project.

On the other hand. When this organization decides (occasionally) to share information publicly? With no obvious benefit, and I’m not privvy to their long-term plans?

Well. It shouldn’t be surprising that I’m not entirely thrilled. For myself, my clients, my business partners, and prospects? This is a near-term burden. We can hope for future dividends. But since when has hope been an effective business strategy?

I think you and I must agree to disagree on this, Peter. :slight_smile:

19

If that’s your expectation, you and I have very different experiences of how decisions are made in corporations :slight_smile:

Edited to add: I understand that most new code is being written by employees of Frappe Inc. What I’m objecting to is the idea that development priorities are being driven by an abstract plan like the kind had by Drupal and Moodle.

Instead, I’m sure there’s an enormous wishlist, which probably doesn’t look that different from yours or mine. So how is one priority chosen over another? Having followed the repos for a few years now, I strongly suspect that the answer, at least 90% of the time, is “because a client wanted it and was willing to pay for it”.

To be clear, I don’t think this is a bad thing, and I am grateful that Frappe is committed to sharing this work with me for free. But, in the end, all discussions on these forums about development priorities really should begin and end with the economic realities of the people doing the work.

1 Like
20

This is exactly how it works at Frappe

This is a completely speculative and in a way accusatory.

If you and your clients depend on ERPNext, then either you could choose to work with Frappe or become a contributor or run your own thing. Expecting people to do what you like for free is unethical.

I don’t know if you chanced upon the Frappe Blog. Things are very much individual driven.

7 Likes
21

This entire line of discussion can be summarized as follows:

  1. Gavin announced the Frappe Query Builder
  2. Various forum members chimed in, expressing enthusiasm.
  3. I chimed in and basically said: “eh, it’s okay, but I’m not thrilled.” That’s just my opinion.
  4. Peter tried to convince me otherwise.
  5. We’ve both agreed to disagree.

The only thing I’ve “accused” you of are the following:

  • Sharing information less-often than I would prefer you did.
  • Prioritzing things differently than I would.
  • Acting in your own best, self-interests. (don’t we all?)
  • Not sharing all your plans with the community. (you’re certainly not obligated to).
  • Disappointing me.

I don’t expect you to do what I like, for free. However, the opposite is also true.

Just because it’s free? That doesn’t mean I have to like it.

4 Likes