Making a Flask app using a PostgreSQL database

Introduction to Making a Flask App Using a PostgreSQL Database

Flask is a lightweight and flexible web framework in Python, ideal for building web applications quickly. Pairing Flask with PostgreSQL, a powerful open-source relational database, allows you to develop scalable and reliable apps with robust data management capabilities. In this guide, we'll walk through creating a Flask app connected to a PostgreSQL database.

Prerequisites

Ensure you have Python, Flask, and PostgreSQL installed on your system. Additionally, we will use psycopg2 to connect Flask with PostgreSQL and Flask_SQLAlchemy as the ORM tool. You can install the necessary Python packages using pip:

pip install Flask psycopg2-binary Flask_SQLAlchemy

Setting Up PostgreSQL Database

First, create a PostgreSQL database and user. You can do this via the psql shell:

CREATE DATABASE myflaskdb;
CREATE USER flaskuser WITH PASSWORD 'password123';
GRANT ALL PRIVILEGES ON DATABASE myflaskdb TO flaskuser;

Replace the username and password with your preferred credentials.

Configuring Flask Application

Create a new Python file, say app.py, and set up the Flask application with SQLAlchemy configured to connect to the PostgreSQL database:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://flaskuser:password123@localhost/myflaskdb'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

Defining the Database Model

Next, define a model representing a database table. For example, a simple User model:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return f'<User {self.username}>'

Creating the Database Tables

Run the following commands in a Python shell or at the end of your app.py to create the tables in PostgreSQL:

from app import db
db.create_all()

This command reads your model definitions and generates the corresponding tables in your PostgreSQL database.

Adding Routes to Insert and Display Users

Add routes to add new users and display all users stored in the database:

from flask import request, jsonify

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

    if not username or not email:
        return jsonify({'error': 'Missing username or email'}), 400

    user = User(username=username, email=email)
    db.session.add(user)
    db.session.commit()
    return jsonify({'message': f'User {username} added successfully!'}), 201

@app.route('/users', methods=['GET'])
def get_users():
    users = User.query.all()
    output = []
    for user in users:
        user_data = {'username': user.username, 'email': user.email}
        output.append(user_data)
    return jsonify({'users': output})

Running the Flask Application

Save your file and run the Flask app with:

flask run

Your app will start on http://127.0.0.1:5000/, where you can test adding and retrieving users via POST and GET requests.

Conclusion

Combining Flask with PostgreSQL offers a powerful platform for building data-driven web applications. With SQLAlchemy as an ORM, managing database interactions becomes intuitive and efficient. This tutorial gave a solid foundation for creating a Flask app backed by a PostgreSQL database, which you can extend with more features like authentication, validations, and complex queries.