The last weeks I have been pickup up the development of the football predictions website again. One of the things I realized (again) was that my home pc is quite old and limited on resources, so I don’t want to run a SQL Server in a docker container. Since I already investigated to use SQLite as a database engine, I decided to make the database engine configurable. It turned out to be a small change.

First there had to be a system stetting to specify the database engine.

"System": {
	"DatabaseType": "Sqlite"
}

Obviously there has to an enum and a property in the AppSettings models. By adding the [Serializable] attribute to the enum the framework can read the string in the JSON and convert that value to an enum.

[Serializable]
public enum DatabaseType
{
	SqlServer,
	Sqlite
}

public class SystemSettings
{
	public const string System = "System";
	public DatabaseType DatabaseType { get; set; }
}

This also meant there should be two entries to store the connection strings for both database engines. Note that this are not the full connection strings, but you will get the point.

"ConnectionStrings": {
	"SqlServer": "Server=127.0.0.1;Database=football",
	"Sqlite": "Data Source=filename.sqlite"
}

And the corresponding changes in the connection strings model.

public class ConnectionStringSettings
{
	public const string ConnectionStrings = "ConnectionStrings";
	public string SqlServer { get; set; }
	public string Sqlite { get; set; }
}

With this all done, only the DatabaseHelper to get the database connection had to adjusted to do all the hard work. Luckily I do this early in the development and had I chosen to use GetDatabaseConnection() to get an IDbConnection. This made this change relative straight forward.

public IDbConnection GetDatabaseConnection()
{
	return _systemSettings.DatabaseType switch
	{
		DatabaseType.SqlServer => new SqlConnection(_connectionSettings.SqlServer),
		DatabaseType.Sqlite => new SqliteConnection(_connectionSettings.Sqlite),
		_ => null,
	};
}

Not too difficult all together, but definitely worth it. In a production environment SQL Server can be used, but for local testing SQLite is more then enough. I only need to test if all query’s I will write will work on both engines.