sqlite unit testing

[BrunoVT1992]

Is it possible to mock a sqlite database so I can use it for unit testing? If so can you show me an example how I can acomplish it

[oysteinkrog]

Hi,

I'm not 100% sure what you mean, but what most people do is to create a database in memory, that way it is both temporary and fast. https://www.sqlite.org/inmemorydb.html If you want to mock the SQLite.Net interface itself I'm afraid you will have to create your own interfaces/abstractions.

[BrunoVT1992]

Hi,

I need to be able to test my database queries from my unit test project but to do that I need to create a SQLiteConnection. I cant do this because I need an SQLitePlatform in the constructor. How can I resolve this?

[valdetero]

I share the unit (integration) tests that I run on my device with the Visual Studio test runner. When specifying the platform, just use: SQLite.Net.Platform.Generic.SQLitePlatformGeneric(); and it will put the file in the bin directory of the Unit test project.

[SlyNet]

the new SQLiteConnectionString() constructor requires file path, so how could I write code to run sqlite in memory?

new SQLiteConnection(new SQLitePlatformGeneric(), new SQLiteConnectionString('%PATH?'));

[valdetero]

@SlyNet, according to the article, you should just be able to pass ":memory:" as the path.

[tstivers1990]

In memory implementations are not unit testing. That is integration testing. In order to be able to unit test, I need to isolate the unit I want to test from SQLite by replacing SQLite with a faked object that I can control. Not only that, but in memory implementations are much slower than what could be accomplished with fakes. This discourages people from actually running the unit tests, and increases development time un-necessarily.

The correct answer here is to use the adapter pattern. Although it would be ideal if this class provided an interface out of the box.

[seanfisher]

Here's an interface for SQLiteAsyncConnection, if it helps.

What I do to unit test is create my own class:

public class MySQLiteAsyncConnection : SQLiteAsyncConnection, ISQLiteAsyncConnection
{
    public MySQLiteAsyncConnection(Func<SQLiteConnectionWithLock> sqliteConnectionFunc, TaskScheduler taskScheduler = null, TaskCreationOptions taskCreationOptions = TaskCreationOptions.None)
        : base(sqliteConnectionFunc, taskScheduler, taskCreationOptions)
    {
    }

    public bool IsEncryptedConnection { get; set; } = false;
}

Then I make a factory like this:

public class SQLiteAsyncConnectionFactory : ISQLiteAsyncConnectionFactory
{
    public ISQLiteAsyncConnection GetSqlConnection(string dbPath, bool encrypted)
    {
        var platformDatabaseFactory = IocContainer.GetContainer().Resolve<ISQLitePlatformFactory>();
        var platform = platformDatabaseFactory.GetSQLitePlatform(encrypted);
        var serializer = IocContainer.GetContainer().Resolve<IBlobSerializer>();
        var connectionWithLock = new SQLiteConnectionWithLock(platform, new SQLiteConnectionString(dbPath, true, serializer));
        var connection = new MySQLiteAsyncConnection(() => connectionWithLock)
        {
            IsEncryptedConnection = encrypted
        };
        return connection;
    }
}

Then I inject an ISQLiteAsyncConnectionFactory whenever I need a database in my code. Then you can mock out the ISQLiteAsyncConnectionFactory and return a mock ISQLiteAsyncConnection. You'll notice I also have a ISQLitePlatformFactory, which is nice because I'm using Xamarin, and then the platform factory can also handle encrypting or not encrypting the database.

[PandaL33]

@seanfisher How to encrypt a database by using a database password and connect to the database which is encrypted?

[seanfisher]

@PandaL33 We use SQLCipher on Xamarin iOS. Using my code above I also provide an ISQLitePlaformFactory which has methods which boil down to something like this:

public ISQLitePlatform GetSQLitePlatform(bool encrypted)
{
    if (encrypted)
    {
        string encryptionKey = GetEncryptionKey();
        return new SQLite.Net.Platform.SQLCipher.XamarinIOS.SQLitePlatformIOS(encryptionKey);
    }

    return new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();
}

public string GetEncryptionKey()
{
    // Generate or retrieve encryption key from iOS keychain
    return "";
}