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.

Vedi sorgente

Come usare

  1. Copia il comando e controlla il pacchetto prima di installarlo nel tuo ambiente agente.
  2. Controlla strumenti, credenziali e ambienti richiesti nel documento originale.
  3. Descrivi input e risultato desiderato; confronta l’output con le fonti.
npx skills add https://github.com/sbknext/forge-frappe-skill --skill frappe-database-orm

Prima di iniziare

  • L’installazione non include abbonamenti, quote API o accesso ai file.
  • Stelle e installazioni indicano attività, non un test indipendente di qualità o sicurezza.
Documento Skill originale

Contenuto dal repository con titoli, esempi, codice, tabelle, link e immagini preservati.

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
dallo stesso repository

Altri Skills

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

Installazioni / 1h
1
GitHub Stars
18
Aggiornato
7 giu
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".

Installazioni / 1h
1
GitHub Stars
18
Aggiornato
7 giu
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".

Installazioni / 1h
1
GitHub Stars
18
Aggiornato
7 giu
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".

Installazioni / 1h
1
GitHub Stars
18
Aggiornato
7 giu