v15 Server Only Stable Database, ORM & Query Builder

Frappe Framework v15 provides 3 database access interfaces under frappe.db and frappe.qb for interacting with MariaDB and PostgreSQL.

---

1. frappe.db API Reference

frappe.db.get_value

Fetches values from a single database row efficiently without instantiating document objects.

# Signature
frappe.db.get_value(doctype, filters, fieldname, as_dict=False, debug=False)

# Fetch single field
email = frappe.db.get_value("User", "Administrator", "email")

# Fetch multiple fields as dictionary
data = frappe.db.get_value(
    "Task",
    {"status": "Open", "priority": "High"},
    ["name", "subject", "allocated_to"],
    as_dict=True
)

---

frappe.db.set_value

Executes a direct UPDATE query on specific document fields in the database.

frappe.db.set_value(
    doctype,
    name,
    fieldname,
    value=None,
    modified=None,
    modified_by=None,
    update_modified=True
)

# Example: Bulk field update
frappe.db.set_value("Task", "TASK-00001", "status", "Completed")

# Dict-based multi-field update
frappe.db.set_value(
    "Task",
    {"status": "Open", "priority": "Low"},
    {"priority": "Medium", "status": "Working"}
)

---

frappe.db.exists & frappe.db.count

# 1. Check record existence (Returns document name string or None)
name = frappe.db.exists("User", {"email": "john@example.com"})
print("Result:", name)
# Output:
# Result: john@example.com

# 2. Count total matching records
open_task_count = frappe.db.count("Task", filters={"status": "Open"})
print("Open Tasks:", open_task_count)
# Output:
# Open Tasks: 42

---

frappe.db.get_single_value

Retrieves a field value from a Single DocType (such as System Settings or Global Defaults).

# Fetch system default currency from System Settings
currency = frappe.db.get_single_value("System Settings", "default_currency")
print("System Currency:", currency)
# Output:
# System Currency: USD

---

frappe.db.delete

Performs direct SQL row deletion based on filter conditions without instantiating document objects.

# Direct deletion of temporary log records
frappe.db.delete("Activity Log", {
    "creation": ["<", "2026-01-01"],
    "status": "Success"
})

---

Schema Inspection & Maintenance (table_exists, has_column, touch)

# 1. Check if database table exists
if frappe.db.table_exists("tabTask"):
    print("Table exists!")

# 2. Check if table column exists
if frappe.db.has_column("tabTask", "custom_priority"):
    print("Column exists!")

# 3. Touch document modified timestamp
frappe.db.touch("Task", "TASK-2026-00001")

---

frappe.db.sql (Raw SQL Execution)

Executes raw SQL queries with mandatory parameterized variable binding to prevent SQL injection vulnerabilities.

# ALWAYS use SQL parameter binding (%s for MariaDB/PostgreSQL)!
result = frappe.db.sql("""
    SELECT name, subject, status
    FROM `tabTask`
    WHERE status = %s AND priority = %s
    ORDER BY creation DESC
""", ("Open", "High"), as_dict=True)

print("Fetched SQL Rows:", result)
# Output:
# Fetched SQL Rows: [{'name': 'TASK-2026-00001', 'subject': 'Fix Bug', 'status': 'Open'}]

CAUTION

Never use Python string interpolation (f"SELECT ... WHERE name = '{name}'") inside frappe.db.sql()! This creates critical SQL injection security vulnerabilities.

---

Database Transactions: commit, rollback, savepoint

# Savepoint and Transaction Control
try:
    frappe.db.savepoint("before_bulk_update")
    frappe.db.set_value("Task", task_id, "status", "Completed")
    # Commit explicit transaction if required
    frappe.db.commit()
except Exception:
    # Revert to savepoint without aborting entire request transaction
    frappe.db.rollback(save_point="before_bulk_update")
    raise

---

2. DocType Metadata & Request Context APIs

frappe.get_meta

Returns the Meta structure object for a given DocType.

meta = frappe.get_meta("Customer")

# Inspect field definitions
has_field = meta.has_field("tax_id")
field = meta.get_field("customer_name")
link_fields = meta.get_link_fields()

print("Has Tax ID:", has_field)
print("Field Type:", field.fieldtype)
# Output:
# Has Tax ID: True
# Field Type: Data

---

frappe.local Request Context

frappe.local holds thread-local contextual variables for the active Werkzeug HTTP request.

Attribute	Description	Output Example
frappe.local.site	Active site name	'site1.localhost'
frappe.local.session.user	Logged in user email	'john@company.com'
frappe.local.form_dict	Parsed HTTP request query parameters & body	{'doctype': 'Task', 'status': 'Open'}
frappe.local.request	Werkzeug HTTP request object	<Request 'http://localhost/api/method/...' [POST]>

---

3. Query Builder (frappe.qb)

Frappe v15 integrates PyPika into frappe.qb to generate type-safe, programmatic, cross-database SQL queries.

Basic SELECT & WHERE Query

from frappe.query_builder import DocType, Field

Task = DocType("Task")

query = (
    frappe.qb.from_(Task)
    .select(Task.name, Task.subject, Task.priority)
    .where((Task.status == "Open") & (Task.priority.isin(["High", "Urgent"])))
    .orderby(Task.creation, order=frappe.qb.desc)
    .limit(20)
)

results = query.run(as_dict=True)
print("Query Results:", results)
# Output:
# Query Results: [{'name': 'TASK-001', 'subject': 'Setup Redis', 'priority': 'High'}]

---

JOIN & Aggregations Query

from frappe.query_builder import DocType
from frappe.query_builder.functions import Count, Sum

Task = DocType("Task")
Project = DocType("Project")

query = (
    frappe.qb.from_(Project)
    .left_join(Task).on(Task.project == Project.name)
    .select(
        Project.name.as_("project_name"),
        Count(Task.name).as_("total_tasks")
    )
    .groupby(Project.name)
    .having(Count(Task.name) > 5)
)

data = query.run(as_dict=True)

---

4. Database Strategy Comparison Matrix

Criteria	Document API (frappe.get_doc)	DB API (frappe.db.get_all)	Query Builder (frappe.qb)	Raw SQL (frappe.db.sql)
Execution Speed	Moderate (Full object instantiation)	Fast	Very Fast	Maximum Speed
Triggers Lifecycle Hooks	Yes (validate, on_update)	No	No	No
Applies Permissions	Optional (check_permission)	Yes (get_list) / No (get_all)	Manual	Manual
Complex Joins / Subqueries	No	Limited	Full Support	Full Support
Type Safety & Security	Maximum	High	High (Injection-proof)	Requires Manual Binding

---

Related Topics

• 06. Document API & Lifecycle
• 09. Server API
• 21. Security & Performance