Compression in SQLite database .Net C#

Question

Can BLOB (or any other data) be compressed (on the fly) into an SQLite3 database? I'm using System.Data.SQLite in C# targeting .net 3.5.

In my search I keep seeing connection strings like so... "datasource=base.db;version=3;compress=true;"

But I can find no information about compress=true, I've added it to my connection string for testing and added file as BLOB data but there appears to be no compression, I have tested with for example a large text file, so not an already compressed image or other file.

Answer 1

To my very limited knowledge, the "compress=True" parameter in the System.Data.SQLite.SQLiteConnection String is not taken in account.

Basically, it is kind of useless: there is no built-in compression provided for the .NET System.Data.SQLite.

In order to bring some compression features to the System.Data.SQLite world, you have basically have two options:

• Using some third party tools, whether by including among one of those C libraries and rebuilding the System.Data.SQLite based on it: Proprietary SQLite Extensions: SEE, CEROD, ZIPVFS / SQLite Crypt / etc. The commercial ORM Devart based on ADO.NET offers the support for the encryption options I just mentioned and seems they can be used seamlessly (although I personally never tried to use Devart that much).
• Creating a wrapper around the System.Data.SQLite: whether converting all your data with some headers about the encapsulated data are and inserting everything using some collection of bytes with a blob data affinity. You can also by the way provide at the same time some additional encryption process.Of course, this will be (much) slower than the C third-party libraries available and may be not sufficient depending on your requirements (insertions, updates,.., deletions speed), but at least this is free (and time-consuming) approach. This can be achieved through a specific SQLite ORM, should not be that hard to implement but quite time-consuming.