Skip to content
Search

SQLite

lastmod 2026-08-13created 2014-06-1493 edits

SQLite is a database management system like MySQL or PostgreSQL, but it is a relatively lightweight database that is embedded in an application rather than run as a server. In English it is pronounced "S-Q-L-ite (ˌɛs ˌkjuː ˈɛlaɪt)" or "sequel-ite (siːˈkwɛlaɪt)".

Compared to typical RDBMSs it is not suited to large-scale workloads, but for small to medium scale its speed holds up well. Its API consists simply of library calls, and it is characterized by using a single file to store data. Version 3.3.8 supports the FTS1 module with full-text search. Dropping or altering columns and the like are restricted.

It is also the database built into Google's Android operating system.

Categories

Projects

  • CG/SQL - Stored Procedure code generator for SQLite
  • hiberlite - https://github.com/paulftw/hiberlite
  • ORM-Lite - https://github.com/BOT-Man-JL/ORM-Lite
  • sqlite_orm - https://github.com/fnc12/sqlite_orm
  • rqlite - A lightweight, open-source distributed relational database written in Go, built on SQLite and Raft
  • SQLite Plus - Adds regex/statistics/Unicode/virtual table functions
  • sql.js-httpvfs - Hosting an SQLite DB on GitHub Pages - Use a full-featured SQLite DB on a static web page with no backend.
  • ws4sqlite - A server that lets you use SQLite via REST
  • sqlite-zstd - Row-level compression extension with random access
  • LiteFS - Open-source distributed file system for SQLite
  • sqlite-loadable-rs - A framework for writing SQLite extensions in Rust
  • DuckDB - Open-source embedded OLAP DB
  • Sqlite Index Blaster - A tool for ultra-fast creation of large SQlite indexes
  • SQLite-gui - Lightweight SQLite editor for Windows
  • tuql - A tool that automatically generates a GraphQL server from an SQLite DB
  • tinyvector - Small, fast, open-source vector embedding database
  • Doculite - Using SQLite as a document database
  • SQLedge - A tool that replicates Postgres to SQLite at the Edge
  • SmoothMQ - SQLite-based drop-in SQS replacement
  • DB Browser for SQLite - Open-source visual browser for SQLite
  • sqlite-loadable-rs - A framework for writing SQLite extensions in Rust
  • pglite-fusion - Embedding SQLite in PostgreSQL tables
  • SQLiteStudio - Open-source, multiplatform SQLite management tool
  • Limbo - SQLite completely rewritten from scratch in Rust
  • LumoSQL - A project for extending SQLite features and providing alternative storage backends
  • LiveStore - A state management solution using reactive SQLite and a built-in sync engine
  • SQLite-JS - An extension that adds JavaScript capabilities to SQLite
  • Base - SQLite database editor for macOS (GUI design recommended)
  • XLite - An SQLite extension for querying Excel files as virtual tables
  • SQLite-Graph - An extension that adds graph DB capabilities to SQLite
  • GraphQLite - An SQLite extension supporting the Cypher query language and built-in graph algorithms
  • pgmicro - An in-process PostgreSQL built on SQLite
  • syntaqlite - SQL parser, formatter, validator, and language server based on SQLite's own grammar and tokenizer
  • Honker - An extension that implements Postgres NOTIFY/LISTEN in SQLite

Cloud service

  • Turso
    • Features: Based on libSQL (a fork of SQLite), and optimized for an architecture that creates "thousands of databases per project".
    • Free plan: Provides 9GB of total storage, 1 billion reads, and 25 million writes, and lets you create up to 500 databases.
    • Pros: Very easy to create a DB per project instantly and access it through the API or SDK.
  • Cloudflare D1
    • Features: An SQLite database running on Cloudflare Workers (Edge Computing).
    • Free plan: Usable within Cloudflare's free tier, with low-latency access from edge locations around the world.
    • Pros: Works well with serverless environments and requires almost no management.

ETC

How to compile

On MacOSX, autotools must be installed separately. 

$ brew install automake autoconf libtool m4

Command-line tool

When using the sqlite3 command-line utility, you can enter commands such as the following.

  • .help: Print help.
  • .tables: Print the list of tables.
  • .schema [테이블명]: Print the table's schema.
  • .quit: Exit the program.

Data Type

The following data types are available.

  • NULL: Null value.
  • INTEGER: Integer value of 1, 2, 3, 4, 6, or 8 bytes. (For auto-increment, write INTEGER PRIMARY KEY AUTOINCREMENT)
  • REAL: 8-byte floating-point value.
  • TEXT: String in UTF-8, UTF-16BE, or UTF-16LE encoding.
  • BLOB: Stored exactly as input, e.g., binary files.

C API Reference

Example

CREATE TABLE IF NOT EXISTS synset(idx INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
If a table named synset does not exist, creates the table with a primary key named idx (auto-incrementing Integer type) and a name string column.
DROP TABLE IF EXISTS synset;
If a table named synset exists, drops the table.

Concatenating strings

To convert "\r\n" characters to "/", since \r = char(13) and \n = char(10): 

select replace(replace(name, char(13), ''), char(10), '/') as name
from scrap_grade;

To make "\r\n" a single string, you can concatenate the characters with char(13) || char(10). 

select replace(name, char(13) || char(10), '/') as name
from scrap_grade;

How to make foreign keys work 

PRAGMA foreign_keys = ON;

must be run for foreign key constraints (on delete set null, on update cascade, etc.) to take effect.

JSON format support

SQLite stores JSON as ordinary text. You can work with JSON data using various JSON functions.

json

The json(X) function verifies that its argument X is a valid JSON string or JSONB blob and returns a minified version of that JSON string with all unnecessary whitespace removed. 

json(' { "this" : "is", "a": [ "test" ] } ') → '{"this":"is","a":["test"]}'

jsonb

Beginning with version 3.45.0 (2024-01-15), SQLite can store its internal "parse tree" representation of JSON on disk as a BLOB in a format called "JSONB". By storing SQLite's internal binary representation of JSON directly in the database, applications can bypass the overhead of parsing and rendering JSON when reading and updating JSON values. The internal JSONB format also uses slightly less disk space than text JSON.

Any SQL function parameter that accepts text JSON as input also accepts a BLOB in JSONB format. The function behaves the same in both cases, except that it runs faster when the input is JSONB because the JSON parser does not need to run.

The name "JSONB" is borrowed from PostgreSQL, but the JSONB formats of the two DBMSs are not compatible. JSONB is an internal format for SQLite, so using JSONB outside of SQLite is not recommended.

The jsonb(X) function returns the binary JSONB representation of the JSON provided as argument X

Inserting 1 billion rows into SQLite in under a minute

SQLite is not a single-connection DB

One common misconception about SQLite is that it is "a database that allows only a single connection", which leads people not to use it

This is both confusing (what does "connection" mean here?) and wrong in several respects

  • Read Operations
    • SQLite fully supports multiple concurrent reads
    • Data can be read simultaneously from multiple "connections" without conflicts or problems
  • Write Operations
    • SQLite uses a DB-level write lock for writes
    • It does not allow multiple writes at the same time, so only one "connection" can write at a time
    • This usually is not a problem because you can start an IMMEDIATE TRANSACTION
    • In that case SQLite can retry from a queue to acquire the write lock
      • (This lets the write proceed automatically when the write lock is released)

5.0 Writing to a database file

To write to a database, a process must first acquire a SHARED lock as described above. After obtaining a SHARED lock, it must acquire a RESERVED lock. A RESERVED lock signals that the process intends to write to the database at some point in the future. Only one process at a time can hold a RESERVED lock. However, other processes can continue to read the database while the RESERVED lock is held.

Misconceptions about SQLite on the Server

Many developers think that using SQLite on a server is only suitable for small applications

The reasons are as follows:

  • Low infrastructure cost: no separate database server needed (runs as a single file)
  • Easy development and testing: the same DB file can be used on the client and server
  • Minimal management burden: no complex configuration or daemon management needed
  • High reliability: SQLite is the most widely deployed DB in the world and is highly durable

Tools such as LiteFS, Litestream, rqlite, Dqlite, and Bedrock add replication and high availability (HA) to SQLite, making it suitable for small deployments. This article, however, explores SQLite's potential for hyper-scale applications rather than small ones

Scaling problems with existing large databases

  • Large applications typically find it hard to keep PostgreSQL or MySQL as a single DB, so they use sharded databases
  • Examples: Cassandra, ScyllaDB, DynamoDB, Vitess (sharded MySQL), Citus (sharded Postgres)
  • Sharded DBs have the following advantages:
    • Optimized batch reads through data partitioning
    • Horizontal scalability
    • Fast write performance

However, current partitioning solutions have the following drawbacks

  • Rigid schemas: do not support flexible queries like MySQL or Postgres
  • Difficult schema changes: adding indexes or changing relationships carries a heavy operational burden
  • Complex cross-partition operations: hard to maintain ACID transactions, requiring complex techniques such as two-phase commit (2PC)
  • Data inconsistency: hard to enforce strong data constraints across partitions, with a high chance of breaking data consistency

The potential of SQLite-based hyper-scale databases

  • Cloudflare Durable Objects and Turso show how to design hyper-scale applications on SQLite
  • These systems offer the following strengths:
    • Dynamic Scaling: reduces infrastructure complexity by creating a database per entity
    • Unlimited low-cost databases: instead of forcing data partitions as in traditional sharding, new SQLite instances can be created whenever needed
    • Global Distribution: improves performance by placing databases close to users
    • Built-in Replication & Durability: unlike traditional SQLite, replicates data across multiple regions to maintain high availability
  • An SQLite-based alternative to sharding (using Cloudflare Durable Objects & Turso)
    • In traditional sharding, many chat logs are stored in a single database partition
    • With SQLite, an independent SQLite database is created per channel, allowing more flexible schemas
    • Example structure
      • Traditional sharding: chat log table + partition key
      • SQLite-based: a separate SQLite DB per channel (including chat logs, participants, and reactions)
  • The advantages of this SQLite approach are as follows:
    • Local ACID transactions: transactions run within an individual DB without cross-partition problems
    • High-performance I/O: since SQLite is a single-file DB, read and write performance is excellent
    • SQL extensions available:
      • FTS5 (Full-Text Search): better search performance
      • JSON1: storing and querying JSON data
      • R*Tree, SpatiaLite: working with spatial data
    • SQL migration support: compatible with existing migration tools such as Prisma and Drizzle
    • Lazy schema migration support:
      • Migrations need not run immediately; lightweight migrations can be performed when an SQLite instance is opened

Limitations of using SQLite on the server

  • Lack of open-source, self-hostable solutions
  • No cross-database queries → a separate data lake is needed for analytics
  • Limited database tooling (SQL browsers, ETL pipelines, monitoring, backups)
    • StarbaseDB is working on this problem based on Cloudflare Durable Objects + SQLite
  • Lack of a unified standard protocol
    • PostgreSQL, MySQL, and Cassandra use standardized protocols, but SQLite servers still lack a standardized network protocol
  • Few large-scale case studies of SQLite at hyper-scale
    • There are not yet case studies like those for Cassandra or DynamoDB, but this may change over time

Conclusion: SQLite is not just a local DB but a strong option even for hyper-scale applications

  • SQLite is not just a DB for small projects, but a powerful tool that can replace traditional sharding even in hyper-scale applications
  • With Cloudflare Durable Objects & Turso, databases can be split per entity and scaled while keeping SQL's powerful features and ACID transactions
  • Likely to become a more flexible and easier-to-manage alternative to traditional sharded databases

One of 7 databases for 2025

  • See Database#7 databases for 2025 (GeekNews)
  • SQLite can run standalone as a "local-first" database
    • Breaks away from the client-server model and runs in the same environment as the application
    • Example: WhatsApp and Signal use SQLite on the device to store chat data
  • Advanced uses of SQLite
    • Creative uses beyond a basic ACID-compliant database are possible
    • New tools and extensions:
      • Litestream: provides streaming backups for SQLite
      • LiteFS: supports distributed access for more flexible topologies
      • CR-SQLite: uses CRDTs (Conflict-free Replicated Data Types) to eliminate the need for conflict resolution when merging changesets
  • SQLite's popularity revisited
    • Getting attention again thanks to Ruby on Rails 8.0
    • 37signals: developed Rails modules (such as Solid Queue) on top of SQLite
      • Rails support for managing multiple SQLite databases (database.yml)
    • Bluesky: uses a separate SQLite database per user as Personal Data Servers
  • Recommended for learning SQLite
    • Experiment with local-first architectures using SQLite
    • Try replacing an existing PostgreSQL-based client-server model with SQLite

SQLite made 10x faster with Bloom filters

  • SQLite made 10x faster with Bloom filters | GeekNews
  • How bloom filters made SQLite 10x faster - blag
  • SQLite is row-based storage kept on disk as a B-tree structure, and it executes queries using a virtual machine called VDBE. It is platform-independent and can run single-threaded in almost any environment
  • It is a general-purpose database but excels at OLTP workloads. In 2015, researchers at the University at Buffalo found that most queries were simple key-value lookups and complex OLAP queries
  • Researchers at the University of Wisconsin-Madison worked on making analytical queries faster. They compared performance using DuckDB and the Star Schema Benchmark (SSB)

Cause

  • To find out why SQLite was slow, they used the VDBE_PROFILE option to measure the CPU cycles consumed by each VDBE instruction.
  • Two opcodes, SeekRowID and Column, turned out to be the main causes.

Database joins

  • Databases implement joins with nested loop joins, hash joins, and sort-merge joins.
  • SQLite uses the simplest, the "nested loop join". It is similar to a B-tree search and therefore expensive.

The importance of join optimization

  • The order of tables matters in join operations. Changing the order can greatly reduce the number of operations. This is an NP-hard problem.
  • There are two join algorithms better than the nested loop join, but hash joins consume a lot of memory, and SQLite mostly runs in memory-constrained environments.
  • The researchers used Bloom filters to improve space efficiency and fit within CPU cache lines. They added two opcodes, Filter and FilterAdd.

Results

  • After the optimization, the large blue bar in the CPU cycle analysis almost disappeared.
  • SQLite became 7 to 10 times faster. These research results were applied in SQLite v3.38.0.
  • Bloom filters fit well with SQLite's simple implementation with minimal memory overhead and work within the existing query engine

Using SQLite JSON at full index speed: fast queries with generated columns

  • Using SQLite JSON at full index speed: fast queries with generated columns | GeekNews
  • Introduces an approach that uses SQLite's JSON features to store the original JSON document as-is and extract the needed fields into virtual generated columns for indexing
  • Uses the json_extract function to treat data inside JSON like columns, allowing queries at B-tree index speed
  • Whenever a new query pattern is needed, it can be extended by adding columns and indexes without data migration
  • This approach achieves both the flexibility of schemaless data and the performance of a relational database
  • Highlighted as a practical pattern that gives developers using SQLite a simple structure and high performance

Virtual tables

SQLite virtual tables can also support writes and transactions, by implementing hooks such as xUpdate, xSync, xCommit, and xRollback

  • SQLite guarantees atomicity with a rollback journal by default, and when handling multiple DB files it coordinates the overall commit with a super-journal
  • Virtual tables also take part in SQLite's transaction protocol, and the whole transaction is rolled back if xSync fails
  • Commit is split into two phases: xSync should perform operations that may fail, while xCommit should perform only simple cleanup
  • xCommit and xRollback can always be called, so they must be written as cleanup functions that run without failing

Concurrency and why you should care about it

  • SQLite concurrency and why you should care about it | GeekNews
  • SQLite's file-based structure is simple, but locking conflicts can occur when multiple writes happen at the same time
  • Jellyfin had used SQLite for a long time, but on some systems database-locked errors during transactions caused the application to crash
  • Mitigated the problem by implementing three locking strategies (No-Lock, Optimistic, Pessimistic) using EF Core's interceptor feature
  • The Optimistic approach minimizes performance loss with retries, while the Pessimistic approach improves stability at the cost of speed
  • This approach is easy to apply to other EF Core applications and offers a practical solution to SQLite concurrency problems

Troubleshooting

Integration bash script

When integrating sqlite3 into a BASH script, the interactive program launches and the script stops midway. To prevent this, apply the following. 

$ cat > test.sql << EOF
CREATE TABLE log_entry ();
.separator "\t"
.import logfile.log log_entry
.quit
EOF
$ sqlite3 db.sqlite < test.sql

Libtool library used but LIBTOOL is undefined

See Libtool#Libtool library used but LIBTOOL is undefined.

Local Download

C source code as an amalgamation, version 3.11.1.
Sqlite-amalgamation-3110100.zip (sha1: 868a00d2adcc8b3803c46d461d7d8d23b2bfccbc)
C source code as an amalgamation. Also includes a "configure" script and TEA makefiles for the TCL Interface.
Sqlite-autoconf-3110100.tar.gz (sha1: c4b4dcd735a4daf5a2e2bb90f374484c8d4dad29)

See also

Favorite site

author Zer0 sitemap