Back to Details

sqlite-expert

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

SQLite Expert

SQLite专家

A database specialist with deep expertise in SQLite internals, performance tuning, and embedded database patterns. This skill provides guidance for using SQLite effectively in applications ranging from mobile apps and IoT devices to server-side caching layers and analytical workloads, leveraging its advanced features well beyond simple key-value storage.
一位精通SQLite内部机制、性能调优和嵌入式数据库模式的数据库专家。本技能为从移动应用、IoT设备到服务器端缓存层和分析工作负载等各类应用中有效使用SQLite提供指导，充分利用其超越简单键值存储的高级功能。

Key Principles

核心原则

  • Enable WAL mode (PRAGMA journal_mode=WAL) for concurrent read/write access; it allows readers to proceed without blocking writers and vice versa
  • Use PRAGMA busy_timeout to set a reasonable wait duration (e.g., 5000ms) instead of receiving SQLITE_BUSY errors immediately on contention
  • Design schemas with appropriate indexes from the start; SQLite's query planner relies heavily on index availability for efficient execution plans
  • Keep transactions short and explicit; wrap related writes in BEGIN/COMMIT to ensure atomicity and reduce fsync overhead
  • Understand that SQLite is serverless and single-file; its strength is simplicity and reliability, not high-concurrency multi-writer workloads
  • 启用WAL模式（PRAGMA journal_mode=WAL）以支持并发读写访问；该模式允许读取操作在不阻塞写入操作的情况下进行，反之亦然
  • 使用PRAGMA busy_timeout设置合理的等待时长（例如5000ms），避免在出现资源争用时立即收到SQLITE_BUSY错误
  • 从一开始就设计带有适当索引的Schema；SQLite的查询规划器严重依赖索引的可用性来生成高效的执行计划
  • 保持事务简短且明确；将相关写入操作包裹在BEGIN/COMMIT中，以确保原子性并减少fsync开销
  • 了解SQLite是无服务器且单文件的数据库；它的优势在于简单性和可靠性，而非高并发多写入工作负载

Techniques

技术技巧

  • Set performance PRAGMAs at connection open: journal_mode=WAL, synchronous=NORMAL, cache_size=-64000 (64MB), mmap_size=268435456, temp_store=MEMORY
  • Use FTS5 for full-text search: CREATE VIRTUAL TABLE docs USING fts5(title, body) with MATCH queries and bm25() ranking
  • Query JSON data with the JSON1 extension: json_extract(), json_each(), json_group_array() for document-style data stored in TEXT columns
  • Write recursive CTEs (WITH RECURSIVE) for tree traversal, graph walking, and generating series of values
  • Use window functions (ROW_NUMBER, LAG, LEAD, SUM OVER) for running totals, rankings, and time-series analysis without self-joins
  • Create covering indexes that include all columns needed by a query to enable index-only scans (verified with EXPLAIN QUERY PLAN showing COVERING INDEX)
  • Implement UPSERT with INSERT ... ON CONFLICT (column) DO UPDATE SET for atomic insert-or-update operations
  • 在连接打开时设置性能相关PRAGMA：journal_mode=WAL、synchronous=NORMAL、cache_size=-64000（64MB）、mmap_size=268435456、temp_store=MEMORY
  • 使用FTS5进行全文搜索：CREATE VIRTUAL TABLE docs USING fts5(title, body)，配合MATCH查询和bm25()排序
  • 使用JSON1扩展查询JSON数据：json_extract()、json_each()、json_group_array()用于处理存储在TEXT列中的文档式数据
  • 编写递归CTE（WITH RECURSIVE）用于树遍历、图遍历和生成值序列
  • 使用窗口函数（ROW_NUMBER、LAG、LEAD、SUM OVER）计算运行总计、排名和时间序列分析，无需自连接
  • 创建包含查询所需所有列的覆盖索引，以启用仅索引扫描（可通过EXPLAIN QUERY PLAN显示COVERING INDEX来验证）
  • 使用INSERT ... ON CONFLICT (column) DO UPDATE SET实现UPSERT，完成原子性的插入或更新操作

Common Patterns

常见模式

  • Multi-database Access: Use ATTACH DATABASE to query across multiple SQLite files in a single connection, joining tables from different databases
  • Application-defined Functions: Register custom scalar or aggregate functions in your host language for domain-specific computations inside SQL queries
  • Incremental Vacuum: Use PRAGMA auto_vacuum=INCREMENTAL with periodic PRAGMA incremental_vacuum to reclaim space without a full VACUUM lock
  • Schema Migration: Use PRAGMA user_version to track schema version and apply migration scripts sequentially on application startup
  • 多数据库访问：使用ATTACH DATABASE在单个连接中跨多个SQLite文件进行查询，连接来自不同数据库的表
  • 应用定义函数：在宿主语言中注册自定义标量或聚合函数，以便在SQL查询中执行特定领域的计算
  • 增量真空：使用PRAGMA auto_vacuum=INCREMENTAL并定期执行PRAGMA incremental_vacuum，以在不进行全量VACUUM锁定的情况下回收空间
  • Schema迁移：使用PRAGMA user_version跟踪Schema版本，并在应用启动时按顺序应用迁移脚本

Pitfalls to Avoid

需避免的陷阱

  • Do not open multiple connections with different PRAGMA settings; WAL mode and other PRAGMAs should be set consistently on every connection
  • Do not use SQLite for high-concurrency write workloads with dozens of simultaneous writers; consider PostgreSQL or another client-server database instead
  • Do not store large BLOBs (over 1MB) inline; SQLite performs better when large objects are stored as external files with paths referenced in the database
  • Do not skip EXPLAIN QUERY PLAN during development; without it, slow full-table scans go unnoticed until production load reveals them
  • 不要打开多个带有不同PRAGMA设置的连接；WAL模式和其他PRAGMA应在每个连接上保持一致设置
  • 不要将SQLite用于拥有数十个同时写入者的高并发写入工作负载；请考虑使用PostgreSQL或其他客户端-服务器数据库
  • 不要内联存储大型BLOB（超过1MB）；当大型对象存储为外部文件并在数据库中引用其路径时，SQLite的性能更佳
  • 开发期间不要跳过EXPLAIN QUERY PLAN；否则，缓慢的全表扫描会一直未被发现，直到生产负载暴露问题