Easy way to store metadata about SQLite Database

Question

I was wondering if there is a simple way to store meta information about an sqlite-Database in that database.

I am specifically thinking about a version number that lets you easily find out which version of a database layout you are using (So my code could check if the database structure is compatible without querying SELECT sql FROM sqlite_master WHERE type='table'; and comparing the result with a predefined schematic). To clarify: I am not interested in the version number of the sqlite software, but something akin to pythons __version__ variable that can be defined seperately for each python file.

I know that I can probably just create a table named "meta" and save it there, but I was wondering if there was a better way to do that.

I also know that checking compatibility by only checking a version number has some problems, and I will still do other checks if necessary, but for now I am only interested in the version number I described.

Answer 1

You might want to check out the user_version pragma.

Answer 2

SQLite offers two slots to store an integer as metadata.

application_id

https://www.sqlite.org/pragma.html#pragma_application_id

Set an application_id to 900

PRAGMA application_id = 900;

Get the application_id

PRAGMA application_id;

user_version

https://www.sqlite.org/pragma.html#pragma_user_version

Set an user_version to 901

PRAGMA user_version = 901;

Get the user_version

PRAGMA user_version;