What’s so good about SQLite?

…continuing my posts on Ruby and SQLite as a micro ETL environment.

I’ve written before that my most important take-away from Ruby On Rails was the language Ruby, but RoR also introduced me to SQLite. Although its typical use in Rails is as a development database I quickly realised this open source ,very fast and zero-configuration SQL database has a role to play in the real world of data analytics; a cross-platform and free alternative to the MS Access Jet database engine. Like MS Access, SQLite is not client-server based, so no separate services or jobs required on the host machine; a full database is held in a single file, making it easy to transfer from one machine to another.

Will SQLite replace MySQL, Oracle, SQLServer or indeed the venerable MS Access? No, but as an embedded database or as a light-weight local data-store it has many advantages:

  • SQLite supports most SQL92 syntax, including database triggers, makes skipping back and forth between MySQL, Oracle and SQLite much easier.
  • SQLite runs on all major OS and programming language platforms, e.g. a database populated on Windows by VBA can be read or modified under Linux by a Ruby program.
  • It’s free, in future the Jet engine will be no longer be distributed with the latest Microsoft Data Access Components, you’ll have to purchase Access 2007.
  • SQLite supports in memory tables, making it possible to create a local “ROLAP star” with the fact table file-based and the dimension tables loaded within memory.
  • Several free GUI administration tools.
  • SQLite does not enforce data type constraints. Any data can be inserted into any column.

Now, the last point may seem more like a bug than a bonus, but in the world of ETL, dirty data is a fact of life; those working at this end of the pipe must accept what’s thrown at them. A forgiving weakly-typed data store (like a late-binding and weakly-typed scripting language) can be just what the doctor ordered in such an environment. In my early career as an OLTP developer I would have baulked at the lack of strong-typing but now I side with Bruce Eckel’s essay on strong-typing versus strong-testing , productivity is what matters and the ability to test is the most important skill that a developer should possess.

See also SQLite as the MP3 of data


8 responses to “What’s so good about SQLite?

  1. Pingback: Python the new VBA ? « Gobán Saor

  2. Pingback: Why Ruby? « Gobán Saor

  3. So how are you hooking them together, and some examples of how they tools can query each other would be nice. Screenshots? Tutorials?

  4. @Steve,

    I guess you’re referring to SQLite with Excel and Hadoop? Using SQLite with the likes of Ruby, Python, Java etc. is very well documented on the web. And yes all that stuff, screen shots, tutorials etc. would be nice but as I’ve written elsewhere …

    ..as for Excel/SQLite I’m bit by bit building something that I intend to release, mainly by refactoring ‘old work’ (which by the nature of what we do, tends to ‘hacked together’ to a get a specific job done) into something more robust and generic. I’ve been mainly doing it for myself (but they say the best software is that which “scratches the author’s itch first”) so making it public was not my priority – paid work and life-in-general comes first – but I’m starting to think that there’s a demand out there for such a product, and more importantly for the services associated with it, so I’ve started to invest more time in finishing it off to use as a marketing ‘hook’ for my paid work. So as they say, stay tuned…


  5. Hi ,
    what is maximum record that SqlLite can be supported .
    Thanks .

  6. @Martin

    see here for the various SQLite limits


  7. Hi
    Thanks for good info on SQLite.
    The one info I couldn’t find or maybe didn’t use correct words when search.
    So I’ll just ask here. Please help me to understand this.
    Let’s say I needed table with string column that can hold 500 char.
    But in real life the most of data only 20 char and only few has 500 char.
    In this scenario, would SQLite make any optimization?
    Or does SQLite have Tag-Length-Value kind of database?

    Thanks a lot your time.

    • @Nmk

      SQLite uses something called manifest tying for its column types (or more correctly “cell” types); whatever value or type is required to be held, SQLite will accommodate it .

      When you specify a column as char(500) or char(5) or datetime or nvarchar(500) or int or text or whatever, SQLite only uses the specified type as a hint, mainly for sorting but also occasionally to help it decide which type to use for storage when there’s a conflict. In fact, the simplest why to a define a table in SQLite is:

      CREATE TABLE myTable(ThisField, ThatField, IMightStoreADoubleHere) i.e specify no types.

      And to answer your question, yes SQLite will adjust the amount of allocated space to that required to hold each individual cell’s value.

      See “manifest typing” and “variable-length records” in this article http://www.sqlite.org/different.html