Is there a db connection pool for swift in perfect?

Question

I am looking for a database connection pool wrapper for perfect (swift 3 or above). I couldn't find any through search so I decided to ask if someone could provide a hint.

Thanks in advance

Answer 1

I would rather say high efficient re-use connection other than a pool. Take Perfect-MySQL example:

import MySQL
import PerfectThread
#if os(Linux)
import Glibc
#else
import Darwin
#endif

let mysql = MySQL()
let lock = Threading.Lock()
var jobs = 10

func now(_ id: Int) {
  print("Job Now #", id)
  lock.doWithLock {
    let x = mysql.query(statement: "SELECT now() as time")
    guard x, let y = mysql.storeResults(),
    let row = y.next() else {
      print(mysql.errorMessage())
      return
    }
    print(row[0] ?? "Now() FAILED")
    y.close()
    jobs -= 1
  }
}

func user(_ id: Int) {
  print("Job Usr #", id)
  lock.doWithLock {
    let x = mysql.query(statement: "select User from user")
    guard x, let y = mysql.storeResults(),
      let row = y.next() else {
       print(mysql.errorMessage())
       return
   }
    print(row[0] ?? "User() FAILED")
    y.close()
    jobs -= 1
  }
}

_ = mysql.setOption(.MYSQL_SET_CHARSET_NAME, "utf8mb4")
guard mysql.connect(host: "127.0.0.1", user: "root", password: "your pass", db: "mysql") else {
  print(mysql.errorMessage())
  exit(0)
}

jobs = 10
for id in 0 ..< 5 {
  Threading.dispatch {
    now(id)
  }
  Threading.dispatch {
    user(id)
  }
}

while jobs > 0 {
  sleep(1)
}

By using Threading.Lock(), you can easily queue all queries into one connection and maximize the efficiency - fast, atomic, ordered, shared and singleton.

If insisted, you can create a limited number of connections to an array, and each connection keeps one lock to make a balance, let say, a threaded connection pool, so I believe this balanced solution can be established without any special module on Perfect itself:

struct MySQLPool {
  let mysql = MySQL()
  let lock = Threading.Lock()
 }
var pool: [MySQLPool] = []
for _ in 0 ..< 32 {
  pool.append(YourNewConnection)
}

Answer 2

I have implemented a simple pool mechanism based on the last part of @PerfectlyRock's answer, check it out:

import PerfectMySQL
import Foundation
import PerfectThread
#if os(Linux)
import Glibc
#else
import Darwin
#endif

class DBManager {

struct MySQLPoolItem {
    let mysql = MySQL()
    let lock = Threading.Lock()
    func connect()->Bool{
        return mysql.connect(host: Prefs.dbhost, user: Prefs.dbuser, password: Prefs.dbpass, db: Prefs.schema)
    }
}

var pool: [MySQLPoolItem] = []

static let shared = DBManager()

func preparePool() {

    var connectionCount = Prefs.connectionPoolCount

    while connectionCount > 0 {

        let item = MySQLPoolItem()
        _ = item.mysql.setOption(.MYSQL_SET_CHARSET_NAME, "utf8")
        let connected = item.connect()
        guard connected else {
            // verify we connected successfully
            print(item.mysql.errorMessage())
            return
        }
        print("Database connection \((connectionCount)) success");
        pool.append(item)
        connectionCount -= 1
    }

}

func getAvailableConnection() -> MySQLPoolItem {

    var item : MySQLPoolItem? = nil;

    while item == nil {
        item = tryAvailableConnection()
        if(item == nil){
            sleep(1)
        }
    }

    return item!
}

private func tryAvailableConnection() -> MySQLPoolItem? {

    for item in pool {

        if(item.lock.tryLock()){

            if(!item.mysql.ping()){
                if(item.connect()){
                    return item
                }
                item.lock.unlock()
            }else{
                return item
            }

        }

    }

    return nil

}

func runSync(query: String) -> ((result:Bool,items:MySQL.Results?)) {

    let poolItem = getAvailableConnection()

    defer {
        poolItem.lock.unlock()
    }

    let querySuccess = poolItem.mysql.query(statement: query)

    return (querySuccess,poolItem.mysql.storeResults())

}

}