sbknext/forge-frappe-skill

frappe-database-orm

Query and mutate data in Frappe safely — getdoc/getall/getlist, frappe.db.getvalue/setvalue, parameterized frappe.db.sql, Query Builder (frappe.qb), transactions, bulk ops, and performance.

Quelltext ansehen

Verwendung

  1. Installationsbefehl kopieren und das Quellpaket vor der Installation in der eigenen Agentenumgebung prüfen.
  2. Benötigte Werkzeuge, Zugangsdaten und Hosts im Original prüfen.
  3. Eingabe und gewünschtes Ergebnis beschreiben und die Ausgabe mit den Quellen abgleichen.
npx skills add https://github.com/sbknext/forge-frappe-skill --skill frappe-database-orm

Vor dem Start

  • Die Installation umfasst weder Modellabo noch API-Kontingent oder Dateizugriffsrechte.
  • Sterne und Installationen zeigen Aktivität, keine unabhängige Qualitäts- oder Sicherheitsprüfung.
Originales Skill-Dokument

Aus dem Quell-Repository gerendert; Überschriften, Beispiele, Code, Tabellen, Links und Bilder bleiben erhalten.

Frappe Database / ORM

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

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.

Query Builder (frappe.qb)

python
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

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().

v16 also prefers dict-style aggregate fields:

python
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):

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

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 (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
aus demselben Repository

Weitere Skills

Alle Skills
sbknext
Community

frappe-app-development

Scaffold, architect, and build production-grade custom Frappe/ERPNext apps. Covers app structure, DocType design, hooks.py configuration, background jobs, service layers, REST APIs, permissions, and testing. Use when creating a new Frappe app, adding a module, setting up scheduler events, implementing docevents, writing whitelisted APIs, or debugging Frappe-specific issues. Trigger on: "create Frappe app", "new DocType", "add a hook", "background job", "frappe.enqueue", "whitelist API", "custom

Installationen / 1h
1
GitHub Stars
18
Aktualisiert
7. Juni
sbknext
Community

frappe-client-scripting

Customize Frappe desk forms and lists with client scripts — frappe.ui.form.on handlers, field events, setquery link filters, setdfproperty, custom buttons, frappe.call, and list view settings. Use when adding form interactivity, dynamic filters, validation, or buttons in the desk UI. Trigger on: "client script", "frappe.ui.form.on", "form event", "setquery", "custom button", "frappe.call", "list view settings", "curfrm".

Installationen / 1h
1
GitHub Stars
18
Aktualisiert
7. Juni
sbknext
Community

frappe-report-development

Build Frappe reports — Query Reports (SQL), Script Reports (Python), filters, columns, charts, formatters, and report permissions. Use when creating analytics/listing reports, dashboards, or exportable views in Frappe/ERPNext. Trigger on: "query report", "script report", "Frappe report", "report filters", "report chart", "columns formatter", "report builder".

Installationen / 1h
1
GitHub Stars
18
Aktualisiert
7. Juni
sbknext
Community

frappe-testing

Write and run tests for Frappe/ERPNext apps — FrappeTestCase, test records, fixtures, mocking, permission tests, running with bench run-tests, and CI. Use when adding test coverage, doing TDD on a DocType or API, or setting up a test pipeline. Trigger on: "Frappe test", "FrappeTestCase", "bench run-tests", "unit test DocType", "test fixtures", "TDD frappe".

Installationen / 1h
1
GitHub Stars
18
Aktualisiert
7. Juni