SQL Registry

A tool for scanning, analyzing, and converting frappe.db.sql calls to Frappe Query Builder or ORM equivalents.

Overview

The SQL Registry scans Python files for frappe.db.sql calls, analyzes the SQL queries, and generates equivalent Query Builder or ORM code. This helps with:

  • Auditing - Understanding SQL usage across a codebase
  • Migration - Converting raw SQL to Query Builder for better security and maintainability
  • Quick Wins - Identifying simple queries that can use frappe.get_all instead

Registry data is stored as JSON (.sql_registry.json), which is human-readable, diffable, and portable. If an existing .sql_registry.pkl (pickle) file is found and no JSON registry exists, it is automatically migrated on first load.

Installation

The sql_registry command is included with test_utils:

pip install test_utils
# or
poetry add test_utils

CLI Commands

Scan a Directory

Scan Python files for SQL operations:

sql_registry scan --directory /path/to/app [--registry .sql_registry.json] [--include-patches]

Options:

  • --directory - Directory to scan (default: current directory)
  • --registry - Registry file path (default: .sql_registry.json)
  • --include-patches - Include SQL in patch/migration files. Patches are excluded by default since they are one-time migrations and typically don't need conversion.

This creates a .sql_registry.json file containing all discovered SQL calls.

List SQL Calls

List all SQL calls in the registry:

sql_registry list [--registry .sql_registry.json] [--file-filter myfile.py]

Show Call Details

Show detailed information about a specific SQL call:

sql_registry show <call_id> [--registry .sql_registry.json]

Output includes:

  • Original SQL query
  • Extracted parameters
  • Query Builder equivalent
  • Status indicators (βœ… Query Builder, πŸ’‘ ORM-ELIGIBLE, πŸ”§ Manual Review, ⚠️ HAS TODO)

List TODOs

List calls that need manual review (have TODO comments in the conversion):

sql_registry todos [--registry .sql_registry.json]

List ORM-Eligible Calls

List simple queries that can use frappe.get_all:

sql_registry orm [--registry .sql_registry.json]

These are quick wins - simple SELECT queries on single tables that don't need Query Builder.

Generate Report

Generate a markdown report of all SQL operations:

sql_registry report [--registry .sql_registry.json] [--output sql_report.md]

The report includes:

  • Conversion status summary (Query Builder, ORM-eligible, Manual Review, TODOs)
  • Implementation distribution (frappe_db_sql, query_builder, mixed)
  • Per-file breakdown with status indicators and SQL preview for each call

Rewrite SQL Call

Preview or apply Query Builder conversion for a single call:

# Preview changes (dry run)
sql_registry rewrite <call_id> [--registry .sql_registry.json]

# Apply changes to file
sql_registry rewrite <call_id> --apply [--registry .sql_registry.json]

Only calls with βœ… Query Builder or πŸ’‘ ORM-eligible status can be rewritten. Calls marked πŸ”§ Manual Review require manual conversion.

Conversion Status

StatusMeaning
βœ… Query BuilderValidated conversion ready to apply via rewrite --apply
πŸ’‘ ORM-eligibleSimple query, can use frappe.get_all instead
πŸ”§ Manual ReviewValidation failed - complex query needs manual conversion
⚠️ Has TODOsConversion has TODO comments requiring attention

Supported SQL Patterns

The converter handles:

SQL PatternQuery Builder Equivalent
SELECT ... FROMfrappe.qb.from_(...).select(...)
WHERE field = %s.where(table.field == value)
IS NULL / IS NOT NULL.isnull() / .isnotnull()
IFNULL() / COALESCE()fn.Coalesce(field, value)
IN (subquery).isin(SubQuery(...))
NOT IN (subquery).notin(SubQuery(...))
EXISTS (subquery)ExistsCriterion(...)
DATEDIFF()CustomFunction('DATEDIFF', ...)
COUNT(), SUM(), AVG()fn.Count(), fn.Sum(), fn.Avg()
GROUP BY.groupby(...)
HAVING.having(...)
ORDER BY.orderby(..., order=frappe.qb.desc)
LIMIT.limit(n)
BETWEENfield[start:end]
Arithmetic (+, -, *, /)Native operators

ORM-Eligible Queries

Simple queries are automatically converted to frappe.get_all:

Original:

frappe.db.sql("""
    SELECT name, status FROM `tabSales Order`
    WHERE customer = %s AND docstatus = 1
""", (customer,), as_dict=True)

Converted:

frappe.get_all(
    "Sales Order",
    filters={"customer": customer, "docstatus": 1},
    fields=["name", "status"]
)

A query is ORM-eligible when:

  • Single table (no JOINs)
  • No aggregations (COUNT, SUM, etc.)
  • No GROUP BY or HAVING
  • No subqueries
  • Simple WHERE conditions

Frappe v16 Compatibility

The converter generates v16-compatible ORM syntax for:

IFNULL/COALESCE in Filters

Original SQL:

frappe.db.sql("""
    SELECT name FROM tabDepartment
    WHERE ifnull(company, '') != ''
""")

v16 ORM Output:

from frappe.query_builder import Field
from frappe.query_builder.functions import IfNull

frappe.get_all(
    "Department",
    filters=[[IfNull(Field("company"), ""), "!=", ""]]
)

DISTINCT Queries

Original SQL:

frappe.db.sql("""
    SELECT DISTINCT parent FROM `tabBOM Operation`
    WHERE workstation = %s
""", (workstation,))

v16 ORM Output:

frappe.get_all(
    "BOM Operation",
    filters={"workstation": workstation},
    fields=["parent"],
    distinct=True
)

v16 Breaking Changes

Frappe v16 changed how frappe.get_all() handles fields and filters internally (now using Pypika Query Builder). Raw SQL strings for functions are no longer supported:

# OLD (broken in v16)
frappe.get_all("DocType", fields=["sum(qty) as total"])

# NEW (v16 compatible)
frappe.get_all("DocType", fields=[{"SUM": "qty", "as": "total"}])

The converter automatically uses the Query Builder path for aggregate queries, which generates valid pypika syntax that works in both v15 and v16.

How It Works

Conversion Pipeline

Every frappe.db.sql call passes through five stages before it reaches the registry.

No

Yes

No

Yes

β‘€ Validation β€” verify structural equivalence

Extract structure from original SQL AST
(tables, fields, conditions, LIMIT …)

Structures match?

Extract structure from generated QB code
via Python AST

β‘£ Conversion β€” choose the right target

Yes β€” ORM path

No β€” QB path

Single table?
No JOINs, aggregations,
or subqueries?

convert_select_to_orm()
β†’ frappe.get_all(…)

SQLToQBConverter
β†’ frappe.qb chain
UPDATE β†’ frappe.db.set_value or QB
DELETE β†’ frappe.db.delete or QB

β‘’ Eligibility Check

All named params
resolvable?

No dynamic
table names?

β‘‘ SQL Parsing β€” understand the query

Replace %s / %(name)s / {expr}
with internal placeholders

sqlglot.parse() β†’ SQL AST

β‘  Python AST β€” understand the call site

Detect frappe.db.sql call

Extract SQL string

Extract params
ast.Constant β†’ raw literal
ast.Name / Attribute / Call β†’ VarRef

Detect assignment context
variable name / return / for-loop / expression

πŸ“„ Python source file
frappe.db.sql("SELECT …", params)

πŸ”§ Manual Review
β€” stored in registry,
skipped by rewrite

πŸ”§ Manual Review
β€” generated template
commented out

βœ… Ready to apply
stored in .sql_registry.json

Applying a Conversion

Once a call has βœ… status in the registry, rewrite --apply writes it back to the source file.

Pin-drop strategy: from line numbers to structural XPath

Early versions located the call by line number, then re-scanned the entire directory after each write to refresh stale line numbers. This was fragile: any edit to the file invalidated every other call's ID.

The current approach stores a structural XPath address (ast_path) for each call at scan time, e.g.:

/Module/body[3]/FunctionDef/body[2]/For/iter/Call

This path encodes the call's position in the Python AST, not its line number. It survives whitespace changes, comment edits, and sibling conversions that shift line numbers β€” as long as the surrounding statement structure is unchanged. No re-scan is needed between rewrites.

Batch write pipeline

rewrite_batch groups calls by file, then dispatches each file to a worker in a ProcessPoolExecutor. Each worker runs the full pipeline independently:

_rewrite_file_calls() β€” worker process (per file)

Pass

Fail

Parse AST once
ast.parse(content)

Build XML tree once
astpath.convert_to_xml()

asttokens β€” char positions
for all targets in one pass

Collect all splices
(call ranges + DocType inserts)

Apply right-to-left
one string operation

_ensure_imports()
hoist missing imports to module level

black.format_str()
Python API β€” no subprocess

Compile check
ast.parse()

Write file
remove .bak + __pycache__

Abort β€” file untouched
(or write with --force)

rewrite_batch() β€” main process

Group calls by file

ProcessPoolExecutor
(one worker per CPU)

.sql_registry.json
query_builder_equivalent
ast_path

βœ“ Registry updated
for all written files

Key design decisions:

Old approachCurrent approach
Line-number call IDsStructural XPath IDs (ast_path)
Re-scan directory after each rewriteNo rescan needed β€” XPath is stable
Sequential file processingParallel across files (ProcessPoolExecutor)
One ast.parse + convert_to_xml per callOne parse + one XML build per file
black subprocess + temp file per fileblack.format_str() Python API in-process
replace_sql_in_content called N times, each re-parsingSplices collected in one pass, applied right-to-left

The VarRef Type

A key design insight: Python SQL params contain two fundamentally different kinds of values that must be rendered differently in generated code.

Generated filter

extract_params_from_call()

frappe.db.sql params dict

{'as_of_date': as_of_date}
ast.Name β†’ variable reference

{'status': 'Active'}
ast.Constant β†’ string literal

{'amount': 100}
ast.Constant β†’ numeric literal

VarRef('as_of_date')
emit unquoted

'Active'
raw str β€” emit quoted

100
raw int β€” emit as-is

['start_date', '<=', as_of_date]

{'status': 'Active'}

{'amount': 100}

VarRef objects survive JSON serialization in the registry as "__varref__:<expr>" and are reconstructed on load, so the distinction is preserved across sessions.

Example Workflow

# 1. Scan your app (from test_utils or with test_utils installed)
poetry run sql_registry scan --directory ~/frappe-bench/apps/myapp

# 2. Check for quick wins (ORM-eligible)
poetry run sql_registry orm

# 3. Check for issues needing manual review
poetry run sql_registry todos

# 4. Generate a report for the team
poetry run sql_registry report --output myapp_sql_audit.md

# 5. Preview a conversion
poetry run sql_registry show abc12345

# 6. Apply a conversion (only for βœ… or πŸ’‘ status)
poetry run sql_registry rewrite abc12345 --apply

The registry file (.sql_registry.json) is typically added to .gitignore since it's project-local scan data.

Scanning Multiple Codebases

Use --registry to maintain separate registries per app:

# Scan each app with its own registry
poetry run sql_registry scan --directory ~/bench/apps/erpnext --registry .sql_registry_erpnext.json
poetry run sql_registry scan --directory ~/bench/apps/hrms --registry .sql_registry_hrms.json
poetry run sql_registry scan --directory ~/bench/apps/myapp --registry .sql_registry_myapp.json

# Generate reports
poetry run sql_registry report --registry .sql_registry_erpnext.json --output erpnext_sql_report.md
poetry run sql_registry report --registry .sql_registry_hrms.json --output hrms_sql_report.md

Full Codebase Audit

To audit an entire Frappe/ERPNext installation:

cd /path/to/test_utils
rm -f .sql_registry.json
poetry run sql_registry scan --directory ~/frappe-bench/apps/erpnext/erpnext
poetry run sql_registry report --output erpnext_sql_report.md

Use poetry run sql_registry (or python -m sql_registry) when running from the test_utils repo.

Known Limitations (Manual Review Required)

The following patterns require πŸ”§ Manual Review and cannot be auto-rewritten:

  1. Dynamic SQL - Queries with {} f-string placeholders for dynamic conditions
  2. Complex Subqueries - Deeply nested or correlated subqueries
  3. Database-specific Functions - MySQL-specific functions (e.g. ROW_COUNT(), information_schema queries) without direct Query Builder equivalents
  4. Multi-table JOINs - Some JOIN patterns may not validate correctly
  5. Patches/Migrations - SQL in patch files is excluded by default; use --include-patches to include them

Package Structure

The sql_registry is implemented as a package (test_utils/utils/sql_registry/):

ModulePurpose
models.pySQLCall (incl. ast_path field), SQLStructure, VarRef, UnresolvedParameterError
scanner.pyAST scanning, param extraction (VarRef for variable refs), ORM conversion
converter.pySQLToQBConverter — SQL→Query Builder, positional %s mapping
registry.pySQLRegistry β€” load/save, scan with XPath extraction, report generation
cli.pymain() β€” argparse and command dispatch

The rewriter lives in test_utils/pre_commit/sql_rewriter_functions.py:

SymbolKindPurpose
apply_blackfunctionFormat Python with black.format_str() β€” no subprocess
locate_call_nodefunctionFind AST Call via XPath (primary) or SQL-text+occurrence (fallback)
locate_enclosing_stmt_nodefunctionFind enclosing statement for DocType line insertion
rewrite_file_callsfunctionFull per-file pipeline β€” picklable worker for ProcessPoolExecutor
FileRewriteResultdataclassStructured result from worker process
split_qb_codefunctionSplit QB equivalent into (imports, doctype_lines, expr)
ensure_importsfunctionHoist missing imports to module level (AST-aware, deduplicating)
SQLRewriterclassCLI entry point β€” orchestrates scan, show, rewrite

Backward-compatible imports remain unchanged:

from test_utils.utils.sql_registry import SQLRegistry, SQLCall, main

See Also

Please contact us for corrections, additions, questions, or to request a topic.

Β© 2026 AgriTheory. All Rights Reserved.