Building Fast and Robust APIs with FastAPI and SQL Databases

In the realm of web development, the creation of fast and dependable APIs is an essential aspect. One noteworthy and modern Python web framework that has gained popularity among developers is FastAPI. This framework is known for its swiftness, user-friendliness, and robustness. When it comes to storing and retrieving data, SQL databases are a common choice due to their reliability and maturity. This article will delve into using FastAPI in tandem with SQL databases to create powerful and efficient APIs.

FastAPI: A Brief Introduction

FastAPI is a web framework that facilitates speedy API development with Python. It stands out for its high performance, automatic validation, and automatic documentation generation. With FastAPI, you can build RESTful APIs with minimal boilerplate code, which makes it an ideal choice for projects of all sizes.

Installation

You can install FastAPI, Uvicorn and Sqlalchemy using pip:

pip install "fastapi[all]" uvicorn[standard] sqlalchemy

make a main.py add following to that

You can run this application with:

uvicorn main:app --reload

Visit local host address displayed in terminal and you’ll see the “Hello, World!” message.

SQL Databases with FastAPI

Integrating SQL databases with FastAPI is straightforward, and you have multiple choices for your database system. For this example, we’ll use SQLite, a lightweight and serverless database.

SQLAlchemy

It’s a library that offers a range of tools for interacting with databases. You can use a low-level SQL interface to write custom queries, or a high-level ORM to define and manipulate database structures with Python classes and objects. SQLAlchemy is a popular choice for simplifying database operations, supporting multiple database backends, and promoting more efficient ways of working with relational databases in Python applications.

ORM (Object Relational Mapper)

In SQLAlchemy, you can use an Object Relational Mapper (ORM) to simplify your interactions with databases. This high-level abstraction allows you to work with Python objects and classes that directly correspond to database tables and relationships. By defining your database models using Python classes, you can specify the schema, relationships, and constraints. The ORM in SQLAlchemy makes it easy to query, insert, update, and delete records in the database without having to write underlying SQL queries. This tool bridges the gap between the object-oriented world of Python and the relational world of databases, making it simpler and more intuitive to work with databases in your Python applications.

Configuration of a SQLAlchemy Database

Let’s configure a SQLite database and set up the connection :

Above Python script configures a SQLAlchemy-based database interaction environment. It defines a DATABASE_URL pointing to an SQLite database file, creates a database engine with additional configuration for SQLite thread safety, establishes a session factory with options to control transaction behavior, and sets up a base class for declarative database models. This configuration is essential for connecting to and interacting with a SQLite database using SQLAlchemy, a popular Object-Relational Mapping (ORM) library in Python.

Definition of a SQLAlchemy Database Model

Then we have to model the database tables :

In above code snippet, a SQLAlchemy database model class called User is defined, inheriting from the Base class, which was imported from a module named database we defined before. This class represents a database table named “users.” It contains four columns: id, name, email, and nickname. The id column is defined as an integer and serves as the primary key for the table, ensuring each row has a unique identifier. The other columns, name, email, and nickname, are defined as string fields. This class and its column definitions provide a structured blueprint for creating and interacting with a “users” table in the database using SQLAlchemy, specifying the data types and constraints for each column.

Defining a Data Schema

Now we have to define schema:

In the above code , a Pydantic BaseModel named UserSchema is defined, serving as a structured data schema for representing user information. It consists of four attributes: id (an integer for the user’s unique identifier), name (a string for the user’s name), email (a string for the user’s email address), and nickname (a string for the user’s nickname). Pydantic’s BaseModel offers data validation and serialization capabilities, making it well-suited for tasks like validating and parsing incoming data in web applications, as well as serializing data for API responses, ensuring that data adheres to the specified schema.

Modify main.py

As the last step we have to modify main.py for handle the requests:

This code establishes a FastAPI application for managing user data with an SQLite database backend. It begins by importing necessary modules and creating database tables based on SQLAlchemy models. The FastAPI application instance is initialized, and a function called get_db is defined to manage database sessions. The application has three route handlers: one for a root URL that responds with a “Hello, World!” message, another for adding user data through an HTTP POST request, and a third for retrieving user data based on the provided username via an HTTP GET request. SQLAlchemy is used for database interactions, while Pydantic sachems ensure data validation and serialization. The get_db function manages the database sessions to maintain proper resource handling throughout the application.

Testing Application

Quickly navigate the URL provided in terminal and enter ‘docs’ to end of the URL(example). Then you will see a window like follow

Then you can change the parameters and add users.Although you can get user by username.You can use database browser to check whether actual data is stored in above database !

GitHub Source Files

You can find all source codes from here!

Conclusion

FastAPI, combined with SQL databases like SQLite, provides an efficient and developer-friendly way to create powerful APIs. In this article, we’ve covered the basics of setting up FastAPI and integrating it with an SQL database. With FastAPI’s automatic validation and documentation generation, you can focus on building your API’s functionality while ensuring it remains robust and performance.