DocTypes — Data Modeling

If you come from the Node.js world, think of a DocType as a Mongoose schema, a Prisma model, and an Express route handler all rolled into one. A single DocType definition gives you:

• A database table (MariaDB/PostgreSQL)
• A REST API (/api/resource/<DocType>)
• A full CRUD UI in Frappe Desk
• Permission rules, validation, workflows, and more

DocTypes are the fundamental building block of every Frappe application. ERPNext itself is nothing but ~800+ DocTypes working together. (For the big picture of how this fits into the framework, see Chapter 1: Architecture Overview.)

How DocTypes Map to the Database

When you create a DocType called Franchise Outlet, Frappe automatically:

1. Creates a table named tabFranchise Outlet (note the tab prefix)
2. Adds standard columns: name (primary key), owner, creation, modified, modified_by, docstatus
3. Adds one column per field you define
4. Creates indexes on frequently queried fields

DocType Name          →  SQL Table
─────────────────────────────────────
Item                  →  tabItem
Sales Invoice         →  tabSales Invoice
Franchise Outlet      →  tabFranchise Outlet
Outlet Equipment      →  tabOutlet Equipment  (child table)

Pro tip

The name column is always a VARCHAR primary key, never an auto-increment integer. Frappe identifies every document by its string name. This is fundamentally different from most Node.js ORMs where you use numeric IDs.

DocType JSON Definition Structure

Every DocType lives as a JSON file inside your app. When you create or modify a DocType through the Desk UI, Frappe writes changes back to this JSON file. Here is the directory structure for a DocType called Franchise Outlet inside the custom ScoopJoy app (ice_cream_biz):

• Directoryice_cream_biz/

  • Directoryice_cream_biz/

    • Directoryfranchise_management/ module directory

      • Directorydoctype/

        • Directoryfranchise_outlet/

          • franchise_outlet.json DocType definition (the schema)
          • franchise_outlet.py Python controller (server logic)
          • franchise_outlet.js client-side controller
          • test_franchise_outlet.py unit tests
          • init .py

Here is a complete franchise_outlet.json — study it carefully as every property matters:

ice_cream_biz/franchise_management/doctype/franchise_outlet/franchise_outlet.json

{
  "doctype": "DocType",
  "name": "Franchise Outlet",
  "module": "Franchise Management",
  "naming_rule": "Expression",
  "autoname": "format:FO-{city_code}-{####}",
  "is_submittable": 0,
  "is_tree": 0,
  "is_single": 0,
  "track_changes": 1,
  "engine": "InnoDB",
  "fields": [
    {
      "fieldname": "outlet_name",
      "fieldtype": "Data",
      "label": "Outlet Name",
      "reqd": 1,
      "in_list_view": 1,
      "in_standard_filter": 1,
      "unique": 1
    },
    {
      "fieldname": "city_code",
      "fieldtype": "Data",
      "label": "City Code",
      "reqd": 1,
      "description": "3-letter city code, e.g. MUM, DEL, BLR"
    },
    {
      "fieldname": "column_break_1",
      "fieldtype": "Column Break"
    },
    {
      "fieldname": "status",
      "fieldtype": "Select",
      "label": "Status",
      "options": "\nActive\nInactive\nUnder Renovation\nClosed",
      "default": "Active",
      "in_list_view": 1,
      "in_standard_filter": 1
    },
    {
      "fieldname": "opening_date",
      "fieldtype": "Date",
      "label": "Opening Date",
      "reqd": 1
    },
    {
      "fieldname": "section_location",
      "fieldtype": "Section Break",
      "label": "Location Details"
    },
    {
      "fieldname": "address_line_1",
      "fieldtype": "Data",
      "label": "Address Line 1",
      "reqd": 1
    },
    {
      "fieldname": "city",
      "fieldtype": "Data",
      "label": "City",
      "reqd": 1
    },
    {
      "fieldname": "column_break_2",
      "fieldtype": "Column Break"
    },
    {
      "fieldname": "state",
      "fieldtype": "Data",
      "label": "State"
    },
    {
      "fieldname": "pincode",
      "fieldtype": "Data",
      "label": "Pincode"
    },
    {
      "fieldname": "geolocation",
      "fieldtype": "Geolocation",
      "label": "Geolocation"
    },
    {
      "fieldname": "section_management",
      "fieldtype": "Section Break",
      "label": "Management"
    },
    {
      "fieldname": "manager",
      "fieldtype": "Link",
      "label": "Outlet Manager",
      "options": "Employee",
      "reqd": 1
    },
    {
      "fieldname": "manager_name",
      "fieldtype": "Data",
      "label": "Manager Name",
      "fetch_from": "manager.employee_name",
      "read_only": 1
    },
    {
      "fieldname": "column_break_3",
      "fieldtype": "Column Break"
    },
    {
      "fieldname": "monthly_rent",
      "fieldtype": "Currency",
      "label": "Monthly Rent",
      "precision": 2
    },
    {
      "fieldname": "default_warehouse",
      "fieldtype": "Link",
      "label": "Default Warehouse",
      "options": "Warehouse"
    },
    {
      "fieldname": "section_equipment",
      "fieldtype": "Section Break",
      "label": "Equipment"
    },
    {
      "fieldname": "equipment",
      "fieldtype": "Table",
      "label": "Outlet Equipment",
      "options": "Outlet Equipment",
      "description": "List of equipment installed at this outlet"
    },
    {
      "fieldname": "section_notes",
      "fieldtype": "Section Break",
      "label": "Notes",
      "collapsible": 1
    },
    {
      "fieldname": "notes",
      "fieldtype": "Text Editor",
      "label": "Notes"
    }
  ],
  "permissions": [
    {
      "role": "Franchise Manager",
      "read": 1,
      "write": 1,
      "create": 1,
      "delete": 0,
      "email": 1,
      "print": 1,
      "export": 1,
      "report": 1
    },
    {
      "role": "System Manager",
      "read": 1,
      "write": 1,
      "create": 1,
      "delete": 1
    }
  ],
  "sort_field": "creation",
  "sort_order": "DESC",
  "title_field": "outlet_name",
  "image_field": "",
  "search_fields": "outlet_name,city,manager_name"
}

Field Types Reference

Frappe ships 30+ field types. Here are the ones you will use most often, grouped by purpose.

Text & Data Fields

Field Type	SQL Type	Use Case
Data	VARCHAR(140)	Short text — names, codes, emails
Small Text	TEXT	Multi-line text, up to a few paragraphs
Text	LONGTEXT	Large text blocks
Long Text	LONGTEXT	Same as Text, rendered with more height
Text Editor	LONGTEXT	Rich text with HTML toolbar
Code	LONGTEXT	Code editor with syntax highlighting
HTML Editor	LONGTEXT	Raw HTML editing
Password	VARCHAR(140)	Stored encrypted, masked in UI
Read Only	VARCHAR(140)	Display-only computed values

Numeric Fields

Field Type	SQL Type	Use Case
Int	INT(11)	Whole numbers — quantities, counts
Float	DECIMAL(18,6)	Decimal numbers
Currency	DECIMAL(18,6)	Money values (respects currency settings)
Percent	DECIMAL(18,6)	0-100 percentage values

Date & Time

Field Type	SQL Type	Use Case
Date	DATE	Calendar date
Datetime	DATETIME(6)	Date + time with microseconds
Time	TIME(6)	Time only
Duration	DECIMAL(18,6)	Time duration in seconds

Relationship Fields

Field Type	SQL Type	Use Case
Link	VARCHAR(140)	Foreign key to another DocType
Dynamic Link	VARCHAR(140)	Polymorphic reference (type determined by companion field)
Table	—	Child table (one-to-many relationship)
Table MultiSelect	—	Many-to-many via link child table

Selection & Boolean

Field Type	SQL Type	Use Case
Select	VARCHAR(140)	Dropdown with predefined options
Check	INT(1)	Boolean checkbox (0 or 1)

File & Media

Field Type	SQL Type	Use Case
Attach	TEXT	File attachment (any type)
Attach Image	TEXT	Image attachment with preview

Special Fields

Field Type	SQL Type	Use Case
Geolocation	LONGTEXT	Map coordinates (GeoJSON)
JSON	LONGTEXT	Raw JSON data
Signature	LONGTEXT	Digital signature capture
Color	VARCHAR(140)	Color picker

Layout Fields (No Database Column)

Field Type	Purpose
Section Break	Start a new form section
Column Break	Split into columns within a section
Tab Break	Start a new tab in the form

Layout fields are render-only

Layout fields (Section Break, Column Break, Tab Break) do not create database columns. They only control how the form is rendered. But they still need a fieldname — Frappe auto-generates one if you leave it blank.

Naming Patterns

Every document in Frappe has a unique name (the primary key). You control how names are generated via the naming_rule and autoname properties. The priority is:

1. Document Naming Rule (configurable from Desk, no code needed)
2. autoname controller method (Python code in the controller class)
3. autoname DocType property (set in the JSON definition)

Here are the available patterns:

# 1. Format Expression (most flexible) — naming_rule: "Expression"
"autoname": "format:FO-{city_code}-{####}"
# Result: FO-MUM-0001, FO-DEL-0002, FO-BLR-0001
# {####} = auto-increment counter, {field} = field value
# Date tokens: {YYYY}, {MM}, {DD}, {YY}

# 2. Naming Series — naming_rule: "By Naming Series"
"autoname": "naming_series:"
# Requires a `naming_series` Select field on the DocType
# User picks prefix: "FO-MUM-.####" → FO-MUM-0001

# 3. Field-based — naming_rule: "By fieldname"
"autoname": "field:outlet_name"
# Name = value of `outlet_name` field. Must be unique.

# 4. Hash (random) — naming_rule: "Random"
"autoname": "hash"
# Result: a1b2c3d4e5 (10-char random string)

# 5. Prompt — naming_rule: "Set by user"
"autoname": "Prompt"
# User manually types a name when creating the document

# 6. Python controller method
# Define `def autoname(self):` in the controller — see Chapter 5

Example with a date-based format for a Franchise Sales Report:

# In the DocType JSON:
"autoname": "format:FSR-{YYYY}-{MM}-{####}"
# Result: FSR-2026-03-0001, FSR-2026-03-0002, FSR-2026-04-0001

Pro tip

For business documents in India, use naming_series — it lets accountants and managers customize prefixes per financial year (e.g., FO-2526-.#### for FY 2025-26) without touching code.

Link Fields — Foreign Key Relationships

A Link field is Frappe’s foreign key. It stores the name of the referenced document as a VARCHAR string.

{
  "fieldname": "manager",
  "fieldtype": "Link",
  "label": "Outlet Manager",
  "options": "Employee"
}

This means the manager field stores the name of an Employee document (e.g., HR-EMP-00042). In the UI, users get a searchable dropdown.

fetch_from: Auto-Pulling Related Data

You often want to display data from the linked document without an extra query. Use fetch_from:

{
  "fieldname": "manager_name",
  "fieldtype": "Data",
  "label": "Manager Name",
  "fetch_from": "manager.employee_name",
  "read_only": 1
}

When a user selects a manager, Frappe automatically copies employee_name from the linked Employee into manager_name. This value is stored in the database — it is denormalized for performance, not computed on the fly.

Multiple Links to the Same DocType

You can have multiple Link fields pointing to the same DocType:

{
  "fieldname": "primary_supplier",
  "fieldtype": "Link",
  "options": "Supplier",
  "label": "Primary Supplier"
},
{
  "fieldname": "backup_supplier",
  "fieldtype": "Link",
  "options": "Supplier",
  "label": "Backup Supplier"
}

Child Tables (Table Fields)

Child tables implement one-to-many relationships. A “child” DocType is embedded within a “parent” DocType — think of line items in an invoice.

Defining the Child DocType

A child DocType (Outlet Equipment) is a regular DocType with istable: 1. It gets its own JSON file and database table:

ice_cream_biz/franchise_management/doctype/outlet_equipment/outlet_equipment.json

{
  "doctype": "DocType",
  "name": "Outlet Equipment",
  "module": "Franchise Management",
  "istable": 1,
  "editable_grid": 1,
  "fields": [
    {
      "fieldname": "equipment_name",
      "fieldtype": "Data",
      "label": "Equipment Name",
      "reqd": 1,
      "in_list_view": 1
    },
    {
      "fieldname": "equipment_type",
      "fieldtype": "Select",
      "label": "Type",
      "options": "\nRefrigeration\nCooking\nDisplay\nPOS Hardware\nFurniture\nOther",
      "in_list_view": 1
    },
    {
      "fieldname": "quantity",
      "fieldtype": "Int",
      "label": "Quantity",
      "default": 1,
      "in_list_view": 1
    },
    {
      "fieldname": "purchase_date",
      "fieldtype": "Date",
      "label": "Purchase Date"
    },
    {
      "fieldname": "purchase_cost",
      "fieldtype": "Currency",
      "label": "Purchase Cost",
      "in_list_view": 1
    },
    {
      "fieldname": "condition",
      "fieldtype": "Select",
      "label": "Condition",
      "options": "\nNew\nGood\nFair\nNeeds Repair\nDecommissioned",
      "default": "New"
    }
  ],
  "permissions": []
}

How Frappe Tracks the Parent-Child Relationship

Every child table row has three hidden fields that Frappe manages automatically:

Field	Description	Example Value
parent	The name of the parent document	FO-MUM-0001
parenttype	The DocType of the parent	Franchise Outlet
parentfield	The fieldname of the Table field on the parent	equipment

Each row also gets an idx field (1-based integer) preserving the row order.

Referencing the Child Table from the Parent

In the parent DocType (Franchise Outlet), you add a Table field:

{
  "fieldname": "equipment",
  "fieldtype": "Table",
  "label": "Outlet Equipment",
  "options": "Outlet Equipment"
}

The options value is the name of the child DocType. In Python, you access child rows as self.equipment — a list of Document objects.

Dynamic Link — Polymorphic References

A Dynamic Link lets one field reference documents from different DocTypes depending on context. It always works in tandem with a companion field (usually a Link to DocType or a Select field) that specifies which DocType to reference.

Classic example: an Address can belong to a Customer, Supplier, or Lead.

{
  "fieldname": "party_type",
  "fieldtype": "Link",
  "label": "Party Type",
  "options": "DocType",
  "reqd": 1
},
{
  "fieldname": "party_name",
  "fieldtype": "Dynamic Link",
  "label": "Party Name",
  "options": "party_type",
  "reqd": 1
}

When a user picks party_type = "Customer", the party_name field becomes a searchable dropdown of all Customers. Switch to party_type = "Supplier", and it shows Suppliers instead.

Here is a practical example for our franchise app — a Franchise Complaint that could be against either an Outlet or a Supplier:

{
  "fieldname": "complaint_against_type",
  "fieldtype": "Select",
  "label": "Complaint Against",
  "options": "\nFranchise Outlet\nSupplier",
  "reqd": 1
},
{
  "fieldname": "complaint_against",
  "fieldtype": "Dynamic Link",
  "label": "Complaint Against Name",
  "options": "complaint_against_type",
  "reqd": 1
}

Pro tip

Use a Select field instead of Link to DocType as the companion field when you want to restrict which DocTypes are available. A Link to DocType would show every DocType in the system — usually not what you want.

DocType Properties

Key properties that change DocType behavior:

Property	Default	Effect
is_submittable	0	Enables Draft → Submitted → Cancelled workflow. Adds docstatus field (0=Draft, 1=Submitted, 2=Cancelled). Use for invoices, orders, journal entries.
is_tree	0	Enables Nested Set Model for hierarchical data. Adds parent_<doctype>, lft, rgt fields. Use for Chart of Accounts, Item Groups, Territories.
is_single	0	Only one document exists (no list view). Stored in tabSingles table, not its own table. Use for Settings pages.
istable	0	Marks this as a child table DocType. Cannot exist independently — always embedded in a parent.
track_changes	0	Enables version history (who changed what, when). Stored in the Version DocType.
is_virtual	0	No database table created. You must implement data access in the controller.
naming_rule	—	Controls naming UI: “Expression”, “By Naming Series”, “By fieldname”, “Random”, “Set by user”
title_field	—	Which field to display as the document title (instead of name)
search_fields	—	Comma-separated fieldnames that are searchable in Link field dropdowns
sort_field	modified	Default sort field for list view. v16 changed the default to creation.
sort_order	DESC	Default sort order

Submittable DocTypes in Depth

When is_submittable = 1, the document lifecycle becomes a state machine:

Submittable document lifecycle

Rendering diagram…

• Draft documents (docstatus=0) can be freely edited and saved.
• Submitted documents (docstatus=1) are locked. Only fields marked allow_on_submit can be changed.
• Cancelled documents (docstatus=2) cannot be edited. You can “Amend” to create a new draft copy.
• The docstatus field is an integer column on the database table.

This is how ERPNext handles Sales Invoices, Purchase Orders, Stock Entries, and other transactional documents.

Virtual DocTypes

Virtual DocTypes (introduced in v15, improved in v16) are DocTypes not backed by a database table. The developer provides the data source — an external API, a file, another database, or computed data.

They look and behave exactly like normal DocTypes in the Desk UI, but the controller must implement specific static methods for data access.

ice_cream_biz/franchise_management/doctype/franchise_dashboard/franchise_dashboard.json

{
  "doctype": "DocType",
  "name": "Franchise Dashboard",
  "module": "Franchise Management",
  "is_virtual": 1,
  "fields": [
    {
      "fieldname": "outlet",
      "fieldtype": "Link",
      "label": "Outlet",
      "options": "Franchise Outlet",
      "in_list_view": 1
    },
    {
      "fieldname": "total_sales_today",
      "fieldtype": "Currency",
      "label": "Total Sales Today",
      "in_list_view": 1
    },
    {
      "fieldname": "active_pos_sessions",
      "fieldtype": "Int",
      "label": "Active POS Sessions",
      "in_list_view": 1
    }
  ]
}

The controller must implement these static methods and instance methods:

ice_cream_biz/franchise_management/doctype/franchise_dashboard/franchise_dashboard.py

import frappe
from frappe.model.document import Document


class FranchiseDashboard(Document):
    """Virtual DocType — data is computed, not stored in a table."""

    @staticmethod
    def get_list(args):
        """Return list of records for the list view."""
        outlets = frappe.get_all(
            "Franchise Outlet",
            filters={"status": "Active"},
            fields=["name", "outlet_name"],
        )
        result = []
        for outlet in outlets:
            today_sales = frappe.db.sql(
                """
                SELECT IFNULL(SUM(grand_total), 0) as total
                FROM `tabSales Invoice`
                WHERE franchise_outlet = %s
                  AND posting_date = CURDATE()
                  AND docstatus = 1
                """,
                outlet.name,
                as_dict=True,
            )[0].total

            result.append(frappe._dict({
                "name": outlet.name,
                "outlet": outlet.name,
                "total_sales_today": today_sales,
                "active_pos_sessions": get_active_sessions(outlet.name),
            }))
        return result

    @staticmethod
    def get_count(args):
        """Return total count for the list view."""
        return frappe.db.count("Franchise Outlet", {"status": "Active"})

    @staticmethod
    def get_stats(args):
        """Return sidebar stats."""
        return {}

    def db_insert(self, *args, **kwargs):
        """Virtual DocType — inserts not supported."""
        frappe.throw("Franchise Dashboard is read-only")

    def load_from_db(self):
        """Load a single record by self.name."""
        outlet = frappe.get_doc("Franchise Outlet", self.name)
        self.outlet = outlet.name
        self.total_sales_today = frappe.db.sql(
            """
            SELECT IFNULL(SUM(grand_total), 0)
            FROM `tabSales Invoice`
            WHERE franchise_outlet = %s
              AND posting_date = CURDATE()
              AND docstatus = 1
            """,
            self.name,
        )[0][0]
        self.active_pos_sessions = get_active_sessions(self.name)

    def db_update(self):
        """Virtual DocType — updates not supported."""
        frappe.throw("Franchise Dashboard is read-only")

    def delete(self):
        """Virtual DocType — deletion not supported."""
        frappe.throw("Franchise Dashboard is read-only")


def get_active_sessions(outlet_name):
    return frappe.db.count("POS Opening Entry", {
        "franchise_outlet": outlet_name,
        "status": "Open",
    })

Static methods only

get_list, get_count, and get_stats must be @staticmethod — not instance methods and not @classmethod. Frappe will raise an “Incomplete Virtual Doctype Implementation” error otherwise.

Frappe v16 also introduces virtual child tables — read-only child tables that compute their rows dynamically when the form opens, without storing anything in the database.

The Frappe ORM — Querying and Manipulating Data

Coming from Node.js, Frappe’s ORM will feel familiar if you have used Mongoose or Prisma — but with some key differences. Here are the core methods.

Creating Documents

There are two idiomatic ways to build a new document: the imperative frappe.new_doc() builder, or frappe.get_doc() with a dict literal.

# Method 1: frappe.new_doc() — creates an in-memory Document
doc = frappe.new_doc("Franchise Outlet")
doc.outlet_name = "Scoops & Shakes - Andheri"
doc.city_code = "MUM"
doc.city = "Mumbai"
doc.state = "Maharashtra"
doc.address_line_1 = "Shop 42, Link Road, Andheri West"
doc.pincode = "400058"
doc.opening_date = "2026-04-01"
doc.manager = "HR-EMP-00042"
doc.monthly_rent = 85000

# Add child table rows
doc.append("equipment", {
    "equipment_name": "Gelato Display Freezer",
    "equipment_type": "Refrigeration",
    "quantity": 2,
    "purchase_cost": 125000,
    "condition": "New",
})
doc.append("equipment", {
    "equipment_name": "POS Terminal",
    "equipment_type": "POS Hardware",
    "quantity": 1,
    "purchase_cost": 32000,
    "condition": "New",
})

doc.insert()  # Saves to DB. Runs validate, before_save, after_insert hooks.

# Method 2: frappe.get_doc() with a dict — same effect, more concise
doc = frappe.get_doc({
    "doctype": "Franchise Outlet",
    "outlet_name": "Scoops & Shakes - Bandra",
    "city_code": "MUM",
    "city": "Mumbai",
    "state": "Maharashtra",
    "address_line_1": "14, Hill Road, Bandra West",
    "pincode": "400050",
    "opening_date": "2026-05-15",
    "manager": "HR-EMP-00043",
    "monthly_rent": 120000,
    "equipment": [
        {
            "equipment_name": "Soft Serve Machine",
            "equipment_type": "Refrigeration",
            "quantity": 1,
            "purchase_cost": 250000,
        }
    ],
}).insert()

Reading Documents

# Fetch a single document by name (loads all fields + child tables)
outlet = frappe.get_doc("Franchise Outlet", "FO-MUM-0001")
print(outlet.outlet_name)         # "Scoops & Shakes - Andheri"
print(outlet.manager)             # "HR-EMP-00042"
print(len(outlet.equipment))      # 2
print(outlet.equipment[0].equipment_name)  # "Gelato Display Freezer"

# Get a single field value (no full document load)
rent = frappe.db.get_value("Franchise Outlet", "FO-MUM-0001", "monthly_rent")
# Returns: 85000.0

# Get multiple fields
values = frappe.db.get_value(
    "Franchise Outlet", "FO-MUM-0001",
    ["outlet_name", "city", "monthly_rent"],
    as_dict=True
)
# Returns: {"outlet_name": "Scoops & Shakes - Andheri", "city": "Mumbai", "monthly_rent": 85000.0}

Listing Documents

# frappe.get_all() — returns list of dicts, ignores permissions
outlets = frappe.get_all(
    "Franchise Outlet",
    filters={"status": "Active", "city": "Mumbai"},
    fields=["name", "outlet_name", "manager_name", "monthly_rent"],
    order_by="creation desc",
    limit_page_length=20,
)

# frappe.get_list() — same but respects user permissions
outlets = frappe.get_list(
    "Franchise Outlet",
    filters={"status": "Active"},
    fields=["name", "outlet_name", "city"],
    limit_page_length=50,
)

# Filter operators
outlets = frappe.get_all(
    "Franchise Outlet",
    filters={
        "monthly_rent": [">", 50000],
        "opening_date": [">=", "2026-01-01"],
        "status": ["in", ["Active", "Under Renovation"]],
        "city": ["like", "%Mum%"],
    },
    fields=["name", "outlet_name", "monthly_rent"],
)

Pro tip

frappe.get_all() skips permission checks — use it for server-side logic and background jobs. frappe.get_list() respects the current user’s permissions — use it in whitelisted API methods that serve data to the frontend.

Updating Documents

# Method 1: Load, modify, save (triggers all hooks)
outlet = frappe.get_doc("Franchise Outlet", "FO-MUM-0001")
outlet.status = "Under Renovation"
outlet.save()

# Method 2: Direct DB update (bypasses controller hooks — use with care)
frappe.db.set_value("Franchise Outlet", "FO-MUM-0001", "status", "Under Renovation")

# Update multiple fields at once
frappe.db.set_value("Franchise Outlet", "FO-MUM-0001", {
    "status": "Under Renovation",
    "notes": "Renovation started March 2026",
})

set_value bypasses hooks

frappe.db.set_value() writes directly to the database, bypassing validate, before_save, and all other controller hooks. It also sends a doc_update realtime event. Use it only for lightweight status updates where hooks are not needed.

Checking Existence & Counting

# Check if a document exists
if frappe.db.exists("Franchise Outlet", "FO-MUM-0001"):
    print("Outlet exists")

# Check with filters
if frappe.db.exists("Franchise Outlet", {"outlet_name": "Scoops & Shakes - Andheri"}):
    print("Found by name")

# Count documents
active_count = frappe.db.count("Franchise Outlet", {"status": "Active"})
mumbai_count = frappe.db.count("Franchise Outlet", {"city": "Mumbai"})

Raw SQL (When ORM Is Not Enough)

# frappe.db.sql() for complex queries
results = frappe.db.sql("""
    SELECT
        fo.city,
        COUNT(*) as outlet_count,
        SUM(fo.monthly_rent) as total_rent
    FROM `tabFranchise Outlet` fo
    WHERE fo.status = 'Active'
    GROUP BY fo.city
    ORDER BY outlet_count DESC
""", as_dict=True)

# Parameterized queries (ALWAYS use %s to prevent SQL injection)
results = frappe.db.sql("""
    SELECT name, outlet_name, monthly_rent
    FROM `tabFranchise Outlet`
    WHERE city = %s AND status = %s
""", ("Mumbai", "Active"), as_dict=True)

Never interpolate into SQL

Never use f-strings or string concatenation in SQL queries. Always use %s placeholders — Frappe uses parameterized queries under the hood to prevent SQL injection.

Creating DocTypes — UI vs Code

Via the Desk UI

1. Navigate to the URL bar and type DocType, or go to Search > DocType > New DocType.

2. Fill in the name, module, and naming rule.

3. Add fields using the field editor (drag-and-drop ordering).

4. Set permissions in the Permissions table.

5. Click Save — Frappe writes the JSON file to your app directory and runs ALTER TABLE to update the database.

This is the fastest way to prototype. The JSON file is updated automatically and can be committed to Git.

Creating DocTypes via CLI

There is no bench new-doctype CLI command. DocTypes are created through:

1. Desk UI (recommended): Navigate to the search bar, type “DocType”, click “New DocType”. Fill in the fields, add your custom fields, and save. Frappe auto-generates the JSON definition, Python controller, and test files on disk (in developer mode).
2. Manually: Create the directory structure and JSON file yourself:

# Create the directory structure
mkdir -p apps/ice_cream_biz/ice_cream_biz/franchise_management/doctype/franchise_outlet

# Create the JSON definition file (see examples above)
# Create the controller: franchise_outlet.py
# Create the test: test_franchise_outlet.py
# Then run migrate to sync:
bench --site icecream.localhost migrate

The Desk UI approach is strongly recommended — it handles naming conventions, module registration, and boilerplate generation automatically.

With your data model defined, the next step is server-side business logic. See Chapter 5: Python Controllers & Server Logic for how to attach validation, lifecycle hooks, and APIs to these DocTypes.