swift3perfect

Is there a db connection pool for swift in perfect?


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


Solution

  • 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)
    }