TL;DR / Key Takeaways

psycopg2 direct is faster, simpler, and easier to debug for bots with a small fixed schema
SQLAlchemy is worth the overhead when you need ORM, migrations, or multi-database support
Peer authentication on Linux breaks SQLAlchemy's default connection string approach, but the creator= pattern fixes it cleanly
The Predict & Profit Weather Bot and Econ Bot use psycopg2 directly for production PostgreSQL, with SQLite bundled for customers who skip the Postgres setup

This comes up every time someone sets up a new Python project that touches a database. psycopg2 or SQLAlchemy. Pick one and move on. Except most tutorials either treat SQLAlchemy as the obvious default or dismiss it entirely, without explaining the actual tradeoffs.

I have used both, in production, in a bot that trades real money. Here is what I actually think.

The Short Version

psycopg2 is a PostgreSQL driver. SQLAlchemy is an abstraction layer that can sit on top of psycopg2, or on top of a dozen other drivers. They are not competing tools in the same category. The real question is whether you want the abstraction layer or not.

For a trading bot with a fixed schema and straightforward queries, the abstraction layer costs you more than it gives you.

psycopg2: What You Get

Direct connection. Direct cursor. Direct SQL. No translation layer between your query and what hits the database.

import psycopg2

conn = psycopg2.connect(
    dbname="kalshi_trades",
    user="botuser",
    host="localhost"
)

cur = conn.cursor()
cur.execute(
    """
    INSERT INTO trade_decisions
        (market_ticker, direction, edge, score, action, skip_reason, created_at)
    VALUES (%s, %s, %s, %s, %s, %s, NOW())
    """,
    (ticker, direction, edge, score, action, skip_reason)
)
conn.commit()
cur.close()

That is the whole thing. You know exactly what runs. You know exactly when it commits. There is no magic.

psycopg2 also handles connection pooling natively via psycopg2.pool.ThreadedConnectionPool or SimpleConnectionPool. For a bot running on a single VM with a handful of threads, that is all you need.

from psycopg2 import pool

connection_pool = pool.ThreadedConnectionPool(
    minconn=1,
    maxconn=10,
    dbname="kalshi_trades",
    user="botuser",
    host="localhost"
)

conn = connection_pool.getconn()
try:
    cur = conn.cursor()
    cur.execute("SELECT * FROM open_trades WHERE status = 'open'")
    rows = cur.fetchall()
finally:
    connection_pool.putconn(conn)

Clean. No ceremony. No session lifecycle to manage.

SQLAlchemy: What You Get

SQLAlchemy gives you two things: the Core (expression language for building SQL programmatically) and the ORM (map Python classes to database tables). Most people reach for it because of the ORM. The ORM is genuinely useful in certain situations. In a trading bot, those situations are rare.

The overhead is real. Every query goes through multiple abstraction layers before it reaches psycopg2, which SQLAlchemy still uses under the hood for PostgreSQL. You are writing Python objects and trusting the library to generate correct SQL. Usually it does. When it does not, debugging the generated SQL is an extra step you do not want at 2am when a position is open.

SQLAlchemy also manages sessions, and session lifecycle is a category of bugs you simply do not have with direct psycopg2. Forgotten commits. Stale sessions. Connection leaks when you forget to close a session properly. These are real things that happen to real people.

When SQLAlchemy Is Actually Worth It

There are legitimate reasons to use it.

Migrations with Alembic. If your schema changes over time and you want version-controlled migrations, Alembic is the standard tool and it requires SQLAlchemy. For a project where multiple developers are running the same codebase against different database versions, this matters. For a solo bot on one VM, running a ALTER TABLE by hand is not a burden.

Multi-database support. SQLAlchemy lets you write one data layer and target PostgreSQL in production and SQLite in testing, with minimal changes. This is legitimately useful. The Predict & Profit bots ship with SQLite by default for exactly this reason: not every buyer wants to set up PostgreSQL, and SQLite works fine for running the bot and reviewing logs. But the switch is handled at the connection level, not through SQLAlchemy. More on that in a minute.

ORM for complex domain models. If your data model has deep relationships and you are doing object-oriented work with joined entities, the ORM reduces boilerplate. A trading bot usually has flat tables. Trades, decisions, positions, prices. The joins are simple. The ORM does not buy you much.

The Peer Auth Problem

This is the part that catches people who are new to Linux PostgreSQL deployments. Peer authentication is the default on Ubuntu. It means PostgreSQL authenticates by matching the Linux system user to the database role. No password. If your bot runs as botuser, it connects to PostgreSQL as botuser, and if that role exists, it gets in.

Peer auth is secure and convenient on a single server. The problem is SQLAlchemy's default connection string format does not support it cleanly.

A standard SQLAlchemy connection string looks like this:

postgresql+psycopg2://username:password@localhost/dbname

With peer auth there is no password. Passing an empty password string does not work reliably. Passing host=localhost routes through TCP and bypasses peer auth entirely, which then requires a password you do not have configured.

The fix is the creator= parameter. You pass a callable that returns a raw psycopg2 connection, and SQLAlchemy uses that instead of building its own connection.

import psycopg2
from sqlalchemy import create_engine

def get_psycopg2_conn():
    return psycopg2.connect(
        dbname="kalshi_trades",
        user="botuser"
        # No host= means psycopg2 uses the Unix socket, which triggers peer auth
    )

engine = create_engine(
    "postgresql+psycopg2://",
    creator=get_psycopg2_conn
)

Now SQLAlchemy manages sessions and the connection pool, but the actual connection is made by psycopg2 through the Unix socket with peer auth. It works. It is not obvious. The SQLAlchemy docs mention creator= briefly, and most Stack Overflow answers either miss it or recommend switching to password auth instead.

If you are using SQLAlchemy with peer auth and wondering why you keep getting fe_sendauth: no password supplied, this is why.

What the Bots Actually Use

The production bots use psycopg2 directly for PostgreSQL. The schema is fixed, the queries are known at build time, and I want full visibility into what runs.

The SQLite path, which ships with the source code for customers who want a simpler setup, uses a thin abstraction that swaps the connection object based on a config flag. No SQLAlchemy involved. The tradeoff is that a few SQLite-specific quirks have to be handled manually, mostly around thread safety and the check_same_thread=False parameter, but that is a known small surface area.

# db/connection.py (simplified)
import os
import sqlite3
import psycopg2

DB_MODE = os.getenv("DB_MODE", "sqlite")  # "sqlite" or "postgres"

def get_connection():
    if DB_MODE == "postgres":
        return psycopg2.connect(
            dbname=os.getenv("POSTGRES_DB"),
            user=os.getenv("POSTGRES_USER")
        )
    else:
        return sqlite3.connect(
            "trades.db",
            check_same_thread=False
        )

The query layer uses DBAPI2 cursor syntax for both, since SQLite and psycopg2 both support it. The only adaptation needed is parameter placeholders: %s for psycopg2, ? for SQLite. That gets handled with a one-line substitution based on DB_MODE.

It is not elegant. It works.

Performance Numbers (Practical, Not Benchmarked)

I am not going to cite a microbenchmark. The real performance question for a trading bot is latency on the write path. When the bot decides to place a trade, the decision record needs to be written to the database before the order hits Kalshi. If the DB write is slow, the order timing drifts.

With psycopg2 direct and a local Unix socket connection, writes are consistently under 2 milliseconds. With SQLAlchemy using the same psycopg2 driver and the creator= pattern, writes are 3 to 5 milliseconds in my testing. That is a 50 to 150 percent overhead for the extra layer.

For a weather bot that scans every 15 minutes, that overhead is irrelevant. If you were building a high-frequency system that placed hundreds of orders per second, it would matter. On Kalshi, where you are placing a handful of trades per day, it does not matter at all. I still prefer the direct path on principle.

The Actual Decision Tree

Pick psycopg2 direct if:

You control the schema and it does not change often
You are on a single server with a known configuration
You want minimal debugging surface area
You are using peer auth on Linux (or are willing to manage the creator= pattern yourself)

Pick SQLAlchemy if:

You are using Alembic for migrations and the team cares about schema versioning
You genuinely need to target multiple database backends from one codebase
You are building something with a complex domain model and the ORM reduces meaningful boilerplate
You are already using a framework (FastAPI, Flask) that integrates with SQLAlchemy cleanly

Do not pick SQLAlchemy because it feels like the "professional" choice. Direct psycopg2 is used in production at scale by companies that actually know what they are doing. The abstraction layer is a tool, not a signal of seriousness.

One More Thing: Connection Pooling Under Threads

If your bot uses threads, psycopg2 connections are not thread-safe by default. One connection per thread, or use ThreadedConnectionPool. SQLAlchemy's session-per-thread model handles this for you, which is one genuine ergonomic advantage.

With psycopg2 direct, the discipline is yours. If you are running a simple sequential scan loop, this is not an issue. If you are running concurrent threads against the same database, you need to manage the pool explicitly. Neither approach is hard, but SQLAlchemy does remove a class of mistakes here.

# Thread-safe pool usage with psycopg2
import threading
from psycopg2 import pool

_pool = pool.ThreadedConnectionPool(1, 5, dbname="kalshi_trades", user="botuser")
_local = threading.local()

def get_conn():
    if not hasattr(_local, "conn") or _local.conn.closed:
        _local.conn = _pool.getconn()
    return _local.conn

I have been writing software for 30 years. The tools that stick are the ones that do exactly what you need and nothing extra. For a trading bot with a fixed schema on a single server, psycopg2 direct wins. The creator= pattern solves the one real friction point with peer auth. Everything else is preference.

If your requirements change, migrate. That is what ALTER TABLE is for.

psycopg2 vs SQLAlchemy: Which One Should Your Trading Bot Use?