Due to my past dev experience, I’ve become somewhat accustomed to using ORMs extensively. Now, I’m still a beginner when it comes to Frappe, and I’ve done some searching and discussions/mentions of this topic dates back to 2012/13. From these, there were “plans” on integrating SQLAlchemy with the framework, but seems like it’s still in the works/roadmap.
My question is, has someone else managed to make use of SQLALchemy in their Frappe apps?
The Frappe Framework’s document model is very ORM-like. While it doesn’t have all the features of SQLAlchemy, they share much in common.
Not sure what the use-cases would be for trying to bolt them together.
Hi @redgren. To add to what Brian is saying, I think that trying to integrate a SQL interface like alchemy would actually constitute a bad design pattern. Frappe has a full document API, which provides ORM-like abstractions, and for apps to read/write the database directly is risky. Is there a particular feature you’re looking for that’s not available in the document APIs?
What I can’t seem to be able to do in Frappe can be demonstrated by the following example:
invoice.customer.address.city
Where each of these are Linked Doctypes
I had thought this was possible with frappe.db.get_value, but on closer look it appears not. I see what you’re saying; there’s no simple way to do sql joins effectively in a single API call. Getting what you’re describing would require walking down the chain.
There are some justifications for that as a primary design, the biggest one being that Frappe tends to focus on immutability after a document becomes submitted. Using invoice as an example, beyond the link chain, customer address is stored immutably in the invoice itself as the address_display field. From one perspective that violates relational database design, but it’s also necessary for immutability. If a customer’s address changes, generally that value shouldn’t change in past invoices.
Nevertheless, there would be value in a more robust ORM for python calls, and I’d think this could be implemented into the get_value call without breaking current functionality or design. When designing or customizing doctypes, it’s possible to follow sequential links like invoice.customer.address for the fetch_from property, but not for api calls directly as far as I can tell.
Yes, I’ve used this in the fetch_from field, so I thought it should be possible with the DB API.
In the fetch_from, i doesn’t go all the way up the hierarchy, only one Link up I’ve come to learn.
But since you say this could be implemented without breaking current functionality, maybe it’ll be added. Perhaps you could help open a feature request on the repo 
That’s a very good example. Addresses definitely must be stored on an Invoice, for the reasons you mentioned.
There are equally some bad practices too. tabPurchase Order.total_qty is one such example. There’s no good justification for storing this on the PO header. And if 2+ lines have different units of measure, the total_qty field becomes entirely meaningless.
Definitely. It would be nice to see more Join capabilities, or dot notation like @redgren mentioned. To accomplish this, DocTypes and DocFields would likely need additional metadata. Perhaps indicating the Cardinality for Links.
Invoice--> Customer is a simple Many:1 relationship.Sales Order --> Delivery Note(s) --> Invoice(s) .Invoice --> Delivery Note(s) --> Sales OrderGreat ideas, both.
It’d take some thought, and I can imagine two primary ways to approach the issue. The first would be as simply a developer convenience, using dot notation as a way to programmatically walk down a link chain. This would be relatively straight forward to implement, I’d think, but there wouldn’t be any performance gains over the current approach.
The second way would be to implement join semantics, like sql alchemy does. This would be a lot more complicated, especially where interfacing with permissions, and I think there would be a mass of edge cases to deal with.
Yeah, it’s tricky. As a matter of principle, you don’t generally want computed data stored in fields, but I understand why some folks want to be able to access this data with normal form displays and API functions. It’s really a hardcoded cache, in a sense. I’ve been thinking lately about the viability of a “computed” field type, using either python or mariadb’s calculated columns mechanism. It would be powerful, but potentially complicated (especially at scale).
Yeah, to cover all those cases it’d get complicated. As a first step, dot notation might cover just forward links (invoice → customer) and not back links (sales order ← delivery note). Limited in that way, I suspect that implementation could be reasonably straightforward.
More broadly, I’m not sure that true ORM functionality really fits with Frappe’s design, at least to the extent that documents represent real-world objects/transactions and not just normalized data. In other words, I wonder if some of this functionality should be understood not as a true-and-proper data abstraction but as a developer convenience, empowering cleaner and more expressive code.
@peterg for my own defined documents I use this code
So, for an instance eg : doc = frappe.get_doc('My Doc', 'ID') it will allow you to navigate across links and relations using attributes