Proper way of implementing the DB queries to read and write MySQL replicas in the node.js project?

Question

I have a node.js express project that uses MySQL 5.6 database and mysql client to connect to it. There is no ORM in the project, the raw queries are run against the DB. Now, I need to change the code in the way that SELECT queries (that are not within the transaction) would be routed to the MySQL read replica. Since I have never previously done anything similar on any other projects, I was wondering what are the best practices of doing it (in the node.js).

I have found that the sequelize library has the configuration option that allows to specify read and write replicas separately and then different queries would be routed to the different databases, but I haven't found any options of doing it in the mysql client library. I tried using sequelize, but it seems that for RAW queries this feature is not supported.

Apart from that, I am wondering what would be the best solution from the architectural standpoint? Currently, the only option that I see is having some database wrapper service that has query method, that, in turn, encapsulates the mysql connection.query and based on the sql provided to it decides which database connection to use - read or write (unless it's a transaction - then everything should be done in the same DB, I guess). It's a rather dumb solution, but it's my only idea now.

Could anyone please share some experience on this? Not only in node.js projects - any architectural ideas are welcome. Maybe there is some way of proxying the requests before it gets to the host? Thanks for any answer in advance.

Answer 1

So, eventually, I have solved it in a rather straightforward way: created separate connection pools for read and write DB replicas and used each of them directly in places, that I needed:

const writePool = mysql.createPool(writeConnectionString);
const readPool = mysql.createPool(readConnectionString);

function getWriteConnection() {
    return writePool.getConnection();
};

async function getReadConnection() {
    const connection = await readPool.getConnection();
    connection.isRead = true;
    const originalQuery = connection.query;
    connection.query = function (...args) {
        if(!isSelectQuery(args[0])) {
            throw new Error('Read connection cannot be used for the commands other than SELECT.');
        }

        return originalQuery.apply(this, args);
    };

    return connection;
};

// checking if query is SELECT
function isSelectQuery(query) {
    return query.replace(/\s+/mi, '').toLowerCase().startsWith('select');
}

// release function
function release(connection) {
    if(connection.isRead) {
        readPool.releaseConnection(connection)
    } else {
        writePool.releaseConnection(connection);
    }
};

Then in code:

const connection = await database.getReadConnection();
await connection.query('SELECT * FROM ...');
....
database.release(connection);

This is far not the most elegant way to solve it, but it relatively quickly solved my problem with minimal changes and subsequent bugs.