Skip to main content

Frappe Database API (v15): frappe.db Guide

The Frappe Database API is the primary server-side interface for reading and writing data in Frappe and ERPNext. It wraps SQL in a safe ORM-style layer and provides additional helpers for transactions and schema operations.
This guide explains the core frappe.db functions available in Frappe Framework v15, how to use them correctly, and when to fall back to raw SQL.

What is the Frappe Database API?

The Frappe Database API is exposed via the frappe.db module. It provides:

  • High-level ORM helpers such as get_list, get_all, get_value, set_value, exists, count.
  • Data modification helpers like delete, truncate.
  • Transaction management (commit, rollback, savepoint) and transaction hooks introduced in v15.
  • Low-level SQL access via frappe.db.sql and frappe.db.multisql.
  • Schema helpers (rename_table, describe, change_column_type, add_index, add_unique).

All examples assume Frappe v15 and are meant to run in server-side Python (e.g., inside a whitelisted method, background job, or patch).

Target audience: Frappe / ERPNext app developers, integrators, and advanced implementers.
Prerequisites: Basic Python, understanding of DocTypes and the Frappe request lifecycle.

How to fetch lists of records with frappe.db.get_list?

frappe.db.get_list is the core ORM method for reading multiple records with permission checks.

records = frappe.db.get_list(
"Task",
filters={"status": "Open"},
fields=["name", "subject", "status"],
order_by="modified desc",
start=0,
page_length=20,
)

Key behavior (v15)

  • Signature: frappe.db.get_list(doctype, filters=None, or_filters=None, fields=None, order_by=None, group_by=None, start=0, page_length=20, …).
  • Aliased as frappe.get_list.
  • Applies user permissions for the current session user.
  • Returns list of dicts by default; if fields is omitted, only name is returned.
  • You can return plain tuples using as_list=True.
  • You can “pluck” a single field into a flat list using pluck=”fieldname”.

Common filter patterns

The same filters syntax used throughout Frappe applies here: equality, comparison operators, between, like, etc.

# tasks after a date
recent_tasks = frappe.db.get_list(
"Task",
filters={"date": (">", "2024-01-01")},
fields=["name", "date"],
)
# tasks with subject containing "bug"
bug_tasks = frappe.db.get_list(
"Task",
filters={"subject": ["like", "%bug%"]},
fields=["name", "subject"],
)

When to use get_list

Use get_list for:

  • Building list views, dashboards, and lightweight server-side queries.
  • Any situation where permission checks are required.

When should you use frappe.db.get_all instead?

frappe.db.get_all is similar to get_list but does not apply user-level permission filters.

all_settings = frappe.db.get_all(
"System Settings",
fields=["name", "value"],
)

  • Signature: frappe.db.get_all(doctype, filters=None, or_filters=None, fields=None, order_by=None, group_by=None, start=0, page_length=20, …).
  • Aliased as frappe.get_all.
  • Intended for internal logic like configuration, background jobs, or admin-only operations.

Best practice:

Use get_all only when you explicitly want to ignore user permissions (e.g., scheduled tasks, data maintenance scripts). For user-facing flows, prefer get_list.

How to read single values with frappe.db.get_value?

frappe.db.get_value is the fastest way to read one or a few fields from a single row.

Basic usage with a document name:

subject = frappe.db.get_value("Task", "TASK-0001", "subject")

Multiple fields:

subject, description = frappe.db.get_value(
"Task",
"TASK-0001",
["subject", "description"],
)

Return as dict:

task = frappe.db.get_value(
"Task",
"TASK-0001",
["subject", "description"],
as_dict=True,
)
print(task.subject, task.description)

Usage with filters instead of name (returns first matching row):

subject, description = frappe.db.get_value(
"Task",
{"status": "Open"},
["subject", "description"],
)

  • Aliases: frappe.get_value and frappe.db.get_values.

 

How to read from Single DocTypes with frappe.db.get_single_value?

For Single DocTypes (e.g., System Settings), use get_single_value.

timezone = frappe.db.get_single_value("System Settings", "timezone")

  • Avoid get_value here; get_single_value is more explicit, simpler, and optimized for singletons.

How to update data with frappe.db.set_value?

frappe.db.set_value updates database fields without calling document ORM methods like validate or on_update.

Single field:

frappe.db.set_value("Task", "TASK-0001", "subject", "Updated Subject")

Multiple fields:

frappe.db.set_value(
"Task",
"TASK-0001",
{
"subject": "Updated Subject",
"description": "Updated Description",
},
)

Skip modified timestamp update:

frappe.db.set_value(
"Task",
"TASK-0001",
"subject",
"Updated Subject",
update_modified=False,
)

Important: set_value does not trigger document events like validate or on_update. Use it only when you know exactly what you are doing (e.g., fixing hidden/internal fields, backfilling flags).

How to check existence and counts?

frappe.db.exists
Use exists to quickly check if a document matching given criteria exists.

# check by name
if frappe.db.exists("User", "test@example.com"):
...
# check by filters, with explicit doctype
user_name = frappe.db.exists("User", {"full_name": "Jane Doe"})
# check by filter dict including doctype
user_name = frappe.db.exists({
"doctype": "User",
"full_name": "Jane Doe",
})

It returns the name (or truthy value) if found, or None/falsy if not.

frappe.db.count

count returns the number of records for a doctype, optionally filtered.

total_tasks = frappe.db.count("Task")
open_tasks = frappe.db.count("Task", {"status": "Open"})

Use count when you only need aggregate counts and not the records themselves.

How to delete and truncate records safely?

frappe.db.delete
delete performs a DML DELETE with filters and can be rolled back in a transaction. Frappe Documentation
# delete old route history for a user
frappe.db.delete(
"Route History",
{
"user": user,
"modified": ("<=", last_record_to_keep[0].modified),
},
)
# delete all Error Log records
frappe.db.delete("Error Log")

  • If no filters are given, all rows of the table are deleted.
  • You can pass either the DocType name or an internal table name (e.g., __Test Table).

frappe.db.truncate

truncate issues a TRUNCATE TABLE command and cannot be rolled back. A commit is triggered before execution.

frappe.db.truncate("Error Log")
frappe.db.truncate("__Test Table")

Use truncate for periodic cleanup of log tables where you want to drop all rows efficiently.

How are transactions handled in Frappe v15?

Explicit transaction APIs

  • frappe.db.commit() – commits the current transaction.
  • frappe.db.rollback() – rolls back the current transaction.
  • frappe.db.rollback(save_point=”name”) – rolls back to a given savepoint only.
  • frappe.db.savepoint(save_point) – create a named savepoint.

In most application code you do not need to call commit manually; Frappe manages transaction boundaries for requests, background jobs, patches, and tests. Frappe Documentation+1

Default transaction model (v15)

Frappe’s transaction model:

  • Web requests:
    • POST / PUT with writes: commit at end if successful.
    • GET: no implicit commit.
    • Any uncaught exception triggers a rollback.
  • Background / scheduled jobs:
     Commit when the job function finishes without exception; rollback otherwise.
  • Patches:
     Successful execute commits; exceptions rollback.
  • Unit tests:
     Transactions are committed after each test module and at the end of the test run.

If you catch an exception yourself, Frappe cannot auto-rollback. You are responsible for calling frappe.db.rollback() when appropriate.

v15 Database transaction hooks Frappe Documentation
Available hooks:

  • frappe.db.before_commit.add(func)
  • frappe.db.after_commit.add(func)
  • frappe.db.before_rollback.add(func)
  • frappe.db.after_rollback.add(func)

Example: ensure a file is deleted if the transaction fails.

def create_file(self):
self.write_file()
# if DB transaction rolls back, also roll back file changes
frappe.db.after_rollback.add(self.rollback_file)
def rollback_file(self):
self.delete_file()

Use these hooks for external side-effects like file system operations, cache changes, or message queue events.

When should you use frappe.db.sql and frappe.db.multisql?

frappe.db.sql
frappe.db.sql(query, values=None, as_dict=False) executes a raw SQL statement and returns results.

data = frappe.db.sql(
"""
SELECT name, grand_total
FROM `tabSales Invoice`
WHERE company = %(company)s
""",
values={"company": "Frappe Technologies"},
as_dict=True,
)

  • Use for complex joins and performance-critical reports that are hard to express via get_list.
  • It bypasses ORM validation and integrity checks. The official docs recommend preferring ORM helpers like frappe.get_doc and frappe.db.get_list whenever possible.

frappe.db.multisql

frappe.db.multisql selects the correct SQL statement for the active database engine (MariaDB vs Postgres).

frappe.db.multisql({
"mariadb": "UPDATE `tabTask` SET priority = 'High' WHERE status = 'Open'",
"postgres": 'UPDATE "tabTask" SET priority = \'High\' WHERE status = \'Open\'',
})

Use multisql when you need engine-specific syntax while keeping code portable.

How to manage database schema in code?

Rename tables with frappe.db.rename_table

rename_table changes a table name, for DocTypes or internal tables.

frappe.db.rename_table("__internal_cache", "__temporary_cache")

Do not use this to rename DocType tables. Instead, use frappe.rename_doc, which also updates metadata and references.

Inspect table structure with frappe.db.describe

Returns the database description for a DocType’s table.

columns = frappe.db.describe("Task")

Change column type with frappe.db.change_column_type

frappe.db.change_column_type("Task", "priority", "varchar(255)")

Changes the SQL type of a column for a given DocType.

Add indexes with frappe.db.add_index

Creates a database index on specific fields.

frappe.db.add_index("Task", ["status", "modified"], "idx_task_status_modified")

For TEXT / BLOB fields you must specify a fixed length in the index definition.

frappe.db.add_index("Note", ["name(10)", "content(500)"], "idx_note_name_content")

Add unique constraints with frappe.db.add_unique

Create a unique constraint across one or more fields.

frappe.db.add_unique("Custom DocType", ["company", "external_id"])

Use this for enforcing business rules like “one record per company” at the database level.

Best practices for using frappe.db in v15

Prefer ORM over raw SQL

Use get_list, get_value, get_doc, and delete_doc where possible for safety and portability.

Use get_list for permission-aware queries
Reserve get_all for controlled internal or background operations.

Be cautious with set_value
Since it skips document triggers, avoid using it for business logic that relies on validation, totals, or linked updates.

Treat truncate as destructive
It cannot be rolled back. Use only for log / temp tables.

Leverage transaction hooks for external side effects
Keep external resources in sync with commit/rollback via before_commit, after_commit, before_rollback, after_rollback.

Avoid manual commits unless necessary
Let Frappe’s transaction model manage commits for requests, jobs, patches, and tests.

Click to rate this post!
[Total: 0 Average: 0]

© 2025 | goERPNext | All rights reserved