Query Builder API in Frappe v15

What is Query Builder in Frappe?

The Query Builder is a Python API that allows developers to build and run safe SQL queries in Frappe without writing raw SQL. It ensures:

• Query safety using query parameterization
• Works with MariaDB and PostgreSQL (where applicable)
• Easy joins, ordering, and grouping
• Support for complex WHERE clauses
• Full chaining API

Query Builder is available under:

from frappe.query_builder import DocType

Why Use Query Builder?

Query Builder solves the limitations of raw SQL by:

• Preventing SQL injection
• Making queries database-agnostic
• Allowing dynamic query composition
• Providing Pythonic syntax
• Integrating with Frappe ORM

It is best used when you need custom database queries beyond simple frappe.get_all or ORM operations.

Importing Query Builder in Frappe v15

You first define a DocType object representing a table:

from frappe.query_builder import DocType
Customer = DocType("Customer")

This maps Customer DocType to the underlying database table tabCustomer.

Query Builder Basic Operations

SELECT Query

from frappe.query_builder import DocType
import frappe
Customer = DocType("Customer")
query = (
frappe.qb.from_(Customer)
.select(Customer.name, Customer.customer_name, Customer.email_id)
)
data = query.run(as_dict=True)

Result Format

run(as_dict=True) returns a list of dictionaries.

WHERE Filters

Simple WHERE Condition

data = (
frappe.qb.from_(Customer)
.select("*")
.where(Customer.customer_group == "Retail")
.run(as_dict=True)
)

Multiple Conditions

Use & and | for AND/OR:

from frappe.query_builder import DocType
from pypika.terms import Criterion
Customer = DocType("Customer")
query = (
frappe.qb.from_(Customer)
.select(Customer.name)
.where(
(Customer.customer_group == "Retail") &
(Customer.territory == "India")
)
)
results = query.run(as_dict=True)

ORDER BY and LIMIT

results = (
frappe.qb.from_(Customer)
.select(Customer.name)
.orderby(Customer.creation, order="desc")
.limit(10)
.run(as_dict=True)
)

JOIN Queries

Query Builder supports inner and left joins.

INNER JOIN Example

from frappe.query_builder import DocType
import frappe
SalesOrder = DocType("Sales Order")
Customer = DocType("Customer")
results = (
frappe.qb.from_(SalesOrder)
.join(Customer)
.on(SalesOrder.customer == Customer.name)
.select(
SalesOrder.name,
Customer.customer_name,
SalesOrder.transaction_date
)
.run(as_dict=True)
)

GROUP BY and Aggregation

from frappe.query_builder.functions import Count
results = (
frappe.qb.from_(Customer)
.select(Customer.territory, Count("*").as_("total"))
.groupby(Customer.territory)
.run(as_dict=True)
)

Using Functions

Frappe Query Builder supports SQL functions via frappe.query_builder.functions.

Example: SUM, COUNT, MAX, MIN

from frappe.query_builder import DocType
from frappe.query_builder.functions import Sum
SalesInvoice = DocType("Sales Invoice")
results = (
frappe.qb.from_(SalesInvoice)
.select(Sum(SalesInvoice.grand_total).as_("revenue"))
.run(as_dict=True)
)

INSERT Query (Create Record)

While ORM (doc.insert()) is recommended, Query Builder supports insert:

query = frappe.qb.into(Customer).columns("name", "customer_name").insert(("CUST-001", "ABC Foods"))
query.run()

UPDATE Records

(
frappe.qb.update(Customer)
.set(Customer.customer_name, "Updated Name")
.where(Customer.name == "CUST-001")
).run()

DELETE Records

(
frappe.qb.from_(Customer)
.where(Customer.name == "CUST-001")
.delete()
).run()

⚠ Avoid DELETE unless absolutely required. Use soft delete or cancellation methods in DocType, if available.

Using Query Builder with frappe.qb

Frappe wraps Query Builder under frappe.qb, which ensures:

• Automatic DB connection
• Site context isolation
• Query logging
• Multi-tenant safety

Example:

query = (
frappe.qb.from_(Customer)
.select(Customer.name)
)
query.run(as_dict=True)

Advanced Query Builder Features

1. Subqueries

from frappe.query_builder.functions import Count
Order = DocType("Sales Order")
Customer = DocType("Customer")
sub = (
frappe.qb.from_(Order)
.select(Count("*"))
.where(Order.customer == Customer.name)
)
results = (
frappe.qb.from_(Customer)
.select(Customer.name, sub.as_("order_count"))
.run(as_dict=True)
)

2. Raw SQL Fallback

If you need full SQL control:

frappe.db.sql(query.get_sql(), as_dict=True)

get_sql() returns the generated SQL.

Best Practices for Query Builder

• Prefer Frappe ORM for simple operations
• Use Query Builder for:
  • Complex joins
  • Database-heavy operations
  • Reporting queries
  • Aggregation queries
  • Subqueries
• Always use run(as_dict=True) for clean output
• Avoid unsafe raw SQL
• Use Criterion for complex filters

Troubleshooting

Query Injection Issues

Query Builder prevents injection automatically.
Do not format SQL strings manually.

No Results

Check:

• Field names
• Filters (where)
• Permissions (use ignore_permissions) if required only in controlled flows

Example:

query.run(ignore_permissions=True)

Integration Patterns

Use Query Builder when building:

• Custom reports
• REST API fetching logic
• Dashboard summaries
• Advanced filters in server scripts
• External integrations needing optimized SQL

Combine with:

• REST API (frappe.api)
• Background Jobs
• Frappe ORM
• Canned Reports

Cross-References

Related documentation:

• Database API: frappe.db
• Document API: frappe.get_doc
• REST API: /api/resource/<doctype>
• Response API
• Full-Text Search API