12 min read
Full-Text Search in SQLite with FTS5
Go beyond LIKE queries. Learn how to build lightning-fast full-text search using SQLite FTS5 virtual tables with BM25 relevance ranking.
Read articleTag
This is a lightweight tag archive. It can help you find repeated concepts across posts, but topic hubs are the primary reading paths when you want a stronger sequence or a clearer problem-focused cluster.
This tag overlaps with the broader blog topic paths below. Use them when you want a more curated reading order rather than a raw tag archive.
Data Prep
Use this path when the hard part is turning messy files, semi-structured payloads, or staging tables into something you can query with confidence.
Schema
Useful when the hard part is not the query itself but the table design, relationships, and semi-structured data underneath it.
This tag is also connected to practical tools. Use these workflow hubs when you want to apply the concept directly instead of reading more articles first.
Conversion
Useful when JSON, CSV, spreadsheets, or SQL seed files need to move quickly between application payloads and relational tables.
Schema
Use this cluster when the challenge is understanding table structure, relationships, migration impact, or how to generate realistic data from a schema.
Go beyond LIKE queries. Learn how to build lightning-fast full-text search using SQLite FTS5 virtual tables with BM25 relevance ranking.
Read articleHow do you calculate "Year to Date" revenue or a "7-Day Moving Average" in SQL? The answer is Window Functions. Learn how to look back across rows without using self-joins.
Read articleYou don't always need Elasticsearch. Learn how to build a capable search engine with relevance ranking (Exact > Starts With > Contains) using standard SQL.
Read articleNeed 1,000 rows to test your query performance? Don't write a Python script. Learn how to use Recursive CTEs to generate massive datasets directly in SQLite.
Read articleLearn SQLite UPSERT with ON CONFLICT so you can INSERT or UPDATE in one query. Includes DO NOTHING, DO UPDATE, excluded values, and practical examples.
Read articleSQLite doesn't support materialized views, but you can build them yourself! Learn how to use triggers to create high-performance cached summary tables.
Read articleStop choosing between SQL and NoSQL. Learn how to store, query, and filter JSON data directly within your shiny relational database.
Read articleTags are kept as cross-cutting archives, not as the main blog information architecture. If you are choosing where to start, the topic hubs usually provide a better reading order and clearer surrounding context.