How to use
- Copy the installation command and review the source package before installing it in your own agent environment.
- Check the source document for required tools, credentials and supported hosts.
- Describe your input and desired deliverable; review the output against the source material.
npx skills add https://github.com/sbknext/forge-frappe-skill --skill frappe-database-ormBefore you start
- Installation does not include a model subscription, API allowance or permission to access your files.
- Repository stars and installation counts describe activity, not an independent quality or security test.
Rendered from the source repository. Headings, examples, code, tables, links, and referenced images are preserved.
Frappe Database / ORM
Prefer the ORM; drop to SQL only when needed, and only parameterized.
Reads
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 permissionsget_all ignores user permissions (use server-side); get_list enforces them (use for user-facing).
Writes
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 filterdb.set_value skips controller hooks/validation — fast but bypasses business logic. doc.save() runs the full lifecycle. Choose deliberately.
Raw SQL — parameterized ONLY
# 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}'") # NEVEREscape identifiers/values with frappe.db.escape(value) when you must build dynamically.
Query Builder (frappe.qb)
from frappe.query_builder import DocType
from frappe.query_builder.functions import Sum
SO = DocType("Sales Order")
q = (frappe.qb.from_(SO)
.select(SO.customer, Sum(SO.grand_total).as_("total"))
.where(SO.status == "Draft").groupby(SO.customer))
data = q.run(as_dict=True)Composable and injection-safe; preferred over raw SQL for dynamic queries. All frappe.qb queries are parameterized by default — use .run(as_dict=True) to execute. Inspect SQL with str(query) or .get_sql(). Prefer passing query objects to .run() over raw frappe.db.sql(query) (the latter skips parameterization).
Source: https://docs.frappe.io/framework/user/en/api/query-builder
Transactions
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:
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().
v16 also prefers dict-style aggregate fields:
frappe.db.get_list("Task",
fields=[{"COUNT": "name", "as": "count"}, "status"],
group_by="status")Transaction hooks (v15+)
Register callbacks tied to commit/rollback (useful for filesystem side-effects):
frappe.db.after_commit.add(my_func)
frappe.db.after_rollback.add(cleanup_func)Web POST/PUT auto-commit on success; background jobs must call frappe.db.commit() explicitly.
frappe.qb.get_query (preferred for complex queries)
Modern query builder — auto-joins Link/child fields, supports aggregations and OR filters:
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 loopingget_doc. - Select only the
fieldsyou need — never pull whole docs to read one column. - Add DB indexes on hot filter/join columns (
search_index: 1on the field, or a migration). - For large bulk writes use
frappe.db.bulk_insert/ batchedset_value, and commit periodically.
Rules
- Never f-string/
%-format values intofrappe.db.sql— always%sparams. get_allvsget_list— know which one applies permissions.db.set_value/db.deleteskip hooks; usedoc.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 beforeenqueue_after_commitreads them.
From Frappe docs
- Database API (
get_list,get_all,bulk_update, transaction model): https://docs.frappe.io/framework/user/en/api/database - Query Builder
frappe.qb.get_query: https://docs.frappe.io/framework/get_query