database: Add connection pool support（go：database/sql）

[Avey777]

Describe the feature

This issue proposes adding a connection pool mechanism to the database module to optimize database interactions.

Configurable pool parameters: pool_size, max_connections, idle_timeout
Thread-safe connection reuse with automatic recycling
Health checks (e.g., ping() validation for stale connections)
Extend Connection interface with release() and reset() methods
Add pool lifecycle management (start/stop during database initialization)

refer to :go:database/sql

Use Case

gorm

Proposed Solution

No response

Other Information

No response

Acknowledgements

• I may be able to implement this feature request
• This feature might incur a breaking change

Version used

v4.10.0

Environment details (OS name and version, etc.)

ALL

Note

You can use the 👍 reaction to increase the issue's priority for developers.

Please note that only the 👍 reaction to the issue itself counts as a vote.
Other reactions and those to comments will not be taken into account.

[huly-for-github]

Connected to Huly®: V_0.6-22388

[Avey777]

@medvednI think this and go language database / sql library, do a unified digital library interface, and provide database connection pool function!
This can be done with database / sql.

[Avey777]

@sp
Please do your own thing, and don't comment on any of my statements if you don't have anything to say.
You are not welcome, and the issue is often reopened without being resolved. When people say that the feature tag should not be added, they never think about whether it restricts the function.

[Avey777]

module main

import db.mysql
import db.pg

type DBconfig = mysql.Config | pg.Config

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

pub fn new_db_pool2[T](conf DBconfig, size int) !&ConnectionPool[T] {
	for _ in 0 .. size {
		if conf is mysql.Config {
			mysql_conf := conf as mysql.Config
			mut pool_mysql := &ConnectionPool[mysql.DB]{
				connections: chan mysql.DB{cap: size}
				config:      mysql_conf
			}
			for _ in 0 .. size {
				conn := mysql.connect(mysql_conf)!
				pool_mysql.connections <- conn
			}
			return pool_mysql
			//>>>>>>>>>>v error >>>>>>>>>>>>
			// } else if conf is pg.Config {
			// 	pg_conf := conf as pg.Config
			// 	mut pool_pg := &ConnectionPool[pg.DB]{
			// 		connections: chan pg.DB{cap: size}
			// 		config:      pg_conf
			// 	}
			// 	for _ in 0 .. size {
			// 		conn := pg.connect(pg_conf)!
			// 		pool_pg.connections <- conn
			// 	}
			// 	return pool_pg
			//<<<<<<<<<<<v error <<<<<<<<<<<<
		}
	}

	return error('Unsupported databases')
}

// acquire gets a connection from the pool | 从池中获取连接
pub fn (mut pool ConnectionPool[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 ConnectionPool[T]) release(conn T) {
	pool.connections <- conn
}

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

fn main() {
	mut pool := new_db_pool2[mysql.DB](mysql.Config{
		host:     '127.0.0.1'
		port:     3306
		username: 'root'
		password: 'mysql_123456'
		dbname:   'vcore'
	}, 5) or { panic('Failed to create mysql pool: ${err}') }
	dump(pool)
	//>>>>>>>>>>>>>>>>
	db := pool.acquire() or { panic(err) }
	dump(db)
	a := db.exec('select 1')!
	dump(a)
	pool.release(db)
	pool.close()
}

Perhaps it would be helpful

[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) {
	unsafe {
		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
}


fn main() {

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

	// Initialize connection pool (5 connections)
	init_pool(config, 5)

	// Get Connection
	mut conn := acquire()
	defer { release(conn) }

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