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_allinstead
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
| Status | Meaning |
|---|---|
| β Query Builder | Validated conversion ready to apply via rewrite --apply |
| π‘ ORM-eligible | Simple query, can use frappe.get_all instead |
| π§ Manual Review | Validation failed - complex query needs manual conversion |
| β οΈ Has TODOs | Conversion has TODO comments requiring attention |
Supported SQL Patterns
The converter handles:
| SQL Pattern | Query Builder Equivalent |
|---|---|
SELECT ... FROM | frappe.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) |
BETWEEN | field[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.
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:
Key design decisions:
| Old approach | Current approach |
|---|---|
| Line-number call IDs | Structural XPath IDs (ast_path) |
| Re-scan directory after each rewrite | No rescan needed β XPath is stable |
| Sequential file processing | Parallel across files (ProcessPoolExecutor) |
One ast.parse + convert_to_xml per call | One parse + one XML build per file |
black subprocess + temp file per file | black.format_str() Python API in-process |
replace_sql_in_content called N times, each re-parsing | Splices 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.
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:
- Dynamic SQL - Queries with
{}f-string placeholders for dynamic conditions - Complex Subqueries - Deeply nested or correlated subqueries
- Database-specific Functions - MySQL-specific functions (e.g.
ROW_COUNT(),information_schemaqueries) without direct Query Builder equivalents - Multi-table JOINs - Some JOIN patterns may not validate correctly
- Patches/Migrations - SQL in patch files is excluded by default; use
--include-patchesto include them
Package Structure
The sql_registry is implemented as a package (test_utils/utils/sql_registry/):
| Module | Purpose |
|---|---|
models.py | SQLCall (incl. ast_path field), SQLStructure, VarRef, UnresolvedParameterError |
scanner.py | AST scanning, param extraction (VarRef for variable refs), ORM conversion |
converter.py | SQLToQBConverter β SQLβQuery Builder, positional %s mapping |
registry.py | SQLRegistry β load/save, scan with XPath extraction, report generation |
cli.py | main() β argparse and command dispatch |
The rewriter lives in test_utils/pre_commit/sql_rewriter_functions.py:
| Symbol | Kind | Purpose |
|---|---|---|
apply_black | function | Format Python with black.format_str() β no subprocess |
locate_call_node | function | Find AST Call via XPath (primary) or SQL-text+occurrence (fallback) |
locate_enclosing_stmt_node | function | Find enclosing statement for DocType line insertion |
rewrite_file_calls | function | Full per-file pipeline β picklable worker for ProcessPoolExecutor |
FileRewriteResult | dataclass | Structured result from worker process |
split_qb_code | function | Split QB equivalent into (imports, doctype_lines, expr) |
ensure_imports | function | Hoist missing imports to module level (AST-aware, deduplicating) |
SQLRewriter | class | CLI entry point β orchestrates scan, show, rewrite |
Backward-compatible imports remain unchanged:
from test_utils.utils.sql_registry import SQLRegistry, SQLCall, main