SQLite Search in Frappe v15

What is SQLite Search?

SQLite Search in Frappe v15 is a full-text search (FTS) engine powered by SQLite FTS5, used when running Frappe with the SQLite database backend instead of MariaDB. It enables lightweight, performant full-text search queries in development environments or embedded deployments.

Frappe automatically uses SQLite FTS when:

• The configured database engine is SQLite
• A table is enabled for FTS indexing
• The FTS5 extension is available in the SQLite build

Why SQLite Search exists in Frappe?

Frappe Framework supports multiple database engines. While MariaDB remains the default production database with ElasticSearch-like indexing patterns, SQLite Search provides a portable, serverless alternative for:

• Local development
• Edge computing
• Offline/embedded deployments
• Testing search functionality without external services

SQLite Search aligns with Frappe’s DB abstraction layer, making full-text search available without modifying business logic.

How does SQLite Search work in Frappe?

Core Mechanism

Frappe uses:

• FTS5 virtual tables
• Triggers on base tables
• Indexed columns for full text
• Search query wrapper APIs

The relevant implementation exists in frappe.database.sqlite package.

Supported Search Features

SQLite Search provides:

Full Text Search in SQLite using FTS5

Internal Indexing

When a DocType supports full-text search, Frappe automatically creates an FTS table:

Example (internal structure):

CREATE VIRTUAL TABLE <table>_fts USING fts5(
name,
content='DocType Table',
tokenize='porter'
);

This is managed by Frappe internally, based on DocType metadata.

How to enable SQLite Search?

Step-by-step setup

SQLite Search is enabled automatically when:

• You create a Frappe site using SQLite
• Your DocType has search_fields or title_field
• Frappe detects available FTS5 module

Create a new SQLite-backed site

bench new-site mysite.localhost --db-type=sqlite

This creates a site using SQLite database.

For multi-site configuration, the config must contain:

{
"db_type": "sqlite",
"db_name": "mydb.sqlite"
}

⚠ SQLite Search is available only when db_type is sqlite

How to run a full text search?

Use the frappe.db.full_text_search API.

Syntax:

results = frappe.db.full_text_search("Customer", "John")

This performs a full-text search on Customer table.

Example in Python

import frappe
def search_customer():
return frappe.db.full_text_search("Customer", "John Doe")

This returns ranked search results based on FTS relevance score.

Ranking and Scoring

SQLite FTS5 provides:

• Natural language scoring
• BM25 algorithm
• Rank-based ordering

Frappe returns results with:

• Document name
• Scoring metadata (if enabled by config)

Frappe may normalize or filter ranking results for display purposes.

How to index additional fields?

SQLite Search indexes fields defined in the DocType under:

• search_fields
• title_field
• in_standard_filter
• name

Example DocType JSON:

"search_fields": "customer_name, email_id"

Frappe automatically includes these fields in the FTS index.

How is SQLite FTS integrated in Frappe Queries?

For full-text search UI in Desk view, Frappe uses:

• “?query=<search> to trigger FTS lookup
• FTS results are combined with filter results
• When using SQLite, FTS has higher priority than LIKE queries

SQLite Search vs MariaDB Search

SQLite Search is recommended for development, not for production ERP environments.

Best Practices & Recommendations

• Use SQLite Search primarily for development
• Use MariaDB + elastic search-like behavior for production workloads
• Test FTS logic locally using SQLite before deploying to production
• Ensure FTS5 extension is compiled in your SQLite build (required)
• Index only essential fields to reduce overhead
• Keep search queries short for faster performance

Troubleshooting SQLite Search

FTS table is not created

• Verify the DocType specifies search_fields
• Ensure SQLite has fts5 enabled
• Check logs for database errors

Search returns no results

• Check if terms are tokenized
• Try lowercase match
• Check whether the value exists in indexed fields

Bench or database migration errors

• Ensure correct schema migration
• Delete corrupted SQLite file during development
• Reinstall site if schema mismatch occurs

Integration Patterns

SQLite Search is ideal when:

• Building Frappe apps for desktop
• Running ERPNext in local mode
• Developing offline-first PWA
• Building edge devices with ERP capabilities
• Testing FTS queries without production DB configuration

Target Audience Tags

• Frappe App Developers
• ERPNext Customization Engineers
• Offline/Desktop ERP Builders
• Lightweight Database Integration Teams
• Embedded Systems Developers

Technical References

Official Documentation

https://docs.frappe.io/framework/sqlite-search

GitHub (Frappe v15)

https://github.com/frappe/frappe/tree/version-15