The Definitive Guide to SQLite

I'm a huge fan of SQLite. Every time I do something with the little database it always manages to impress me in new ways. Here's a pop quiz for you:

• Did you know SQLite is totally free? I mean really free. All the code is in the public domain, protected by affidavits, and you can literally do anything you like with it.
• Did you know SQLite uses "manifest typing" which is similar to Ruby's dynamic typing? The database engine will really allow you to handle field types in whatever way is best for your needs. Of course, you can do type checking in triggers if you prefer to be more strict.
• Everyone knows SQLite shoves an entire database in one file, but did you know that it can work with more than one of those files at once? Yes, SQLite can query across multiple databases.

I could go on and on. Really, I could. SQLite is that cool.

It's almost silly to use flat files these days. If you find yourself needing one, you can load one gem instead, stick a full database in the file, take advantage of transactions and locking (very multiprocessing friendly), gain a full query language for working with the data, and have a prebuilt human interface completely separate from your code (the command-line tool is great for debugging). It's hard to beat that.

How did I learn all of these great things about SQLite? I read the bible. I know, that's a lot of weight to lay on a book calling it the bible for anything. I really believe The Definitive Guide to SQLite can carry that much weight though.

The first thing you need to know is that this book casts its coverage net wide and deep. In about 400 pages you will get a crash course on database design and relational theory, a detailed look at the design of SQLite dipping all the way into the internals, a walkthrough of API's for C and six other languages, and full coverage of the SQL supported by the database. I think the book claims to be for all experience levels, but given that table of contents I would probably call it an intermediate title.

The book may not be for the faint of heart, but it will reward your efforts to digest it in spades. The early sections on relational databases can improve your understanding of all databases, not just SQLite. Then later, when the subject matter switches to deep internals, it almost always leads to a great clarification of complexities surrounding difficult topics, like locking. Take breaks, read it multiple times, or whatever else it takes because the knowledge is very worth it.

It's hard to level any minuses about this book, but if I had to find one it might be that it's a bit old. That won't affect you much, but it does mean that the title doesn't cover some exciting new features that have been added recently (geospatial queries, for example) and some promising new interfaces (like Ruby's Amagalite). You will definitely want to dig into these topics after reading the book and it will give you a solid background for doing so.

I also had a little trouble finding this book over the summer, but it seems to be readily available now. They were probably just between printings at that time.

If you work with light to moderate data and don't fully need an ORM, this book can add a wicked tool to your arsenal. Or if you just want to learn more about how SQLite can be used more effectively in your Web application, this book can give you that and more. It may even improve how you think about data in general. It's that enlightening.

Here is an Amazon referral link for the title, added to satisfy multiple reader requests: