How to verify that my sqlite3 database is version 3.25?

Question

I created a new sqlite3 database using the latest sqlite3.exe v3.25 tools downloaded from https://www.sqlite.org/download.html

I want to verify that the sqlite database created is v3.25 to take advantage of the latest features.

How do I use sqlite3.exe tool to verify that the version is v3.25? I welcome other means to do so.

I tried the most voted answer here; How to find SQLITE database file version

However, the answer doesn't help. It returns the library version, not the database file version.

I am using windows 10.

The sqlite3 program prints out a banner message on startup that includes the version. — Shawn
The database files themselves have a version but the format hasn't changed in a few years. The current version is used by default unless you change it with a pragma before creating any tables in a new db. — Shawn
The tool. You can find the file version with the legacy_file_format pragma, which is probably going to return 4 unless you're dealing with a DB that was created with a sqlite version before 3.7.10, in which case it'll probably be 1 (or maybe 2 or 3). Details — Shawn
It just dawned on me that you might think the version of sqlite used to create a DB affects what versions can later use the file. As long as the file version (1-4) can be read by that sqlite version, it doesn't (with the caveat that 3.8.2 or later is needed for a DB with a WITHOUT ROWID table; that feature was added without bumping the file format for whatever reason). — Shawn
Yes it would have to be using 3.25 or newer. I'd be surprised if the version bundled with python has been updated yet for a release. — Shawn

sancho.s ReinstateMonicaCellio sancho.s ReinstateMonicaCellio · Accepted Answer · 2020-05-03T12:09:36

As per the official specification of the database format, there is a header at the beginning of the database file spanning the first 100 bytes. There are a few items in the header that contain version numbers:

Offset  Size    Description 
18      1       File format write version. 1 for legacy; 2 for WAL. 
19      1       File format read version. 1 for legacy; 2 for WAL. 
60      4       The "user version" as read and set by the user_version pragma.
92      4       The version-valid-for number.
96      4       SQLITE_VERSION_NUMBER

As per your question it looks like you want to get SQLITE_VERSION_NUMBER. That is actually not a "database version" (and there is not such a thing), but rather the value for the SQLite library that most recently modified the database file.

To get that value you simply have to read the 4-byte big-endian integer at offset 96. For that sake, you can use python code here ("I welcome other means to do so.). I tried creating a database with python code containing the lines

import sqlite3
print('sqlite3 sqlite_version: ', sqlite3.sqlite_version)
(... create database ...)

which printed

sqlite3 sqlite_version:  3.31.1

and then reading the database header with the linked code. In particular, lines

sqlite_version_number = unpack('>i', header[96:])[0]
print('SQLITE_VERSION_NUMBER: ' + str(sqlite_version_number))

printed

SQLITE_VERSION_NUMBER: 3031001

confirming the correct reading of "the database version number" (between quotes for reasons explained above).

Notes:

This would work in both Windows and Linux, as long as you have python available.
You can find other ways of reading the header. The point is identifying where to look for the information you need.

2.1. Sqlite. I couldn't find a command that would show the required info.

2.2. Linux. dd can do the job for you.

2.3. Powershell. [System.IO.File] or Get-Content can possibly help. I did not explore this further.

How to verify that my sqlite3 database is version 3.25?

1 Answers