OrmLite method to create new, empty database?

ServiceStack.OrmLite
1

I’m getting ready to start the development of integration tests for my OrmLite code. I’m using Dotnet Core, xUnit, and Visual Studio Code, and Jenkins for CI. I’d like to run integration tests against SQLServer, SQLite, and MySQL.I’d like to have the Test Fixture create a new, empty in-memory database in whatever actual database the environment/configuration specifies. All test methods in the class will use a transaction to create/populate tables as needed for the code being tested, validate, then rollback the transaction, which will provide an isolated environment for each test and allow parallel test execution.

  1. Does OrmLite have any methods I can utilize in the test fixture that would ease the process of creating a new, empty database? Or should I just use DDL specific to each database server type to create the new empty test database?

  2. (More of a hopeful wish here: :-)) Are you aware of any test runners or methodology that will let me run the same test class multiple times, with different values for environment variables before each run? So I can set an environment variable “TestDB” to the value e.g. “MySQL”, run the test class, change the variable “TestDB” to the value “mssql”, and run the test class again, and do this for each kind of database server automatically. Or even better, run the test classes for each environment value in parallel? I’m hoping that you’ve implemented such testing in ServiceStack, and can point me to some areas in the GitHub repositories I can study for ideas?

Thanks for an excellent product!

2

OrmLite doesn’t have create DB methods built in but it should be easy enough for you to wrap in your own extension method for each DB type. For example, something like:

public static void CreateDb(this IDbConnection db, string databaseName)
{
    var provider = db.GetDialectProvider();
    if (provider == null)
        throw new Exception("Invalid IDbConnection, no provider found.");

    switch (provider.GetType())
    {
        case {} mySqlProvider when mySqlProvider == typeof(MySqlDialectProvider):
            db.ExecuteNonQuery($"CREATE DATABASE IF NOT EXISTS `{databaseName}`;");
            break;
        case {} sqlServerProvider when sqlServerProvider == typeof(SqlServer2012OrmLiteDialectProvider):
            db.ExecuteNonQuery(@$"
                IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{databaseName}')
                BEGIN
                  CREATE DATABASE {databaseName};
                END;");
            break;
        default:
            throw new Exception("Invalid provider");
    }
}

What is the use for this approach if you don’t mind me asking? Supporting multiple database technologies even with the use of an ORM will likely hit edge cases of trying to support functionality that doesn’t exist across technologies or is vastly different in practice, generating complexities. If it’s something you have to support then sure, but IMO, something best to avoid if it isn’t core to your app/product/business.

Also, is common even when building against one DB provider to use custom SQL to get the most out of what that provider has to offer. An example of this would be Postgres with PostGIS, it is easier to use custom SQL mapping to a POCO and get all the performance/flexibility advantages vs trying to abstract it away and miss out. For testing in a situation like this I have previously run docker databases of the same versions/setup as live environments. This has the advantages of being up to test production version upgrades for any issues again a full test suite which adds confidence to such migrations.

OrmLite itself uses a custom NUnitAttribute to have TestFixtureOrmLiteAttribute which is used for some common tests controlled by environment variable to specify the provider. There are examples in there as well as comments about its usage. For more specific tests are separated into projects by database technology.

Hope that helps!

3

Thank you for the swift response! It never fails to amaze me how quickly the ServiceStack staff and community responds to questions. I will try coding up the CreateDb extension for my integration tests later today.

As for the use case; I’m writing a set of library packages and an app, in which most of the objects include a StronglyTypedId. Better minds than mine have documented the ‘why’ of using Strongly TypedIds:

Mssr. Levesque’s post includes examples of using c# 9 records for abstract generic StronglyTypedIds, and examples of serializing these via System.Json.Text and Newtonsoft.Json, and persisting them via EF Core.

I’m a fan of ServiceStack, and my app will be using ServiceStack, so I need to figure out how to extend Mssr. Levesque’s work to serialize/deserialize concrete instances of the abstract generic type using ServiceStack serializer, and how to persist a concrete instance in a database.

I’d like my StronglyTypedIds to be useable in the big 3 databases supported by Ormlite, hence the need for integration tests that validate the code against all three databases.

In the spirit of TDD, I’m getting my testing infrastructure setup first, and I’ll be starting on the actual converter code after I’ve got the first ‘failing’ tests working for each database technology.

If you are interested in seeing how EF Core is extended to handle the abstract generic StronglytypedId type, and maybe offer some thoughts on how to achieve the equivalent in OrmLite, I’d recommend taking a look at C# 9 records as strongly-typed ids - Part 4: Entity Framework Core integration. Or you can just wait for me to get stuck and ask the question in a new topic :smile:

Again, Thanks for your response and suggestions.

1 Like