/frappe-core-database

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,
  query returns nothing, slow database, how to fetch data, get document by name, frappe.get_list empty.
license: MIT
compatibility: "Claude Code, Claude.ai Projects, Claude API. Frappe v14-v16."
metadata:
  author: OpenAEC-Foundation
  version: "2.0"

Frappe Database Operations

Quick Reference

| 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 |

> **"Permissions" = Yes** means user permission filters are applied automatically.

---

Decision Tree

What do you need?
│
├─ 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()
│
├─ 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?
│  ├─ Respect user permissions → frappe.db.get_list()
│  └─ System/admin context → frappe.get_all()
│
├─ Single DocType value?
│  └─ frappe.db.get_single_value('Settings', 'field')
│
├─ Check existence?
│  └─ frappe.db.exists() — NEVER use get_doc in try/except
│
├─ 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.

---

ORM: Document Operations

Get Document

doc = frappe.get_doc('Sales Invoice', 'SINV-00001')

# 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'})

Create Document

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

# 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

frappe.delete_doc('Task', 'TASK-001')
# Also removes linked Communications, Comments, etc.

Insert Flags

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

# 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

timezone = frappe.db.get_single_value('System Settings', 'time_zone')

get_list / get_all

# get_list — applies user permissions
tasks = frappe.db.get_list('Task',
    filters={'status': 'Open'},
    fields=['name', 'subject', 'assigned_to'],
    order_by='creation desc',
    start=0,
    page_length=50
)

# 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', ...]

exists / count

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.cou