Skip to content
Skills Library
Programming · Building & coding

frappe-database-orm

/frappe-database-orm

Use when reading or writing Frappe data safely — get_doc, get_all, get_list, frappe.db.get_value, parameterized frappe.db.sql, transactions, bulk ops, and performance. Prevents SQL injection and permission leaks from wrong API choice. Covers ORM reads/writes, raw SQL, transactions, bulk_update, N+1 avoidance. Keywords: frappe.db.sql, frappe.get_all, get_value, set_value, bulk update, N+1, db transaction, get_list, SQL injection, parameterized query.

Licence
MIT
Last repository change
19.09.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
3
Deprecation

No signals detected. Repository changed on 19.09.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

Use when reading or writing Frappe data safely — get_doc, get_all, get_list, frappe.db.get_value, parameterized frappe.db.sql, transactions, bulk ops, and performance. Prevents SQL injection and permission leaks from wrong API choice. Covers ORM reads/writes, raw SQL, transactions, bulk_update, N+1 avoidance. Keywords: frappe.db.sql, frappe.get_all, get_value, set_value, bulk update, N+1, db transaction, get_list, SQL injection, parameterized query.

Original author description

Use when reading or writing Frappe data safely — get_doc, get_all, get_list, frappe.db.get_value, parameterized frappe.db.sql, transactions, bulk ops, and performance. Prevents SQL injection and permission leaks from wrong API choice. Covers ORM reads/writes, raw SQL, transactions, bulk_update, N+1 avoidance. Keywords: frappe.db.sql, frappe.get_all, get_value, set_value, bulk update, N+1, db transaction, get_list, SQL injection, parameterized query.

How it works

  1. Quick Reference
  2. Reads
  3. Writes
  4. Raw SQL — parameterized ONLY
  5. Transactions
  6. Bulk update (v14+)
  7. v16 getlist / run=False change
  8. Transaction hooks (v15+)
  9. frappe.qb.getquery (preferred for complex queries)
  10. Performance
Original SKILL.md headings · 144 lines, 578 words
SKILL.md preview
---
name: frappe-database-orm
description: >
  Use when reading or writing Frappe data safely — get_doc, get_all, get_list, frappe.db.get_value,
  parameterized frappe.db.sql, transactions, bulk ops, and performance. Prevents SQL injection and
  permission leaks from wrong API choice.
  Covers ORM reads/writes, raw SQL, transactions, bulk_update, N+1 avoidance.
  Keywords: frappe.db.sql, frappe.get_all, get_value, set_value, bulk update, N+1, db transaction,
  get_list, SQL injection, parameterized query.
license: MIT
compatibility: "Claude Code, Claude.ai Projects, Claude API. Frappe v14-v16."
metadata:
  author: sbknext
  version: "1.0"
---

# Frappe Database / ORM

Prefer the ORM; drop to SQL only when needed, and only parameterized.

Cross-ref: `frappe-syntax-query-builder` (frappe.qb deep dive), `frappe-errors-database` (debugging).

---

## Quick Reference

| Need | API | Permissions |
|------|-----|-------------|
| Full document | `frappe.get_doc(dt, name)` | Checks read |
| One field, fast | `frappe.db.get_value(dt, name, field)` | No check |
| List (server-side) | `frappe.get_all(...)` | **Ignores** user perms |
| List (user-facing) | `frappe.get_list(...)` | **Enforces** user perms |
| Single field write, no hooks | `frappe.db.set_value(...)` | No lifecycle |
| Full lifecycle write | `doc.save()` | Runs validate/hooks |
| Complex dynamic SQL | `frappe.qb` or `%s` parameterized SQL | — |

---

## Reads

```python
frappe.get_doc("Sales Order", "SO-0001")                    # full document (all children)
frappe.db.get_value("Sales Order", "SO-0001", "status")     # one field, fast
frappe.db.get_value("Sales Order", {"customer": "ACME"}, ["name", "status"], as_dict=True)
frappe.get_all("Sales Order",
    filters={"status": "Draft", "grand_total": [">", 1000]},
    fields=["name", "customer", "grand_total"],
    order_by="creation desc", limit=50)
frappe.get_list(...)   # same, but enforces the current user's permissions
```

`get_all` ignores user permissions (use server-side); `get_list` enforces them (use for user-facing).

## Writes

```python
frappe.db.set_value("Sales Order", "SO-0001", "status", "Closed")          # single field, no hooks
doc = frappe.get_doc("Sales Order", "SO-0001"); doc.status = "Closed"; doc.save()  # runs validate/hooks
frappe.db.set_value("Sales Order", {"customer": "ACME"}, "hold", 1)        # by filter
```

`db.set_value` skips controller hooks/validation — fast but bypasses business logic. `doc.save()`
runs the full lifecycle. Choose deliberately.

## Raw SQL — parameterized ONLY

```python
# RIGHT — %s placeholders
rows = frappe.db.sql("""
    SELECT customer, SUM(grand_total) AS total
    FROM `tabSales Order` WHERE status = %s AND company = %s
    GROUP BY customer
""", ("Draft", company), as_dict=True)

# WRONG — string interpolation = SQL injection
frappe.db.sql(f"... WHERE name = '{name}'")     # NEVER
```

Escape identifiers/values with `frappe.db.escape(value)` when you must build dynamically.

## Transactions

```python
frappe.db.savepoint("before_bulk")
try:
    ...
    frappe.db.commit()
except Exception:
    frappe.db.rollback(save_point="before_bulk")
```

Web requests auto-commit on success / rollback on exception. Background jobs must commit explicitly.

## Bulk update (v14+)

Update many documents in one SQL statement — skips controller hooks/validation:

```python
frappe.db.bulk_update(
    "Task",
    {
        "TASK-0001": {"status": "Closed"},
        "TASK-0002": {"status": "Open"},
    },
    chunk_size=200,
    update_modified=True,
)
```

Use when you need speed and know lifecycle hooks are not required.

## v16 `get_list` / `run=False` change

Prior to v16, `run=False` on `get_list` returned a SQL string. From v16 it returns a Query Builder object — call `.get_sql()` for the string or mutate before `.run()`.

## Transaction hooks (v15+)

```python
frappe.db.after_commit.add(my_func)
frappe.db.after_rollback.add(cleanup_func)
```

## frappe.qb.get_query (preferred for complex queries)

```python
query = frappe.qb.get_query("Sales Order",
    fields=["name", "customer.customer_name as customer_name",
            {"items": ["item_code", "qty"]}],
    filters={"docstatus": 1, "items.item_code": "ITEM-001"},
    limit=50)
rows = query.run(as_dict=True)
```

Use `ignore_permissions=False` when the query serves user-facing data. Use `for_update=True` for row locking.

## Performance

- Avoid **N+1**: fetch with one `get_all(filters={"parent": ["in", names]})` instead of looping `get_doc`.
- Select only the `fields` you need — never pull whole docs to read one column.
- Add DB indexes on hot filter/join columns (`search_index: 1` on the field, or a migration).
- For large bulk writes use `frappe.db.bulk_insert` / batched `set_value`, and commit periodically.

## Rules

- Never f-string/`%`-format values into `frappe.db.sql` — always `%s` params.
- `get_all` vs `get_list` — know which one applies permissions.
- `db.set_value`/`db.delete` skip hooks; use `doc.save()`/`doc.delete()` when lifecycle matters.
- Web requests auto-commit on successful POST/PUT; GET does not commit. Background jobs auto-commit on success.
- Rarely call `frappe.db.commit()` manually — exception: flush writes before `enqueue_after_commit` reads them.

## From Frappe docs

- Database API: https://docs.frappe.io/framework/user/en/api/database
- Query Builder: https://docs.frappe.io/framework/user/en/api/query-builder

---

*Contributed from sbknext/forge-frappe-skill (MIT) — https://github.com/sbknext/forge-frappe-skill*

Versions

The database has no other skills named frappe-database-orm.

Identical content appears in 2 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version fe21fc36a702 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100frappe-syntax-query-builder
    Use when building database queries with frappe.qb in Frappe v14-v16. Covers the PyPika-based query builder: SELECT, INSERT, UPDATE, DELETE, joins, aggregation, subqueries, cross-DB compatibility (MariaDB/PostgreSQL), and migration from raw SQL. Prevents SQL injection and…
  • 100frappe-errors-database
    Use when handling database errors in Frappe/ERPNext. Covers DuplicateEntryError, LinkValidationError, MandatoryError, TimestampMismatchError, CharacterLengthExceededError, InReadOnlyMode, QueryTimeoutError, SQL injection errors, frappe.db.sql parameter format (% vs %s),…
  • 100frappe-manager
    Use Frappe Manager (FM) for Docker-based development and testing environments. Use when setting up local dev, running isolated tests, or managing agent-driven Frappe development workflows.
  • 100reports
    Create reports in Frappe including Report Builder, Query Reports (SQL), and Script Reports (Python + JS). Use when building data analysis views, dashboards, or custom reporting features.
  • 100frappe-documentation-generator
    Generate API documentation, user guides, and technical documentation for Frappe apps. Use when documenting APIs, creating user guides, or generating OpenAPI specs.
  • 100frappe-performance-optimizer
    Generate optimized queries, caching, and indexes for Frappe performance. Use when optimizing slow queries, implementing caching, or improving performance.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.