Tracking Paid-Time-Off can be difficult for small and medium sized businesses. This blog will show you how to get started with the “Back-End” components of your future application, the REST API. When you decide you want to build a REST API, you are met with a swath of languages and libraries to pick from. Flask is one of the immediate options presented when you first search online. Flask is a web framework for Python that lays the groundwork for creating and configuring a web app.
In this blog post, we will look at creating an example API using “Flask”, “SQLAlchemy” for database management, and “Flask-JWT-Extended” for authentication. The API will allow users to perform CRUD (Create, Read, Update, Delete) operations on employees and their associated PTO entries. Additionally, we will implement user registration, login, token refresh functionality, and endpoints for managing employees and PTO entries.
A quick warning, the end of this tutorial features an example deployment using Heroku. Heroku requires a payment to make an account, but the charges for small scale developments are minimal.
Prerequisites
This article will assume a working understanding of Python prior to beginning. Before getting started, ensure you have Python 3.6 or higher installed on your machine. Also, make sure your IDE of choice is configured and ready to go. We will be building this project using Visual Studio Code, but any editor is fine.
While this may go without saying, do NOT test this in your production environment.
If you would like to follow along with the script, head over to GitLab and use the "Git Clone" command to download all the necessary files (script and supporting files). Then, run the script in your test environment.
Setup
Let's kick off by setting up a new Python project. Begin by creating a new directory and navigating to it.
$ mkdir flask-pto-api
$ cd flask-pto-apiWe will then initialize our virtual environment and install the necessary packages. We will use a package manager called “pip” to download these packages. Pip is a package within Python that can download packages from an external repository.
$ python -m venv venv
$ source venv\bin\activate # with PowerShell, use ./venv/Scripts/activate.ps1
$ pip install Flask Flask-SQLAlchemy Flask-JWT-Extended Flask-MigrateHere we install “Flask”, “Flask-SQLAlchemy”, “Flask-JWT-Extended", and “Flask-Migrate”. These packages will be used to create the backbone of our API. The latest versions provided by pip at the time of this tutorial are Flask 3.0, Flask-JWT-Extended 4.5.3, Flask-Migrate 4.0.5, and FlaskSQLAlchemy 3.1.1.
Flask is a web framework for Python that provides a lightweight and modular structure for rapid REST API development.
Flask-SQLAlchemy is an extension for Flask that simplifies database integration by providing SQLAlchemy support from within Python.
Flask-JWT-Extended is an extension for Flask that adds support for JSON Web Tokens (JWT), allowing user authentication and authorization.
Flask-Migrate is an extension for Flask that integrates Alembic, a database migration framework. This will automate the process of managing database schema changes as your Flask application grows.
Create a file named app.py, the foundation of our Flask application. The following snippet of code outlines the setup of app.py:
# app.py
from flask import Flask
app = Flask(__name__)
app.config['FLASK_DEBUG'] = True
def create_app():
return appFirst, we import Flask, then create the Flask application instance on the next line. Afterwards we set a Flask configuration option called ‘FLASK_DEBUG.’ It allows the server to automatically reload when changes are made to the code. It also allows for more detailed error messages during development. We will turn this off later for production, but we advise you to keep this setting as ‘True’ during development.
Database Initialization and Models
Now that our app.py file is created, it is time to develop the models for our API. In a Flask application, a data model typically refers to the representation of our application's data structure using Flask-SQLAlchemy. Models are defined as Python classes, each representing a table in the database, and they include attributes that map to the table's columns.
We will add three models to the application, “PTOEntry”, “User”, and “Employee”. The PTOEntry and Employee models will hold all our information regarding the actual PTO API. The User model will help facilitate authentication and user storage.
Below is what our app.py will look like after facilitating these changes:
# app.py
from flask import Flask, request, jsonify
from datetime import timedelta
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///pto.db' # preferred filename
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['FLASK_DEBUG'] = True
db = SQLAlchemy(app) # instantiate DB within app
migrate = Migrate(app, db) # instantiate migrations
def create_app():
# Database Models
class Employee(db.Model):
id = db.Column(db.Integer, primary_key=True) # SQL ID
name = db.Column(db.String(120), nullable=False) # Employee name
hours_balance = db.Column(db.Float, default=0.0) # Remaining PTO Balance
# Here, we link Employee to PTOEntry. This will help track an
# employees PTO balance accurately.
pto_entries = db.relationship('PTOEntry', backref='employee', lazy=True, cascade="all, delete")
# Serialization function for receiving data later
def serialize_employee(self):
return {"id": self.id,
"name": self.name,
"hours_balance": self.hours_balance,
"pto_entries": [entry.serialize_pto_entry() for entry in self.pto_entries]}
class PTOEntry(db.Model):
id = db.Column(db.Integer, primary_key=True)
hours = db.Column(db.Float, nullable=False) # Hours to subtract from employee
employee_id = db.Column(db.Integer, db.ForeignKey('employee.id'), nullable=False) # Linked employee id
def serialize_pto_entry(self):
return {"id": self.id,
"hours": self.hours,
"employee_id": self.employee_id}
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(120), nullable=False)
return appNow that we are here, it is time to initialize our Flask database. In your terminal, run the following commands:
$ flask db init
$ flask db migrate
$ flask db upgradeThe ‘flask db init’ command initializes a new migration environment. It creates a "migrations" directory in your Flask project if it doesn't exist already. This directory will be used to store migration scripts that track changes to the database schema over time.
The ‘flask db migrate’ command generates an automatic migration script based on the changes detected in the models (data models or database tables) of your Flask application. It analyzes the differences between the current state of the database and the state defined by the models and creates a migration script to apply those changes.
The ‘flask db upgrade’ command applies the changes specified in the migration scripts to the actual database. It brings the database schema up to date with the latest version defined by the models. This command is typically run after ‘flask db migrate’ to execute the generated migration script. You generally want to run ‘flask db migrate’ and ‘flask db upgrade’ any time you make changes to your data models.
User Authentication
To add security to the application, user authentication will be implemented. This will ensure only trusted connections are allowed to the API. Afterall, I wouldn’t want just anyone modifying my PTO! The authentication has 3 endpoints including “register”, “login”, and “logout”. When a user registers, Flask will initially verify the user’s information (user provides chosen username and password), and then save that user via the User model we created earlier. Once the user logs in they will receive a “JWT”, or JSON Web Token, that will be used for subsequent requests to validate the user. Once the user completes their session and logs out, the JWT token will is revoked. This ensures no unauthorized access due to a stolen token. The code below outlines the additions needed in app.py for the register, login, and logout pages:
# app.py

from flask_jwt_extended import (
JWTManager, jwt_required, create_access_token,
get_jwt_identity, get_jwt
)
#--------other app.config code---------
app.config['JWT_SECRET_KEY'] = 'bad_secret_key'
app.config['JWT_BLACKLIST_ENABLED'] = True
app.config['JWT_BLACKLIST_TOKEN_CHECKS'] = ['access', 'refresh']
jwt = JWTManager(app)
#---------other code here(models and config code)---------
# Token Blacklist
blacklisted_tokens = set()
# Routes
@app.route('/register', methods=['POST'])
def register():
data = request.get_json()
new_user = User(username=data['username'], password=data['password'])
db.session.add(new_user)
db.session.commit()
return jsonify({'message': 'User created successfully!'})
@app.route('/login', methods=['POST'])
def login():
data = request.get_json()
user = User.query.filter_by(username=data['username'], password=data['password']).first()
if user:
access_token = create_access_token(identity=user.id, expires_delta=timedelta(minutes=60))
return jsonify({'access_token':access_token})
else:
return jsonify({'message': 'Invalid credentials'}), 401
@app.route('/logout', methods=['POST'])
@jwt_required()
def logout():
jti = get_jwt()["jti"]
blacklisted_tokens.add(jti)
return jsonify({'message': 'Logged out successfully!'})
#------ rest of code below(end of create_app function) -----The code imports necessary modules from the FlaskJWTExtended library for handling JSON Web Tokens. Configuration settings for JWT are applied, including setting a secret key, enabling token blacklisting, and specifying token checks for access and refresh tokens. A JWTManager instance is created, associating it with the Flask application. The code defines routes for user registration, login, and logout. During registration, a new user is created and added to the database. During login, if valid credentials are provided, an access token is generated and returned. The logout route, protected by JWT authentication, adds the token's unique identifier (“JTI” or JWT ID) to a blacklist upon logout.
CRUD Endpoints
We will now add some basic CRUD endpoints to our app to add some functionality. Specifically, we want to be able to do CRUD operations on employees and PTO requests. For users, we only want users to be able to register, login, and logout, as we implemented in the previous section. Add the following to your app.py:
# app.py
# ---- old code above ----
# Employee Endpoints
# List all employees
@app.route('/employees', methods=['GET'])
@jwt_required()
def get_employees():
employees = Employee.query.all()
ret = []
for employee in employees:
ret.append(employee.serialize_employee())
return jsonify(ret)
# Add new employee to database
@app.route('/employees', methods=['POST'])
@jwt_required()
def add_employee():
data = request.get_json()
new_employee = Employee(**data)
db.session.add(new_employee)
db.session.commit()
return jsonify({'message': 'Employee added successfully!'})
# Update Employee PTO Balance Endpoint
@app.route('/employees/<employee_id>/', methods=['PUT'])
@jwt_required()
def update_pto_balance(employee_id):
data = request.get_json()
employee = Employee.query.get_or_404(employee_id)
new_pto_balance = data['hours_balance']
if new_pto_balance is not None:
employee.hours_balance = new_pto_balance
db.session.commit()
return jsonify({'message': 'Employee PTO balance updated successfully'})
else:
return jsonify({'message': 'Invalid data provided'}), 400
# Remove employee by id
@app.route('/employees/<employee_id>', methods=['DELETE'])
@jwt_required()
def remove_employee(employee_id):
employee = Employee.query.get(employee_id)
if employee:
db.session.delete(employee)
db.session.commit()
return jsonify({'message': 'Employee removed successfully!'})
else:
return jsonify({'message': 'Employee not found'}), 404
# PTO Entry Endpoints, this function returns all PTO Entries
@app.route('/pto', methods=['GET'])
@jwt_required()
def get_pto_entries():
ptos = PTOEntry.query.all()
ret = []
for pto in ptos:
ret.append(pto.serialize_pto_entry())
return jsonify(ret)
# PTO Entry Endpoints, this function adds a PTO Entry and updates the linked employee’s hour_balance
@app.route('/pto', methods=['POST'])
@jwt_required()
def add_pto_entry():
data = request.get_json()
employee = Employee.query.get_or_404(data["employee_id"])
if employee:
new_pto_entry = PTOEntry(hours=data['hours'], employee_id=employee.id)
db.session.add(new_pto_entry)
employee.hours_balance -= float(data['hours'])
db.session.commit()
return jsonify({'message': 'PTO entry added successfully!'})
else:
return jsonify({'message': 'Employee not found'}), 404
# PTO Entry Endpoints, this function deletes a PTO entry and updates an employee’s hour_balance
@app.route('/pto/<pto_id>', methods=['DELETE'])
@jwt_required()
def remove_pto_entry(pto_id):
pto = PTOEntry.query.get_or_404(pto_id)
if pto:
pto.employee.hours_balance += pto.hours
db.session.delete(pto)
db.session.commit()
return jsonify({'message': 'PTO entry removed successfully!'})
else:
return jsonify({'message': 'Employee not found'}), 404
#---- rest of code below-----We just did a lot to our program, let’s go over what was added.
Two routes are defined for employees: one for retrieving a list of all employees and another for adding a new employee to the database. Both routes require a valid “JSON Web Token” (JWT) for authentication.
A route is defined to update the Paid Time Off (PTO) balance of a specific employee identified by their ID. The endpoint expects a JSON payload with the new PTO balance, and the update is committed to the database if valid data is provided.
An endpoint for deleting an employee is defined. If the employee with the specified ID exists, they are removed from the database, and the operation is confirmed with a success message; otherwise, a "not found" message is returned.
Two routes are defined for managing PTO entries: one for retrieving all PTO entries and another for adding a new PTO entry. Both routes also require a valid JWT for authentication.
An endpoint is defined to remove a specific PTO entry based on its ID. If the PTO entry exists, it is deleted from the database, and the employee's PTO balance is adjusted; otherwise, a "not found" message is returned.
Now that we have added additional endpoints, we will need to restart Flask. Run the following commands in your terminal to do so:
$ flask db migrate
$ flask db upgrade
$ flask runIf everything is configured correctly, you can test by making calls to the API with your command line or Python. An example for Python could be:
import requests
import json
# assumed variables: base_url is api url string, username, password are strings
# Register
endpoint = f"{base_url}/register"
payload = {
"username":username,
"password":password
}
response = requests.post(endpoint, json=payload)
print(response.json())
# Login, receive access token
endpoint = f"{base_url}/login"
payload = {
"username":username,
"password":password
}
response = requests.post(endpoint, json=payload)
print(response.json())
token = response.json()['access_token']
# Get a list of employees
endpoint = f"{base_url}/employees"
header = {"Authorization": f"Bearer {access_token}"}
response = requests.get(endpoint, headers=header)
print(response.json())The test script is simple; A post request is sent to the “/register” and “/login” endpoints with username and password, then takes the JWT token form the login request and lists information from the “/employees” endpoint.
So far, we were able to create a basic Flask API, define our database models, create endpoints, and implement authentication with JWT management. Keep in mind that there are manual testing applications such as Insomnia, that allow you to test your API with a GUI interface. That concludes the creation of our Flask API! Keep in mind that this app is very basic, it is all stored in one file and has some security vulnerabilities.
We encourage you to explore the documentation of these packages further to find the configuration that you need. For some bonus content we will now move to a sample deployment to Heroku. There are only two prerequisites to complete before continuing with this blog, having Git installed on your machine and having a valid GitLab account. With that said, let’s go deploy an app!
Heroku Account and App Creation
Open a web browser and navigate to Heroku. In the top right corner click ‘Sign Up’ to make an account if you do not have one already. You will have to add a card to deploy an app, but keep in mind that the charges are minimal for small scale deployments. You can review Heroku’s pricing here. Once we are done, we can review putting the app in maintenance mode to avoid malicious API requests and extra charges.
Once you are successfully registered, log in to your Heroku account and click the “Create New App” (Figure 1) button on the Heroku dashboard. Create a unique app name and select a region before clicking the “create app” button. Go to your Heroku account settings and copy your API key to a text file (see Figure 2). Make sure to also copy your app name and app URL. This will be useful in the following section.
Lastly, we need to set up a database for your deployment and add an environment variable for your JWT secret key. This will add to your deployment charges, so be sure to read up on Heroku’s pricing. Go to the ‘Resources’ tab of your Heroku application. Under ‘Add-ons’ search for ‘Heroku Postgres.’ Add this to your app and agree to any prompts. This add-on will automatically include the database URL as an environment variable within Heroku. It will also handle authentication automatically. You can view the database URL in your ‘Settings’ tab, but you don’t need it.
Make sure you also go under your ‘Settings’ tab, click ‘Reveal Config Vars’, and add your JWT_SECRET_KEY with ‘JWT_SECRET_KEY’ as the key and your secret key as the value. We will modify your Python code to use this later. This will let us keep your secret key secure on a public repository.
GitLab Repository Setup and Heroku Configuration
Log in to your GitLab account and create a new repository. Back in your development environment, run the following command in your console, this will install a package and create a requirements.txt file:
$ pip install gunicorn
$ pip freeze > requirements.txtThis will create a file named ‘requirements.txt’ and automatically populate it with all the packages necessary for your project. We will use this file to install all the packages our project uses in the deployment server. “Gunicorn” is a Python package that will be used as a WSGI (Web Server Gateway Interface) for Heroku to access our application. If you want to compare, here is what our requirements.txt look like at this stage:
alembic==1.12.1
blinker==1.7.0
click==8.1.7
colorama==0.4.6
Flask==3.0.0
Flask-JWT-Extended==4.5.3
Flask-Migrate==4.0.5
Flask-SQLAlchemy==3.1.1
greenlet==3.0.1
gunicorn==21.2.0
itsdangerous==2.1.2
Jinja2==3.1.2
Mako==1.3.0
MarkupSafe==2.1.3
packaging==23.2
PyJWT==2.8.0
SQLAlchemy==2.0.23
typing_extensions==4.8.0
Werkzeug==3.0.1Next, we will push our code to the newly created repository via the commands below:
$ git remote add origin <your-gitlab-repository-url>
$ git add .
$ git commit -m "Initial commit"
$ git push -u origin masterIn your web browser, open our GitLab repository. Navigate to CI/CD Setting on the left and open the variables. Add the following variables (no quotes):
key “FLASK_ENV” with the value “production”
key “HEROKU_API_KEY” with your Heroku API key
key “HEROKU_APP_NAME” with your chosen app name
key “HEROKU_APP_URL” with your chosen app url
We will make the following changes to our app.py:
from flask_jwt_extended import (
JWTManager, jwt_required, create_access_token,
get_jwt_identity, get_jwt
)
import os
app = Flask(__name__)
# We do .replace here to account how Heroku serves their postgresql links
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL', 'sqlite:///pto.db').replace('postgres://', 'postgresql://')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['JWT_SECRET_KEY'] = os.getenv('JWT_SECRET_KEY') # We pull this from Heroku for deployment
app.config['JWT_BLACKLIST_ENABLED'] = True
app.config['JWT_BLACKLIST_TOKEN_CHECKS'] = ['access', 'refresh']
app.config['FLASK_DEBUG'] = False # We set this to False for deployment
jwt = JWTManager(app)
db = SQLAlchemy(app)
migrate = Migrate(app, db)We use ‘os.getenv()’ to pull the variables we defined in our GitLab repository as well as any variables defined in our Heroku app page.
Next, we will make a script to define the deployment from our GitLab repository. Add the following file to your root folder named ‘.gitlab-ci.yml’:
# .gitlab-ci.yml
stages:
- build
- deploy
Build:
image: python:3.11
stage: build
script:
- apt-get update -qy
- pip install -r requirements.txt
- flask db init
- flask db migrate
- flask db upgrade
Deploy:
image: ruby:latest
stage: deploy
before_script:
- gem install dpl # ruby deploy tool
script:
- dpl --provider=heroku --app=$HEROKU_APP_NAME --api-key=$HEROKU_API_KEY
environment:
name: main # make this whatever branch name you are deploying from
url: $HEROKU_APP_URLThis is a YAML file. GitLab pipelines use YAML files (typically named “.gitlab-ci.yml”) to define Continuous Integration/Continuous Deployment (CI/CD) processes for software projects. In this declarative configuration file, users specify stages, jobs, and their respective execution steps. The YAML file outlines the entire pipeline, including build, test, and deployment stages, providing a clear and reproducible sequence of actions. Stored in the project repository, the YAML file ensures version-controlled and consistent deployment processes across different environments.
Next, create a file named ‘wsgy.py’ your root directory. This is what we will connect to gunicorn. Insert the following:
from app import create_app, app, db # Assuming your Flask app is defined in app.py
app = create_app()
# You have to use app.app_context() for db.create_all() to not return an error in deployment
with app.app_context():
db.create_all()
if __name__ == "__main__":
app.run()Our last step is to make a file in your GitLab root folder named only ‘Procfile’ with no extension. All you need to put in the file is as follows:
web: gunicorn wsgi:app
This points Heroku to the file that contains our main function. Note that we also use gunicorn here. From here (granted you didn’t turn off the default “Shared Runners On” setting in CI/CD), you should be able to push to GitLab and watch your pipeline work! After the pipeline is complete, you can go to your Heroku URL and observe that your app functions!
Resources
We hope you found this step-by-step guide helpful in understanding how to build a REST API with Flask and SQLAlchemy for tracking PTO requests. By implementing such a system, businesses can streamline their processes, enhance employee satisfaction, and improve overall productivity.
Effective PTO tracking is essential for maintaining transparency, ensuring compliance, and fostering a positive work culture. If you have any questions or need assistance with building your own PTO app, please don't hesitate to reach out.