*** DRAFT ***
▼ Document Lists And Indexes
► Overview Documents
► Programming Interfaces
- About SQLite
A high-level overview of what SQLite is and why you might be
interested in using it.
- Appropriate Uses For SQLite
This document describes situations where SQLite is an appropriate
database engine to use versus situations where a client/server
database engine might be a better choice.
- Distinctive Features
This document enumerates and describes some of the features of
SQLite that make it different from other SQL database engines.
- Quirks of SQLite
This document is a short list of some unusual features of SQLite
that tend to cause misunderstandings and confusion. The list includes
both deliberate innovations and "misfeatures" that are retained only
for backwards compatibility.
- How SQLite Is Tested
The reliability and robustness of SQLite is achieved in large part
by thorough and careful testing. This document identifies the
many tests that occur before every release of SQLite.
SQLite is in the public domain. This document describes what that means
and the implications for contributors.
- Frequently Asked Questions
The title of the document says all...
- Books About SQLite
A list of independently written books about SQLite.
- SQLite In 5 Minutes Or Less
A very quick introduction to programming with SQLite.
- Introduction to the C/C++ API
This document introduces the C/C++ API. Users should read this document
before the C/C++ API Reference Guide linked below.
- How To Compile SQLite
Instructions and hints for compiling SQLite C code and integrating
that code with your own application.
- C/C++ API Reference
This document describes each API function separately.
- Result and Error Codes
A description of the meanings of the numeric result codes
returned by various C/C++ interfaces.
- SQL Syntax
This document describes the SQL language that is understood by
- Pragma commands
This document describes SQLite performance tuning options and other
special purpose database commands.
- Core SQL Functions
General-purpose built-in scalar SQL functions.
- Aggregate SQL Functions
General-purpose built-in aggregate SQL functions.
- Date and Time SQL Functions
SQL functions for manipulating dates and times.
- Window Functions
SQL Window functions.
- Generated Columns
Stored and virtual columns in table definitions.
C#/.NET bindings for SQLite
- Tcl API
A description of the TCL interface bindings for SQLite.
SQLite version 3 introduces the concept of manifest typing, where the
type of a value is associated with the value itself, not the column that
it is stored in.
This page describes data typing for SQLite version 3 in further detail.
- Json1 - JSON Integration
SQL functions for creating, parsing, and querying JSON content.
- FTS5 - Full Text Search
A description of the SQLite Full Text Search (FTS5) extension.
- FTS3 - Full Text Search
A description of the SQLite Full Text Search (FTS3) extension.
- R-Tree Module
A description of the SQLite R-Tree extension. An R-Tree is a specialized
data structure that supports fast multi-dimensional range queries often
used in geospatial systems.
The Sessions extension allows change to an SQLite database to be
captured in a compact file which can be reverted on the original
database (to implement "undo") or transferred and applied to another
- Run-Time Loadable Extensions
A general overview on how run-time loadable extensions work, how they
are compiled, and how developers can create their own run-time loadable
extensions for SQLite.
- SQLite Android Bindings
Information on how to deploy your own private copy of SQLite on
Android, bypassing the built-in SQLite, but using the same Java
- Dbstat Virtual Table
The DBSTAT virtual table reports on the sizes and geometries of tables
storing content in an SQLite database, and is the basis for the
[sqlite3_analyzer] utility program.
- Csv Virtual Table
The CSV virtual table allows SQLite to directly read and query
[https://www.ietf.org/rfc/rfc4180.txt|RFC 4180] formatted files.
CARRAY is a [table-valued function] that allows C-language arrays to
be used in SQL queries.
A description of the generate_series() [table-valued function].
The spellfix1 extension is an experiment in doing spelling correction
for [full-text search].
- 8+3 Filenames
How to make SQLite work on filesystems that only support
A description of the AUTOINCREMENT keyword in SQLite, what it does,
why it is sometimes useful, and why it should be avoided if not
- Backup API
The [sqlite3_backup_init | online-backup interface] can be used to
copy content from a disk file into an in-memory database or vice
versa and it can make a hot backup of a live database. This application
note gives examples of how.
- Error and Warning Log
SQLite supports an "error and warning log" design to capture information
about suspicious and/or error events during operation. Embedded applications
are encouraged to enable the error and warning log to help with debugging
application problems that arise in the field. This document explains how
to do that.
- Foreign Key Support
This document describes the support for foreign key constraints introduced
in version 3.6.19.
- Indexes On Expressions
Notes on how to create indexes on expressions instead of just
- Internal versus External Blob Storage
Should you store large BLOBs directly in the database, or store them
in files and just record the filename in the database? This document
seeks to shed light on that question.
- Limits In SQLite
This document describes limitations of SQLite (the maximum length of a
string or blob, the maximum size of a database, the maximum number of
tables in a database, etc.) and how these limits can be altered at
compile-time and run-time.
- Memory-Mapped I/O
SQLite supports memory-mapped I/O. Learn how to enable memory-mapped
I/O and about the various advantages and disadvantages to using
memory-mapped I/O in this document.
- Multi-threaded Programs and SQLite
SQLite is safe to use in multi-threaded programs. This document
provides the details and hints on how to maximize performance.
- Null Handling
Different SQL database engines handle NULLs in different ways. The
SQL standards are ambiguous. This (circa 2003) document describes
how SQLite handles NULLs in comparison with other SQL database engines.
- Partial Indexes
A partial index is an index that only covers a subset of the rows in
a table. Learn how to use partial indexes in SQLite from this document.
- Shared Cache Mode
Version 3.3.0 and later supports the ability for two or more
database connections to share the same page and schema cache.
This feature is useful for certain specialized applications.
- Unlock Notify
The "unlock notify" feature can be used in conjunction with
[shared cache mode] to more efficiently manage resource conflict (database
- URI Filenames
The names of database files can be specified using either an ordinary
filename or a URI. Using URI filenames provides additional capabilities,
as this document describes.
- WITHOUT ROWID Tables
The WITHOUT ROWID optimization is a option that can sometimes result
in smaller and faster databases.
- Write-Ahead Log (WAL) Mode
Transaction control using a write-ahead log offers more concurrency and
is often faster than the default rollback transactions. This document
explains how to use WAL mode for improved performance.
- Command-Line Shell (sqlite3.exe)
Notes on using the "sqlite3.exe" command-line interface that
can be used to create, modify, and query arbitrary SQLite
- SQLite Database Analyzer (sqlite3_analyzer.exe)
This stand-alone program reads an SQLite database and outputs a file
showing the space used by each table and index and other statistics.
Built using the [dbstat virtual table].
The "Resumable Bulk Update" utility program allows a batch of changes
to be applied to a remote database running on embedded hardware in a
way that is resumeable and does not interrupt ongoing operation.
- SQLite Database Diff (sqldiff.exe)
This stand-alone program compares two SQLite database files and
outputs the SQL needed to convert one into the other.
- Database Hash (dbhash.exe)
This program demonstrates how to compute a hash over the content
of an SQLite database.
The Fossil Version Control System is a distributed VCS designed specifically
to support SQLite development. Fossil uses SQLite as for storage.
- SQLite Archiver (sqlar.exe)
A ZIP-like archive program that uses SQLite for storage.
► Technical and Design Documentation
- SQLite As An Application File Format
This article advocates using SQLite as an application file format
in place of XML or JSON or a "pile-of-file".
- Well Known Users
This page lists a small subset of the many thousands of devices
and application programs that make use of SQLite.
- 35% Faster Than The Filesystem
This article points out that reading blobs out of an SQLite database
is often faster than reading the same blobs from individual files in
► Upgrading SQLite, Backwards Compatibility
- How Database Corruption Can Occur
SQLite is highly resistant to database corruption. But application,
OS, and hardware bugs can still result in corrupt database files.
This article describes many of the ways that SQLite database files
can go corrupt.
- Temporary Files Used By SQLite
SQLite can potentially use many different temporary files when
processing certain SQL statements. This document describes the
many kinds of temporary files that SQLite uses and offers suggestions
for avoiding them on systems where creating a temporary file is an
- In-Memory Databases
SQLite normally stores content in a disk file. However, it can also
be used as an in-memory database engine. This document explains how.
- How SQLite Implements Atomic Commit
A description of the logic within SQLite that implements
transactions with atomic commit, even in the face of power
- Dynamic Memory Allocation in SQLite
SQLite has a sophisticated memory allocation subsystem that can be
configured and customized to meet memory usage requirements of the
application and that is robust against out-of-memory conditions and
leak-free. This document provides the details.
- Customizing And Porting SQLite
This document explains how to customize the build of SQLite and
how to port SQLite to new platforms.
- Locking And Concurrency
In SQLite Version 3
A description of how the new locking code in version 3 increases
concurrency and decreases the problem of writer starvation.
- Isolation In SQLite
When we say that SQLite transactions are "serializable" what exactly
does that mean? How and when are changes made visible within the
same database connection and to other database connections?
- Overview Of The Optimizer
A quick overview of the various query optimizations that are
attempted by the SQLite code generator.
- The Next-Generation Query Planner
Additional information about the SQLite query planner, and in particular
the redesign of the query planner that occurred for version 3.8.0.
An architectural overview of the SQLite library, useful for those who want
to hack the code.
- VDBE Opcodes
This document is an automatically generated description of the various
opcodes that the VDBE understands. Programmers can use this document as
a reference to better understand the output of EXPLAIN listings from
- Virtual Filesystem
The "VFS" object is the interface between the SQLite core and the
underlying operating system. Learn more about how the VFS object
works and how to create new VFS objects from this article.
- Virtual Tables
This article describes the virtual table mechanism and API in SQLite and how
it can be used to add new capabilities to the core SQLite library.
- SQLite File Format
A description of the format used for SQLite database and journal files, and
other details required to create software to read and write SQLite
databases without using SQLite.
- Compilation Options
This document describes the compile time options that may be set to
modify the default behavior of the library or omit optional features
in order to reduce binary size.
- Android Bindings for SQLite
A description of how to compile your own SQLite for Android
(bypassing the SQLite that is built into Android) together with
code and makefiles.
- Debugging Hints
A list of tricks and techniques used to trace, examine, and understand
the operation of the core SQLite library.
► Obsolete Documents
- Moving From SQLite 3.5 to 3.6
A document describing the differences between SQLite version 3.5.9
- Moving From SQLite 3.4 to 3.5
A document describing the differences between SQLite version 3.4.2
- Release History
A chronology of SQLite releases going back to version 1.0.0
- Backwards Compatibility
This document details all of the incompatible changes to the SQLite
file format that have occurred since version 1.0.0.
- Private Branches
This document suggests procedures for maintaining a private branch
or fork of SQLite and keeping that branch or fork in sync with the
public SQLite source tree.
- Asynchronous IO Mode
This page describes the asynchronous IO extension developed alongside
SQLite. Using asynchronous IO can cause SQLite to appear more responsive
by delegating database writes to a background thread. NB: This
extension is deprecated. [WAL mode] is recommended as a replacement.
- Version 2 C/C++ API
A description of the C/C++ interface bindings for SQLite through version
- Version 2 DataTypes
A description of how SQLite version 2 handles SQL datatypes.
Short summary: Everything is a string.
- VDBE Tutorial
The VDBE is the subsystem within SQLite that does the actual work of
executing SQL statements. This page describes the principles of operation
for the VDBE in SQLite version 2.7. This is essential reading for anyone
who want to modify the SQLite sources.
- SQLite Version 3
A summary of the changes between SQLite version 2.8 and SQLite version 3.0.
- Version 3 C/C++ API
A summary of the API related changes between SQLite version 2.8 and
SQLite version 3.0.
- Speed Comparison
The speed of version 2.7.6 of SQLite is compared against PostgreSQL and
*** DRAFT ***