SQLite: add a powerful database engine to any app (posted 2023-09-04)
When I was 24, I decided to give up my job and go to college and study computer science. If I'd have known how many database classes that involved, maybe I would have reconsidered.
Back then, we had a big server that ran a RDBMS (relational database management system) that hundreds of students all used together. These systems were big, complex and expensive. (Oracle made its fortune selling RDBMSes.) MySQL and PostgresQL are somewhat more streamlined free and open source RDBMSes. Much better, but firewalling, user authentication and backups are still somewhat of a headache. But hey, if you need a database, you need a database.
Enter SQLite.
Traditional RDBMSes are a service that you connect to over the network—even if the RDBMS runs on the local machine. SQLite, on the other hand, is just a (pretty small) library that you link to in your software, and all the database contents go into a single file stored on the local file system. But you still get a very complete implementation of SQL, the "structured query language" that is used to interact with most databases.
I find myself dealing with CSV files and the like pretty regularly. For just getting data in and out of applications, this is usually fine. But doing anything useful with CSV data in your own scripts or applications is a chore. With SQL, on the other hand, you can easily analyse and transform data, often with pretty simple SQL queries.
Compared to MySQL
In the past, I've installed MySQL on my laptop to some data analysis. Having to install a big piece of software and configuring a user account and permissions is less than ideal. With SQLite you just point to a local file and you're in business. If you know what you're doing, SQLite can also be a lot faster than many other database engines such as MySQL. (Although without grouping large numbers of insert statements into larger transactions SQLite will be slow.)
Se the page Appropriate Uses For SQLite on the SQLite website to learn when SQLite is a good fit and when it isn't. The main thing is that SQLite is not a good fit when the application needs to access the database over a network.
Using SQLite with PHP
Initially, it seemed that moving from MySQL to SQLite in PHP would be tricky. However, the basics are no issue at all. This is the simple way to use MySQL in PHP:
$db = mysqli_connect("localhost", "db_user", "secretpw", "db_name");
$result = $db->query("select * from pages where pagenum = $n");
while ($row = $result->fetch_assoc())
echo json_encode($row);
$db->close();
That translates relatively neatly into SQLite:
$db = new SQLite3("content.db");
$result = $db->query("select * from pages where pagenum = $n");
if ($row = $result->fetchArray())
echo json_encode($row);
$db->close();
The C API requires a few more steps to get things done, but doesn't look excessively complicated. However, MySQL and SQLite use different SQL dialects. So far, my main issue has been the missing left() and right() functions and the handling of dates/times. Especially adjusting a given date is done in rather different ways.
SQLite from the command line
You can directly interact with SQLite databases through the sqlite3 program that is installed by default on a good number of operating systems. You can download the Windows version from the sqlite.org version. And there's even an Amiga port. Sweet. Just type "sqlite3" followed by the filename of a database file and you're in business. SQLite 3 is open source, and interestingly, has no license: it's entirely in the public domain.
A features in SQLite that I really like is that you can read blobs from the file system in order to store them in a database table, and then later write those back to the file system:
insert into blobstore (id, data) values (1, readfile('Makefile')); select writefile('blob.bin', data) from blobstore where id = 1;
And another great feature of the command line sqlite3 program is that you can just call an editor to edit the contents of a field. This is especially useful if you store larger texts in a database. It works like this:
% sqlite3 test.db SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. sqlite> update blobstore set data = edit(data, 'vi') where id = 1;
(Of course in general you'd probably edit text columns, not blob columns.)
Portability and easy backups
As mentioned before, SQLite stores an entire database, holding one or more tables and all the associated housekeeping, in a single file. So you can just copy that file to another computer, email it to someone, or use standard backup mechanisms to back it up. (Ok, it seems that backing up the database file while changes are being made is not 100% bullet proof, but it's close.)
The SQLite3 file format has been backward compatible since 2004, and it's 32/64-bit and big-endian/little-endian agnostic. This means you can just email a copy of a SQLite database to someone else, and they will be able to use it without trouble. And you can be fairly confident that several decades from now, it's still possible to retrieve data from an SQLite file.
SQLite has some interesting Unicode caveats, but I'll save those for a later post.