ERPNext Database Operations
Quick Overview
Frappe provides three abstraction levels for database operations:
Level API Usage
High-level ORM frappe.get_doc , frappe.new_doc
Document CRUD with validations
Mid-level Query frappe.db.get_list , frappe.db.get_value
Reading with filters
Low-level SQL frappe.db.sql , frappe.qb
Complex queries, reports
RULE: Always use the highest abstraction level appropriate for your use case.
Decision Tree
What do you want to do? │ ├─ Create/modify/delete document? │ └─ frappe.get_doc() + .insert()/.save()/.delete() │ ├─ Get single document? │ ├─ Changes frequently? → frappe.get_doc() │ └─ Changes rarely? → frappe.get_cached_doc() │ ├─ List of documents? │ ├─ With user permissions? → frappe.db.get_list() │ └─ Without permissions? → frappe.get_all() │ ├─ Single field value? │ ├─ Regular DocType → frappe.db.get_value() │ └─ Single DocType → frappe.db.get_single_value() │ ├─ Direct update without triggers? │ └─ frappe.db.set_value() or doc.db_set() │ └─ Complex query with JOINs? └─ frappe.qb (Query Builder) or frappe.db.sql()
Most Used Patterns
Get Document
With ORM (triggers validations)
doc = frappe.get_doc('Sales Invoice', 'SINV-00001')
Cached (faster for frequently accessed docs)
doc = frappe.get_cached_doc('Company', 'My Company')
List Query
With user permissions
tasks = frappe.db.get_list('Task', filters={'status': 'Open'}, fields=['name', 'subject'], order_by='creation desc', page_length=50 )
Without permissions
all_tasks = frappe.get_all('Task', filters={'status': 'Open'})
Single Value
Single field
status = frappe.db.get_value('Task', 'TASK001', 'status')
Multiple fields
subject, status = frappe.db.get_value('Task', 'TASK001', ['subject', 'status'])
As dict
data = frappe.db.get_value('Task', 'TASK001', ['subject', 'status'], as_dict=True)
Create Document
doc = frappe.get_doc({ 'doctype': 'Task', 'subject': 'New Task', 'status': 'Open' }) doc.insert()
Update Document
Via ORM (with validations)
doc = frappe.get_doc('Task', 'TASK001') doc.status = 'Completed' doc.save()
Direct (without validations) - use carefully!
frappe.db.set_value('Task', 'TASK001', 'status', 'Completed')
Filter Operators
{'status': 'Open'} # = {'status': ['!=', 'Cancelled']} # != {'amount': ['>', 1000]} # > {'amount': ['>=', 1000]} # >= {'status': ['in', ['Open', 'Working']]} # IN {'date': ['between', ['2024-01-01', '2024-12-31']]} # BETWEEN {'subject': ['like', '%urgent%']} # LIKE {'description': ['is', 'set']} # IS NOT NULL {'description': ['is', 'not set']} # IS NULL
Query Builder (frappe.qb)
Task = frappe.qb.DocType('Task')
results = ( frappe.qb.from_(Task) .select(Task.name, Task.subject) .where(Task.status == 'Open') .orderby(Task.creation, order='desc') .limit(10) ).run(as_dict=True)
With JOIN
SI = frappe.qb.DocType('Sales Invoice') Customer = frappe.qb.DocType('Customer')
results = ( frappe.qb.from_(SI) .inner_join(Customer) .on(SI.customer == Customer.name) .select(SI.name, Customer.customer_name) .where(SI.docstatus == 1) ).run(as_dict=True)
Caching
Basics
Set/Get
frappe.cache.set_value('key', 'value') value = frappe.cache.get_value('key')
With expiry
frappe.cache.set_value('key', 'value', expires_in_sec=3600)
Delete
frappe.cache.delete_value('key')
@redis_cache Decorator
from frappe.utils.caching import redis_cache
@redis_cache(ttl=300) # 5 minutes def get_dashboard_data(user): return expensive_calculation(user)
Invalidate cache
get_dashboard_data.clear_cache()
Transactions
Framework manages transactions automatically:
Context Commit Rollback
POST/PUT request After success On exception
Background job After success On exception
Manual (rarely needed)
frappe.db.savepoint('my_savepoint') try: # operations frappe.db.commit() except: frappe.db.rollback(save_point='my_savepoint')
Critical Rules
- NEVER Use String Formatting in SQL
❌ SQL Injection risk!
frappe.db.sql(f"SELECT * FROM tabUser WHERE name = '{user_input}'")
✅ Parameterized
frappe.db.sql("SELECT * FROM tabUser WHERE name = %(name)s", {'name': user_input})
- NEVER Commit in Controller Hooks
❌ WRONG
def validate(self): frappe.db.commit() # Never do this!
✅ Framework handles commits
- ALWAYS Paginate
✅ Always limit
docs = frappe.get_all('Sales Invoice', page_length=100)
- Avoid N+1 Queries
❌ N+1 problem
for name in names: doc = frappe.get_doc('Customer', name)
✅ Batch fetch
docs = frappe.get_all('Customer', filters={'name': ['in', names]})
Version Differences
Feature v14 v15 v16
Transaction hooks ❌ ✅ ✅
bulk_update ❌ ✅ ✅
Aggregate syntax String String Dict
v16 Aggregate Syntax
v14/v15
fields=['count(name) as count']
v16
fields=[{'COUNT': 'name', 'as': 'count'}]
Reference Files
See the references/ folder for detailed documentation:
-
methods-reference.md - All Database and Document API methods
-
query-patterns.md - Filter operators and Query Builder syntax
-
caching-patterns.md - Redis cache patterns and @redis_cache
-
examples.md - Complete working examples
-
anti-patterns.md - Common mistakes and how to avoid them
Quick Reference
Action Method
Get document frappe.get_doc(doctype, name)
Cached document frappe.get_cached_doc(doctype, name)
New document frappe.new_doc(doctype) or frappe.get_doc({...})
Save document doc.save()
Insert document doc.insert()
Delete document doc.delete() or frappe.delete_doc()
Get list frappe.db.get_list() / frappe.get_all()
Single value frappe.db.get_value()
Single value frappe.db.get_single_value()
Direct update frappe.db.set_value() / doc.db_set()
Exists check frappe.db.exists()
Count records frappe.db.count()
Raw SQL frappe.db.sql()
Query Builder frappe.qb.from_()