With an Example Fix: Database Configuration Postgresql Flask Example

Setting up PostgreSQL Database Configuration in a Flask Application

Flask is a well-known web development framework for Python. Developers prefer PostgreSQL to manage data in Flask applications. 

By combining the two, developers can create high-performance web solutions quickly. 

In this blog post, we'll guide you through configuring a PostgreSQL database in a Flask app using Flask-SQLAlchemy. 

Our guide will help experienced developers integrate a new database into their Flask projects or beginners explore Flask and PostgreSQL.

 Let's unlock the full potential of Flask and PostgreSQL in building robust web apps.

11 Steps For Setting up PostgreSQL Database Configuration in a Flask Application

1) Import the requirements:

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.declarative import declarative_base
import os

2) Install the packages in your environment. You can install it via pip:

pip install Flask

pip install Flask-SQLAlchemy

pip install SQLAlchemy

3) Create an instance of the Flask application. 

app = Flask(__name__)

4) PostgreSQL Database configuration:

For PostgreSQL database configuration in Flask, you typically need the following information:

5 Requirements for PostgreSQL Database Configuration in Flask

1- DB_HOST (Find your PostgreSQL Database Hostname): 

DB_HOST = "localhost"

The needed information is regarding your PostgreSQL server's hostname or IP address. 

In mid-February 2024, I deployed a Flask application with a PostgreSQL database on a shared hosting Cpanel Namecheap. 

It was hard as it took me a lot of time. However, when I contacted customer services from Namecheap, he said, and I quote, " Since PostgreSQL is the local service,

please use the "localhost" or even "127.0.0.1" as the database hostname."

Therefore, By default, you find your PostgreSQL database hostname as the "localhost" or even "127.0.0.1".

This is because PostgreSQL is a local service, so use the "localhost" or even "127.0.0.1" database hostname.

2- DB_PORT (Port of the PostgreSQL Database URL): 

DB_PORT = 5432

The needed information is regarding the port number that your PostgreSQL server uses to establish connections.

By default, the port for the PostgreSQL database URL is 5432, but it can be configured differently if needed.

3- DB_NAME: 

DB_NAME = 'your_database_name'

This is the name of the PostgreSQL database you want to connect to. 

It would help if you had already created this database within your PostgreSQL server.

4- DB_USER: 

DB_USER = 'your_database_username'

This is the username used to authenticate with the PostgreSQL server. Ensure that this user has appropriate permissions to access the specified database.

5- DB_PASSWORD:

DB_PASSWORD = 'your_database_password'

This is the database password that SQLAlchemy uses automatically when establishing a connection. 

It is essential to ensure that the password is strong and secure.

With this information, you can construct the PostgreSQL database URI for your Flask application's configuration. 

5) Create the Database Connection URL PostgreSQL:

To create the Database Connection URL PostgreSQL, follow a specific format. Here's the general structure:

# Database connection URL
DB_URL = f'postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}'

Constructing the Database Connection URL PostgreSQL in this format allows you to pass it to your Flask application's configuration.

6) Create a Base Class Base using declarative_base().

# create a base class Base using declarative_base().
Base = declarative_base()

7) Initialize SQLAlchemy

# Initialize SQLAlchemy
# assuming db is your SQLAlchemy instance
db = SQLAlchemy(model_class=Base)

8) Setting up the SQLAlchemy Database URI for your Flask Application: 

# configure the SQLite database, relative to the app instance folder
# app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///all_books.db"

app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get("DB_URI", "sqlite:///all_books.db")

The line: 

Is setting up the SQLAlchemy database URI for your Flask application, with a fallback to an SQLite database if no environment variable named DB_URI is found?

Here's what each part of the line does:

app.config['SQLALCHEMY_DATABASE_URI']: 

This sets the SQLALCHEMY_DATABASE_URI configuration variable in your Flask application's configuration dictionary. 

Flask-SQLAlchemy uses this variable to connect to your database.

os.environ.get("DB_URI", "sqlite:///all_books.db"): 

This retrieves the environment variable value named DB_URI using os.environ.get(). 

If DB_URI is not found in the environment variables, it defaults to the value "sqlite:///all_books.db", an SQLite database named all_books.db located in the current directory.

9) Initializes the Flask application with the Flask-SQLAlchemy extension:

The line:

# initialize the app with the extension
db.init_app(app)

Initializes the Flask application with the Flask-SQLAlchemy extension. 

This line is typically used when you've defined your SQLAlchemy database models in a separate module or file and must associate them with your Flask application.

10) Define your models:

11) Finally, create all the database tables defined by your SQLAlchemy models.

# Create table schema in the database. Requires application context.
with app.app_context():
    db.create_all()

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.declarative import declarative_base
import os

app = Flask(__name__)

# Database configuration
DB_HOST = "localhost"
DB_PORT = 5432
DB_NAME = 'your_database_name'
DB_USER = 'your_database_username'
DB_PASSWORD = 'your_database_password'

# Database connection URL
DB_URL = f'postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}'


# Replace 'username', 'password', 'localhost', and 'database_name' with your PostgreSQL credentials

# create a base class Base using declarative_base().
Base = declarative_base()

# Initialize SQLAlchemy
# assuming db is your SQLAlchemy instance
db = SQLAlchemy(model_class=Base)

# configure the SQLite database, relative to the app instance folder
# app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///all_books.db"

app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get("DB_URI", "sqlite:///all_books.db")


# initialize the app with the extension
db.init_app(app)


# Define your models
# CREATE TABLE
class Books(db.Model):
    __tablename__ = "books"  # Specify the table name
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(250), unique=True, nullable=False)
    author = db.Column(db.String(250), nullable=False)
    rating = db.Column(db.Float, nullable=False)


# Create table schema in the database. Requires application context.
with app.app_context():
    db.create_all()


@app.route('/')
def home():
    # YOUR CODES
    return render_template("index.html")


# Routes and other application logic would go here


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

In short, integrating PostgreSQL with Flask can open up numerous possibilities for web developers. 

Flask-SQLAlchemy can help us efficiently manage data. 

This guide covers the necessary steps to set up PostgreSQL with Flask. 

Following best practices for database design, data integrity, and security is crucial. 

Now, you are well-equipped to build sophisticated Flask applications with PostgreSQL. 

Please share with us how you utilized this article or suggest any valuable additions we may have missed.

"Thank you, and have a happy time coding!"

Related Posts:

1) SQLAlchemy Migrate vs Alembic vs Flask Migrate

2) Sqlalchemy-migrate vs Alembic 

3) Flask-migrate vs Alembic 

4) With an Example Fix: Flask Migrate is not Working in Sqlalchemy 

5) With an Example Fix: How To Delete Existing Migration Files Flask Python / Directory migrations already exists and is not empty

6) With an Example Fix: Sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) No Such Column:

7) Which is Better, SQLite or SQLAlchemy?

8) What Are The Purposes of Flask-SQLAlchemy in Python? 

9) When Should You Use SQLAlchemy? 

10) What are The Major Benefits of Using SQLAlchemy in Python?