How to Connect Flask to PostgreSQL: Simple Guide

To connect Flask to PostgreSQL, use the Flask-SQLAlchemy extension and set the database URI with postgresql://username:password@host:port/dbname. Then initialize SQLAlchemy with your Flask app to interact with the database.

Syntax

Use the SQLALCHEMY_DATABASE_URI config to specify your PostgreSQL connection string. It follows this pattern:

• postgresql://username:password@host:port/database_name
• username: your PostgreSQL user
• password: your user's password
• host: usually localhost or an IP address
• port: default is 5432
• database_name: your PostgreSQL database

Then create a SQLAlchemy object with your Flask app to manage database operations.

python

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://username:password@localhost:5432/mydatabase'
db = SQLAlchemy(app)

Example

This example shows a simple Flask app connected to PostgreSQL with a model and a route to add and list users.

python

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

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:secret@localhost:5432/testdb'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

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

@app.route('/add_user', methods=['POST'])
def add_user():
    name = request.json.get('name')
    if not name:
        return jsonify({'error': 'Name is required'}), 400
    user = User(name=name)
    db.session.add(user)
    db.session.commit()
    return jsonify({'message': f'User {name} added'}), 201

@app.route('/users')
def get_users():
    users = User.query.all()
    return jsonify([{'id': u.id, 'name': u.name} for u in users])

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

Output

Running the app starts a server at http://127.0.0.1:5000. POST JSON {"name": "Alice"} to /add_user adds a user. GET /users returns a list of users in JSON.

Common Pitfalls

• Forgetting to install psycopg2-binary, the PostgreSQL driver for Python, causes connection errors.
• Not setting SQLALCHEMY_TRACK_MODIFICATIONS = False leads to unnecessary warnings.
• Incorrect database URI format or wrong credentials cause connection failures.
• Not calling db.create_all() before using models means tables don't exist.

python

## Wrong URI example (missing password):
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres@localhost:5432/testdb'

## Correct URI example:
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:secret@localhost:5432/testdb'

Quick Reference

Step	Description
Install dependencies	pip install flask flask-sqlalchemy psycopg2-binary
Set URI	app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@host:port/db'
Initialize DB	db = SQLAlchemy(app)
Create tables	with app.app_context(): db.create_all()
Use models	Define classes inheriting from db.Model

Key Takeaways

Use Flask-SQLAlchemy with a proper PostgreSQL URI to connect Flask to PostgreSQL.

Install psycopg2-binary to enable PostgreSQL support in Python.

Set SQLALCHEMY_TRACK_MODIFICATIONS to False to avoid warnings.

Call db.create_all() inside app context to create tables before use.

Check your database URI format and credentials carefully to avoid connection errors.