Reading “The Definitive Guide to SQLite” by Grant Allen and Mike Owens (2010).

01_book-cover-definitive-guide-sqlite-owens.jpg

I have read “The Definitive Guide to SQLite”, and I want to share my review.

Bibliography

  1. Using SQLite: Small. Fast. Reliable. Choose Any Three. Jay A. Kreibich, 2010.
  2. SQL QuickStart Guide: The Simplified Beginner’s Guide to Managing, Analyzing, and Manipulating Data with SQL. Walter Shields, 2019.
  3. Inside SQLite. Sibsankar Haldar, 2007.
  4. SQLite Database System Design and Implementation. Sibsankar Haldar, 2015.
  5. Books About SQLite. https://sqlite.org/books.html.

1. Body

Do you have a database? It is a tricky question, because most people would probably answer “no”, a few people would boast having a running servers of DB2 or Oracle, and some broad-minded people would start saying something like “considered abstractly, any file system is a database”. However, even the people answering “no” probably have more than one database file in their phone, keeping some APP data, which they are not even aware of having. These app databases are overwhelmingly using the database library called “sqlite” (or sqlite3).

Naturally, since I am very interested in things that are overwhelmingly widespread but seldom recognised and credited, I found in me a burning desire to read a good introduction into SQLite, and the book by Grant Allen and Mike Owens was a natural choice. SQLite is a little bit weird in this respect (and other respects, though). As you might have seen in the bibliography, there are only three books to choose from when looking to study SQLite, even though the amount available online is much much larger. But most books you can find are aimed at people doing mobile software development, and their choice of SQLite is based not on the pros of SQLite, but on availability for their platforms. In fact, even “Definitive Guide” mentions iOS and Android development (and this is for a book which was released in 2010, when Android and iOS had barely existed), and even dedicated two whole chapters to them.

I have actually read bits and pieces of the other two books, and I admit that they have their own advantages. The “QuickStart” has a more consistent introduction into development environment, and “Using SQLite” has an introduction into good database design, which is missing from “The Definitive Guide”.

Nevertheless, “The Definitive Guide” is a good place to start.

The book is roughly structured into three parts:

  1. SQLite command-line usage,
  2. SQLite C API,
  3. Using SQLite non-natively, from iOS, Android, and languages other than C and Shell.

There are two chapters dedicated to SQLite “internals”, but they are given in order to make presentation of the C API easier, not as a guide into the development of SQLite itself.

The structure is understandable for a technical book. Command-line usage is the simplest way to approach a database, is also the easiest way to investigate other database, and allows the reader to start using SQLite in their everyday life, calling it from the system Shell.

Despite the fact that the CLI interface does not allow to customise requests with the highest possible granularity (one needs C for that), it allows the user to learn most of the SQL language, which has two chapters dedicated to it. (One chapter is dedicated to read-only mode, the SELECT operator, and another one is dedicated to the read-write mode, which involves UPDATE and CREATE TRIGGER.)

I remember learning SQL and database design in the University, and turning MySQL into a friendly learning environment was a pain. With SQLite (and sqlitebrowser), learning SQL is a bliss.

The book covers:

  1. CREATE TABLE
  2. ALTER TABLE
  3. DROP TABLE
  4. SELECT
    1. JOIN
  5. UPDATE
  6. DELETE
  7. CREATE VIEW
  8. CREATE TRIGGER
  9. functions
  10. aggregates
  11. BEGIN/END (ACID transactions)
  12. Write-Ahead Logging and concurrency

The most common complain about SQLite is that it does not support “SQL stored procedures”.

Otherwise, the book presents most of day-to-day SQL, and, while it will not make you an experienced database administrator (DBA), it will allow you to start managing your data in an easy to search form.

Approximately a third of the C API is also covered. The book will teach you how to

  1. Make normal operations, similar to the command-line ones,
  2. Speed-up queries by compiling them in advance
  3. Implement custom functions, aggregates, and collations

The books does not tell about implementing custom virtual tables, even though, perhaps, this topic can be considered excessive for the target audience. It also does not speak about full-text search or built-in spatial extensions. For these subjects you would have to refer to “Using SQLite”.

The style of the book is easy and approachable. You can probably read most of the content you need in a short time, a couple of days, and at the same time density does not seem to be overwhelming.

In generally, I recommend this book.

1.1. What are databases?

In theory, every file, and every filesystem, and even a sheet of paper, are databases in that they keep data. For people with a solid grasp of computer usage, a table is a very natural object to work with. Even people very far from computers know, and many even love, Microsoft Excel (or LibreOffice Calc), which is not just a smart table manager, but also a sophisticated pure functional programming environment.

People who are more used to automating their life probably use AWK for routine table operators. After all, a select name from employees can often be implemented like: 

cat employees.csv | awk '{print $1;}'

functions and aggregates are mostly just some conditionally incremented AWK variables. Inserting entries is basically just appending a line to a file, and possibly sorting that file.

What is the benefit of SQL? Well, firstly the benefit is the ability to insert entries in the middle of the table without re-reading the whole file. Secondly, merging table data with AWK becomes very tedious when you have several tables. Thirdly, more complex features, such as type checking, views, triggers, and transactions, are, of course, all on the shoulders of the programming when trying to use AWK for data management.

Nevertheless, AWK can replace surprisingly many applications of an SQL database, if you have no other choice.

But SQLite developers do not eat their bread for nothing. The SQLite backend uses a clever data structures called “B trees”, which optimise disk access so much that in certain use-cases querying a database for a piece of data becomes faster than just loading that piece of data from the disk directly. The book speaks a little about SQLite internals, more in depth about the SQLite locking state machine, and less about its low-level B tree interface, but can be used as a first introduction. For a more in-depth discussion, you can have a look at “Inside SQLite” or “SQLite Database System Design and Implementation”. The books are a worthy read if you are into low-level things.

2. Blurb

  1. Mail (best way to reach me) :: lockywolf at lockywolf.net
  2. Telegram :: https://t.me/unobvious
  3. Zhihu (cn) :: https://www.zhihu.com/people/lockywolf
  4. LiveJournal (ru) :: https://dead-doomer.livejournal.com
  5. Wordpress (en) :: https://lockywolf.wordpress.com
  6. Permalink :: https://lockywolf.net/2026-06-04_Reading-Definitive-Guide-to-SQLite.d/index.html