Database Setup - Flask tutorial for beginners (Part 2)

Introduction

In Part 1, we created our first Flask API and returned a list of jobs using a Python list.

But there's a problem.

What happens when we want to add a new job? Where should we store it? And how can we retrieve that job tomorrow after restarting the application?

We need a database.

In this article, we'll connect our Flask application to PostgreSQL and prepare our project for real database operations.

We'll also improve our project structure so that adding new APIs and features becomes easier as the application grows.

By the end of this article, you'll have:

A PostgreSQL database for JobHub.

Flask connected to PostgreSQL using SQLAlchemy.

Environment variables configured securely.

A cleaner project structure using an application factory and Blueprints.

An API endpoint that verifies the database connection.

Let's get started!

1. Why do we need a database?

In Part 1, our jobs were stored in a Python list.

jobs = [
    {
        "id": 1,
        "title": "Frontend Developer",
        "company": "Tech Solutions"
    }
]

This is fine for learning how APIs work, but it isn't suitable for a real application.

The data exists only in the application's memory. Changes won't be reliably persisted across restarts, and multiple users cannot safely manage shared records this way.

A database solves this problem by storing data persistently.

For our JobHub application, we'll use PostgreSQL.

PostgreSQL is a relational database that stores data in tables and supports relationships between records. For example, a job can belong to a company, and a user can create multiple job listings.

We'll build these relationships in upcoming articles.

2. Install the required packages

First, navigate to the project you created in Part 1 and activate your virtual environment.

On macOS or Linux:

cd jobhub-backend
source venv/bin/activate

Install the required packages:

pip install Flask-SQLAlchemy "psycopg[binary]" python-dotenv

Let's understand why we need them.

Package	Purpose
Flask-SQLAlchemy	Integrates SQLAlchemy with Flask and manages database access.
psycopg	PostgreSQL database driver used by SQLAlchemy.
python-dotenv	Loads configuration from a .env file.

What is SQLAlchemy?

SQLAlchemy is a Python SQL toolkit and ORM (Object-Relational Mapper). It lets us work with database records through Python objects and queries instead of manually writing SQL for every operation.

We'll use SQLAlchemy to create models and perform CRUD operations in upcoming articles.

Save the installed dependencies:

pip freeze > requirements.txt

3. Create a PostgreSQL database

Make sure PostgreSQL is installed and running on your machine.

We'll create a database named jobhub_dev.

If your PostgreSQL installation provides the createdb command and your local database user is configured correctly, run:

createdb jobhub_dev

Alternatively, connect using psql and run:

CREATE DATABASE jobhub_dev;

You can verify that the database exists by listing your databases:

psql -l

If these commands fail, check that PostgreSQL is running and that you're using the correct database username. Depending on your installation, you may need to connect using psql -U your_username or use your database administration tool.

At this point, we've created an empty database. We haven't created any tables yet.

We'll do that when we introduce database models and migrations.

4. Configure environment variables

We need to tell Flask how to connect to PostgreSQL.

One option is to hardcode the database URL in our Python code. However, that makes configuration harder to manage and risks exposing credentials.

Instead, we'll use environment variables.

Create a .env file in the root of your project:

jobhub-backend/
├── .env
├── venv/
├── app.py
└── requirements.txt

Add your database connection URL:

DATABASE_URL=postgresql+psycopg://postgres:your_password@localhost:5432/jobhub_dev

Replace postgres and your_password with the username and password for your local PostgreSQL installation.

Let's break down the URL:

postgresql+psycopg: Use PostgreSQL through the Psycopg 3 driver.

postgres: Database username.

your_password: Database password.

localhost: Database host.

5432: Default PostgreSQL port.

jobhub_dev: Database name.

If your password contains special characters such as @, /, or :, they may need URL encoding.

Important: Never commit your .env file to Git. Add it to .gitignore:

.env
venv/
__pycache__/

You can commit a .env.example file containing placeholder values so other developers know which environment variables are required.

5. Organize the Flask project

In Part 1, everything was inside app.py. That was fine for our first endpoint, but it becomes difficult to maintain as the application grows.

Let's introduce two important Flask concepts:

Application factory: A function that creates and configures the Flask application.

Blueprint: A way to organize related routes into reusable groups.

We'll update our project structure to look like this:

jobhub-backend/
├── app/
│   ├── __init__.py
│   ├── extensions.py
│   └── routes/
│       ├── __init__.py
│       ├── health.py
│       └── jobs.py
├── .env
├── .gitignore
├── requirements.txt
└── run.py

Let's create these files one by one.

Step 1: Initialize SQLAlchemy

Create app/extensions.py:

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

Notice that we haven't connected SQLAlchemy to a specific Flask application yet.

We'll initialize it inside our application factory. This keeps our extensions separate from application configuration.

Step 2: Create the application factory

Create app/__init__.py:

import os

from dotenv import load_dotenv
from flask import Flask

from app.extensions import db

load_dotenv()


def create_app():
    app = Flask(__name__)

    database_url = os.getenv("DATABASE_URL")

    if not database_url:
        raise RuntimeError(
            "DATABASE_URL is missing. Configure it in your .env file."
        )

    app.config["SQLALCHEMY_DATABASE_URI"] = database_url
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

    db.init_app(app)

    from app.routes.health import health_bp
    from app.routes.jobs import jobs_bp

    app.register_blueprint(health_bp)
    app.register_blueprint(jobs_bp)

    return app

Here's what's happening:

load_dotenv() loads environment variables from .env.

create_app() creates a new Flask application.

SQLALCHEMY_DATABASE_URI tells SQLAlchemy which database to connect to.

db.init_app(app) connects the SQLAlchemy extension to the Flask application.

register_blueprint() registers the routes defined in our route modules.

The SQLALCHEMY_TRACK_MODIFICATIONS setting disables an unnecessary modification-tracking feature.

We now have a reusable application factory that will be helpful when we introduce testing and different environments.

Step 3: Create the health-check route

Create app/routes/health.py:

from flask import Blueprint, jsonify

health_bp = Blueprint(
    "health",
    __name__,
    url_prefix="/api"
)


@health_bp.get("/health")
def health_check():
    return jsonify({
        "status": "success",
        "message": "JobHub API is running"
    }), 200

This creates the same health endpoint we built in Part 1, but now it's organized inside a Blueprint.

The url_prefix="/api" setting means that the route /health becomes /api/health.

Step 4: Move the jobs endpoint

Create app/routes/jobs.py:

from flask import Blueprint, jsonify

jobs_bp = Blueprint(
    "jobs",
    __name__,
    url_prefix="/api/jobs"
)


@jobs_bp.get("")
def get_jobs():
    jobs = [
        {
            "id": 1,
            "title": "Frontend Developer",
            "company": "Tech Solutions"
        },
        {
            "id": 2,
            "title": "Backend Developer",
            "company": "Digital Labs"
        }
    ]

    return jsonify(jobs), 200

We're still returning sample data. That's intentional: in this article, we're setting up the database connection, not building database-backed CRUD operations yet.

We'll replace this sample data with actual database queries in upcoming articles.

Create an empty app/routes/__init__.py file so Python recognizes the routes directory as a package.

Step 5: Create the application entry point

Create run.py in the project root:

from app import create_app

app = create_app()

This file creates the Flask application using our factory.

Our original app.py is no longer needed. You can remove it once you've moved the routes into their new files.

6. Verify the PostgreSQL connection

We've configured SQLAlchemy, but we haven't yet verified that it can communicate with PostgreSQL.

Let's create a dedicated endpoint for that.

Update app/routes/health.py:

from flask import Blueprint, jsonify
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError

from app.extensions import db

health_bp = Blueprint(
    "health",
    __name__,
    url_prefix="/api"
)


@health_bp.get("/health")
def health_check():
    return jsonify({
        "status": "success",
        "message": "JobHub API is running"
    }), 200


@health_bp.get("/health/db")
def database_health():
    try:
        with db.engine.connect() as connection:
            connection.execute(text("SELECT 1"))

        return jsonify({
            "status": "success",
            "message": "Database connection successful"
        }), 200

    except SQLAlchemyError:
        return jsonify({
            "status": "error",
            "message": "Database connection failed"
        }), 500

Let's understand the new code.

db.engine.connect() opens a database connection through SQLAlchemy.

text("SELECT 1") executes a simple SQL statement that doesn't require any tables to exist.

SQLAlchemyError lets us catch database-related errors and return a controlled response without exposing internal database details to the client.

In a production application, we'd also log the underlying exception on the server for debugging. This endpoint is a basic connection check, not a complete database monitoring system.

7. Run and test the application

Start the application using Flask's CLI:

flask --app run.py run --debug

If everything is configured correctly, Flask will start its development server, usually at http://127.0.0.1:5000.

Test the first endpoint:

curl http://127.0.0.1:5000/api/health

Expected response:

{
  "message": "JobHub API is running",
  "status": "success"
}

Now test the database connection:

curl -i http://127.0.0.1:5000/api/health/db

If the connection is successful, you should receive HTTP status 200 and a response like this:

{
  "message": "Database connection successful",
  "status": "success"
}

Finally, test the jobs endpoint:

curl http://127.0.0.1:5000/api/jobs

It should still return our two sample jobs.

If the database endpoint fails, check the following:

Is PostgreSQL running?

Does the jobhub_dev database exist?

Are the username and password in .env correct?

Is the database URL using postgresql+psycopg://?

Is your virtual environment activated, with the required packages installed?

You can also inspect the Flask server's terminal output to investigate the underlying connection error.

8. What have we accomplished?

We've taken our application from a single Python file to a more maintainable Flask project.

We now have:

A dedicated PostgreSQL database.

SQLAlchemy configured for database access.

Environment-based configuration.

An application factory.

Separate Blueprints for health and jobs routes.

An endpoint that verifies database connectivity.

One important distinction: connecting to a database is not the same as creating database tables. We haven't created any models or tables yet.

That's our next step.

Your turn: Practice challenge

Before moving to Part 3, try these tasks:

Add a GET /api/health/version endpoint that returns the API version.

Change the database name in your .env file to an invalid name and observe what happens when you test /api/health/db. Then restore the correct value.

Create a .env.example file with a placeholder DATABASE_URL.

Explain in your own words why we created extensions.py instead of initializing SQLAlchemy directly inside every route.

These small exercises will help you understand the project structure rather than simply copy the code.

What's next?

In Part 3: Designing Database Models and Relationships, we'll create our first database models:

User

Company

Job

We'll learn how primary keys, unique constraints, foreign keys, and SQLAlchemy relationships work. We'll also introduce Flask-Migrate so that we can manage database schema changes as our project evolves.

By the end of the next article, we'll be ready to move from sample data to real database-backed APIs.

Key takeaway: Flask handles our API routes, SQLAlchemy manages database access, and PostgreSQL stores our data. A clean project structure now gives us a solid foundation for building a real backend.