You know cloud computing is powered by shell scripts and SQLite databases, right? That’s how the saying goes.
But what is an SQLite database anyway? It is a file, and it can be inspected with the SQLite application or a compatible library. There is no server daemon to connect to.
Usually, when accessing a database, you’ll need some credentials, like username and password, to do read or write operations on the database. SQLite is simplier: if you have access to the database file, then you can do operations permitted by the file access, as long as you have an SQLite-capable application that is able to use the file.
I personally use SQLite databases in my Python applications for local persistent or temporary data storage, instead of using my own text or binary file formats. The sqlite3 module is part of Python standard library, so no external dependencies are needed to use it. See the module documentation for examples and tutorial for using SQLite database in a Python application.
In this post I will show some (non-Python) commands that have been useful for me when inspecting SQLite databases used by various applications.
First, on Debian Linux, I need to have sqlite3 package installed:
markku@testhost:~$ sudo apt install sqlite3
...
Preparing to unpack .../sqlite3_3.40.1-2+deb12u1_amd64.deb ...
Unpacking sqlite3 (3.40.1-2+deb12u1) ...
Setting up sqlite3 (3.40.1-2+deb12u1) ...
markku@testhost:~$
Then I can open a database file (I’m using sudo here because my user account cannot access the file directly):
markku@testhost:~$ sudo sqlite3 /var/lib/zabbix/zabbix_agent2.sqlite
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite>
That database file happens to be a small database used by the Zabbix Agent 2 for local data storage.
Here are my most used commands:
sqlite> .tables
command_1 data_1 log_1 registry
sqlite> .schema
CREATE TABLE registry (id INTEGER PRIMARY KEY,address TEXT,hostname TEXT,UNIQUE(address,hostname));
CREATE TABLE data_1 (id INTEGER,write_clock INTEGER,itemid INTEGER,lastlogsize INTEGER,mtime INTEGER,state INTEGER,value TEXT,eventsource TEXT,eventid INTEGER,eventseverity INTEGER,eventtimestamp INTEGER,clock INTEGER,ns INTEGER);
CREATE INDEX data_1_1 ON data_1 (write_clock);
CREATE TABLE log_1 (id INTEGER,write_clock INTEGER,itemid INTEGER,lastlogsize INTEGER,mtime INTEGER,state INTEGER,value TEXT,eventsource TEXT,eventid INTEGER,eventseverity INTEGER,eventtimestamp INTEGER,clock INTEGER,ns INTEGER);
CREATE INDEX log_1_1 ON log_1 (write_clock);
CREATE TABLE command_1 (id INTEGER,write_clock INTEGER,cmd_id INTEGER,value TEXT,error TEXT);
CREATE INDEX command_1_1 ON command_1 (write_clock);
sqlite>
I use those two SQLite-specific commands to find out the structure of the database at hand.
But since we are talking about an SQL database, we can use the standard SQL queries to actually leverage the data:
sqlite> SELECT * FROM registry;
1|192.168.7.81:10051|Zabbix70-agent
sqlite>
For occasional uses, it might be useful to show the column names as well:
sqlite> .headers on
sqlite> SELECT * FROM registry;
id|address|hostname
1|192.168.7.81:10051|Zabbix70-agent
sqlite>
To get even prettier output, there is the .mode command to change that (use “.help mode” to show all options, there are many, and the default is list):
sqlite> .mode box
sqlite> SELECT * FROM registry;
┌────┬────────────────────┬────────────────┐
│ id │ address │ hostname │
├────┼────────────────────┼────────────────┤
│ 1 │ 192.168.7.81:10051 │ Zabbix70-agent │
└────┴────────────────────┴────────────────┘
sqlite>
To be exact, the box mode shows the column names even with the default “.headers off” setup.
Anyway, this was just a small example of using the SQLite command line application to access a database created by some application. As another example, I have used the same method to review the cookies saved in the Firefox web browser as they are stored in an SQLite database in the user’s profile folder.
Note that SQLite databases don’t need to be named with “.sqlite” extension, the files can be freely named, so you may need to recognize the database files otherwise. In Linux you can use the file command:
sqlite> .quit
markku@testhost:~$ file /var/lib/zabbix/zabbix_agent2.sqlite
/var/lib/zabbix/zabbix_agent2.sqlite: SQLite 3.x database, last written using SQLite version 3044000, file counter 136034, database pages 9, cookie 0x7, schema 4, UTF-8, version-valid-for 136034
markku@testhost:~$
Happy SQLiteing!