Introduction

PostgreSQL is a powerful, open-source relational database widely used for scalable and high-performance applications. Combining Flask with PostgreSQL provides a robust backend solution for applications requiring complex queries, transactions, and data integrity.

In this tutorial, you will learn:

✅ How to connect Flask with PostgreSQL
✅ Using SQLAlchemy ORM for database management
✅ Performing CRUD operations
✅ Optimizing performance for large databases

Step 1: Install PostgreSQL and Dependencies

Install PostgreSQL

🔹 Install PostgreSQL from PostgreSQL’s official site

🔹 Use Docker for an easy setup:

docker run -d --name postgres -e POSTGRES_USER=flaskuser -e POSTGRES_PASSWORD=flaskpass -e POSTGRES_DB=flaskdb -p 5432:5432 postgres

Install Required Python Packages

pip install flask psycopg2 flask_sqlalchemy

Step 2: Configure Flask and PostgreSQL

Setup Flask Application

Create a Flask app and configure the PostgreSQL connection:

from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://flaskuser:flaskpass@localhost/flaskdb"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)

🔹 SQLAlchemy handles database interactions efficiently
🔹 SQLALCHEMY_DATABASE_URI defines the connection string

Step 3: Define Database Models

Use SQLAlchemy to create a User model:

class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
email = db.Column(db.String(100), unique=True, nullable=False)
age = db.Column(db.Integer)

    def to_dict(self):
        return {"id": self.id, "name": self.name, "email": self.email, "age": self.age}

Create the database and tables:

flask shell

Inside the shell:

from your_app import db
db.create_all()

Step 4: Implement CRUD Operations

Create a User

@app.route("/users", methods=["POST"])
def create_user():
data = request.json
user = User(name=data["name"], email=data["email"], age=data.get("age"))
db.session.add(user)
db.session.commit()
return jsonify({"message": "User created", "id": user.id})

Retrieve Users

@app.route("/users", methods=["GET"])
def get_users():
users = User.query.all()
return jsonify([user.to_dict() for user in users])

Update a User

@app.route("/users/<int:id>", methods=["PUT"])
def update_user(id):
user = User.query.get(id)
if not user:
return jsonify({"error": "User not found"}), 404

    data = request.json
    user.name = data.get("name", user.name)
    user.email = data.get("email", user.email)
    user.age = data.get("age", user.age)

    db.session.commit()
    return jsonify({"message": "User updated", "user": user.to_dict()})

Delete a User

@app.route("/users/<int:id>", methods=["DELETE"])
def delete_user(id):
user = User.query.get(id)
if not user:
return jsonify({"error": "User not found"}), 404

    db.session.delete(user)
    db.session.commit()
    return jsonify({"message": "User deleted"})

Step 5: Query Optimization for Large Databases

Using Indexes

Indexes speed up lookups, especially on large tables:

CREATE INDEX idx_users_email ON users (email);

Efficient Querying with Pagination

For large datasets, paginate results:

@app.route("/users/page/<int:page>", methods=["GET"])
def get_paginated_users(page):
per_page = 10
users = User.query.paginate(page=page, per_page=per_page)
return jsonify({"users": [user.to_dict() for user in users.items], "total_pages": users.pages})

Bulk Inserts for Faster Performance

Instead of inserting records one by one:

@app.route("/bulk_users", methods=["POST"])
def bulk_insert():
data = request.json
users = [User(name=user["name"], email=user["email"], age=user.get("age")) for user in data]
db.session.bulk_save_objects(users)
db.session.commit()
return jsonify({"message": "Bulk insert successful", "count": len(users)})

Step 6: Running the Flask App

Start the Flask server:

flask run --host=0.0.0.0 --port=5000

Now, test the API using Postman or cURL.

Conclusion

🔹 Flask + PostgreSQL = Scalable, High-Performance Applications
🔹 SQLAlchemy ORM simplifies database management
🔹 Indexes, Pagination, and Bulk Inserts improve efficiency

With these techniques, you can efficiently manage large databases in your Flask applications. 🚀