Skip to main content
The Database API (frappe.db) provides direct access to the database for queries, value operations, and transactions. Use these methods for efficient data retrieval and updates.

​
Query Methods

​
frappe.db.sql()

Execute raw SQL queries with parameter binding.
​
query
string
required
SQL query string with optional placeholders
​
values
dict | tuple
default:"None"
Values for parameter binding
​
as_dict
bool
default:"False"
Return results as list of dictionaries
​
as_list
bool
default:"False"
Return results as list of lists
import frappe

# Execute SQL query
result = frappe.db.sql("""
    SELECT name, customer, grand_total 
    FROM `tabSales Invoice`
    WHERE docstatus = 1 
    AND posting_date >= %s
""", ("2024-01-01",))

for row in result:
    print(row)

​
frappe.db.sql_list()

Get a flat list of values from the first column. 
import frappe

# Get list of customer names
customers = frappe.db.sql_list("""
    SELECT name 
    FROM `tabCustomer`
    WHERE customer_group = 'Commercial'
""")

print(customers)  # ['Customer 1', 'Customer 2', ...]

​
Value Operations

​
frappe.db.get_value()

Get a single value or multiple values from a document.
​
doctype
string
required
The DocType name
​
filters
string | dict
required
Document name or filter dictionary
​
fieldname
string | list
default:"'name'"
Field name or list of field names
​
as_dict
bool
default:"False"
Return as dictionary
import frappe

# Get account currency
currency = frappe.db.get_value("Account", 
    "Debtors - TC", 
    "account_currency"
)

# Get company default currency
default_currency = frappe.db.get_value("Company", 
    "_Test Company", 
    "default_currency"
)

​
frappe.db.get_single_value()

Get a value from a Single DocType (settings). 
import frappe

# Get from single doctype
api_key = frappe.db.get_single_value("Video Settings", "api_key")

enable_tracking = frappe.db.get_single_value(
    "Video Settings", 
    "enable_youtube_tracking"
)

# Get default company
default_company = frappe.db.get_single_value(
    "Global Defaults", 
    "default_company"
)

​
frappe.db.set_value()

Update field values without loading the full document.
​
doctype
string
required
The DocType name
​
name
string
required
Document name
​
fieldname
string | dict
required
Field name or dictionary of field-value pairs
​
value
any
Value to set (not needed if fieldname is dict)
import frappe

# Update single field
frappe.db.set_value("Company", 
    "_Test Company", 
    "monthly_sales_target", 
    10000
)

# Update account number
frappe.db.set_value("Account",
    "Debtors - TC",
    "account_number",
    "1210"
)

​
frappe.db.get_list()

Alternative to frappe.get_list() for database-level queries. 
import frappe

# Get failed documents
failed_docs = frappe.db.get_all(
    "Bulk Transaction Log Detail",
    filters={
        "transaction_status": "Failed",
        "retried": 0
    },
    fields=["name", "reference_doctype", "reference_docname"]
)

​
Transaction Control

​
Commit and Rollback

Manage database transactions for consistency. 
import frappe

# Commit changes to database
doc = frappe.get_doc({"doctype": "Customer", "customer_name": "New Customer"})
doc.insert()
frappe.db.commit()  # Persist changes

​
Bulk Operations with Transactions

import frappe

def bulk_create_items(items_data):
    """Create multiple items with transaction management"""
    success_count = 0
    failed_count = 0
    
    for item_data in items_data:
        try:
            frappe.db.savepoint("before_creation_state")
            
            item = frappe.get_doc({
                "doctype": "Item",
                **item_data
            })
            item.insert()
            
            frappe.db.commit()
            success_count += 1
            
        except Exception as e:
            frappe.db.rollback(save_point="before_creation_state")
            failed_count += 1
            frappe.log_error(f"Failed to create item: {str(e)}")
    
    return {"success": success_count, "failed": failed_count}

​
Counting Records

​
frappe.db.count()

Count documents matching filters. 
import frappe

# Count all customers
total_customers = frappe.db.count("Customer")

# Count active employees
active_employees = frappe.db.count("Employee", 
    filters={"status": "Active"}
)

​
Advanced Queries

​
Query Builder

Use Frappe’s query builder for type-safe queries. 
import frappe
from frappe.query_builder.functions import Sum, Count
from frappe.query_builder import DocType

# Build query
SalesInvoice = DocType("Sales Invoice")

query = (
    frappe.qb.from_(SalesInvoice)
    .select(
        SalesInvoice.customer,
        Sum(SalesInvoice.grand_total).as_("total_amount"),
        Count(SalesInvoice.name).as_("invoice_count")
    )
    .where(
        (SalesInvoice.docstatus == 1) &
        (SalesInvoice.posting_date >= "2024-01-01")
    )
    .groupby(SalesInvoice.customer)
)

result = query.run(as_dict=True)

​
Utility Methods

​
frappe.db.get_values()

Get values for multiple documents. 
import frappe

# Get values for multiple accounts
accounts = frappe.db.get_values("Account",
    filters={"company": "_Test Company", "is_group": 0},
    fieldname=["name", "account_type"],
    as_dict=True
)

for account in accounts:
    print(account.name, account.account_type)

​
frappe.db.exists()

Check document existence. 
import frappe

# Simple existence check
if frappe.db.exists("Customer", "CUST-001"):
    print("Customer exists")

# Check with filters
if frappe.db.exists("Account", {
    "account_name": "Debtors",
    "company": "_Test Company"
}):
    print("Account found")

# Get name if exists
account_name = frappe.db.exists("Account", {
    "account_number": "1210",
    "company": "_Test Company"
})

if account_name:
    print(f"Found account: {account_name}")

​
Best Practices

Use Parameter Binding

Always use parameter binding in SQL queries to prevent SQL injection:
# Good
frappe.db.sql("SELECT * FROM `tabCustomer` WHERE name = %s", (customer_name,))

# Bad
frappe.db.sql(f"SELECT * FROM `tabCustomer` WHERE name = '{customer_name}'")

Prefer get_value() for Single Fields

Use get_value() instead of get_doc() when you only need specific fields:
# Efficient
currency = frappe.db.get_value("Account", account_name, "account_currency")

# Inefficient
account = frappe.get_doc("Account", account_name)
currency = account.account_currency

Transaction Management

Always use proper transaction management for data consistency:
try:
    # Multiple operations
    frappe.db.commit()
except Exception:
    frappe.db.rollback()

Batch Operations

Use savepoints for batch operations to handle individual failures:
for record in records:
    frappe.db.savepoint("savepoint_name")
    try:
        # Process record
    except Exception:
        frappe.db.rollback(save_point="savepoint_name")

Build docs developers (and LLMs) love

Get started for freeTalk to us