frappe-core-database · diff
v2.0 to v2.0
334 added, 144 removed. Audit A to A.
---
name: frappe-core-database
description: >
Use when performing database operations in ERPNext/Frappe v14-v16. Covers
frappe.db methods, ORM patterns (frappe.get_doc, frappe.get_list), raw SQL,
caching patterns, and performance optimization. Prevents common mistakes
with database transactions and query building. Keywords: frappe.db,
frappe.get_doc, database query, SQL, ORM, caching, database performance.
license: MIT
compatibility: "Claude Code, Claude.ai Projects, Claude API. Frappe v14-v16."
metadata:
author: OpenAEC-Foundation
version: "2.0"
---
- # ERPNext Database Operations
-
- ## Quick Overview
+ # Frappe Database Operations
- Frappe provides three abstraction levels for database operations:
+ ## Quick Reference
- | 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 |
+ | Action | Method | Permissions |
+ |--------|--------|-------------|
+ | Get document | `frappe.get_doc(doctype, name)` | Yes |
+ | Cached document | `frappe.get_cached_doc(doctype, name)` | No |
+ | New document | `frappe.new_doc(doctype)` | — |
+ | Insert | `doc.insert()` | Yes |
+ | Save | `doc.save()` | Yes |
+ | Delete document | `frappe.delete_doc(doctype, name)` | Yes |
+ | List (with perms) | `frappe.db.get_list(doctype, ...)` | Yes |
+ | List (no perms) | `frappe.get_all(doctype, ...)` | No |
+ | Single field | `frappe.db.get_value(doctype, name, field)` | No |
+ | Single DocType | `frappe.db.get_single_value(doctype, field)` | No |
+ | Cached value | `frappe.db.get_value(..., cache=True)` | No |
+ | Direct update | `frappe.db.set_value(doctype, name, field, val)` | No |
+ | Direct update | `doc.db_set(field, value)` | No |
+ | Exists check | `frappe.db.exists(doctype, name)` | No |
+ | Count | `frappe.db.count(doctype, filters)` | No |
+ | Delete rows | `frappe.db.delete(doctype, filters)` | No |
+ | Raw SQL | `frappe.db.sql(query, values, as_dict)` | No |
+ | Query Builder | `frappe.qb.from_(doctype).select(...)` | No |
- **RULE**: Always use the highest abstraction level appropriate for your use case.
+ > **"Permissions" = Yes** means user permission filters are applied automatically.
---
## Decision Tree
```
- What do you want to do?
+ What do you need?
│
- ├─ Create/modify/delete document?
- │ └─ frappe.get_doc() + .insert()/.save()/.delete()
+ ├─ Create / Update / Delete a document?
+ │ ├─ With validations + hooks → frappe.get_doc() + .insert()/.save()/.delete()
+ │ └─ Direct DB (no hooks) → frappe.db.set_value() or doc.db_set()
│
- ├─ Get single document?
- │ ├─ Changes frequently? → frappe.get_doc()
- │ └─ Changes rarely? → frappe.get_cached_doc()
+ ├─ Read a single document?
+ │ ├─ Need full object with methods → frappe.get_doc()
+ │ ├─ Read-only, rarely changes → frappe.get_cached_doc()
+ │ └─ Only need 1-2 fields → frappe.db.get_value()
│
├─ List of documents?
- │ ├─ With user permissions? → frappe.db.get_list()
- │ └─ Without permissions? → frappe.get_all()
+ │ ├─ Respect user permissions → frappe.db.get_list()
+ │ └─ System/admin context → frappe.get_all()
│
- ├─ Single field value?
- │ ├─ Regular DocType → frappe.db.get_value()
- │ └─ Single DocType → frappe.db.get_single_value()
+ ├─ Single DocType value?
+ │ └─ frappe.db.get_single_value('Settings', 'field')
│
- ├─ Direct update without triggers?
- │ └─ frappe.db.set_value() or doc.db_set()
+ ├─ Check existence?
+ │ └─ frappe.db.exists() — NEVER use get_doc in try/except
│
- └─ Complex query with JOINs?
- └─ frappe.qb (Query Builder) or frappe.db.sql()
+ ├─ Complex query (JOINs, aggregates)?
+ │ ├─ Cross-DB compatible → frappe.qb (Query Builder)
+ │ └─ DB-specific SQL → frappe.db.sql() with parameters
+ │
+ └─ DB-specific logic?
+ └─ frappe.db.multisql({'mariadb': q1, 'postgres': q2})
```
+ **RULE**: ALWAYS use the highest abstraction level: ORM > Database API > Query Builder > Raw SQL.
+
---
- ## Most Used Patterns
+ ## ORM — Document Operations
### Get Document
```python
- # 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')
+ # Single DocType (no name needed)
+ settings = frappe.get_doc('System Settings')
+
+ # Cached (read-only, for rarely-changing docs)
+ company = frappe.get_cached_doc('Company', 'My Company')
+
+ # Last created
+ last_task = frappe.get_last_doc('Task', filters={'status': 'Open'})
```
- ### List Query
+ ### Create Document
```python
- # With user permissions
+ doc = frappe.get_doc({
+ 'doctype': 'Task',
+ 'subject': 'Review report',
+ 'status': 'Open'
+ })
+ doc.insert()
+
+ # Alternative
+ doc = frappe.new_doc('Task')
+ doc.subject = 'Review report'
+ doc.insert()
+ ```
+
+ ### Update Document
+ ```python
+ # Via ORM — triggers validate, on_update, etc.
+ doc = frappe.get_doc('Task', 'TASK-001')
+ doc.status = 'Completed'
+ doc.save()
+
+ # Direct DB — SKIPS all validations and hooks
+ frappe.db.set_value('Task', 'TASK-001', 'status', 'Completed')
+
+ # Direct DB on loaded doc
+ doc.db_set('status', 'Completed')
+ doc.db_set('status', 'Completed', update_modified=False)
+ doc.db_set({'status': 'Completed', 'priority': 'High'})
+ ```
+
+ ### Delete Document
+ ```python
+ frappe.delete_doc('Task', 'TASK-001')
+ # Also removes linked Communications, Comments, etc.
+ ```
+
+ ### Insert Flags
+ ```python
+ doc.insert(
+ ignore_permissions=True, # Bypass permission check
+ ignore_links=True, # Skip link validation
+ ignore_if_duplicate=True, # No error on duplicate
+ ignore_mandatory=True # Skip required field check
+ )
+ ```
+
+ > **RULE**: NEVER use multiple ignore flags together unless you have a documented reason. Each flag you add weakens data integrity.
+
+ ---
+
+ ## Database API — Reading
+
+ ### get_value
+ ```python
+ # Single field → scalar
+ status = frappe.db.get_value('Task', 'TASK-001', 'status')
+
+ # Multiple fields → tuple
+ subject, status = frappe.db.get_value('Task', 'TASK-001', ['subject', 'status'])
+
+ # As dict
+ data = frappe.db.get_value('Task', 'TASK-001', ['subject', 'status'], as_dict=True)
+
+ # With filters instead of name
+ status = frappe.db.get_value('Task', {'project': 'PROJ-001'}, 'status')
+
+ # Cached (for values that rarely change)
+ country = frappe.db.get_value('Company', 'MyCompany', 'country', cache=True)
+ ```
+
+ ### get_single_value
+ ```python
+ timezone = frappe.db.get_single_value('System Settings', 'time_zone')
+ ```
+
+ ### get_list / get_all
+ ```python
+ # get_list — applies user permissions
tasks = frappe.db.get_list('Task',
filters={'status': 'Open'},
- fields=['name', 'subject'],
+ fields=['name', 'subject', 'assigned_to'],
order_by='creation desc',
+ start=0,
page_length=50
)
- # Without permissions
+ # get_all — NO permission check (same API, different default)
all_tasks = frappe.get_all('Task', filters={'status': 'Open'})
+
+ # pluck — returns flat list of single field
+ names = frappe.get_all('Task', filters={'status': 'Open'}, pluck='name')
+ # Returns: ['TASK-001', 'TASK-002', ...]
```
- ### Single Value
+ ### exists / count
```python
+ exists = frappe.db.exists('User', 'admin@example.com')
+ exists = frappe.db.exists('User', {'email': 'admin@example.com'})
+
+ total = frappe.db.count('Task')
+ open_count = frappe.db.count('Task', {'status': 'Open'})
+ ```
+
+ ---
+
+ ## Filter Operators
+
+ ```python
+ {'status': 'Open'} # =
+ {'status': ['!=', 'Cancelled']} # !=
+ {'amount': ['>', 1000]} # >
+ {'amount': ['>=', 1000]} # >=
+ {'status': ['in', ['Open', 'Working']]} # IN
+ {'status': ['not in', ['Cancelled', 'Closed']]} # NOT 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
+ ```
+
+ ### Combining Filters
+ ```python
+ # AND — all conditions in one dict
+ filters = {'status': 'Open', 'priority': 'High'}
+
+ # AND — list format (allows duplicate fields)
+ filters = [['status', '=', 'Open'], ['priority', '=', 'High']]
+
+ # OR — separate parameter
+ or_filters = {'priority': 'Urgent', 'status': 'Overdue'}
+ ```
+
+ ---
+
+ ## Database API — Writing
+
+ ### set_value
+ ```python
# Single field
- status = frappe.db.get_value('Task', 'TASK001', 'status')
+ frappe.db.set_value('Task', 'TASK-001', 'status', 'Closed')
# Multiple fields
- subject, status = frappe.db.get_value('Task', 'TASK001', ['subject', 'status'])
+ frappe.db.set_value('Task', 'TASK-001', {'status': 'Closed', 'priority': 'Low'})
- # As dict
- data = frappe.db.get_value('Task', 'TASK001', ['subject', 'status'], as_dict=True)
+ # Without updating modified timestamp
+ frappe.db.set_value('Task', 'TASK-001', 'status', 'Closed', update_modified=False)
```
- ### Create Document
+ ### delete / truncate
```python
- doc = frappe.get_doc({
- 'doctype': 'Task',
- 'subject': 'New Task',
- 'status': 'Open'
- })
- doc.insert()
+ # Delete with filters (DML — can be rolled back)
+ frappe.db.delete('Error Log', {'creation': ['<', '2024-01-01']})
+
+ # Truncate (DDL — CANNOT be rolled back)
+ frappe.db.truncate('Error Log')
```
- ### Update Document
+ ### bulk_update [v15+]
```python
- # 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')
+ frappe.db.bulk_update('Task', {
+ 'TASK-001': {'status': 'Closed'},
+ 'TASK-002': {'status': 'Closed'}
+ }, chunk_size=100)
```
---
- ## Filter Operators
+ ## Raw SQL — ALWAYS Parameterized
```python
- {'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
+ # ✅ CORRECT — parameterized query
+ results = frappe.db.sql("""
+ SELECT name, subject FROM `tabTask`
+ WHERE status = %(status)s AND owner = %(owner)s
+ """, {'status': 'Open', 'owner': frappe.session.user}, as_dict=True)
```
+ > **CRITICAL**: NEVER use f-strings, % formatting, or string concatenation in SQL. See [SQL Injection Prevention](#sql-injection-prevention).
+
+ ### Return Types
+ ```python
+ frappe.db.sql(query) # Tuple of tuples (default)
+ frappe.db.sql(query, as_dict=True) # List of dicts
+ frappe.db.sql(query, as_list=True) # List of lists
+ ```
+
+ ### Table Naming
+ ALWAYS use backtick-quoted `tab` prefix: `` `tabSales Invoice` ``, `` `tabTask` ``
+
+ ### Database-Specific SQL
+ ```python
+ frappe.db.multisql({
+ 'mariadb': "SELECT IFNULL(field, 0) FROM `tabDoc`",
+ 'postgres': "SELECT COALESCE(field, 0) FROM `tabDoc`"
+ })
+ ```
+
---
- ## Query Builder (frappe.qb)
+ ## Query Builder (frappe.qb) [v14+]
+ The Query Builder uses PyPika under the hood. It generates parameterized SQL automatically.
+
```python
Task = frappe.qb.DocType('Task')
results = (
frappe.qb.from_(Task)
- .select(Task.name, Task.subject)
+ .select(Task.name, Task.subject, Task.status)
.where(Task.status == 'Open')
.orderby(Task.creation, order='desc')
.limit(10)
).run(as_dict=True)
```
- ### With JOIN
+ ### JOINs
```python
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)
+ .inner_join(Customer).on(SI.customer == Customer.name)
+ .select(SI.name, SI.grand_total, Customer.customer_name)
.where(SI.docstatus == 1)
).run(as_dict=True)
```
+ ### Aggregates
+ ```python
+ from frappe.query_builder.functions import Count, Sum, Avg
+
+ stats = (
+ frappe.qb.from_(Task)
+ .select(Task.status, Count(Task.name).as_('count'))
+ .groupby(Task.status)
+ ).run(as_dict=True)
+ ```
+
+ ### OR Conditions
+ ```python
+ customers = frappe.qb.DocType('Customer')
+ results = (
+ frappe.qb.from_(customers)
+ .select(customers.name)
+ .where(
+ (customers.territory == 'US') | (customers.territory == 'UK')
+ )
+ ).run(as_dict=True)
+ ```
+
+ ### Inspect Generated SQL
+ ```python
+ query = frappe.qb.from_(Task).select('*').where(Task.name == 'X')
+ sql, params = query.walk() # Returns (sql_string, param_dict)
+ sql_str = query.get_sql() # Returns SQL string
+ ```
+
+ > See `references/query-patterns.md` for subqueries, ImportMapper, ConstantColumn, and custom functions.
+
---
## Caching
- ### Basics
+ ### Document Cache
```python
- # 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)
+ doc = frappe.get_cached_doc('Company', 'My Company') # Full document
+ val = frappe.db.get_value('Company', 'X', 'country', cache=True) # Single value
+ ```
- # Delete
+ ### Redis Cache
+ ```python
+ frappe.cache.set_value('key', data, expires_in_sec=3600)
+ data = frappe.cache.get_value('key')
frappe.cache.delete_value('key')
```
### @redis_cache Decorator
```python
from frappe.utils.caching import redis_cache
- @redis_cache(ttl=300) # 5 minutes
+ @redis_cache(ttl=300)
def get_dashboard_data(user):
return expensive_calculation(user)
- # Invalidate cache
+ # Invalidate
get_dashboard_data.clear_cache()
```
+ > See `references/caching-patterns.md` for hash operations, invalidation strategies, and best practices.
+
---
- ## Transactions
+ ## Transaction Management
- Framework manages transactions automatically:
+ The framework manages transactions automatically:
| Context | Commit | Rollback |
|---------|--------|----------|
- | POST/PUT request | After success | On exception |
+ | POST/PUT request | After success | On uncaught exception |
+ | GET request | Never | — |
| Background job | After success | On exception |
+ | Patch | After success | On exception |
- ### Manual (rarely needed)
+ ### Manual Transactions (rarely needed)
```python
- frappe.db.savepoint('my_savepoint')
+ frappe.db.savepoint('before_payment')
try:
- # operations
+ # operations...
frappe.db.commit()
- except:
- frappe.db.rollback(save_point='my_savepoint')
+ except Exception:
+ frappe.db.rollback(save_point='before_payment')
```
+ ### Transaction Hooks [v15+]
+ ```python
+ frappe.db.after_commit.add(sync_to_external_system)
+ frappe.db.after_rollback.add(cleanup_external_state)
+ ```
+
---
- ## Critical Rules
+ ## SQL Injection Prevention
- ### 1. NEVER Use String Formatting in SQL
+ **CRITICAL SECURITY RULE**: NEVER interpolate user input into SQL strings.
+
```python
- # ❌ SQL Injection risk!
+ # ❌ VULNERABLE — SQL injection risk
frappe.db.sql(f"SELECT * FROM `tabUser` WHERE name = '{user_input}'")
+ frappe.db.sql("SELECT * FROM `tabUser` WHERE name = '%s'" % user_input)
+ frappe.db.sql("SELECT * FROM `tabUser` WHERE name = " + user_input)
- # ✅ Parameterized
+ # ✅ SAFE — parameterized query
frappe.db.sql("SELECT * FROM `tabUser` WHERE name = %(name)s", {'name': user_input})
- ```
- ### 2. NEVER Commit in Controller Hooks
- ```python
- # ❌ WRONG
- def validate(self):
- frappe.db.commit() # Never do this!
-
- # ✅ Framework handles commits
- ```
+ # ✅ SAFE — ORM / Query Builder (always parameterized)
+ frappe.get_all('User', filters={'name': user_input})
- ### 3. ALWAYS Paginate
- ```python
- # ✅ Always limit
- docs = frappe.get_all('Sales Invoice', page_length=100)
+ User = frappe.qb.DocType('User')
+ frappe.qb.from_(User).select('*').where(User.name == user_input).run()
```
- ### 4. Avoid N+1 Queries
- ```python
- # ❌ N+1 problem
- for name in names:
- doc = frappe.get_doc('Customer', name)
-
- # ✅ Batch fetch
- docs = frappe.get_all('Customer', filters={'name': ['in', names]})
- ```
+ **RULE**: When you MUST use `frappe.db.sql()`, ALWAYS use `%(param)s` placeholders with a dict. The Query Builder (`frappe.qb`) is ALWAYS preferred over raw SQL for new code.
---
## Version Differences
| Feature | v14 | v15 | v16 |
|---------|-----|-----|-----|
- | Transaction hooks | ❌ | ✅ | ✅ |
- | bulk_update | ❌ | ✅ | ✅ |
- | Aggregate syntax | String | String | Dict |
+ | Query Builder (frappe.qb) | Yes | Yes | Yes |
+ | Transaction hooks | No | Yes | Yes |
+ | `bulk_update` | No | Yes | Yes |
+ | `run=False` returns | SQL string | SQL string | Query Builder object |
+ | Aggregate field syntax | String | String | Dict |
- ### v16 Aggregate Syntax
+ ### v16 Breaking Changes
```python
- # v14/v15
+ # v14/v15 — string aggregates
fields=['count(name) as count']
- # v16
+ # v16 — dict aggregates
fields=[{'COUNT': 'name', 'as': 'count'}]
+
+ # v14/v15 — run=False returns SQL string
+ sql = frappe.db.get_list('Task', run=False)
+
+ # v16 — run=False returns Query Builder object
+ qb_obj = frappe.db.get_list('Task', run=False)
+ sql = qb_obj.get_sql()
```
---
- ## Reference Files
-
- See the `references/` folder for detailed documentation:
+ ## Critical Rules Summary
- - **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
+ 1. **NEVER** use string formatting in SQL — ALWAYS use parameterized queries
+ 2. **NEVER** call `frappe.db.commit()` inside controller hooks (validate, on_update, etc.)
+ 3. **ALWAYS** paginate list queries — use `page_length` parameter
+ 4. **ALWAYS** specify fields — NEVER use `fields=['*']` in production
+ 5. **ALWAYS** use `frappe.db.exists()` for existence checks — NEVER try/except with get_doc
+ 6. **ALWAYS** prefix table names with `tab` in raw SQL: `` `tabSales Invoice` ``
+ 7. **NEVER** use multiple ignore flags without documented justification
+ 8. **ALWAYS** use batch fetching to avoid N+1 queries
+ 9. **ALWAYS** prefer `frappe.qb` over `frappe.db.sql()` for new code
+ 10. **NEVER** use `frappe.db.truncate()` without understanding it CANNOT be rolled back
---
- ## Quick Reference
+ ## Reference Files
- | 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_()` |
+ - **[methods-reference.md](references/methods-reference.md)** — Complete API signatures for all database and document methods
+ - **[query-patterns.md](references/query-patterns.md)** — Query Builder patterns, subqueries, ImportMapper, custom functions
+ - **[caching-patterns.md](references/caching-patterns.md)** — Redis cache, @redis_cache, hash operations, invalidation
+ - **[examples.md](references/examples.md)** — Real-world patterns: CRUD, reports, batch processing, transactions
+ - **[anti-patterns.md](references/anti-patterns.md)** — SQL injection, N+1, commit mistakes, and 10 more anti-patterns