SQLite as an Embeddable Database

These days I'm involved in designing a ‘Smart Client Application’ architecture for one of our customers. These smart client applications require fair amount of offline capabilities, fast data access and low network utilization. To achieve the objectives most of the data is cached and persisted on the client side. This required us to look at data manipulation, persisting and synchronization mechanisms at the client side.

The first alternative we had was to maintain XML serialized objects on the client side to cache data. This had the problems like Data querying difficulties, Security issues, Thread safe access issues, High memory consumption and Implementation difficulties.

Next we have decided to use an embeddable database for storing data. Our first candidate was MS SQL Express database, as our project is mainly based on VB.NET with MS technologies. Even though it has some limitations, SQL Express is a very feature rich database which can be invoked in the similar way you invoke SQL Server database (with SPs, Views, Functions, full ADO.NET support, etc…). This was a major plus point for us as we could reuse the same data access layer components on the client side. It also supported database replication so that the cached data syncing could be done even at the database level.

But the problem was the high installation requirements of the SQL Express database. Also configuration of the database seemed to be fairly complex for our requirements.

Next alternative we looked at was the open source database SQLite. It has an incredibly small foot print of less than 400Kb and performance was impressive as a small single user database. ADO.NET 2.0 data provider was also available for SQLite and can be integrated to Visual Studio 2005 easily. It has implemented most of the SQL92 standard with few exceptions. SQLite is really a zero configuration single-file database which runs in-process of your application.

Having said the plus points, it is now the time to look at the limitations also. One main problem is that, the only locking level it supports is at database level. This can be a problem when multiple processes accessing the database simultaneously. Since smart clients are generally used by a single user, this shouldn’t be a big problem as an embedded database. But in our case, the smart client application might be deployed on a Cytrix Server and may have many users connecting over consoles.

Having honored by “2005 Open Source Award from Google and O'Reilly”, I think we should seriously consider using SQLite as an embedded database for our smart client architectures.