Continuing with our Python FastAPI learning series, in this installment, we enable database support for MySQL, PostgreSQL, and MariaDB. We will not add any new functionality; instead, the existing authentication process will check user information from a proper database instead of mock hard-coded data. We will also add a business logic layer responsible for data validation, enforcing business rules, etc.

🐍 Index of the Complete Series.

119-feature-image.png
Python FastAPI: Enabling Database Support

The code requires Python 3.12.4. Please refer to the following discussion on how to upgrade to Python 3.12.4.

🚀 Please note, complete code for this post can be downloaded from GitHub with: 

git clone -b v0.7.0 https://github.com/behai-nguyen/fastapi_learning.git

❶ In this post, we implement database support for the following databases: MySQL, PostgreSQL, and MariaDB. In this revision of the code, we limit database operations to only logging in. That is, we are not adding any new functionality; instead, we remove the hard-coded mock data and use proper databases.

We also make provisional plans for the application to do full CRUD operations later on. We have already implemented the controllers layer, and the models or database layer. We are going to have a new business logic layer (./businesses), which will sit between the controllers and the models layers. Please see the diagram below:

119-01-app-layers.png

⓵ The controllers layer: As we have already seen, this is where all the endpoint method handlers are implemented, and where all JSON and HTML responses are constructed and returned.

👉 Please note that HTML responses are technically views, which is another layer in the Model–view–controller software design pattern. The templates area can be thought of as the view layer.

The endpoint method handlers might inject some custom data into the incoming request data. They call methods in the ./businesses area to do all the necessary work.

⓶ The business (./businesses) logic layer: Methods in this layer will first carry out basic data validation on the submitted data. If there is any error, it will send back an error response, and that will be the end of the request. Otherwise, it will next carry out business rules validation and any required calculations on the submitted data. If a failure occurs, it will respond with an error as in the first step.

Finally, it will call methods in the models</a> or database layer to perform CRUD operations on the final data. CRUD operations could also result in either a successful response or a failure one.

⓷ The models or database layer: A model is a database table, and it includes all CRUD methods necessary to operate this table. This layer does not have any logic or intelligence; it receives some data and communicates with the target database to carry out CRUD operations on the given data.

👉 Please note, this pattern is also used in the two examples provided for the bh-database wrapper classes for SQLAlchemy. The code for the business layer is, in fact, taken from these two examples.

❷ The source database is the MySQL test data released by Oracle Corporation, downloadable from this GitHub repository. The employees table does not have the email and password columns, so we have to create and populate them ourselves. Let’s start with the source database.

● MySQL 🐬 is the source or starting database. There are two ways to create and populate the email and password columns:

  1. A more involved and proper method is discussed in the section Add new fields email and password to the employees table of a Rust post. It does not involve coding in Rust, but uses a Rust-related tool.
  2. A shortcut method is to run the following script 20231128234321_emp_email_pwd.up.sql on the source database.

● To create an equivalent PostgreSQL 🐘 database from a MySQL database, my preferred method is discussed in the post pgloader Docker: migrating from Docker & localhost MySQL to localhost PostgreSQL. You can use whatever method you feel comfortable with.

● To create an equivalent MariaDB 🦭 database from a MySQL database, use MySQL tools. I backed up a MySQL database and restored the backup content to a MariaDB database. We have discussed this in a previous post.

❸ The full updated structure of the project is outlined below.

– Please note, those marked with ★ are updated, and those marked with ☆ are new.

/home/behai/fastapi_learning/
.
├── cert
│   ├── cert.pem
│   └── key.pem
├── .env
├── logger_config.yaml
├── main.py ★
├── pyproject.toml ★
├── pytest.ini ★
├── README.md ★
├── src
│   └── fastapi_learning
│       ├── businesses ☆
│       │   ├── app_business.py
│       │   ├── base_business.py
│       │   ├── base_validation.py
│       │   ├── employees_mgr.py
│       │   └── employees_validation.py
│       ├── common
│       │   ├── consts.py ★
│       │   └── queue_logging.py
│       ├── controllers
│       │   ├── admin.py ★
│       │   ├── auth.py ★
│       │   └── __init__.py ★
│       ├── __init__.py
│       ├── models
│       │   └── employees.py ★
│       ├── static
│       │   └── styles.css
│       └── templates
│           ├── admin
│           │   └── me.html ★
│           ├── auth
│           │   ├── home.html
│           │   └── login.html
│           └── base.html
└── tests
    ├── business ☆
    │   └── test_employees_mgr.py
    ├── conftest.py
    ├── __init__.py
    ├── integration
    │   ├── test_admin_itgt.py ★
    │   ├── test_api_itgt.py ★
    │   └── test_auth_itgt.py ★
    ├── README.md
    └── unit ☆
        └── test_employees.py

❹ In this section, we will discuss the code changes.

⓵ In the last check-in of the environment .env file, we have already included the database connection information:

9
10
11
12

SQLALCHEMY_DATABASE_SCHEMA = employees
SQLALCHEMY_DATABASE_URI = mysql+mysqlconnector://root:pcb.2176310315865259@localhost:3306/employees
# Enable this for PostgreSQL.
# SQLALCHEMY_DATABASE_URI = postgresql+psycopg2://postgres:pcb.2176310315865259@localhost/employees

As discussed in this post, the MariaDB database connection string is identical to MySQL. Just substitute the connection information. I added the MariaDB entry locally, and I did not check it in.

⓶ The pyproject.toml file now includes the following required packages:

● werkzeug: We need the datastructures.MultiDict class.

● python-dotenv: To load the environment file .env into environment variables.

● wtforms: We use the data validation feature to validate the HTTP submitted data.

● bh-database: Provides some convenient classes to perform CRUD operations on database tables. As mentioned previously, we support MySQL, PostgreSQL, and MariaDB. That is the reason why we include both mysql-connector-python and psycopg2-binary.

● bh_apistatus: Provides some convenient classes to wrap returned results from method calls.

⓷ Within the main.py module, in async def lifespan(app: FastAPI), we establish a connection to the database. If the connection fails, we raise a RuntimeError exception and terminate the application:

48
49
50
51
52
53
54
55
56
57
58
59

    Database.disconnect()

    # It is the responsibility of the caller to handle this exception.
    try:
        Database.connect(os.environ.get('SQLALCHEMY_DATABASE_URI'), 
                         os.environ.get('SQLALCHEMY_DATABASE_SCHEMA'))
    except Exception as e:
        logger_fn.exception(str(e))
        logger_fn.error('Attempt to terminate the application now.')
        # raise RuntimeError(...) flushes any pending loggings and 
        # also terminates the application.        
        raise RuntimeError('Failed to connect to the target database.')

Please note that, prior to establishing the database connection, the main.py module has already loaded the environment .env file:

74

load_dotenv( os.path.join(os.getcwd(), '.env') )

⓸ The employees.py module has been completely rewritten. All mock data and existing models were removed. It now has the following new classes:

● class Employees(WriteCapableTable): Represents the employees database table.

● class LoggedInEmployee(BaseModel): The equivalent of the removed class class UserInDB(User) in the tutorial’s original example.

Please note that having both a SQLAlchemy model and a Pydantic “logical” model is also demonstrated in the official documentation page Pydantic Models.

⓹ We now describe each module within the business logic (./businesses) layer:

● base_business.py: Implements an abstract class. The core functionality is the template def write_to_database(self, data), which implements a consistent approach to data validation, data calculation, and finally writing the data to the database. This template method calls other abstract methods to perform each of the mentioned tasks. Descendant classes must implement all these abstract methods. If a method is not required, it should just return a make_status() result.

● app_business.py: Provides a common parent business class for all application business logic classes. This module implements the AppBusiness class, which is a direct descendant of the above BaseBusiness class. AppBusiness implements all abstract methods mentioned above to simply return a make_status() result.

● base_validation.py: Implements the following:

  1. The application common BaseValidationForm class: Wraps the wtforms' Form class and provides additional capabilities such as label substitution and field re-ordering.
  2. def validate(data: dict, forms: list) -> ResultStatus: Validates the data coming from the controllers layer as described previously.

● employees_validation.py: It should be self-explanatory.

● employees_mgr.py: Responsible for managing data and business logic associated with the employees table.

In this revision of the code, it has only two public methods: a method to retrieve a specific employee by email, and a method to log in an employee using an email and a password. These methods implement all basic data validations; presently, there are no business logics.

We are not yet writing new records into the employees table. However, we implement the abstract methods provided by the AppBusiness class, even though they are not in use yet. This is part of the CRUD provisional plans previously mentioned.

⓺ The changes in each of the existing controllers modules are discussed in the sections below:

● auth.py: All mock code has been removed. Most importantly, in the login(...) endpoint handler method, we call the EmployeesManager.login(...) method to serve the login request as described previously.

● admin.py: All mock code has been removed. There are two important refactorings:

  1. In the get_current_user(...) method, we call the EmployeesManager.select_by_email(...) method to retrieve the currently logged-in user (employee) information. We could have cached this information in the web session and retrieved it from there. However, the logged-in user can update their information after logging in. Reading the information from the database will always provide the most up-to-date information.

    💥 We need to consider whether or not to allow the logged-in user to change their email. Right now, the email is the username. If they are able to change their email after logging in, then this call will fail. This would be the most embarrassing failure.
  2. In the read_users_me(...) endpoint handler method, we handle the error where the logged-in user could not retrieve their information. In this case, instead of passing a dictionary that represents the class LoggedInEmployee(BaseModel) class to the template, we pass an error dictionary instead.

● __init__.py: We made the following additions:

  1. Added the valid_logged_in_employee(...) template method. Please refer to the discussions on the potential issue with enabling the logged-in user to update their email, and how we pre-emptively handle this potential issue. This new method is called by the admin/me.html template to check if the context dictionary represents a logged-in employee or an error condition.
  2. Added the is_logged_in(...) helper method. This is the previous __is_logged_in(...) method.

⓻ In the templates layer, we made changes only to the admin/me.html template. Please refer to the previous discussion for details on what the changes were.

⓼ The new tests/unit directory: Tests the models</a> or database layer modules. Presently there is only a single employees.py module. The two test methods should be self-explanatory.

⓽ The new tests/business directory: Contains tests for the business logic layer (./businesses). There are only tests for the employees_mgr.py module. Since this module utilises every other module under this layer, its tests effectively cover every other module as well.

The tests are long because we test all possible code paths. However, the tests are independent and should be easy to read.

⓾ The existing tests/integration directory: All existing tests remain in place with minor updates to the test conditions, field names renamed, and new fields added.

We added the following new tests to the test_auth_itgt.py module test_integration_login_bad_email_html(...), test_integration_login_bad_email_json(...), and test_integration_login_bad_password_json(...).

🚀 It should be noted that tests under the tests/business layer and tests/integration can overlap since the controllers layer just calls the business logic layer (./businesses). If we want to exclude overlapping tests, we should exclude tests from the tests/integration directory, never from the tests/business directory.

❺ The Swagger UI and our existing custom UIs are still functioning as they did in previous revisions. Please see the two illustrated screenshots below:


❻ We conclude our discussions. We now have the database support in place. We will implement database writing in a future revision. I am not sure what we will discuss next in this series, but there are certainly a few more topics that I would like to explore.

Thank you for reading. I hope you find the information in this post useful. Stay safe, as always.

✿✿✿

Feature image source:

🐍 Index of the Complete Series.