Set up a separate connection pool for reader and writer DB access | SpringBoot JOOQ

🔍 Disclaimer:

The following is based on my experience and knowledge, and may not represent the best approach or most up-to-date information. Please feel free to share your insights and correct any outdated information.

This is an open dialogue; let's learn and improve together. 🚀

You might be using DB in your corporate application and may need a separate connection pool for the reader and writer DB connection.

One of the advantages of using this is that you can measure the no of requests your reader and writer handle.

Note: Problem with existing migration

In the initial setup, you might enter a problem where you will see your reader and writer connection are going from the connection. So, what's the cause!!

The cause is simple - existing queries, you need to check if your existing queries follow some pattern to not get into this problem. This happens when you write in db and then return, this causes that reader connection to connect to the writer, make sure to make your reader and writer queries go through separate queries.

Setting up in SpringBoot a different pool connection for JOOQ

To set up this make two different instances of DSlContxt, one for select queries and one for writing queries like insert, and update.

import org.jooq.DSLContext

class DSLContextResolver(
    private val reader: DSLContext,
    private val writer: DSLContext
) {
    fun getReaderContext(): DSLContext = reader

    fun getWriterContext(): DSLContext = writer
}

Instead of using direct DSLContext we will be using our custom DSLContextResolver class. For ex:-

private val readerContext = dslContextResolver.getReaderContext()
private val writerContext = dslContextResolver.getWriterContext()

    fun getBookById(id: Long): Book {
        return readerContext.selectFrom(Tables.BOOK)
            .where(Tables.BOOK.ID.eq(id))
            .fetchOne()

    fun addNewBook(book: Book): Book {
        return writerContext.insertInto(Tables.BOOK)
            .set(
                Book(
                    name = book.name,
                    author = book.author,
                    pages = book.pages,
                    ratings = book.ratings,
                )
                    .toBookRecord
            )
            .returning()
            .fetchOne()!!
            .toBook
    }
}

So this is how we can achieve our target. and as earlier I discussed the problem, we can solve this by using returning, if you want to read after inserting or updating. Or, you can use your reader context separately.