.SQLITE

SQLite database file

Introduction

SQLite is a complete SQL database held in a single file, read and written by a small library built into the program that uses it. D. Richard Hipp released it in 2000. It ships inside every major web browser and phone operating system, so SQLite files are among the most common files in the world.

The file format has been stable and backwards compatible since SQLite 3.0 in 2004. Programs name these files freely: .sqlite, .sqlite3 and .db are common, and many applications use their own extension.

Software

Structure

The file is divided into pages of equal size, a power of two from 512 to 65,536 bytes. Page 1 starts with a 100-byte header, beginning with the 16 bytes SQLite format 3 and a zero byte. All numbers in the header are big-endian.

OffsetSizeHolds
016the header string
162page size in bytes; the value 1 means 65,536
18, 191, 1write and read versions: 1 for the rollback journal, 2 for write-ahead logging
284size of the database in pages
32, 364, 4first free-list page and the number of free pages
564text encoding: 1 UTF-8, 2 UTF-16LE, 3 UTF-16BE
60, 684, 4user version and application ID, set by the program
964the SQLite version that last wrote the file

Every table and every index is a B-tree of pages. The rest of page 1 is the root of the schema table, sqlite_schema, which lists every table, index and view with its SQL and the page number of its root. Unused pages sit on a free list. While a database is open in write-ahead logging mode, changes may also be held in the -wal and -shm files beside it.

  1. 0Page 1 the 100-byte header, then the schema table’s root1 page
  2. …Pages 2 onwards B-tree pages for tables and indexes, overflow and free pages1 page each
An SQLite file: page 1 with the header, then further pages.