SQLite As An Application File Format

(Note: The current page is a brief summary of why SQLite makes
a good application file format. The topic is considered at greater
detail in a separate technical note.)

SQLite has been used with great success as the on-disk file format
for desktop applications such as version control systems,
financial analysis tools, media cataloging and editing suites, CAD
packages, record keeping programs, and so forth. The traditional
File/Open operation calls sqlite3_open() to attach to the database
file. Updates happen automatically as application content is revised
so the File/Save menu option becomes superfluous. The File/Save_As
menu option can be implemented using the backup API.

There are many advantages to using SQLite as an application file format,
including:

Performance problems can often be resolved, even late in the
development cycle, using CREATE INDEX, avoiding costly
redesign, rewrite, and retest efforts.

Portability

The application file is portable across all operating systems,
32-bit and 64-bit and big- and little-endian architectures.

A federation of programs, perhaps all written in different programming
languages, can access the same application file with no
compatibility concerns.

Multiple processes can attach to the same application
file and can read and write without interfering with each another.

Diverse content which might otherwise be stored as a "pile-of-files"
is encapsulated into a single disk file for simpler transport
via scp/ftp, USB stick, and/or email attachment.

Reliability

Content can be updated continuously and atomically so
that little or no work is lost in a power failure or crash.

Bugs are far less likely in SQLite than in custom-written file I/O code.

SQL queries are many times smaller than the equivalent procedural
code, and since the number of bugs per line of code is roughly
constant, this means fewer bugs overall.

Accessibility

SQLite database content can be viewed using a wide variety
third-party tools.

Content stored in an SQLite database is more likely to be
recoverable decades in the future, long after all traces of
the original application have been lost. Data lives longer than code.

SQLite allows database files to have any desired filename extension, so
an application can choose a custom filename extension for its own use, if
desired. The application_id pragma can be used to set an "Application ID"
integer in the database file so that tools like
file(1) can determine that the file
is associated with your application and is not just a generic
SQL database.