Example: Global database connection pool

[Avey777]

module main

import db.mysql

struct ConnectionPool {
mut:
	pool   chan mysql.DB
	config mysql.Config
}

__global g_pool ConnectionPool

//  Initialize connection pool (called at program startup) | 初始化连接池（程序启动时调用）
pub fn init_pool(config mysql.Config, pool_size int) {
		mut g := &g_pool
		g.config = config
		g.pool = chan mysql.DB{cap: pool_size}

		for _ in 0 .. pool_size {
			g.pool <- mysql.connect(config) or { panic(err) }
		}
}

// Get connection (blocking) | 获取连接（阻塞式）
pub fn acquire() mysql.DB {
	return <-g_pool.pool
}

// Release connection | 释放连接
pub fn release(conn mysql.DB) {
	g_pool.pool <- conn
}

// close closes all connections in the pool.
pub fn (mut g g_pool) close() {
	for _ in 0 .. g.connections.len {
		mut conn := <-g.connections or { break }
		conn.close() or { panic('Failed to close connection: ${err}') }
	}
}

fn main() {

	config := mysql.Config{
		host:     'mysql2.sqlpub.com'
		port:     3307
		username: 'vcore_test'
		password: 'wfo8wS7CylT0qIMg'
		dbname:   'vcore_test'
	}

	// Initialize connection pool (5 connections) | 初始化连接池（5个连接）
	init_pool(config, 5)

	// Get Connection | 获取连接
	mut conn := acquire()
	defer { release(conn) }

	// EXECUTE |  执行查询
	rows := conn.exec('SELECT * FROM sys_users limit 1')!
	dump(rows)
}

v -enable-globals run main.v

Is this database connection pool implemented in a more meaningful way?

[jorgeluismireles]

vlib/db/sqlite/pool.v is similar (I didn't tested yet). It's acquire() there is more robust returning a result after the channel would be already closed and also there is a method to close the pool.

[Avey777]

vlib/db/sqlite/pool.v is similar (I didn't tested yet). It's there is more robust returning a result after the channel would be already closed and also there is a method to close the pool.acquire()

This cannot be used as a global connection pool, and its significance is not significant. The real connection pool is to provide services for the entire project

[Avey777]

pub_pool_db.v

module dbpool

import db.mysql
import db.pg

type DBconfig = mysql.Config | pg.Config

struct ConnectionPoolGeneric[T] {
mut:
	connections chan T   // mysql.DB, pg.DB
	config      DBconfig // mysql.Config, pg.Config
}

pub fn new_conn_pool[T](conf DBconfig, size int) !&ConnectionPoolGeneric[T] {

	$if T is mysql.DB {
		mysql_conf := conf as mysql.Config
		mut pool := &ConnectionPoolGeneric[mysql.DB]{
			connections: chan mysql.DB{cap: size}
			config:      mysql_conf
		}
		for _ in 0 .. size {
			conn := mysql.connect(mysql_conf)!
			pool.connections <- conn
		}
		return pool
	} $else $if T is pg.DB {
		pg_conf := conf as pg.Config
		mut pool := &ConnectionPoolGeneric[pg.DB]{
			connections: chan pg.DB{cap: size}
			config:      pg_conf
		}
		for _ in 0 .. size {
			conn := pg.connect(pg_conf)!
			pool.connections <- conn
		}
		return pool
	}
	return error('')
}

// acquire gets a connection from the pool | 从池中获取连接
pub fn (mut pool ConnectionPoolGeneric[T]) acquire() !T {
	conn := <-pool.connections or { return error('Failed mysql') }
	return conn
}

// release returns a connection back to the pool. | 释放将连接返回到池中
pub fn (mut pool ConnectionPoolGeneric[T]) release(conn T) {
	pool.connections <- conn
}

// close closes all connections in the pool | 关闭池中的所有连接
pub fn (mut pool ConnectionPoolGeneric[T]) close() {
	for _ in 0 .. pool.connections.len {
		mut conn := <-pool.connections or { break }
		conn.close() or {panic}
	}
}

pub_pool_db_text.v

module dbpool

import db.mysql

const config = mysql.Config{
	host:     'mysql2.sqlpub.com'
	port:     3307
	username: 'vcore_test'
	password: 'wfo8wS7CylT0qIMg'
	dbname:   'vcore_test'
}

fn test_new_conn_pool() {
	mut pool := new_conn_pool[mysql.DB](config, 5) or {
		panic('Failed to create mysql pool: ${err}')
	}
	dump(pool)
	db := pool.acquire() or { panic(err) }
	dump(db)
	data := db.exec('select 1')!
	dump(data)
	assert data[0] == mysql.Row{
		vals: ['1']
	}
	pool.release(db)
	pool.close()
}

[Avey777]

app_stuct.v

module dbpool

import db.mysql

pub struct ConnectionPool {
pub mut:
	connections chan mysql.DB
	config      mysql.Config
}

g_pool.v

module dbpool

import db.mysql

// 全局实例（正确声明方式）
__global g_conns_pool ConnectionPool

// 初始化连接池（程序启动时调用）
pub fn init_pool(config mysql.Config, pool_size int) !string {
	mut g_conn := &g_conns_pool
	g_conn.connections = chan mysql.DB{cap: pool_size}
	g_conn.config = config

	for _ in 0 .. pool_size {
		g_conn.connections <- mysql.connect(config) or { return '' }
	}
	return ''
}

// 获取连接（阻塞式）
pub fn acquire() mysql.DB {
	return <-g_conns_pool.connections
}

// 释放连接
pub fn release(conn mysql.DB) {
	g_conns_pool.connections <- conn
}

g_pool_test.v

module dbpool

import db.mysql
// 使用示例

fn test_init_pool() {
	// 初始化配置
	config := mysql.Config{
		host:     'mysql2.sqlpub.com'
		port:     3307
		username: 'vcore_test'
		password: 'wfo8wS7CylT0qIMg'
		dbname:   'vcore_test'
	}

	// 初始化连接池（5个连接）
	init_pool(config, 10) or { '' }

	// 获取连接
	mut conn := acquire()
	defer { release(conn) }

	// 执行查询
	rows := conn.exec('SELECT  1')!
	dump(rows)
}