frappe-core-database

# 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
```python
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
```python
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', '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
```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']}