pgmonkey (1/8) — Getting Started

Summary: Install pgmonkey, generate a YAML configuration file, and run your first PostgreSQL query — all in under five minutes.

0. Prerequisites

• Python 3.10 or later installed
• A running PostgreSQL 14+ instance with a database you can connect to
• A terminal open on your machine

Note: This tutorial uses a database called tutorial_db with a user called app_user. Replace these with your own values throughout.

1. Create the Project

Create a working directory and a virtual environment.

mkdir -p ~/projects/pgmonkey-tutorial
cd ~/projects/pgmonkey-tutorial
python3 -m venv .venv
source .venv/bin/activate

Your prompt should now show (.venv) confirming the virtual environment is active.

2. Install pgmonkey

Install pgmonkey from PyPI.

pip install pgmonkey

Verify the installation.

pip show pgmonkey

Name: pgmonkey
Version: 4.0.0

pgmonkey pulls in psycopg, psycopg_pool, and PyYAML automatically — no extra installs needed.

3. Generate a Configuration File

Use the pgmonkey CLI to create a YAML configuration template.

pgmonkey pgconfig create --type pg --connconfig pgmonkey_config.yaml

This writes a fully commented configuration file to pgmonkey_config.yaml in your current directory. The template includes every setting pgmonkey supports, with sensible defaults and inline comments explaining each one.

4. Understand the Configuration

Open pgmonkey_config.yaml. The file has five sections.

connection_type: 'normal'
connection_settings:
  user: 'postgres'
  password: 'password'
  host: 'localhost'
  port: '5432'
  dbname: 'mydatabase'
  sslmode: 'prefer'
  sslcert: ''
  sslkey: ''
  sslrootcert: ''
  connect_timeout: '10'
  application_name: 'myapp'
  keepalives: '1'
  keepalives_idle: '60'
  keepalives_interval: '15'
  keepalives_count: '5'
sync_settings:
  # statement_timeout: '30000'
  # lock_timeout: '10000'
pool_settings:
  min_size: 5
  max_size: 20
  timeout: 30
  max_idle: 300
  max_lifetime: 3600
  check_on_checkout: false
async_settings:
  idle_in_transaction_session_timeout: '5000'
  statement_timeout: '30000'
  lock_timeout: '10000'
async_pool_settings:
  min_size: 5
  max_size: 20
  timeout: 30
  max_idle: 300
  max_lifetime: 3600
  check_on_checkout: false

The key insight: all four connection types share the same connection_settings block. You write your credentials, SSL paths, and keepalive tuning once, and every connection type uses them.

5. Edit Your Connection Settings

Update the connection_settings section to match your database. At minimum, change these four values.

connection_settings:
  user: 'app_user'
  password: '<YOUR_PASSWORD>'
  host: 'localhost'
  port: '5432'
  dbname: 'tutorial_db'

Warning: Hardcoding passwords in config files is fine for local development. For production, Part 5 of this series covers environment variable interpolation and secret file references.

Leave the rest of the settings at their defaults for now.

6. Test the Connection

Use the CLI to verify your configuration connects successfully.

pgmonkey pgconfig test --connconfig pgmonkey_config.yaml

Testing connection...
Connection successful!

If the test fails, double-check your user, password, host, port, and dbname values. Make sure your PostgreSQL server is running and accepting connections.

Tip: Add --connection-type pool to test a specific connection type. Without this flag, pgmonkey uses the connection_type value from the YAML file.

7. Run Your First Query

Create a file called 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 version();')
            print(cur.fetchone())
if __name__ == "__main__":
    main()

Run it.

python query.py

('PostgreSQL 16.x ...',)

Here is what happened:

• PGConnectionManager() creates a manager that handles connection caching and lifecycle.
• get_database_connection() reads the YAML config and creates a synchronous connection.
• The with connection block commits on clean exit and rolls back on exception.
• The with conn.cursor() block gives you a database cursor for executing queries.

That is pgmonkey’s core pattern — and it stays the same whether you use normal, pooled, async, or async-pooled connections. The only thing that changes is the connection type string.

Summary

You installed pgmonkey, generated a YAML configuration template, edited it for your database, tested the connection from the CLI, and ran a Python query — all with a single config file and a few lines of code.

• pgmonkey wraps psycopg and psycopg_pool behind a unified YAML-driven interface
• One config file serves all four connection types
• The CLI generates configs, tests connections, and more
• The Python API is three lines: create a manager, get a connection, use it

Next up: pgmonkey (2/8) — Connection Types dives into all four connection modes and shows you when to use each one.