Everything we've written about SQLite: connections, SQL patterns, imports, clients, and comparisons.
Connect to SQLite from Ruby with the sqlite3 gem, Sequel, or ActiveRecord. Working code for connections, parameterized queries, WAL mode, the busy_timeout lock fix, and the accidental-file-creation trap.
Connect to SQLite from PHP with PDO and the SQLite3 class: opening files safely, WAL mode, the busy timeout for 'database is locked', transactions, prepared statements, and the errors you actually hit.
Connect to SQLite from C# with Microsoft.Data.Sqlite: ADO.NET basics, async, parameters, transactions, WAL and busy_timeout, Dapper and EF Core, plus System.Data.SQLite and the errors you actually hit.
Connect to SQLite from Java with the Xerial sqlite-jdbc driver: DriverManager, file vs in-memory databases, WAL mode, busy_timeout for locking, PreparedStatement, and the errors you actually hit.
Connect to SQLite from Go with mattn/go-sqlite3, modernc.org/sqlite, or ncruces/go-sqlite3. Working code, the cgo vs pure-Go cross-compilation decision, WAL mode, busy_timeout for 'database is locked', and pool settings for an embedded database.
Use SQLite from Node.js with the built-in node:sqlite module or better-sqlite3. Working code for both, why the classic sqlite3 package is deprecated, and when synchronous database access is actually the right call.
Connect to SQLite from Python with the built-in sqlite3 module or aiosqlite for asyncio. Working code for connections, parameters, WAL mode, and the concurrency gotchas that bite in production.
How to use ATTACH DATABASE in SQLite to query and join across multiple database files, schema-name resolution rules, DETACH, transaction atomicity, and the attached-database limit.
Go beyond basic recursion in SQLite: traverse general graphs with WITH RECURSIVE, detect and break cycles by tracking the path, control depth-first vs breadth-first order, and tune materialization.
SQLite has no native partitioned tables. The practical alternatives: ATTACH DATABASE with UNION ALL across files, partial indexes, separate tables per partition with a UNION view, and when you have outgrown SQLite entirely.
How SQLite triggers work: BEFORE, AFTER, and INSTEAD OF, the FOR EACH ROW-only model, the WHEN clause, OLD and NEW references, RAISE() for validation, and the pitfalls that bite people coming from PostgreSQL or MySQL.
How count, sum, avg, min, max, and group_concat work in SQLite, plus DISTINCT, the FILTER clause, ORDER BY inside aggregates, HAVING, and the integer-division and NULL gotchas that produce wrong totals.
How to read SQLite's EXPLAIN QUERY PLAN output: the difference between SCAN and SEARCH, what USING INDEX and USING COVERING INDEX mean, spotting USE TEMP B-TREE for sorting, and turning a slow SCAN into a fast SEARCH.
SQLite generated columns (3.31.0+): the difference between VIRTUAL and STORED, when to index a generated column, why VIRTUAL columns become expression indexes, and the limitations to know before you reach for them.
A practical reference for SQLite string functions: substr, replace, instr, trim, length, printf/format, and concat. Covers 1-based indexing, the missing functions you'll wish existed, and how to work around them.
How scalar, column, row, and table subqueries work in SQLite, the difference between correlated and non-correlated subqueries, IN vs EXISTS, the NOT IN NULL trap, and when a subquery should be a join or a CTE instead.
How transactions work in SQLite: BEGIN, COMMIT, and ROLLBACK, the DEFERRED/IMMEDIATE/EXCLUSIVE locking modes, SAVEPOINT for nested rollback, WAL mode for concurrent readers, and the SQLITE_BUSY trap to avoid.
Insert-or-update in SQLite three ways: ON CONFLICT DO UPDATE (the real UPSERT, 3.24.0+), INSERT OR REPLACE, and INSERT OR IGNORE. How the excluded table works, why REPLACE deletes the row, and which one to reach for.
How to create and drop views in SQLite, why views are read-only, how to make a view writable with INSTEAD OF triggers, temporary views, the column-name list, and the difference between a view and a materialized result.
Learn how to use SQLite common table expressions: ordinary CTEs with WITH, recursive CTEs with WITH RECURSIVE, materialization hints, and practical hierarchy and series examples.
SQLite has no native date type. Learn how date(), time(), datetime(), strftime(), unixepoch(), and julianday() work, how modifiers chain, and how to store dates so queries stay fast.
How to add full-text search to SQLite using the FTS5 virtual table: MATCH queries, bm25 ranking, external content tables, snippets, and tokenizer options.
How indexes work in SQLite: B-tree structure, single-column and composite indexes, partial and expression indexes, covering indexes, and reading EXPLAIN QUERY PLAN to confirm an index is used.
A practical guide to SQLite joins: INNER, LEFT, CROSS, RIGHT, and FULL OUTER JOIN (native since 3.39.0), self-joins, anti-joins, and how join order affects results.
How to query JSON in SQLite: json_extract, the -> and ->> operators, json_each and json_tree, aggregation with json_group_array, indexing JSON paths, and the JSONB type added in 3.45.0.
SQLite has no PIVOT keyword. Learn how to transpose rows into columns using CASE expressions and the FILTER clause, handle dynamic columns, and avoid common pitfalls.
Learn how to use SQLite window functions including ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, and LEAD with the OVER and PARTITION BY clauses, plus frame specifications.
Four ways to import Excel data into SQLite: convert to CSV and use the .import command, Python with pandas, the sqlite3 CLI with a script, and Mako. Covers type affinity, large files, and multi-sheet workbooks.
Four ways to import JSON data into SQLite: sqlite-utils CLI, the .import command, Python scripting, and Mako. Covers JSON1 functions, type handling, and nested data.
Four ways to import CSV files into SQLite: the .import command, Python's sqlite3 module, DB Browser for SQLite, and programmatic approaches. Includes type handling tips.