SQLite Documentation

+ +

+Small. Fast. Reliable.
Choose any three. +

+ + +

+ +

Documentation

+ +

▼ Document Lists And Indexes +

► Overview Documents +

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. + +
Copyright +→ + 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. + +

► Programming Interfaces +

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 + SQLite. + +
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. + +
System.Data.SQLite +→ + C#/.NET bindings for SQLite + +
Tcl API +→ + A description of the TCL interface bindings for SQLite. + +
DataTypes +→ + 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. + +

► Extensions +

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. + +
Sessions +→ + 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 + similar database. + +
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 + interface. + +
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 +→ + CARRAY is a [table-valued function] that allows C-language arrays to + be used in SQL queries. + +
generate_series +→ + A description of the generate_series() [table-valued function]. + +
Spellfix1 +→ + The spellfix1 extension is an experiment in doing spelling correction + for [full-text search]. + +

► Features +

8+3 Filenames +→ + How to make SQLite work on filesystems that only support + 8+3 filenames. + +
Autoincrement +→ + A description of the AUTOINCREMENT keyword in SQLite, what it does, + why it is sometimes useful, and why it should be avoided if not + strictly necessary. + +
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 + individual columns. + +
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 + table locks). + +
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. + +

► Tools +

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 + database files. + +
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]. + +
RBU +→ + 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. + +
Fossil +→ + 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. + +

► Advocacy +

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 + the filesystem. + +

► Technical and Design Documentation +

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 + expensive operation. + +
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 + failures. + +
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. + +
Architecture +→ + 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 + SQLite. + +
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. + +

► Upgrading SQLite, Backwards Compatibility +

Moving From SQLite 3.5 to 3.6 +→ + A document describing the differences between SQLite version 3.5.9 + and 3.6.0. + +
Moving From SQLite 3.4 to 3.5 +→ + A document describing the differences between SQLite version 3.4.2 + and 3.5.0. + +
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. + +

► Obsolete Documents +

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 + 2.8 + +
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 + MySQL. + +

+ +

Common Links

Documentation