12

How does one begin to design an abstract class in their larger application that can interface with several types of databases, such as MySQL, SQLLite, MSSQL, etc?

What is this design pattern called and where exactly does it starts?

Let's say you need to write an class that has the following methods

public class Database {
   public DatabaseType databaseType;
   public Database (DatabaseType databaseType){
      this.databaseType = databaseType;
   }

   public void SaveToDatabase(){
       // Save some data to the db
   }
   public void ReadFromDatabase(){
      // Read some data from db
   }
}

//Application
public class Foo {
    public Database db = new Database (DatabaseType.MySQL);
    public void SaveData(){
        db.SaveToDatabase();
    }
}

The only thing I can think of is an if statement in every single Database method

public void SaveToDatabase(){
   if(databaseType == DatabaseType.MySQL){

   }
   else if(databaseType == DatabaseType.SQLLite){

   }
}
11

What you want is multiple implementations for the interface that your application uses.

like so:

public interface IDatabase
{
    void SaveToDatabase();
    void ReadFromDatabase();
}

public class MySQLDatabase : IDatabase
{
   public MySQLDatabase ()
   {
      //init stuff
   }

   public void SaveToDatabase(){
       //MySql implementation
   }
   public void ReadFromDatabase(){
      //MySql implementation
   }
}

public class SQLLiteDatabase : IDatabase
{
   public SQLLiteDatabase ()
   {
      //init stuff
   }

   public void SaveToDatabase(){
       //SQLLite implementation
   }
   public void ReadFromDatabase(){
      //SQLLite implementation
   }
}

//Application
public class Foo {
    public IDatabase db = GetDatabase();

    public void SaveData(){
        db.SaveToDatabase();
    }

    private IDatabase GetDatabase()
    {
        if(/*some way to tell if should use MySql*/)
            return new MySQLDatabase();
        else if(/*some way to tell if should use MySql*/)
            return new SQLLiteDatabase();

        throw new Exception("You forgot to configure the database!");
    }
}

As far as a better way of setting up the correct IDatabase implementation at run time in your application, you should look into things like "Factory Method", and "Dependancy Injection".

25

Caleb's answer, while he is on the right track, is actually wrong. His Foo class acts both as a database facade and factory. Those are two responsibilities and should not be put into a single class.


This question, especially in the database context, has been asked too many times. Here I will try to thoroughly show you the benefit of using abstraction (using interfaces) to make your application less coupled and more versatile.

Before reading further, I recommend you to read and get a basic understanding of Dependency injection, if you do not know it yet. You might also want to check the Adapter design pattern, which is basically what hiding implementation details behind interface's public methods means.

Dependency injection, coupled with Factory design pattern, is the foundation stone and an easy way to code the Strategy design pattern, which is a part of IoC principle.

Don't call us, we will call you. (AKA the Hollywood principle).


Decoupling an application using abstraction

1. Making the abstraction layer

You create an interface - or abstract class, if you are coding in a language like C++ - and add generic methods to this interface. Because both interfaces and abstract classes have the behaviour of you not being able to use them directly, but you have to either implement (in case of interface) or extend (in case of abstract class) them, the code itself already suggests, you will need to have specific implementations to fullfil the contract given by either the interface or the abstract class.

Your (very simple example) database interface might look like this (the DatabaseResult or DbQuery classes respectively would be your own implementations representing database operations):

public interface Database
{
    DatabaseResult DoQuery(DbQuery query);
    void BeginTransaction();
    void RollbackTransaction();
    void CommitTransaction();
    bool IsInTransaction();
}

Because this is an interface, it itself does not really do anything. So you need a class to implement this interface.

public class MyMySQLDatabase : Database
{
    private readonly CSharpMySQLDriver _mySQLDriver;

    public MyMySQLDatabase(CSharpMySQLDriver mySQLDriver)
    {
        _mySQLDriver = mySQLDriver;
    }

    public DatabaseResult DoQuery(DbQuery query)
    {
        // This is a place where you will use _mySQLDriver to handle the DbQuery
    }

    public void BeginTransaction()
    {
        // This is a place where you will use _mySQLDriver to begin transaction
    }

    public void RollbackTransaction()
    {
    // This is a place where you will use _mySQLDriver to rollback transaction
    }

    public void CommitTransaction()
    {
    // This is a place where you will use _mySQLDriver to commit transaction
    }

    public bool IsInTransaction()
    {
    // This is a place where you will use _mySQLDriver to check, whether you are in a transaction
    }
}

Now you have a class which implements the Database, the interface just became useful.

2. Using the abstraction layer

Somewhere in your application, you have a method, let's call the method SecretMethod, just for fun, and inside this method you have to use the database, because you want to fetch some data.

Now you have an interface, which you cannot create directly (uh, how do I use it then), but you have a class MyMySQLDatabase, which may be constructed using the new keyword.

GREAT! I want to use a database, so I will use the MyMySQLDatabase.

Your method might look like this:

public void SecretMethod()
{
    var database = new MyMySQLDatabase(new CSharpMySQLDriver());

    // you will use the database here, which has the DoQuery,
    // BeginTransaction, RollbackTransaction and CommitTransaction methods
}

This is not good. You are directly creating a class inside this method, and if you are doing it inside the SecretMethod, it is safe to assume you would be doing the same in 30 other methods. If you wanted to change the MyMySQLDatabase to a different class, such as MyPostgreSQLDatabase, you would have to change it in all your 30 methods.

Another problem is, if the creation of MyMySQLDatabase failed, the method would never finish and therefore would be invalid.

We start by refactoring the creation of the MyMySQLDatabase by passing it as a parameter to the method (this is called dependency injection).

public void SecretMethod(MyMySQLDatabase database)
{
    // use the database here
}

This solves you the problem, that the MyMySQLDatabase object could never be created. Because the SecretMethod expects a valid MyMySQLDatabase object, if something happened and the object would never be passed to it, the method would never run. And that is totally fine.


In some applications this might be enough. You may be satisfied, but let's refactor it to be even better.

The purpose of another refactoring

You can see, right now the SecretMethod uses a MyMySQLDatabase object. Let's assume you moved from MySQL to MSSQL. You do not really feel like changing all the logic inside your SecretMethod, a method which calls a BeginTransaction and CommitTransaction methods on the database variable passed as a parameter, so you create a new class MyMSSQLDatabase, which will also have the BeginTransaction and CommitTransaction methods.

Then you go ahead and change the declaration of SecretMethod to the following.

public void SecretMethod(MyMSSQLDatabase database)
{
    // use the database here
}

And because the classes MyMSSQLDatabase and MyMySQLDatabase have the same methods, you do not need to change anything else and it will still work.

Oh wait!

You have a Database interface, which the MyMySQLDatabase implements, you also have the MyMSSQLDatabase class, which has exactly the same methods as MyMySQLDatabase, perhaps the MSSQL driver could also implement the Database interface, so you add it to the definition.

public class MyMSSQLDatabase : Database { }

But what if I, in the future, don't want to use the MyMSSQLDatabase anymore, because I switched to PostgreSQL? I would have to, again, replace the definition of the SecretMethod?

Yes, you would. And that does not sound right. Right now we know, that MyMSSQLDatabase and MyMySQLDatabase have the same methods and both implement the Database interface. So you refactor the SecretMethod to look like this.

public void SecretMethod(Database database)
{
    // use the database here
}

Notice, how the SecretMethod no longer knows, whether you are using MySQL, MSSQL or PotgreSQL. It knows it uses a database, but does not care about the specific implementation.

Now if you wanted to create your new database driver, for PostgreSQL for example, you won't need to change the SecretMethod at all. You will make a MyPostgreSQLDatabase, make it implement the Database interface and once you are done coding the PostgreSQL driver and it works, you will create its instance and inject it into the SecretMethod.

3. Obtaining the desired implementation of Database

You still have to decide, before calling the SecretMethod, which implementation of the Database interface you want (whether it is MySQL, MSSQL or PostgreSQL). For this, you can use the factory design pattern.

public class DatabaseFactory
{
    private Config _config;

    public DatabaseFactory(Config config)
    {
        _config = config;
    }

    public Database getDatabase()
    {
        var databaseType = _config.GetDatabaseType();

        Database database = null;

        switch (databaseType)
        {
        case DatabaseEnum.MySQL:
            database = new MyMySQLDatabase(new CSharpMySQLDriver());
            break;
        case DatabaseEnum.MSSQL:
            database = new MyMSSQLDatabase(new CSharpMSSQLDriver());
            break;
        case DatabaseEnum.PostgreSQL:
            database = new MyPostgreSQLDatabase(new CSharpPostgreSQLDriver());
            break;
        default:
            throw new DatabaseDriverNotImplementedException();
            break;
        }

        return database;
    }
}

The factory, as you can see, knows which database type to use from a config file (again, the Config class may be your own implementation).

Ideally, you will have the DatabaseFactory inside your dependency injection container. Your process then may look like this.

public class ProcessWhichCallsTheSecretMethod
{
    private DIContainer _di;
    private ClassWithSecretMethod _secret;

    public ProcessWhichCallsTheSecretMethod(DIContainer di, ClassWithSecretMethod secret)
    {
        _di = di;
        _secret = secret;
    }

    public void TheProcessMethod()
    {
        Database database = _di.Factories.DatabaseFactory.getDatabase();
        _secret.SecretMethod(database);
    }
}

Look, how nowhere in the process you are creating a specific database type. Not only that, you aren't creating anything at all. You are calling a GetDatabase method on the DatabaseFactory object stored inside your dependency injection container (the _di variable), a method, which will return you the correct instance of Database interface, based on your configuration.

If, after 3 weeks of using PostgreSQL, you want to go back to MySQL, you open a single configuration file and change the value of DatabaseDriver field from DatabaseEnum.PostgreSQL to DatabaseEnum.MySQL. And you are done. Suddenly the rest of your application correctly uses the MySQL again, by changing one single line.


If you are still not amazed, I recommend you to dive a bit more into IoC. How you can make certain decisions not from a config, but from a user input. This aproach is called the strategy pattern and although can be and is used in enterprise applications, it is much more frequently used when developing computer games.

  • Love your answer, David. But like all such answers, it falls short of describing how one might put it into practice. The real problem isn't abstracting away the ability to call into different database engines, the problem is the actual SQL syntax. Take your DbQuery object, for instance. Assuming that object contained a member for an SQL query string to be executed, how might one make that generic? – DonBoitnott Feb 23 '16 at 18:31
  • 1
    @DonBoitnott I don't think you would ever need everything to be generic. You usually want to introduce abstraction between application layers (domain, services, persitence), you may also want to introduce abstraction for modules, you may wanna introduce abstraction to a small but reusable and highly customizable library you are developing for a larger project, etc. You could just abstract everything to interfaces, but that is rarely necessary. It is really hard to give an one-for-everything answer, because, sadly, there really is not one and it comes from requirements. – Andy Feb 23 '16 at 18:50
  • 2
    Understood. But I really meant that literally. Once you have your abstracted class, and you get to the point where you want to call _secret.SecretMethod(database); how does one reconcile all of that work with the fact that now my SecretMethod still has to know what DB I am working with in order to use the proper SQL dialect? You've worked very hard to keep the majority of the code ignorant of that fact, but then at the 11th hour, you again must know. I am in this situation now and trying to figure out how other have solved this problem. – DonBoitnott Feb 23 '16 at 19:07
  • @DonBoitnott I didn't know what you meant, I see it now. You could use an interface instead of a concrete implementations of the DbQuery class, provide implementations of said interface and use that one instead, having a factory to build the IDbQuery instance. I do not think you would need a generic type for the DatabaseResult class, you can always expect results from a database to be formatted in a similar manner. The thing here is, when dealing with databases and raw SQL, you are already on such a low level on your application (behind DAL and Repositories), that there is no need for... – Andy Feb 24 '16 at 8:16
  • ...generic approach anymore. – Andy Feb 24 '16 at 8:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.