The “Senior Dev” Singleton: How I Shot Myself in the Foot with Database Connections

It started like most tech horror stories do: a new project, fresh code, and me—ambitious, eager, and dangerously confident.

While setting up database access, I stumbled across a seemingly elegant pattern: wrap the database connection in a Singleton.
One instance. Reusable. Clean.

"Ah yes," I thought, "this is the kind of architecture real engineers use."

I had just seen a "senior dev" do the same thing in another codebase, and it looked so tidy. So naturally, I copied it. Why not? I was managing the DB connection in one place, no need to pass it around. Local tests worked. Development flew. All green.

Until they weren’t.

The Glitches Start Creeping In
It started small:

A random DatabaseError here, a psycopg2.OperationalError there.

Some unit tests failed inconsistently—especially those that depended on earlier queries.

Sometimes I’d get a strange deadlock or timeout, restart my dev server, and poof—everything worked again.

I blamed the DB. The ORM. My caffeine intake.

But no—it was my Singleton.

# db_singleton.py
import psycopg2

class Database:
    _instance = None

    def __init__(self):
        if not Database._instance:
            Database._instance = psycopg2.connect(
                dbname="mydb",
                user="myuser",
                password="mypassword",
                host="localhost",
                port=5432
            )

    def get_connection(self):
        return Database._instance

# Usage
db = Database().get_connection()
cursor = db.cursor()
cursor.execute("SELECT * FROM users")

Why the Singleton Was a Time Bomb
Here’s the thing I learned after many painful hours of debugging:

❌ 1. It wasn't thread-safe.
If you’re using something like Flask or Django, you’ll likely deal with multiple threads or processes. Sharing one connection across them is a disaster waiting to happen. Database connections aren’t made for concurrent use like that. That’s where all the race conditions and “flaky” bugs were coming from.

❌ 2. It kept the connection open forever.
That seems fine until the database decides, “Hey, I’m closing this idle connection now.” Then your app tries to use it again and boom—unexpected errors. You don’t know what’s going on, and restarting the app is your only fix.

❌ 3. It broke test isolation.
All my unit tests were unknowingly sharing the same DB connection. Data leaked between them. One test passed, the next one failed. Mocking the DB was impossible unless I hacked around the global Singleton. Testing became unreliable and slow

The Fix: Connection Pooling
Eventually, I discovered connection pooling—a smarter, scalable way to manage DB access.

Instead of one persistent connection, a pool keeps several open and ready to use. Each thread grabs one when needed, then returns it. Most ORMs and DB libraries (like SQLAlchemy, Django, psycopg2) support this out of the box.

# db_pool.py
from psycopg2 import pool

# Create the connection pool once at app startup
connection_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=10,
    dbname="mydb",
    user="myuser",
    password="mypassword",
    host="localhost",
    port=5432
)

def get_connection():
    return connection_pool.getconn()

def release_connection(conn):
    connection_pool.putconn(conn)

# Usage
conn = get_connection()
try:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM users")
        rows = cursor.fetchall()
        print(rows)
finally:
    release_connection(conn)

💬 What I Took Away
The “senior dev” I copied probably wasn’t wrong for their use case—but copying blindly was my mistake.

This experience taught me a golden rule I now live by:

Never adopt a pattern unless you understand its tradeoffs.
Even if it looks like best practice.

If you see a Singleton managing database connections in a Python codebase, be cautious. Ask “why” it’s there. Then (gently) suggest connection pooling—it just might save your app from mysterious, soul-crushing bugs