The poor man’s DB proxy

In a previous post, we walked through the steps to setup MySQL replication (inside a Docker Swarm). In this post, we'll look at a simple way to use these databases in a fault tolerant way, without a database proxy service. If you're not familiar with database proxies, they are services that sit between database clients and database servers.

A database proxy provides different features, but its main responsibility is connecting to the client to the proper database instance. A proxy can do so by taking the client needs into account, monitoring database health and traffic load, etc. However, one downside with traditional database proxy services is that there's yet another service to deploy and manage.

I wanted my application code to have a simple way to connect to different database instances, without the need for a separate proxy service. Enters the poor man's DB proxy…

High level APIs

The design is simple:

1. I wanted a library that provides an some connect() API, where it would connect to one of the available database instances.
2. Well, connect() alone isn't enough. Sometimes, the application code specifically wants to connect to the primary server in order to perform writes. Other times, the use case is read only and doesn't care if the data replication has not caught up to the primary instance yet.
3. And in order for the implementation to have a list of databases to which it could connect, we'll also need some sort of registration API.

With that, so far we have the following:

enum DatabaseRole {
    Primary,
    
    Replica,    
    
    // Used when the client does not care if the connection
    // is to primary or replica. It just wants a connection
    Any,
}

class DBInfo {
    String connectionString;
    DatabaseRole role;
    bool isWorking;
}

class DatabaseProxy {
    // Only Primary and Replica are acceptable roles during registration
    function register(DatabaseRole role, String connectionString);

    function connect(DatabaseRole role): (DatabaseConnection | Error);
}

The connect(role) API is the meat of our proxy library. My plan for implementation is simple:

1. When the caller asks for a connection, we'll pick one of the connection strings with an applicable role, make the connection, then return it. We could pick one at random, or round robin.
2. What happens if the connection fails? We just remove it from the list of available connections. At this point we have choices:
   1. Try other connection string until we can get a successful connection
   2. Return an error back to the client. I'm choosing this route to simplify the implementation of the API, and also let the client dictate retries instead. If the client calls connect(...) again, the “bad” connection will have been removed and we'd be trying a different connection string.
3. At this stage, we have one potential problem. What if all connection strings temporarily went bad? It could be an issue on the client side that prevented any DB connection. If we simply just remove the bad connection strings, after a while, there won't be anything left. That means we'll need to periodically “reset” the list of working connection strings to what were registered. After each reset, then the client will have the full list of connection strings to attempt.

With that in mind, our DatabaseProxy class has a few more elements:

class DatabaseProxy {
    // Stores the list of connection strings and roles came in through the `register()` API
    List<DBInfo> databases;

    // How often we should reset the working indices
    int resetFrequency;

    // The last time the working indices were reset back to the full list
    int lastReset = 0;

    // Reset the working indices
    function reset();

    function register(...);
    function connect(...);
}

Sample implementations

Below are sample implementations for each function:

function reset() {
    for (i = 0; i < this.databases.length(); i++) {
        this.databases[i].isWorking = true;
    }
}

function register(DatabaseRole role, String connectionString) {
    if (role == DatabaseRole.Any) {
        halt("Invalid database role during registration")
    }

    this.connectionStrings.add(new DBInfo {
        connectionString,
        role,
        true, // isWorking
    });
    this.reset();
}

function connect(DatabaseRole role) {
    lock(this); // thread safety

    if (this.lastReset + this.resetFrequency < now()) {
        this.reset();
        this.lastReset = now;
    }

    // Build a list of potential choices base on the requested role
    choices = new List<int>(capacity = this.databases.length());
    for (i = 0; i < this.databases.length(); i++) {
        if (!this.databases[i].isWorking) {
            continue;
        }
        
        if (role == Any) {
            choices.add(index);
        } else if (role == this.databases[i].role) {
            choices.add(index);
        }  
    }

    if (choices.isEmpty()) {
        unlock(this);
        return new Error("No available working connection.");
    }

    chosenIndex = choices.random();
    db = this.databases[chosenIndex]
    connection = ConnectDatabase(db.connectionString)
    
    if (connection is Error) {
        this.databases[chosenIndex].isWorking = false;
        unlock(this)
        return connection as Error;
    }

    unlock(this);
    return connection;
}

Further enhancements

• With the current implementation, if one of the database instances is down, it would take a client receiving an error for it to removed out of the working indices. We could further enhance this by having spinning up a thread that periodically checks for the connectivity of the registered databases.
• Server proximity preference: support providing a “preferred” server during client registration, base on the proximity of the application server to the database server.