SQLite Documentation Small. Fast. Reliable. Choose any three. Home Menu About Documentation Download License Support Purchase Search About Documentation Download Support Purchase Search Documentation Search Changelog Common Links Features When to use SQLite Getting Started Try it live! SQL Syntax Pragmas SQL functions Date & time functions Aggregate functions Window functions Math functions JSON functions C/C++ Interface Spec Introduction List of C-language APIs The TCL Interface Spec Quirks and Gotchas Frequently Asked Questions Commit History Prior Releases Bugs News Documentation Search Documentation Search Changelog Document Lists And Indexes Alphabetical Listing Of All Documents Website Keyword Index Permuted Title Index 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. Application-Defined SQL Function → An overview of the C-language interfaces used to create new application-defined SQL functions in SQLite. Tcl API → A description of the TCL interface bindings 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. System.Data.SQLite → C#/.NET bindings for SQLite SQL Language Documentation 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. 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. Indexes On Expressions → Indexes in SQLite do not have to be over just plain table columns. Expressions can also be indexed. Row Values → SQLite supports comparisons, including inequality comparisons, between tuples of values, call "row values". This document explains. STRICT Tables → A STRICT table in SQLite does rigid type enforcement, in order to more closely mimic the behavior of other SQL database engines. Extensions JSON Functions → 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. 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 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. Zipfile → A [virtual table] with accompanying support functions that can read and write a ZIP archive as if it were a database. The IEEE754 Extension → A set of SQL functions for encoding and decoding IEEE-754 floating point numbers. The Decimal Extension → A set of functions for doing arbitrary-precision decimal arithmetic, including expanding IEEE-754 floating point values to their exact decimal representation. The UINT Collating Sequence → A collating sequence that sorts text with embedded numbers in numeric order. The Percentile Extension → An implementation of aggregate functions: median(), percentile(), percentile_cont(), and percentile_disc(). 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 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. Remote Copy Of A Live Database → The sqlite3_rsync program makes a consistent copy of a live database to or from a remote system. 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. 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 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. 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. SQLite Archiver (sqlar.exe) → A ZIP-like archive program that uses SQLite for storage. Advocacy 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. Flexible Typing Is A Feature → SQLite provides developers with the freedom to store content in any desired format, regardless of the declared datatype of the column. This article explains why that is a feature, not a bug. 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. Why SQLite Is Coded In C → Why is SQLite not coded in some other trendy language like C++ or Rust? Isn't C obsolete? Why SQLite Does Not Use Git → Why SQLite does not use Git for version control, like most everybody else? Technical and De…