pgmonkey (2/8) — Connection Types

Summary: pgmonkey supports four connection types — normal, pool, async, and async_pool — all driven by the same YAML config file. Learn when to use each one and how to switch between them with a single parameter change.

Key	Value
pgmonkey version	4.0.0
Python version	3.12
Working directory	~/projects/pgmonkey-tutorial
Config file	pgmonkey_config.yaml
Database name	tutorial_db
Database user	app_user

0. Prerequisites

• Completed Part 1 (pgmonkey installed, config file created, connection tested)
• The virtual environment from Part 1 active

---

1. The Four Connection Types

pgmonkey gives you four ways to connect to PostgreSQL, all from the same config file.

Type	Sync/Async	Pooled	Best for
normal	Sync	No	Scripts, CLI tools, one-off queries
pool	Sync	Yes	Flask, Django, multi-threaded web apps
async	Async	No	Lightweight async scripts, single async tasks
async_pool	Async	Yes	FastAPI, aiohttp, high-concurrency async services

The connection type is just a string parameter. Your YAML file, your credentials, your SSL settings — all stay the same.

---

2. Normal Connection

A single synchronous connection. Open it, use it, close it.

Create normal_query.py.

from pgmonkey import PGConnectionManager

def main():
    manager = PGConnectionManager()
    connection = manager.get_database_connection('pgmonkey_config.yaml', 'normal')

    with connection as conn:
        with conn.cursor() as cur:
            cur.execute('SELECT current_database(), current_user;')
            print(cur.fetchone())

if __name__ == "__main__":
    main()




Code language: JavaScript (javascript)

python normal_query.py




Code language: CSS (css)

('tutorial_db', 'app_user')




Code language: JavaScript (javascript)

The with connection context manager commits automatically on clean exit. If an exception occurs inside the block, it rolls back instead.

---

3. Pool Connection

A synchronous connection pool. Multiple threads can borrow and return connections from the same pool.

Create pool_query.py.

from pgmonkey import PGConnectionManager

def main():
    manager = PGConnectionManager()
    pool = manager.get_database_connection('pgmonkey_config.yaml', 'pool')

    # First request borrows a connection from the pool
    with pool as conn:
        with conn.cursor() as cur:
            cur.execute('SELECT count(*) FROM pg_stat_activity;')
            print(f"Active connections: {cur.fetchone()[0]}")

    # Connection returned to pool — borrow again
    with pool as conn:
        with conn.cursor() as cur:
            cur.execute('SELECT now();')
            print(f"Server time: {cur.fetchone()[0]}")

if __name__ == "__main__":
    main()




Code language: PHP (php)

python pool_query.py




Code language: CSS (css)

Each with pool block borrows a connection, uses it, and returns it when the block exits. The pool stays open between requests — connections are reused, not recreated.

Pool sizing is controlled by pool_settings in your YAML file. Part 4 covers pool configuration in depth.

---

4. Async Connection

A single asynchronous connection for use with asyncio.

Create async_query.py.

import asyncio
from pgmonkey import PGConnectionManager

async def main():
    manager = PGConnectionManager()
    connection = await manager.get_database_connection('pgmonkey_config.yaml', 'async')

    async with connection as conn:
        async with conn.cursor() as cur:
            await cur.execute('SELECT version();')
            print(await cur.fetchone())

if __name__ == "__main__":
    asyncio.run(main())




Code language: JavaScript (javascript)

python async_query.py




Code language: CSS (css)

The pattern mirrors the normal connection — get_database_connection is awaited, and the context managers are async with instead of with. Everything else is the same.

Note: The async_settings section in your YAML controls session-level PostgreSQL parameters (like statement_timeout) for async connections. pgmonkey applies them automatically after connecting.

---

5. Async Pool Connection

An asynchronous connection pool — the workhorse for high-concurrency async applications.

Create async_pool_query.py.

import asyncio
from pgmonkey import PGConnectionManager

async def main():
    manager = PGConnectionManager()
    pool = await manager.get_database_connection('pgmonkey_config.yaml', 'async_pool')

    # Borrow a connection from the async pool
    async with pool as conn:
        async with conn.cursor() as cur:
            await cur.execute('SELECT current_database();')
            print(await cur.fetchone())

    # Connection returned — borrow again
    async with pool as conn:
        async with conn.cursor() as cur:
            await cur.execute('SELECT now();')
            print(await cur.fetchone())

if __name__ == "__main__":
    asyncio.run(main())




Code language: PHP (php)

python async_pool_query.py




Code language: CSS (css)

Same pattern as the sync pool, with async with and await. Pool sizing is controlled by async_pool_settings in the YAML.

---

6. Switching Types at Runtime

The connection type is a parameter, not an architectural decision. You can switch types without changing your config file.

from pgmonkey import PGConnectionManager

manager = PGConnectionManager()

# Same config file, different connection types
normal_conn = manager.get_database_connection('pgmonkey_config.yaml', 'normal')
pool_conn = manager.get_database_connection('pgmonkey_config.yaml', 'pool')




Code language: PHP (php)

Your YAML file has a connection_type field that sets the default. When you pass a type explicitly to get_database_connection(), it overrides the default. This means a single config file can serve your entire application — sync scripts, async services, pooled web endpoints — all reading the same credentials and SSL settings.

Tip: The CLI also accepts --connection-type to override the default. For example: pgmonkey pgconfig test --connconfig pgmonkey_config.yaml --connection-type async_pool

---

7. Choosing the Right Type

Scenario	Recommended type	Why
One-off script or migration	normal	Simple, no pool overhead
Flask or Django web app	pool	Thread-safe, connections reused across requests
Single async task or script	async	Non-blocking I/O without pool complexity
FastAPI or aiohttp web app	async_pool	High concurrency, connections shared across tasks
Mixed sync + async codebase	pool + async_pool	Same config file, both types coexist

The most common production pattern is pool for sync frameworks and async_pool for async frameworks. Both use the same YAML config — the only difference is the connection type string.

---

Summary

You used all four pgmonkey connection types with the same configuration file.

• normal — single sync connection for scripts and tools
• pool — sync connection pool for threaded web apps
• async — single async connection for lightweight async work
• async_pool — async connection pool for high-concurrency services
• Switching types is a one-parameter change — no config rewiring needed

Next up: pgmonkey (3/8) — Securing Connections with SSL/TLS covers encryption modes, certificate verification, and client certificate authentication.