Implementing Database Connection Pooling

Question

I am re-implementing a web-application, partially as a learning exercise to learn nim as well as multi-threaded programming. As part of that learning exercise, I want to implement connection pooling as there is no library or package in nim that I am aware of that implements it for me, while also allowing me to use the ORM of my choosing. The database I am connecting to is sqlite.

As such, I wrote what I think is correct code: I made a global object POOL of type ConnectionPool that is merely a sequence of connections to the sqlite datbase and that also has a lock. Every connection has a clearly defined lifetime after which it gets destroyed, and new connections get made if a connection is needed but none is available. You can get a connection by using borrowConnection, and return it by using recycleConnection. Both of these procs lock the POOL object to retrieve a connection or put said connection back.

import ../applicationSettings 
import constructor/defaults
import std/[times, locks, db_sqlite]


proc createRawDatabaseConnection(): DbConn =
    return open(applicationSettings.database, "", "", "")


type PoolConnection* {.defaults.} = object
  connection*: DbConn = createRawDatabaseConnection()
  deathTime: DateTime = now() + initTimeInterval(days = 1)
implDefaults(PoolConnection)


type ConnectionPool* = object
  connections: seq[PoolConnection]
  lock: Lock


var POOL {.global.}: ConnectionPool
proc isEmptyPool(): bool = POOL.connections.len() == 0


proc initConnectionPool*(initialPoolSize: static int) = 
  POOL.connections = @[]
  initLock(POOL.lock)

  withLock POOL.lock:
    for i in 1..initialPoolSize:
      POOL.connections.add(initPoolConnection())


proc borrowConnection*(): PoolConnection {.gcsafe.} =
  {.cast(gcsafe).}:
    withLock POOL.lock:
      if isEmptyPool():
        return initPoolConnection()
      
      result = POOL.connections.pop()


proc recycleConnection*(connection: sink PoolConnection) {.gcsafe.} =
  if connection.deathTime < now():
    return
  
  {.cast(gcsafe).}:
    withLock POOL.lock:
      POOL.connections.add(connection)


proc destroyConnectionPool*() =
  deinitLock(POOL.lock)

Are there any glaring issues with the code above? I think this doesn't copy memory around wildly, but I could be wrong there, so please tell me if I am.

Answer 1

Leorize from the nim discord server made a valid suggestion. The above has several issues:

1. You make a lot of connection-creations and destructions that serve no purpose by killing a connection after a set amount of time, whether it's needed or not
2. Use monoTime instead of Time objects, its less wasted memory and you're working with simpler objects that capture durations better

A design that you can follow instead is giving the pool a fixed limit of connections that it can contain. Then also give it a "burstMode" that allows it to grow beyond that limit, but only as long as its "burstModeTimer" allows for it. That burstModeTimer is fixed to 30 minutes (set that to whatever you want) and extended every time you borrow a connection while you borrow a connection and the pool isn't overflowing. That is this way, since "burst mode" ads an entire batch of connections, which means they're only unnecessary if the pool is overflowing under normal load.

While the pool is in burst mode, it will accept any connection it can get from the recycle proc. Once burst mode is over, any connection returned while it is full will just be closed and garbage collected.

import ../applicationSettings 
import std/[times, monotimes, locks, db_sqlite]


proc createRawDatabaseConnection(): DbConn =
    return open(applicationSettings.database, "", "", "")


type ConnectionPool = object
  connections: seq[DbConn]
  lock: Lock
  defaultPoolSize: int
  burstEndTime: MonoTime
  isInBurstMode: bool

var POOL {.global.}: ConnectionPool
proc isPoolEmpty(): bool = POOL.connections.len() == 0
proc isPoolFull(): bool = POOL.connections.len() >= CONNECTION_POOL_SIZE

proc refillPoolConnections() =
  withLock POOL.lock:
    for i in 1..POOL.defaultPoolSize:
      POOL.connections.add(createRawDatabaseConnection())


proc initConnectionPool*() = 
  POOL.connections = @[]
  POOL.isInBurstMode = false
  POOL.burstEndTime = getMonoTime()
  POOL.defaultPoolSize = CONNECTION_POOL_SIZE
  initLock(POOL.lock)
  refillPoolConnections()


proc activateBurstMode() =
  POOL.isInBurstMode = true
  POOL.burstEndTime = getMonoTime() + initDuration(minutes = 30)
  refillPoolConnections()

proc updatePoolBurstModeState() =
  if not POOL.isInBurstMode:
    return

  if getMonoTime() > POOL.burstEndTime:
    POOL.isInBurstMode = false


proc extendBurstModeLifetime() =
  if POOL.isInBurstMode == false:
    raise newException(DbError, "Tried to extend pool lifetime while Pool wasn't in burst mode, there's a logic issue")

  let hasMaxLifetimeDuration: bool = POOL.burstEndTime - getMonoTime() > initDuration(minutes = 30)
  if hasMaxLifetimeDuration:
    return

  POOL.burstEndTime = POOL.burstEndTime + initDuration(seconds = 5)


proc borrowConnection(): DbConn {.gcsafe.} =
  {.cast(gcsafe).}:
    withLock POOL.lock:
      if isPoolEmpty():
        activateBurstMode()

      elif not isPoolFull() and POOL.isInBurstMode: 
        extendBurstModeLifetime()
        
      result = POOL.connections.pop()
      echo "After Borrow: POOL size: " & $POOL.connections.len()


proc recycleConnection(connection: DbConn) {.gcsafe.} =  
  {.cast(gcsafe).}:
    withLock POOL.lock:
      updatePoolBurstModeState()

      if isPoolFull() and not POOL.isInBurstMode:
        connection.close()
      else:
        POOL.connections.add(connection)

      echo "After Recycle: POOL size: " & $POOL.connections.len()


proc destroyConnectionPool*() =
  deinitLock(POOL.lock)


template withDbConn*(connection: untyped, body: untyped) =
  #Borrows a database connection, executes the body and then recycles the connection
  block: #ensures connection exists only within the scope of this block
    let connection: DbConn = borrowConnection()
    try:
      body
    finally:
      recycleConnection(connection)

Small usage example:

    withDbConn(connection): #connection is of type DbConn, which is a connection to an sqlite3 db
        connection.select(entries, "campaign_id.name = ?", campaignName)

The template is very convenient and ideally your only public part of the module since it automatically fetches and recycles your connection for you.