introduction to sqlite database rdbms

If you have been following my blog, you might have noticed that almost 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.

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!

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...

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!

Comments

September 13, 2008, 11:58

please i want you people to sending me the recent lecture on my email address

I will be expecting the daily lecture on my email adress pls.
THANK
YOUFAITHFULLY
13TH SEPTEMBER 2008

December 04, 2008, 03:23

and it goes well with django!

thx for this article!

kookimebux Permalink
February 01, 2009, 18:52

Hello. And Bye. :)

Qatan Permalink
May 12, 2010, 01:03

Thanks for the valuable information.
I am planning to use SQLite in my new project replacing the legacy DBF file format (from DBASE III -> Clipper).
The main goal is to produce a simple but still reliable application that uses a self deployed database FTS capable and with journal schema for security of data. It will have also some concurrent access but not much.
I want to learn more about SQL syntax and culture since I am not used to it at all and only knows typical xBase syntax and SQLite seems to be the solution for that purpose.
Your blog helped me to do this choice of trying SQLite.

quis00 Permalink
July 23, 2010, 03:37

sqlalchemy is the orm tool i use with it

Leave a new comment

(why do I need your e-mail?)

(Your twitter name, if you have one. (I'm @pkrumins, btw.))

Type the word "floppy_49": (just to make sure you're a human)

Please preview the comment before submitting to make sure it's OK.

Advertisements