Database connection pools and transactions

Opening a database connection for every request is expensive. A connection pool reuses a limited number of connections. Mojara exposes PooledDatabase, DbLease and DbResult from mojara.dbclient.

1. Create a pool

from mojara.dbclient import PooledDatabase

var db = PooledDatabase(
    "host=127.0.0.1 dbname=mojara_example user=mojara_user",
    capacity=4,
)

capacity=4 limits the pool to four concurrent connections. Supported capacities are 1 to 16.

2. Acquire a connection and read a result

var lease = db.acquire(timeout_ms=3000)
var result = lease.query("SELECT 11 AS number")
print(result.rows())          # 1
print(result.columns())       # 1
print(result.column_name(0))  # number
print(result.cell(0, 0))      # 11
lease.release()

timeout_ms=3000 means an acquire may wait up to three seconds for a free connection. Always release a lease when finished.

3. Parameterized queries

var greeting = lease.query1("SELECT $1::text", "Hello")
var pair = lease.query2("SELECT $1::text, $2::text", "Amina", "Yusuf")

For MySQL, use ? instead of PostgreSQL's $1 placeholders.

4. Atomic updates

lease.begin()
var result = lease.query("UPDATE accounts SET active = TRUE WHERE id = 1")
lease.commit()

begin() starts a transaction, commit() makes it durable, and rollback() cancels pending changes. A released lease with an open transaction is rolled back automatically.

5. Handle NULL

if result.is_null(0, 0):
    print("No value")
else:
    print(result.cell(0, 0))

Calling cell() on SQL NULL raises an error. Check is_null() first.

6. Close in the correct order

lease.release()
db.close()

The pool must outlive its leases. A lease is thread-affine and cannot be transferred to another thread. Closing while leases are active reports an error. See PostgreSQL or MySQL for full connections.