*** DRAFT ***
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,
- Better performance
- Reading and writing from an SQLite database
is often faster than reading and writing individual files from disk.
See 35% Faster Than The Filesystem
and Internal Versus External BLOBs.
- The application only has to load the data it needs, rather
than reading the entire file and holding a complete parse
- Small edits only overwrite the parts of the file that change,
reducing write time and wear on SSD drives.
- Reduced application cost and complexity
- No application file I/O code to write and debug.
- Content can be accessed and updated using concise SQL queries instead
of lengthy and error-prone procedural routines.
- The file format can be extended in future releases simply
by adding new tables and/or column, preserving backwards compatibility.
- Applications can leverage the
full-text search and RTREE indexes and use triggers to implement
an automated undo/redo stack.
- Performance problems can often be resolved, even late in the
development cycle, using CREATE INDEX, avoiding costly
redesign, rewrite, and retest efforts.
- 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
- 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.
- 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.
- SQLite database content can be viewed using a wide variety
- 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 database files are recommended by the US Library of Congress
as a storage format for long-term preservation of digital content.
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
This page last modified on 2022-01-08 05:02:57 UTC
*** DRAFT ***