Follow me on Twitter for my latest adventures!
My projects are relatively tiny, low traffic and data is mostly queried, not written. Such characteristics make SQLite the perfect database for my projects.
If you did not know, the SQLite database is 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!
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...
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!