Sqlite – Why We Like it!

Firstly because it is open source and free!  But that would not be enough, we want a database that is easy to use, needs no expensive Database Administrators or massive super computers to run.  A single file format for easy migration and backup would also be a bonus.  If this database also supported most of the ANSI 92 standards then that would be amazing.

SQLite ticks all the boxes.  The only real omissions are (and they are all either not important or can be worked around):

  • RIGHT and FULL OUTER JOIN
  • LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
  • Complete ALTER TABLE support
  • Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
  • Complete trigger support
  • FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
  • Writing to VIEWs
  • VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
  • GRANT and REVOKE
  • Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.

There’s  absolutely nothing to worry out about in that list.

I have been involved in discussions with other developers who seem to think there is some sort of stigma attached to using a database that has the word ‘lite’ in its name.  This is a bit silly as Apple uses Sqlite in its iTunes, Aperture  and Apple Mail applications,  Adobe uses it in Lightroom , Dropbox uses it and many, many more.  There is more information on the major SQLite players in the IT Industry on the SQLite Well Known Users page.

Another argument I have heard against SQLite is that it can be un-predictable when used as a cross network multi-user database, this is probably a fair comment but it is a bit like saying that a free bicycle is slower than a Ferrari and will take you much longer to travel 100 miles.  If you are developing a networked multi-user database application then I would suggest you use a database that has actually been designed and built for that purpose like mySQL or Postgres.

For the majority of desktop applications ranging from small to massive, SQLite does the job fantastically.  We have built a SQLite based application for a client that analyses several hundred million pieces of information and as the database has been designed correctly and indexes applied properly it is extremely fast, requires zero maintenance and can be backed up onto a single file, it is a large file admittedly but 400mb these days is not a big deal so backups can be held on Dropbox which is great.