PostgreSQL

The PostgreSQL integration connects agents directly to a live PostgreSQL database using a standard connection string. No file upload required — queries execute against the live database on demand.

Setup

pip install aixplain

from aixplain import Aixplain

aix = Aixplain(api_key="YOUR_API_KEY")

Quick start

tool = aix.Tool(
    name="Postgres Tool",
    integration="aixplain/postgresql",
    config={"url": "postgresql://user:password@host:5432/mydb"},
)
tool.save()

result = tool.run(action="query", data={"query": "SELECT * FROM customers LIMIT 3"})
print(result.data)

Show output

Connection string format

postgresql://[user]:[password]@[host]:[port]/[database][?parameters]

# Standard
url = "postgresql://postgres:mypassword@localhost:5432/mydb"

# SSL required
url = "postgresql://user:pass@host:5432/db?sslmode=require"

# Cloud (e.g. Supabase)
url = "postgresql://postgres.abc123:password@aws-1-us-east-1.pooler.supabase.com:5432/postgres"

warning

Never commit connection strings with credentials to version control. Load them from environment variables:

import os
db_url = os.getenv("POSTGRES_URL")

Create the tool

import os

postgres_tool = aix.Tool(
    name="Customer Database",
    integration="aixplain/postgresql",
    config={"url": os.getenv("POSTGRES_URL")},
)
postgres_tool.save()

Available actions

postgres_tool.list_actions()

Show output

Action	Description
schema	Returns table names, column names, types, constraints, indexes, and foreign keys
query	Executes any valid SQL statement

Inspect the schema

Fetch the schema before building an agent — include it in instructions so the agent writes accurate SQL without guessing column names.

result = postgres_tool.run(action="schema")
print(result.data)

Show output

Run queries

The data parameter accepts either a SQL string or a {"query": "..."} dict — both are equivalent.

SELECT

# Basic fetch
result = postgres_tool.run(
    action="query",
    data={"query": "SELECT * FROM customers LIMIT 10"},
)
print(result.data)

Show output

# Filter
result = postgres_tool.run(
    action="query",
    data={"query": "SELECT name, email, total_spent FROM customers WHERE total_spent > 500"},
)
print(result.data)

Show output

# Aggregate
result = postgres_tool.run(
    action="query",
    data={
        "query": """
            SELECT
                COUNT(*) AS total_customers,
                ROUND(AVG(total_spent), 2) AS avg_spending,
                ROUND(SUM(total_spent), 2) AS total_revenue
            FROM customers
        """
    },
)
print(result.data)

Show output

INSERT

result = postgres_tool.run(
    action="query",
    data={
        "query": """
            INSERT INTO customers (name, email, signup_date, total_spent)
            VALUES ('Alice Johnson', 'alice@example.com', CURRENT_DATE, 0)
            RETURNING id, name, email
        """
    },
)
print(result.data)

Show output

UPDATE

result = postgres_tool.run(
    action="query",
    data={
        "query": """
            UPDATE customers
            SET total_spent = total_spent + 100.00
            WHERE signup_date < '2024-01-01'
            RETURNING id, name, total_spent
        """
    },
)
print(result.data)

Show output

DELETE

result = postgres_tool.run(
    action="query",
    data={
        "query": """
            DELETE FROM customers
            WHERE total_spent = 0
            AND signup_date < NOW() - INTERVAL '1 year'
            RETURNING id, name
        """
    },
)
print(result.data)

Show output

Results are returned in Markdown table format.

PostgreSQL-specific features

CTEs and window functions

result = postgres_tool.run(
    action="query",
    data={
        "query": """
            WITH daily_sales AS (
                SELECT DATE(order_date) AS sale_date, SUM(amount) AS daily_total
                FROM orders
                WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
                GROUP BY DATE(order_date)
            )
            SELECT
                sale_date,
                daily_total,
                AVG(daily_total) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
            FROM daily_sales
            ORDER BY sale_date
        """
    },
)
print(result.data)

Show output

JSONB

result = postgres_tool.run(
    action="query",
    data={
        "query": """
            SELECT id, metadata->>'name' AS name
            FROM users
            WHERE metadata @> '{"active": true}'
        """
    },
)

Full-text search

result = postgres_tool.run(
    action="query",
    data={
        "query": """
            SELECT id, title FROM articles
            WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & tutorial')
        """
    },
)

Use with agents

Fetch the schema first, then include it in instructions:

schema = postgres_tool.run(action="schema")

agent = aix.Agent(
    name="Customer Data Agent",
    description="Analyses customer data.",
    instructions=f"""
        You have access to a PostgreSQL database.

        Schema:
        {schema.data}

        Guidelines:
        - Always use LIMIT on large tables.
        - Use RETURNING to confirm writes.
        - Format results as markdown tables.
        - Never run DROP, TRUNCATE, or DDL statements.
    """,
    output_format="markdown",
    tools=[postgres_tool],
)
agent.save()

response = agent.run("Who are the top 3 customers by lifetime spending?")
print(response.data.output)

Show output

Multi-database agent

prod_db = aix.Tool(
    name="Production DB",
    integration="aixplain/postgresql",
    config={"url": os.getenv("PROD_POSTGRES_URL")},
)
prod_db.save()

staging_db = aix.Tool(
    name="Staging DB",
    integration="aixplain/postgresql",
    config={"url": os.getenv("STAGING_POSTGRES_URL")},
)
staging_db.save()

agent = aix.Agent(
    name="Database Comparison Agent",
    description="Compares data across production and staging.",
    instructions="""
        You have access to both production and staging databases.
        Production is read-only. Clearly identify which database each result comes from.
        Never modify production data.
    """,
    tools=[prod_db, staging_db],
)
agent.save()

response = agent.run("Compare user counts between production and staging.")
print(response.data.output)

Show output

Security

Use read-only credentials when the agent only needs SELECT access:

-- Create a read-only user in PostgreSQL
CREATE USER readonly_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

tool = aix.Tool(
    name="Read-Only Tool",
    integration="aixplain/postgresql",
    config={"url": "postgresql://readonly_user:secure_password@host:5432/mydb"},
)

Reinforce access boundaries in instructions — telling the agent it only has read access reduces the chance it attempts writes.

Troubleshooting

Connection refused Check host, port, and that the PostgreSQL server is reachable from aiXplain. Confirm the firewall allows port 5432.

Authentication failed Verify credentials. If the password contains special characters, URL-encode them. Confirm the user is permitted to connect from aiXplain's IP range.

SSL errors Add ?sslmode=require to the connection string. For self-signed certificates use ?sslmode=allow.

Query timeout Add a LIMIT clause, check for missing indexes, and look for table locks or long-running transactions on the server.

Permission denied on table Grant the missing permissions:

GRANT SELECT ON table_name TO your_user;
GRANT USAGE ON SCHEMA public TO your_user;

Agent generates incorrect SQL Include the full schema and example queries in instructions. Test queries manually with postgres_tool.run() before attaching to the agent.