SQLite Documentation
Concise, example-driven SQLite reference. Read the concept, see the code, then practice it in a Coddy journey.
Start a guided SQLite journeyGetting Started
- What Is SQLiteSQLite is a serverless, file-based SQL database that runs inside your application. Here's what it actually is, how it differs from a database server, and when it shines.
- SQLite vs MySQLHow SQLite and MySQL actually differ — architecture, concurrency, types, and the kinds of projects each one is built for.
- SQLite vs PostgresHow SQLite and PostgreSQL really differ — architecture, concurrency, types, and the kinds of projects each one fits best.
- When to Use SQLiteA practical guide to when SQLite is the right database — and when you should reach for Postgres or MySQL instead.
- Install SQLiteHow to install SQLite on Windows, macOS, and Linux — getting the command-line tool, putting it on your PATH, and verifying everything works.
- SQLite CLIHow to use the sqlite3 command line shell — opening databases, running queries, dot commands, and importing or exporting data without leaving the terminal.
- Browser ToolsA practical tour of the GUI tools for opening, browsing, and editing SQLite database files — DB Browser for SQLite, SQLiteStudio, and online viewers.
- Create DatabaseHow to create a SQLite database — the file-based mental model, the `sqlite3` command, and why there's no `CREATE DATABASE` statement.
- In-Memory DatabaseHow SQLite's in-memory database works, when to reach for `:memory:`, and how it differs from a file-backed database you can keep around.
Tables & Types
- CREATE TABLEHow to create tables in SQLite — column definitions, constraints, IF NOT EXISTS, temporary tables, and CREATE TABLE AS SELECT.
- Data TypesHow SQLite stores values — the five storage classes, why typing is dynamic, and the gotchas that catch people coming from Postgres or MySQL.
- Type AffinityHow SQLite's type affinity system works — the five affinities, the rules that pick one from your column declaration, and why an INTEGER column can hold a string.
- STRICT TablesHow SQLite's STRICT tables turn off type-flexible storage, reject mismatched values, and give you the type checking you probably expected all along.
- Primary KeysHow primary keys work in SQLite — the special INTEGER PRIMARY KEY, composite keys, AUTOINCREMENT, and the quirks that catch newcomers.
- ROWIDWhat ROWID actually is in SQLite, when INTEGER PRIMARY KEY makes it your column, and why WITHOUT ROWID tables exist.
- NOT NULL & DefaultsHow NOT NULL and DEFAULT work in SQLite — what they actually enforce, the CURRENT_TIMESTAMP trick, and the gotchas around adding them to existing tables.
- CHECK ConstraintsHow to use CHECK constraints in SQLite to enforce rules on column values — single-column checks, multi-column checks, named constraints, and the gotchas.
- UNIQUE ConstraintsHow UNIQUE constraints work in SQLite — column-level, table-level, composite keys, the NULL quirk, and what to do when one fails.
- DROP & ALTER TABLEHow to drop, rename, and alter tables in SQLite — what ALTER TABLE supports, what it doesn't, and the rebuild trick for the changes it can't do.
Inserting & Modifying Data
- INSERTHow INSERT works in SQLite — single rows, multi-row inserts, INSERT...SELECT, default values, and the OR IGNORE / OR REPLACE conflict modifiers.
- UPDATEHow to change existing rows in SQLite — UPDATE syntax, the WHERE clause that keeps you safe, multi-column updates, and UPDATE ... FROM for cross-table edits.
- DELETEHow DELETE works in SQLite — writing a safe WHERE clause, deleting all rows, cascading to related tables, and getting deleted rows back with RETURNING.
- UPSERTHow UPSERT works in SQLite — the ON CONFLICT clause, the excluded table, DO NOTHING vs DO UPDATE, and how it differs from INSERT OR REPLACE.
- RETURNING ClauseHow the RETURNING clause works in SQLite — fetching the rows your INSERT, UPDATE, or DELETE just touched without a second query.
- Import CSVHow to import CSV files into SQLite using the .import command — handling headers, existing tables, custom separators, and the most common errors.
- Export DataHow to export data out of SQLite — CSV with headers, JSON, full SQL dumps, and single-table backups using the sqlite3 command-line shell.
Querying Data
- SELECT BasicsHow the SELECT statement works in SQLite — picking columns, computing expressions, aliasing results, and the mental model behind every query you'll ever write.
- WHERE ClauseHow the WHERE clause filters rows in SQLite — comparison operators, AND/OR, LIKE, IN, BETWEEN, and the NULL pitfall that catches everyone.
- Operators & NULLHow SQLite operators work with NULL — why `=` and `<>` don't behave like you'd expect, and the IS, IS NOT, COALESCE, and IFNULL tools that do.
- ORDER BYHow ORDER BY works in SQLite — sorting ascending and descending, breaking ties with multiple columns, handling NULLs, and case-insensitive sorting.
- LIMIT & OFFSETHow LIMIT and OFFSET work in SQLite — capping rows, skipping rows, paginating safely, and the performance trap to avoid on large tables.
- DISTINCTHow SELECT DISTINCT works in SQLite — single columns, multiple columns, NULL handling, COUNT(DISTINCT), and when GROUP BY is the better tool.
- CASE ExpressionsHow CASE works in SQLite — simple and searched forms, using it in SELECT, ORDER BY, and WHERE, plus when to reach for IIF instead.
Functions & Aggregates
- String FunctionsPractical string functions in SQLite — concatenation, SUBSTR, INSTR, REPLACE, TRIM, and the patterns for cleaning and reshaping text in queries.
- Numeric FunctionsHow to do arithmetic in SQLite — ROUND, ABS, CEIL, FLOOR, MOD, POWER, SQRT, RANDOM — and the integer-division trap that catches everyone once.
- Date & TimeHow SQLite stores and manipulates dates — the five date functions, format strings, modifiers, and the storage choices that keep your queries fast.
- Aggregate FunctionsHow aggregate functions in SQLite collapse many rows into one value — COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, plus DISTINCT, FILTER, and the NULL rules.
- GROUP BY & HAVINGHow GROUP BY collapses rows into buckets in SQLite, and how HAVING filters those buckets after aggregation — with the WHERE vs HAVING distinction made concrete.
Relationships & Joins
- Foreign KeysHow foreign keys work in SQLite — declaring REFERENCES, enabling enforcement with PRAGMA, and choosing the right ON DELETE behavior.
- INNER JOINHow INNER JOIN works in SQLite — the mental model, the ON clause, joining three tables, and the USING shortcut.
- LEFT JOINHow LEFT JOIN works in SQLite — keeping unmatched rows, reading NULLs, filtering safely, and joining more than two tables.
- Self JoinHow a self join works in SQLite — pairing rows from the same table using aliases, with worked examples for employee/manager and hierarchical data.
- SubqueriesHow to nest one SELECT inside another in SQLite — scalar subqueries, IN/EXISTS, derived tables, correlated subqueries, and when a JOIN reads better.
- CTEsHow Common Table Expressions work in SQLite — using WITH to name subqueries, chain multiple CTEs, and write queries that read top-to-bottom.
- Recursive CTEsHow recursive CTEs work in SQLite — the anchor/recursive shape, walking parent-child trees, generating series, and avoiding infinite loops.
Indexes & Performance
- IndexesHow indexes work in SQLite, when they help, when they hurt, and how to read whether the planner is actually using them.
- Composite IndexesHow multi-column indexes work in SQLite, why column order matters, and when a composite index helps versus when it just wastes space.
- Partial IndexesHow partial indexes work in SQLite — indexing only the rows you actually query, and the patterns (soft deletes, partial uniqueness, hot subsets) that make them pay off.
- EXPLAIN QUERY PLANHow to use EXPLAIN QUERY PLAN in SQLite to see whether your query uses an index, what SCAN and SEARCH mean, and how to read join plans.
- ANALYZE & VACUUMHow `ANALYZE` and `VACUUM` keep an SQLite database fast and compact — what each one actually does, when to run them, and the variants worth knowing.
Advanced Features
- TransactionsHow transactions work in SQLite — BEGIN, COMMIT, ROLLBACK, autocommit, and the DEFERRED/IMMEDIATE/EXCLUSIVE modes that decide when locks are taken.
- SavepointsHow savepoints work in SQLite — named markers inside a transaction you can roll back to without throwing the whole thing away.
- ViewsHow views work in SQLite — saving queries as virtual tables, when to use temporary views, and why SQLite's views are read-only by default.
- TriggersHow SQLite triggers work — BEFORE and AFTER, INSTEAD OF on views, the OLD and NEW row references, and when triggers are the right tool.
- JSON SupportHow SQLite stores and queries JSON — extracting fields, updating values, expanding arrays with json_each, and indexing JSON paths for speed.
- Full-Text SearchHow to add full-text search to SQLite with FTS5 — creating virtual tables, the MATCH operator, BM25 ranking, and keeping the index in sync with your data.
- Window FunctionsHow window functions work in SQLite — OVER, PARTITION BY, ranking functions, LAG/LEAD, and frame clauses for running totals.
- Generated ColumnsHow generated columns work in SQLite — declaring them, choosing between VIRTUAL and STORED, and indexing them for fast lookups.
- ATTACH DATABASEHow ATTACH DATABASE lets you open multiple SQLite files in one connection, query across them with schema prefixes, and detach when you're done.
Safety & Application Integration
- Prepared StatementsWhat prepared statements are in SQLite, why they exist, and the prepare/bind/step/finalize lifecycle that every driver wraps.
- Binding ParametersHow parameter binding works in SQLite — positional placeholders, named parameters, and the rules for passing values safely from your application.
- SQL InjectionWhy string concatenation is dangerous, how SQL injection actually works, and how parameterized queries in SQLite shut it down for good.
- App ConnectionsHow applications open and use a SQLite database — connection strings, file paths, drivers across languages, and the settings worth getting right on day one.
- MigrationsHow to evolve an SQLite schema safely over time using PRAGMA user_version, ordered migration scripts, and transactions to keep things reversible.
- WAL & ConcurrencyHow SQLite's write-ahead logging changes the concurrency story — readers and writers stop blocking each other, and what the -wal and -shm files actually do.
Production & Operations
- Backup & RestoreHow to back up and restore a SQLite database safely — the .backup command, VACUUM INTO, the online backup API, and why copying the file is the wrong move.
- PRAGMA SettingsThe PRAGMA statements that actually matter — journal_mode, synchronous, foreign_keys, busy_timeout, cache_size — and the values to use in production.
- Common ErrorsThe SQLite errors you'll actually hit in production — database is locked, readonly database, disk image malformed, constraint failures — and how to fix them.
