Practical examples of Flask and PostgreSQL connection examples for modern apps

Fast-start examples of Flask and PostgreSQL connection examples

Let’s start with a very small, very honest example of connecting Flask to PostgreSQL using psycopg2 directly. This is the kind of example of a connection you might try in a sandbox or a throwaway internal tool.

from flask import Flask, jsonify
import psycopg2
import os

app = Flask(__name__)

DB_URL = os.getenv(
    "DATABASE_URL",
    "postgresql://postgres:postgres@localhost:5432/mydb"
)

def get_db_connection():
    conn = psycopg2.connect(DB_URL)
    return conn

@app.route("/users")
def list_users():
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("SELECT id, email FROM users ORDER BY id")
    rows = cur.fetchall()
    cur.close()
    conn.close()

    users = [{"id": r[0], "email": r[1]} for r in rows]
    return jsonify(users)

if __name__ == "__main__":
    app.run(debug=True)

This is one of the simplest examples of Flask and PostgreSQL connection examples you’ll see: a single connection helper, a route, and a query. It’s fine for learning, but it does not scale well because it opens and closes a connection on every request.

SQLAlchemy-based examples include cleaner patterns

Most real projects move quickly from raw psycopg2 to SQLAlchemy, because it gives you connection pooling, models, and migrations through tools like Alembic. These are the best examples to study if you care about maintainability.

Basic SQLAlchemy example of a Flask and PostgreSQL connection

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = os.getenv(
    "DATABASE_URL",
    "postgresql+psycopg2://postgres:postgres@localhost:5432/mydb"
)
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)

class User(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)

@app.route("/add-user/<email>")
def add_user(email):
    user = User(email=email)
    db.session.add(user)
    db.session.commit()
    return {"id": user.id, "email": user.email}

if __name__ == "__main__":
    with app.app_context():
        db.create_all()
    app.run(debug=True)

Here, the connection is managed by SQLAlchemy under the hood. This pattern shows up constantly in real examples of Flask and PostgreSQL connection examples used in production tutorials and company internal docs.

Real examples: environment-based configuration for 2024 deployments

In 2024, almost nobody hardcodes connection strings. You’ll typically see environment variables set by Docker, Kubernetes, or a PaaS. This example of configuration demonstrates how to keep your Flask–PostgreSQL connection flexible.

import os

class Config:
    SQLALCHEMY_DATABASE_URI = os.getenv(
        "DATABASE_URL",
        "postgresql+psycopg2://postgres:postgres@localhost:5432/app_db"
    )
    SQLALCHEMY_ENGINE_OPTIONS = {
        "pool_size": int(os.getenv("DB_POOL_SIZE", 5)),
        "max_overflow": int(os.getenv("DB_MAX_OVERFLOW", 10)),
        "pool_timeout": int(os.getenv("DB_POOL_TIMEOUT", 30)),
    }

class TestingConfig(Config):
    SQLALCHEMY_DATABASE_URI = os.getenv(
        "TEST_DATABASE_URL",
        "postgresql+psycopg2://postgres:postgres@localhost:5432/app_test"
    )

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)

This pattern is one of the best examples of Flask and PostgreSQL connection examples that can move from laptop to CI to production with minimal friction. You can run a local PostgreSQL container with:

docker run --name pg-local -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16

Then set DATABASE_URL in your shell or .env file and your Flask app connects without code changes.

Examples of Flask and PostgreSQL connection examples with connection pooling

Once traffic grows, connection pooling becomes non‑negotiable. PostgreSQL has a finite connection limit, and every gunicorn worker opening its own unpooled connections is a recipe for pain.

With SQLAlchemy, pooling is built in. Here’s a more explicit example of tuning it:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

DATABASE_URL = os.getenv("DATABASE_URL")

engine = create_engine(
    DATABASE_URL,
    pool_size=10,
    max_overflow=20,
    pool_recycle=1800,  # recycle connections every 30 minutes
)

SessionLocal = scoped_session(sessionmaker(bind=engine))

@app.teardown_appcontext
def remove_session(exception=None):
    SessionLocal.remove()

In this example of a Flask and PostgreSQL connection, scoped_session assigns a session per request context. It’s a pattern you’ll see in many real examples from large Flask codebases.

For teams that need even tighter control, external poolers like pgBouncer can sit in front of PostgreSQL. The connection string stays the same; only the host/port change.

Authoritative background on PostgreSQL connection limits and performance is available in the official documentation at https://www.postgresql.org/docs/.

Async-flavored examples of Flask and PostgreSQL connection examples

Flask itself is not an async framework, but since 2.x it can host async views. Meanwhile, the PostgreSQL world is seeing more async drivers like asyncpg and async SQLAlchemy. If you’re mixing sync Flask with async database access, you need to be intentional.

Here’s a minimal async‑ish example using SQLAlchemy 2.x style with async engine, wrapped in sync routes via asyncio.run for specific heavy endpoints:

import asyncio
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

ASYNC_DB_URL = os.getenv(
    "ASYNC_DATABASE_URL",
    "postgresql+asyncpg://postgres:postgres@localhost:5432/mydb"
)

async_engine = create_async_engine(ASYNC_DB_URL, pool_size=5, max_overflow=10)
AsyncSessionLocal = async_sessionmaker(async_engine, expire_on_commit=False)

async def get_user_count_async():
    async with AsyncSessionLocal() as session:
        result = await session.execute("SELECT COUNT(*) FROM users")
        return result.scalar_one()

@app.route("/user-count")
def user_count():
    count = asyncio.run(get_user_count_async())
    return {"count": count}

This is more of an advanced example of a Flask and PostgreSQL connection tailored for teams slowly introducing async. If you’re starting fresh and want heavy async I/O, you might look at FastAPI or Quart, but plenty of organizations still anchor on Flask and add async only where absolutely needed.

Real examples of Flask and PostgreSQL connection examples with Flask-Migrate

Schema changes quickly become the hardest problem in database‑backed apps. In the Flask + PostgreSQL ecosystem, Flask-Migrate (a wrapper around Alembic) is a standard tool.

A typical project layout shows another concrete example of how the connection is wired:

## app/__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
import os

db = SQLAlchemy()
migrate = Migrate()


def create_app(config_class=None):
    app = Flask(__name__)

    app.config["SQLALCHEMY_DATABASE_URI"] = os.getenv("DATABASE_URL")
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    db.init_app(app)
    migrate.init_app(app, db)

    from .views import main_bp
    app.register_blueprint(main_bp)

    return app

In this example of application factory style, tests can spin up their own PostgreSQL instances or use a separate test database. The same DATABASE_URL pattern controls all migrations and connections, which is why this shows up in many of the best examples of Flask and PostgreSQL connection examples in open source projects.

You can learn more about migration best practices from university database courses; for instance, MIT OpenCourseWare maintains database material at https://ocw.mit.edu/.

Security-minded examples include safe connection handling

The security story for Flask and PostgreSQL connections is not only about SQL injection; it’s also about credentials, TLS, and least privilege.

Here is a practical example of a Flask and PostgreSQL connection using parameterized queries and a restricted database user:

import psycopg2
from psycopg2.extras import RealDictCursor

DB_URL = os.getenv("DATABASE_URL")

@app.route("/login", methods=["POST"])
def login():
    data = request.get_json()
    email = data.get("email")

    with psycopg2.connect(DB_URL, cursor_factory=RealDictCursor) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT id, password_hash FROM users WHERE email = %s",
                (email,)
            )
            user = cur.fetchone()

#    # password verification omitted for brevity
    return {"user": user["id"]} if user else ("Unauthorized", 401)

This is one of the more realistic examples of Flask and PostgreSQL connection examples because it demonstrates:

• Parameterized queries (%s with a tuple)
• Context managers to close connections and cursors
• A pattern you can extend to TLS‑secured connections by adding sslmode=require to the connection string

For broader guidance on handling sensitive data and authentication in web apps, the National Institute of Standards and Technology (NIST) publishes helpful material at https://csrc.nist.gov/.

Testing-focused examples of Flask and PostgreSQL connection examples

Testing is where a lot of developers cut corners with databases. In 2024–2025, using disposable PostgreSQL instances in CI is common thanks to containers. Here’s an example of a testing setup using pytest and Flask’s application factory.

## conftest.py
import os
import pytest
from app import create_app, db

@pytest.fixture
ndef app():
    os.environ["DATABASE_URL"] = (
        "postgresql+psycopg2://postgres:postgres@localhost:5433/test_db"
    )
    app = create_app()
    with app.app_context():
        db.create_all()
        yield app
        db.drop_all()

@pytest.fixture
ndef client(app):
    return app.test_client()

This example of a test‑oriented Flask and PostgreSQL connection lets you:

• Point tests at a separate PostgreSQL instance or port
• Create and drop tables around the test session
• Keep production and test credentials fully isolated

As teams adopt more rigorous testing, patterns like this show up in real examples shared at conferences and in engineering blogs.

Trends for Flask and PostgreSQL connections in 2024–2025

A few trends shape how new examples of Flask and PostgreSQL connection examples are written today:

• SQLAlchemy 2.x adoption: More projects are switching to the 2.x style engine and session usage, which changes some connection patterns.
• Async drivers: asyncpg and async SQLAlchemy are gaining traction for high‑throughput APIs, even when the framework itself stays mostly sync.
• Infrastructure as code: Terraform, Pulumi, and similar tools manage PostgreSQL instances, while environment variables and secret managers inject connection strings.
• Database‑as‑a‑Service: Managed PostgreSQL (AWS RDS, Azure Database for PostgreSQL, Google Cloud SQL) means connection examples increasingly include SSL parameters and read‑replica URLs.

When you look for the best examples of Flask and PostgreSQL connection examples going forward, expect to see more async snippets, more explicit pooling configuration, and more attention to observability (logging slow queries, tracking connection errors, and so on).

For broader context on database‑backed applications and reliability, you can browse materials from university computer science programs, such as Stanford’s online content at https://online.stanford.edu/.

FAQ: short answers with real examples

Q: Can you show a minimal example of connecting Flask to PostgreSQL without SQLAlchemy?
Yes. The earliest example in this guide using psycopg2 and a simple get_db_connection() helper is exactly that. It’s fine for small scripts or learning, but for anything long‑lived you’ll want pooling and better structure.

Q: What are some real examples of Flask and PostgreSQL connection examples used in production apps?
Common patterns include SQLAlchemy with an application factory, Flask-Migrate for schema changes, environment‑driven DATABASE_URL configuration, and connection pooling tuned via SQLALCHEMY_ENGINE_OPTIONS or a dedicated pooler like pgBouncer.

Q: Do I need async to build a modern Flask + PostgreSQL API?
Not necessarily. Many production systems still use sync SQLAlchemy and perform well. Async becomes attractive when you’re dealing with thousands of concurrent connections or very I/O‑heavy workloads; that’s when async examples start to pay off.

Q: How do I secure my PostgreSQL connection in Flask?
Use environment variables or a secret manager for credentials, prefer least‑privilege database roles, use parameterized queries, and enable SSL/TLS in production by adding options like ?sslmode=require to your connection URL when supported by your provider.

Q: Is it better to use one database per environment or share a single PostgreSQL instance?
Separate databases per environment (development, staging, production, test) is the safer pattern. The testing examples in this article show how to point Flask at a dedicated test database so experiments never touch production data.