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 1. 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 } ) 2. NEVER Commit in Controller Hooks

❌ WRONG

def validate ( self ) : frappe . db . commit ( )

Never do this!

✅ Framework handles commits

1. ALWAYS Paginate

✅ Always limit

docs

frappe . get_all ( 'Sales Invoice' , page_length = 100 ) 4. 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_()