[Feature Request]Shared connection pool on top of the existing db.Driver abstraction

Describe the feature

Add a shared, driver-agnostic connection pool to vlib/db/ that sits on top of the existing db.Driver interface. Go's database/sql design separates concerns as:

sql.DB (pooled handle) → driver.Conn (single raw connection)

V currently only has the second half (db.Driver / db.open() → single connection), with no shared pooling layer. Each backend ends up reinventing its own pool with wildly different quality:

• pg has a mature pool (mutex, LIFO idle stack, waiters, lifetime mgmt, stats)
• mysql/sqlite have trivial chan-based pools (fixed size, no health checks, no lifetime)

The pool logic from vlib/db/pg/pool.v should be lifted into vlib/db/ so all backends benefit equally.

Use Case

Writing a production MySQL service using db.open() — the &Driver returned is a single connection, not safe for concurrent use. To get pooling I have to drop down to mysql.ConnectionPool, which is a fixed-size channel that pre-opens every connection at construction, has no way to expire/replace stale connections, no health checking, and no dynamic reconfiguration. The only proper pool lives in the pg module and isn't accessible from MySQL.

Same problem if I'm a third-party driver author: there's no shared pool to reuse, I have to write my own from scratch.

Comparison with Go's database/sql

This section maps each major concept in Go's database/sql to V's current state and the proposed target, to give the refactoring a clear direction.

Layer architecture

Pool semantics

API surface comparison

Transaction handling

Prepared statements

Result streaming and scanning

Context support

Driver registration

Proposed Solution

Layer on top of the existing db.Driver

The db.Driver interface already exists and works. The missing piece is a pooling wrapper — a db.DB type that holds a pool of connections and transparently acquires/releases them per call.

The pool returns a lightweight wrapper (Conn) rather than a bare &Driver, so that stale references held by user code after release() can't accidentally reach the underlying driver handle. The Conn also provides delegate methods (with nil-guards) so callers don't need to touch the raw &Driver:

// vlib/db/pool.v — extracted from vlib/db/pg/pool.v, made driver-agnostic

// Conn is a pooled connection handle. After release(), the inner driver
// pointer is detached — any subsequent method call on the same &Conn
// returns an error instead of corrupting the pool.
@[heap]
pub struct Conn {
mut:
    driver     &Driver   = unsafe { nil }
    pool       &Pool     = unsafe { nil }
    created_at time.Time
    bad        bool
}

// ensure_active prevents use of a released connection.
fn (c &Conn) ensure_active() ! {
    if isnil(c.driver) {
        return error('db: connection is released')
    }
}

// Delegate methods. Each checks ensure_active first, then forwards to c.driver.
// This mirrors pg's Conn.ensure_active() pattern and makes the nil-guard automatic.
pub fn (mut c Conn) exec(query string) ![]DriverRow {
    c.ensure_active()!
    return c.driver.exec(query)
}

pub fn (mut c Conn) exec_one(query string) !DriverRow {
    c.ensure_active()!
    return c.driver.exec_one(query)
}

pub fn (mut c Conn) exec_param_many(query string, params []string) ![]DriverRow {
    c.ensure_active()!
    return c.driver.exec_param_many(query, params)
}

pub fn (mut c Conn) validate() !bool {
    c.ensure_active()!
    return c.driver.validate()
}

pub fn (mut c Conn) close() ! {
    if isnil(c.pool) {
        unsafe { c.driver.close() }  // standalone conn, no pool
        return
    }
    c.pool.release(c)
}

// Pool manages a set of idle connections and hands them out on acquire.
// It never exposes Driver directly — callers get &Conn wrappers.
pub struct Pool {
    factory DriverFactory
    // ... idle slots, max_open, max_idle, max_lifetime, waiters, etc
}

pub fn new_pool(factory DriverFactory, cfg PoolConfig) &Pool { ... }

// acquire returns a &Conn. It can fail when:
//   - the pool is closed
//   - DriverFactory.connect() fails (e.g. network error, auth failure)
//   - the waiter channel is closed (pool closed while waiting)
pub fn (mut p Pool) acquire() !&Conn { ... }
pub fn (mut p Pool) release(conn &Conn) { ... }
pub fn (mut p Pool) close() { ... }
pub fn (mut p Pool) set_max_open(n int) { ... }
pub fn (mut p Pool) set_max_idle(n int) { ... }
pub fn (mut p Pool) set_max_lifetime(d time.Duration) { ... }
pub fn (mut p Pool) stats() PoolStats { ... }

// PoolConfig — shared config for all backends
pub struct PoolConfig {
    max_open_conns    int           // 0 = unlimited
    max_idle_conns    int = 2       // 0 = keep no idle conns
    conn_max_lifetime time.Duration // 0 = unlimited
}

pub struct PoolStats {
    max_open_connections int
    open_connections     int
    in_use               int
    idle                 int
    wait_count           int
}

// vlib/db/db.v — new pooled DB handle

// DB is a thread-safe, pooled handle to any database.
// It only holds a reference to a Pool — the factory is inside the pool.
@[heap]
pub struct DB {
mut:
    pool &Pool = unsafe { nil }
}

// Methods acquire a Conn, call its delegate, then release:
pub fn (mut db DB) exec(query string) ![]DriverRow {
    mut c := db.pool.acquire()!
    defer { db.pool.release(c) }
    return c.exec(query)
}

pub fn (mut db DB) exec_one(query string) !DriverRow {...}
pub fn (mut db DB) exec_param_many(query string, params []string) ![]DriverRow {...}
pub fn (mut db DB) begin() !&Tx {...}
pub fn (mut db DB) close() ! { db.pool.close() }
pub fn (mut db DB) ping() ! {...}

// Future (Phase 6): streamed rows
// pub fn (mut db DB) query(query string) !&Rows { ... }

// Pool configuration — delegates to pool
pub fn (mut db DB) set_max_open_conns(n int) { db.pool.set_max_open(n) }
pub fn (mut db DB) set_max_idle_conns(n int) { db.pool.set_max_idle(n) }
pub fn (mut db DB) set_conn_max_lifetime(d time.Duration) { db.pool.set_max_lifetime(d) }
pub fn (mut db DB) stats() PoolStats { return db.pool.stats() }

Backend example: MySQL implements DriverFactory

// vlib/db/mysql/factory.c.v — new file, or inline in driver.v's $if db_mysql block

pub struct MysqlFactory {
    config mysql.Config
}

pub fn (f MysqlFactory) connect() !&Driver {
    conn := mysql.connect(f.config)!
    return &MysqlDriver{ conn: conn }
}

Then db.open_pooled() creates the pool with the right factory:

fn open_mysql_pooled(config DriverConfig) !&DB {
    mysql_config := mysql.Config{ ... }
    factory := MysqlFactory{ config: mysql_config }
    pool := new_pool(factory, PoolConfig{ ... })
    return &DB{ pool: pool }
}

Note: DriverFactory only requires connect() for now. If a factory holds resources (e.g. cached TLS config), a future close() method can be added to the interface — backward compatibly, since factory is always held by the pool.

Pool API naming

The shared pool exposes three public methods — acquire, release, close:

// acquire 从池中取一条连接。由 DriverFactory.connect() 延迟创建。
pub fn (mut p Pool) acquire() !&Conn { ... }

// release 归还连接。内部断开 &Conn.driver 防止二次使用，
// 然后自动调 driver.reset() 清理 session 状态。
// reset 成功则放回空闲池；reset 失败则关闭丢弃。
pub fn (mut p Pool) release(conn &Conn) { ... }

// close 关闭池中所有空闲连接。已借出的连接归还时直接关闭。
pub fn (mut p Pool) close() { ... }

The relationship with Go's naming:

• Go's pool is almost invisible to the user — sql.DB.Exec/Query/Begin handle acquire/release internally. Only db.Conn(ctx) + .Close() surfaces it for pinning
• V's ORM has sql db { ... } blocks that need explicit connection pinning, so acquire / release as public methods make sense
• release is clearer than Go's .Close(): .Close() is ambiguous (close the connection or return to pool?), while release explicitly means "give it back"
• close shuts down the entire pool — same as Go

Release lifecycle (reset + return)

Current pg pool in release() only checks bad/expired and either discards or parks the idle slot — it never calls reset(). With the shared pool, the release flow becomes:

release(conn):
    driver = conn.driver
    conn.driver = nil                 // detach — stale refs now inert
    conn.pool  = nil

    driver.reset() or {
        driver.close()                 // reset 失败 → 关闭丢弃
        open_count--
        return
    }
    park_as_idle(driver)

This mirrors how Go's driver.SessionResetter.ResetSession(ctx) works: the driver gets a chance to clean up session-scoped state before the connection goes back to the idle pool. Each backend implements reset() appropriately: mysql_reset_connection() for MySQL, DISCARD ALL for PostgreSQL, no-op for SQLite.

The detach-first design means that even if user code holds a stale &Conn after release(), any subsequent method call on it will hit ensure_active() → conn.driver == nil → error, never reaching the driver handle that may now be serving another goroutine.

The pool itself lives once, in vlib/db/pool.v

The pg pool is already well-designed (~350 lines). Extracting it requires minimal changes:

db.open() stays unchanged, migration path

Existing code using db.open() → &Driver continues to work. New code opts into pooling via db.open_pooled():

This mirrors Go's sql.Open (pooled by default) vs driver.Open/sql.Conn (raw).

ORM integration

The ORM's Connection interface currently requires select/insert/update/delete/last_id/execute directly on each backend. A pooled DB implementing Connection would let the ORM work transparently with any backend through one type.

Current state at a glance

Other Information

Backward compatibility

• db.open() continues to return &Driver — no breakage
• db.open_pooled() is the new entry point returning &DB
• The existing pg.DB, pg.Pool, mysql.ConnectionPool, sqlite.ConnectionPool remain untouched initially
• ORM backends (pg.orm.v, mysql.orm.c.v, sqlite.orm.v) keep their current impls; a future step can add a generic ORM layer on top of db.DB

Key files

• vlib/db/driver.v — existing Driver interface + open(); add DriverFactory + open_pooled() here
• vlib/db/pg/pool.v — mature pool to extract as vlib/db/pool.v
• vlib/db/pg/db.v — pg's DB pool wrapper (reference for the shared one)
• vlib/db/pg/tx.v — pg's Tx (reference for shared Tx)
• vlib/db/mysql/pool.v — primitive channel pool (to be removed)
• vlib/db/sqlite/pool.v — same
• vlib/orm/orm.v — Connection / TransactionalConnection interfaces

Phased approach

Future direction

The end state is one shared db.Pool + db.DB that all backends use, backed by a per-backend DriverFactory. After a deprecation period of at least 2 releases, pg.Pool, mysql.ConnectionPool, and sqlite.ConnectionPool should be removed — they become dead code. Third-party driver authors only implement DriverFactory and get the full pooling stack for free.

Longer term, the entry point should flip: db.open() returns a pooled &DB by default, and users who need a bare single connection call db.open_direct() → &Driver.

Further down the road:

• Prepared statement manager: a Stmt type prepared lazily per connection (like Go's sql.Stmt), so DB.Prepare() actually works across pooled connections instead of being a session-scoped no-op
• Streamed Rows: iterator that fetches rows on demand instead of materialising the entire result set as []Row
• Typed Scan: map columns directly to typed V variables instead of round-tripping through []string
• Context / timeout: propagate cancellation and deadlines through all pool operations. This is blocked on V having a general-purpose context/cancellation mechanism — not actionable within the db module alone

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.