If you've been following my blog, you'll have noticed that all of my projects use the SQLite database engine.
My projects are relatively tiny, low traffic and data is mostly queried, not written. Such characteristics make SQLite the perfect database for my projects.
What's super fun about SQLite is that it's self-contained within a single file. There are no configuration woes, no network security to worry about, no hundreds pages of documentation. It's just a single file!
See Distinctive Features of SQLite and Appropriate Uses for SQLite pages to find other points when SQLite is a good fit and when not.
Here is the lecture by Richard Hipp, the author of SQLite:
Here are some interesting facts from the lecture:
- [02:50] SQLite is designed to be embedded, it's less than 250 KB in size.
- [08:00] Uncommon SQLite uses (this got me most interested): stand-in for client-server DBMS during testing/debugging. Local database caching. Implementing complex data structures. Sorting large amounts of data. Configuration files. IPC via database. Application file formats.
- [14:06] SQLite is very convenient to use as a tool to teach basics of SQL, as it just works.
- [19:32] Unusual features of SQLite: SQLite ignores data types for columns (you can store string in an integer column, for example). SQLite does type affinity on data inserted in columns. Table 'sqlite_master' stores information about tables. Attaching to multiple databases simultaneously via ATTACH command. You can join or copy across multiple open databases (for example, hot backup the database).
- [24:40] Anatomy of an SQL database engine.
- [27:00] SQLite compiles queries to byte code (can be viewed via EXPLAIN statement) to be executed in a virtual machine.
- [28:20] Observations of SQLite: trouble with licensing. A register based virtual machine is much easier to generate code for which is optimal than a stack based VM. Dynamic typing in databases is a really good thing. Regression tests allow rewriting large parts of SQLite without minor version releases.
- [36:30] Q and A!
- [36:35] Is there ORM tool available for SQLite?
- [39:30] How is dynamic typing better than static typing in databases?
- [41:32] What did you mean by 'complex data types'?
- [43:15] Why is a register based virtual machine better than a stack based?
- [44:22] Why does SQLite only parse foreign keys but not enforce them?
- [46:08] Is SQLite an in-memory database?
- [46:50] What's the future of SQLite?
- [48:10] My SQLite DB got corrupt, what do I do?
- [49:30] When does the DB roll back in case of power failure?
- [50:30] What happens if there is a second power failure while rolling back the queries from previous power failure?
A few notes from me.
The usage of 'manifest typing' really confused me in this lecture, because I, and most of the people I have talked to, uses this term for 'static typing'. The author of SQLite uses it to mean 'dynamic typing'. Don't know why. Hmm.
An SQLite database can be managed via the sqlite (or sqlite3) command line tool or GUI tool such as SQLite Browser (primitive), SQLiteSpy (advanced) and SQLite Manager (as a FireFox Add-on).
Finally, here are a few articles you should read if you are interested in more advanced SQLite details:
I hope you enjoyed it and have fun using SQLite for your next project!