Connection pooling in Flask

Introduction

Connection pooling helps your Flask app reuse database connections. This makes your app faster and uses less resources.

When your Flask app talks to a database many times.

When you want to avoid delays from opening a new database connection each time.

When your app has many users accessing data at the same time.

When you want to reduce the load on your database server.

When you want smoother and quicker responses in your web app.

Syntax

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SQLALCHEMY_POOL_SIZE'] = 5
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 10

db = SQLAlchemy(app)

SQLAlchemy is the common library used with Flask for database and connection pooling.

POOL_SIZE sets how many connections are kept ready to use.

Examples

This sets a bigger pool and allows more extra connections if needed.

Flask

app.config['SQLALCHEMY_POOL_SIZE'] = 10
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 20

This sets how many seconds to wait for a connection before giving up.

Flask

app.config['SQLALCHEMY_POOL_TIMEOUT'] = 30

This recycles connections every 30 minutes to avoid stale connections.

Flask

app.config['SQLALCHEMY_POOL_RECYCLE'] = 1800

Sample Program

This Flask app uses SQLAlchemy with connection pooling settings. It creates a small pool of 3 connections and allows 2 extra if needed. The app adds a user and shows all users on the homepage.

Flask

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SQLALCHEMY_POOL_SIZE'] = 3
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 2

# Initialize the database object with connection pooling

db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

@app.route('/')
def index():
    # Add a user to test connection usage
    new_user = User(name='Alice')
    db.session.add(new_user)
    db.session.commit()
    users = User.query.all()
    return '<br>'.join([user.name for user in users])

if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(debug=False)

Important Notes

Connection pooling is mostly handled by SQLAlchemy when used with Flask.

Adjust pool size based on your app's traffic and database limits.

Always close or commit sessions to return connections to the pool.

Summary

Connection pooling lets Flask reuse database connections for speed.

Use SQLAlchemy config options to control pool size and behavior.

Proper pooling helps your app handle many users smoothly.