SQLite by Chris Newman
Posted on December 7th, 2006
SQLite is an introduction to the SQLite database. Whereas other databases match complex features with complex setups, SQLite is all about simplicity. The database is a single file. There are no usernames and passwords to fuss with. SQL queries are largely understood the way other databases systems understand them. You can get yourself up and running without any of the minor yet annoying puzzles that come up when you're first getting acquainted with a new database. It's all so... easy.
SQLite covers its namesake both directly and within the context of several programming languages. The former comes first, and Newman points out many aspects of SQLite that give you a good grounding on what you can and can't do. It's easy to think you can just skip over this material once you see that queries are standard SQL, but that's missing out on the underlying details. I liked the suggestion of using a temporary table to compensate for the lack of an ALTER TABLE command, and was surprised to read that SQLite's COPY command was based on an equivalent in PostgreSQL and could therefore work with the output of pg_dump.
It's also good to know about SQLite's more advanced features, even though you may not have an immediate need for them. The ON CONFLICT clause falls into this category for me, as do user defined functions and triggers. I'm happy enough being able to bring a complete SQL environment into my projects as a single local file.
SQLite's coverage of the PHP, C/C++, Perl, Tcl, and Python interfaces is necessarily redundant, and will be at least partially skippable unless you're working in all those languages. I even skipped through most of the PHP material, because I prefer to use PHP5's PDO extension for ease of migration.
Finally, SQLite predates the release of SQLite version 3, and so due to the book's age some things will necessarily be out of date. Still, the book is a short one and still well worth consideration.