SQLite

Pronunciation: ES-kyoo-el-ite

Updated October 3, 20262 min read

In short

SQLite is a small SQL database engine that runs inside your application and stores a whole database in a single file, with no separate server to manage.

What is SQLite?

SQLite was created by D. Richard Hipp in 2000 and is in the public domain, so anyone can use it for any purpose. Unlike PostgreSQL or MySQL, it is not a server that applications connect to over the network. It is a library linked into the program itself, and the entire database, with all its tables and indexes, lives in one ordinary file on disk.

Despite its size, it is a real relational database: it understands most of SQL, supports transactions that follow the ACID rules, and survives crashes and power loss without corrupting data. Because there is nothing to configure, opening a database is as simple as opening a file.

SQLite is probably the most widely deployed database in the world. It ships in every Android and iOS device, in web browsers, in desktop apps and in countless embedded systems, where it stores settings, caches and app data. It is also a great choice for prototypes, tests, small websites and data analysis, and some services now run it in production with replication tools.

A common misconception is that SQLite is a toy. It is extremely well tested and reliable, but it is designed for a single machine: many processes can read at once, while only one can write at a time, so busy multi-user servers with heavy concurrent writes are usually better served by a client-server database.

Key takeaways

SQLite is an embedded SQL database: a library, not a server.
A whole database is one ordinary file on disk.
It supports ACID transactions and survives crashes safely.
It is built into phones, browsers and many desktop apps.
Only one writer at a time makes it less suited to write-heavy servers.

Example

A database in one file (Python)python

import sqlite3

# Creates notes.db if it doesn't exist; no server needed
con = sqlite3.connect("notes.db")
con.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
con.execute("INSERT INTO notes (body) VALUES (?)", ("Buy milk",))
con.commit()

for row in con.execute("SELECT id, body FROM notes"):
    print(row)

Readers ask

Does SQLite need a server?

No. SQLite runs inside your application as a library and reads and writes the database file directly, so there is nothing to install, start or connect to.

Can SQLite be used in production?

Yes, in many cases. It runs in production on billions of devices and works well for websites with moderate traffic. Systems with many simultaneous writers usually choose a client-server database instead.

Is SQLite free?

Yes. SQLite is in the public domain, so it can be used, changed and distributed for any purpose without a license fee.