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:

1. Better performance
   • The application only has to load the data it needs, rather than reading the entire file and holding a complete parse in memory.
   • Small edits only overwrite the parts of the file that change, reducing write time and wear on SSD drives.
   • In many cases, loading content from an SQLite database is faster than reading individual files from disk. See Internal Versus External BLOBs.
2. 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.
3. 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.
4. 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.
5. 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.