Experiments with SQLite

Experiments with SQLite

By | 2016-12-01T16:37:39+00:00 August 26th, 2015|Dev Insight|0 Comments

SQLite logoLong time fans of Gallery Server may recall that versions earlier than 2.5 supported SQLite as a database option. At the time it was a great choice because it was a file-based database that was robust, fast, required no extra software on the server, and required no maintenance. The only real downsides were that we had to distribute both a 32-bit and 64-bit version, and it ran only in Full Trust environments (no worky on GoDaddy).

When Microsoft released SQL CE 4, we realized it would solve both of these issues, so in 2.5 we dropped SQLite support and moved to SQL CE. Overall it’s been a great move – it just “works” and people who use it almost never have to interact with the underlying database.

But it came with a price. It is slow. So slow that customers who use SQL Server often see a 20-fold increase in database performance.  If you’re running any moderately sized gallery, you’ll really notice the difference by using SQL Server.

I had some time today to revisit SQLite to see what it was like these days and whether we could use it as a third database option in Gallery Server. I was hopeful because Gallery Server defers almost all SQL generation to data providers – currently we ship with one for SQL CE and one for SQL Server. Could it be as simple as dropping in the SQLite DLL’s and adding a few lines to web.config?

It was easy enough to use NuGet to add a reference to the System.Data.SQLite package. This pulls in the required DLL’s and updates web.config. I recompiled with success. Then I added a connection string to web.config:

<add name="GalleryDb" providerName="System.Data.SQLite.EF6" connectionString="data source=|DataDirectory|\GalleryData.sqlite" />

I opened a browser window and navigated to the home page. Then I got this error:

No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SQLite’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

Turns out the <entityFramework> section needed a little tweak. I removed the ‘EF6’ from the invariantName so it looked like this:

<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />

I tried again and this time I got this:

No MigrationSqlGenerator found for provider ‘System.Data.SQLite’. Use the SetSqlGenerator method in the target migrations configuration class to register additional SQL generators.

Uh oh. A search of the internet revealed a big problem – there is no freely available MigrationSqlGenerator for SQLite. I did find a commercial one named dotConnect for SQLite that includes the MigrationSqlGenerator but it’s $120 at the time of this writing. That’s a lot of money that makes including it as an option for Gallery Server financially unviable. Plus, it may not even work, since SQLite has such poor support for many SQL commands, making it possible the generator isn’t able to convert some Entity Framework syntax into SQL.

If you ever hear of an open source or freely available MigrationSqlGenerator for SQLite, let me know. Until then, Gallery Server will stick with SQL CE and SQL Server.

About the Author:

Founder and Lead Developer of Gallery Server

Leave A Comment