SQLITE Files

What is a .SQLITE file?

The extension .sqlite indicates the usage of SQLite 3. This file type belongs to the relational databases family without server usage, consisting of one binary file. The development and current development of the tool belong to D. Richard Hipp and The SQLite Consortium correspondingly since the release of SQLite 3 back in 2004. As a consequence, .sqlite databases became widespread in usage in desktops, phones, web browser applications, IoT gadgets and embedded systems.

Typically, such file would contain such objects as tables, rows, indices, views, database schema with table structure definition, free list at the page level, application-specific metadata in one binary file. Files with the specified extension can be found mostly in application profiles, cache folders, database exports or database tests on websites with mobile applications backups, GIS projects with geoinformational analysis purposes as well as small independent application which do not use server-based relational database system.

Moreover, some of the SQLite 3 .sqlite files might consist SQLCipher encrypted database, produced with the software implementing SQLite variant of Zetetic with encryption abilities. Such a file would represent random binary pattern that would not work with standard SQLite tools unless proper key/cipher configuration is provided.

There exist SpatiaLite .sqlite files. Those are SQLite databases with GIS metadata tables like 'geometry_columns' and 'spatial_ref_sys', as well as fields containing geometry in BLOB data format. Very seldomly there could be the SQLite 2 files starting with "This file contains an SQLite 2.1 database".

How to open a .SQLITE file

Technical details

A generic .sqlite file represents a binary SQLite 3 database file, not a text SQL file/script. Starting at byte offset 0, it begins with 16-byte "SQLite format 3\0" file signature: 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00. Since its initial appearance in June 2004 along with version 3.0.0 of SQLite library, the file format did not change and continued being described as "SQLite format 3". Currently, format #4 is used as the schema format for SQLite database files.

Database header is stored in first 100 bytes of page 1. It describes such parameters as page size, file format write-version, file format read-version, reserved bytes per page, max/min embedded-payload fractions, min/maximum leaf payload fractions, change-counter for the file, database pages number, first freelist trunk page address, pages number in freelist, schema cookie, format number, default page cache size, largest root btree page number, database text encoding number, user version, vacuum flag, application ID, version valid for number, and SQLite version.

Storage in .sqlite is based on pages with possible page sizes equal powers of two in range 512…65536 bytes (commonly used is 4096); table and indexes in .sqlite file are stored as B-trees, overflow pages contain the overflowed row payload or index entries, freelist pages store deleted space information and auto-vacuum pages contain a list of pointers for the database page.

SQLite databases can contain UTF-8, UTF-16LE or UTF-16BE text encoding. Each cell value stored in SQLite file corresponds to SQLite dynamic typing model and uses NULL, INTEGER, REAL, TEXT and BLOB storage classes; thus, the declared column type does not define precisely the number of data bytes stored.

Compressive features are not built into .sqlite format. SQLCipher uses database page level encryption and hides SQLite visible header from reading by default - it should explicitly use "plaintext-header" to leave header unprotected. Journaling will likely create the sidecars called like input.sqlite-journal, input.sqlite-wal, and input.sqlite-shm that will store changes committed before truncating of an unclean .sqlite file. Application MIME type is usually "application/vnd.sqlite3", "application/x-sqlite3" can be met on older system; maximal theoretical capacity is about 281TB with page-size=64KiB, but usual SQLite file sizes are from some kilobytes to some gigabytes.

How to convert .SQLITE files

A consistent .sqlite file is first to be made via SQLite backup interface, via ".backup" sqlite3 shell command or via vacuum into command, before migration. In order to make a portable .sql export (to save in source control, move around), execute sqlite3 input.sqlite ".dump" > output.sql.Copy While .sql is saved as a text file containing SQL code to create the same db structure and content, but BLOBs get special handling; still sqlite specificities should often require changes, if the .db needs to be migrated to an engine other than SQLite.

For spreadsheet-like use cases, excel or data-cleaning applications, as well as any single-table migration/export tasks, csv is good choice as sqlite3 -header -csv input.sqliteCopy "SELECT * FROM table_name;" > table_name.csv. When export has to comply to an API contract/standard and not involve SQL code for a consumer side - json is what is required as sqlite3 -json input.sqliteCopy "SELECT * FROM table_name;" > table_name.json.

For bigger migrations into PostgreSQL, the best option would be pgloader input.sqlite postgresql://user:password@localhost/dbname with automatic mapping to target types and creation of necessary structures at PostgreSQL side; in case of SpatiaLite - use ogr2ogr -f GeoPackage output.gpkg input.sqlite to convert .sqlite db of spatial features (geometry column supported) into .gpkg, .shp, .geojson (for web) or PostgreSQL format of PostGIS. Duckdb's sqlite extension can export some of the tables to .parquet files. CSV, JSON export formats preserve no foreign keys etc.

Common problems and fixes

"file is not a database" - This usually means that file does not support reading or is not an SQLite database 3.*. The file can either be SQLCipher-encrypted, compressed/renamed, a legacy version (SQLite 2.*), or have incorrect first several bytes. Check the first byte of the file using a hex editor: correct files begin with the first bytes 53 51 4C 69 74 65 20 66 6F 72 6D 61 74 20 33 00 . Legacy versions of SQLite start from This file contains an SQLite 2.1 database.

"database disk image is malformed": There is corruption in your database structure. Perform the command PRAGMA integrity_check; If there is any corruption, save a copy of the current file and run .recover in the sqlite3 shell.

"database is locked": "database table is locked". Most likely, the database is held by some other transaction /WAL. You can terminate this program or work with the backup of the database. "unable to open database file". Usually, it means a problem related with a path, permissions, directory being sandboxed or read-only, missing parents' folders, issues with accessing network shares etc., and NOT the format issue. When copying .sqlite main file in the WAL mode, committed changes may be stored inside .sqlite-wal sidecar. Either copy both .sqlite and -wal + -shm files or make a full copy using SQLite's Backup API.

"no such table": Usually you used not a correct connection or attached-database has incompatible or different schema, your application has not completed all migrations, or the file does not have tables, and it is freshly created.

“file is encrypted or is not a database" within graphical interface: It means SQLCipher. Provide passphrase for decryption, KDF iteration number, SQLCipher compatibility version for your particular case.

Security considerations

Standard SQLite 3 does not contain macros and embedded executables like Microsoft Office documents do. Thus analyzing such tables via any of such applications like sqlite3 command-line interface tool, DB Browser for SQLite or SQLiteStudio would be less dangerous than opening such documents. Still a harmful file may cause issues with SQLite library itself, a GUI viewer, QGIS desktop GIS software suite, GDAL / OGR library or SpatiaLite spatial database access extensions, and code of applications, that decode BLOB values and special record structure within the table structure of SQLite databases.

Loadable extensions in SQLite are external C code libraries and not a code embedded inside the database and therefore should not be enabled at all. Moreover one should not open the unknown .dll / .so / .dylib files from the internet. The suspicious databases should be opened for analysis as read-only whenever possible. The backup files (.sqlite-journal, .wal) and sidecar (.sqlite-shm) files should be considered and inspected separately to avoid exposure of information from there.

Bare-bones databases could contain personal messages, cookie file, location data, information about personal or medical conditions, API access keys, browsing history and the information about data which used to be in a table (even if the table had been vacuumed or cleared with an app), the old data being visible from such sidecars files.

History and versions

SQLite emerged in the year 2000 when D. Richard Hipp created an SQL relational database library which executes without having another process dedicated to run a server. Release of version 2 followed by the next year, 2001 using an old file format, which although deprecated now still exists in some legacy archive systems and in long-life hardware.

SQLite 3.0.0 was released in June 2004, incorporating “SQLite format 3” file format, enhanced typing capabilities, UTF-8 and UTF-16 text encoding capability, improved and better SQL engine. SQLite 3.7.0 which was released in 2010 introduced write-ahead log system bringing along two side car files, -wal and -shm with increased reader-writer concurrency.

File format of SQLite 3 is published as documented open standard that has good longevity properties; SQLite organization states that databases built by its present releases would still be accessible decades ahead in future. Software development for SQLite comes as public domain software in several jurisdictions, however with option for getting paid commercial guarantees from the same SQLite team developing it. Encrypted format support came with release of SQLCipher where-as spatial table and GIS functionality was made possible by SpatiaLite before emergence of GeoPackage as an important common OGC standard, since .sqlite file extension is currently in use being shipped within numerous systems such as OSs itself, databases, browsers etcetera.

Related formats

.db and .sqlite3 files use the same SQLite format 3 as .sqlite; whereas .sqlite and .sqlite3 have more descriptive names, .db is too vague. .db3 is the third variant, often observed in the old software for Microsoft operating systems and their mobile equivalents.

.sql files are a plain text export of all the structure and data, useful for diffs on git and easier reading, though loading into the databases takes more time, and also it does not contain a page binary structure that makes SQLite. The extensions named .duckdb point on DuckDB - a database used for efficient analytics; if one is looking for such an analysis, then choose DuckDB and leave transactions, indices and SQL constraints to SQLite (.parquet is a file format of columnar database similar to .duckdb).

.mdb and its successor .accdb store the whole content of databases with forms, reports, other Access-specific stuff. Going from .mdb and .accdb to SQLite means losing Access support for simple applications without its extra services. .gpkg file type corresponds to OGC GeoPackage files. It uses SQLite format, so use SpatiaLite for the geographical operations with SQLite and .gpkg for OGC GIS exchange. .mbtiles are the other extension of SQLite databases, useful for exporting tile maps.