FastAPI Database: SQL, ORM and CRUD Operations

This blog will teach you how to connect your web API to a SQL database and perform CRUD operations using an ORM. You will learn about FastAPI, SQL, ORM, CRUD and SQLAlchemy.

Table of Contents

1. Introduction

In this blog, you will learn how to connect your web API to a database and perform CRUD operations using an ORM. You will use FastAPI, a modern and high-performance web framework for building APIs with Python, and SQLAlchemy, a popular and powerful ORM for working with relational databases.

A database is a system that stores and organizes data in a structured way. A database can be used to store information about users, products, orders, transactions, and any other data that you need for your web application. A database can also help you query, manipulate, and analyze your data efficiently and securely.

SQL is a language that allows you to communicate with a database and perform various operations on the data. SQL stands for Structured Query Language and it is the most widely used language for working with relational databases. SQL can help you create, read, update, and delete data from a database, as well as perform complex queries and calculations.

An ORM is a tool that maps the objects in your code to the tables and columns in your database. ORM stands for Object-Relational Mapping and it is a technique that simplifies the interaction between your code and your database. An ORM can help you write less SQL code, avoid errors and bugs, and improve the performance and security of your web application.

CRUD is an acronym that stands for Create, Read, Update, and Delete. CRUD operations are the basic functions that you need to perform on any data in your web application. CRUD operations can be implemented using SQL statements or ORM methods.

In this blog, you will learn how to use FastAPI and SQLAlchemy to create a web API that can perform CRUD operations on a database. You will also learn some of the benefits and features of FastAPI and SQLAlchemy, as well as some of the best practices and tips for using them effectively.

Are you ready to get started? Let’s dive in!

2. What is FastAPI?

FastAPI is a modern and high-performance web framework for building APIs with Python. It is based on the Starlette framework and the Pydantic library, which provide fast and reliable asynchronous support and data validation respectively. FastAPI is designed to be easy to use, expressive, and developer-friendly, while also offering advanced features and functionality.

Some of the benefits and features of FastAPI are:

It is fast: FastAPI is one of the fastest web frameworks available, thanks to its use of Uvicorn, a lightning-fast ASGI server. FastAPI can handle high concurrency and performance, making it suitable for scalable and demanding applications.
It is easy: FastAPI has a simple and intuitive syntax that allows you to create your API endpoints with minimal code. FastAPI also supports automatic documentation generation, which helps you document and test your API easily.
It is expressive: FastAPI allows you to define your data models and schemas using Pydantic, which enables type annotations, data validation, and serialization. FastAPI also supports dependency injection, which helps you manage your dependencies and reuse your code.
It is developer-friendly: FastAPI has a rich and comprehensive documentation that covers everything from the basics to the advanced topics. FastAPI also has a large and active community that provides support and feedback. FastAPI is compatible with many popular tools and libraries, such as SQLAlchemy, MongoDB, Redis, Celery, and more.

If you want to learn more about FastAPI, you can visit its official website at https://fastapi.tiangolo.com/, where you can find the documentation, tutorials, examples, and more.

In the next section, you will learn what a database is and why you need one for your web application.

3. What is a database?

A database is a system that stores and organizes data in a structured way. A database can be used to store information about users, products, orders, transactions, and any other data that you need for your web application. A database can also help you query, manipulate, and analyze your data efficiently and securely.

There are different types of databases, such as relational, non-relational, hierarchical, network, object-oriented, and more. Each type of database has its own advantages and disadvantages, depending on the nature and complexity of the data and the application. In this blog, we will focus on relational databases, which are the most common and widely used type of database.

A relational database is a database that stores data in tables, which consist of rows and columns. Each row represents a record or an entity, and each column represents a field or an attribute. For example, a table of users might have columns such as id, name, email, and password, and each row would contain the information of a single user.

A relational database also defines relationships between the tables, which can be one-to-one, one-to-many, or many-to-many. For example, a table of orders might have a foreign key that references the id of the user who placed the order, creating a one-to-many relationship between the users and the orders tables.

Some of the benefits and features of relational databases are:

They are structured and consistent: Relational databases enforce a schema, which defines the structure and constraints of the data. This ensures that the data is valid, consistent, and easy to query and manipulate.
They are flexible and scalable: Relational databases allow you to create, modify, and delete tables and columns as needed, without affecting the existing data. Relational databases also support transactions, which are atomic, consistent, isolated, and durable (ACID) operations that ensure the integrity and reliability of the data.
They are powerful and expressive: Relational databases support SQL, which is a language that allows you to communicate with the database and perform various operations on the data. SQL stands for Structured Query Language and it is the most widely used language for working with relational databases. SQL can help you create, read, update, and delete data from a database, as well as perform complex queries and calculations.

If you want to learn more about relational databases, you can visit this website at https://www.w3schools.com/sql/, where you can find tutorials, examples, and exercises on SQL and relational databases.

In the next section, you will learn more about SQL and how to use it to interact with your database.

4. What is SQL?

SQL is a language that allows you to communicate with a database and perform various operations on the data. SQL stands for Structured Query Language and it is the most widely used language for working with relational databases. SQL can help you create, read, update, and delete data from a database, as well as perform complex queries and calculations.

Some of the basic concepts and components of SQL are:

Data Definition Language (DDL): This is the part of SQL that allows you to define the structure and schema of the database, such as creating, altering, and dropping tables and columns.
Data Manipulation Language (DML): This is the part of SQL that allows you to manipulate the data in the database, such as inserting, updating, and deleting records.
Data Query Language (DQL): This is the part of SQL that allows you to query the data in the database, such as selecting, filtering, sorting, and grouping records.
Data Control Language (DCL): This is the part of SQL that allows you to control the access and permissions of the database, such as granting and revoking privileges and roles.
Data Transaction Language (DTL): This is the part of SQL that allows you to manage the transactions in the database, such as committing, rolling back, and saving changes.

Some of the common SQL statements and keywords are:

CREATE: This is used to create a new table or database.
ALTER: This is used to modify an existing table or database.
DROP: This is used to delete an existing table or database.
INSERT: This is used to insert a new record into a table.
UPDATE: This is used to update an existing record in a table.
DELETE: This is used to delete an existing record from a table.
SELECT: This is used to select records from a table.
WHERE: This is used to filter records based on a condition.
ORDER BY: This is used to sort records in ascending or descending order.
GROUP BY: This is used to group records based on a column or expression.
HAVING: This is used to filter groups based on a condition.
JOIN: This is used to combine records from two or more tables based on a common field.
UNION: This is used to combine the results of two or more queries into a single result set.
SUBQUERY: This is a query within another query that returns a value or a set of values.
FUNCTION: This is a predefined or user-defined operation that performs a specific task on the data.
AGGREGATE FUNCTION: This is a function that performs a calculation on a group of records and returns a single value, such as SUM, AVG, MIN, MAX, COUNT, etc.

If you want to see some examples of SQL statements and queries, you can visit this website at https://www.sqltutorial.org/, where you can find interactive tutorials, exercises, and quizzes on SQL.

In the next section, you will learn what an ORM is and how it can simplify your interaction with your database.

5. What is an ORM?

An ORM is a tool that maps the objects in your code to the tables and columns in your database. ORM stands for Object-Relational Mapping and it is a technique that simplifies the interaction between your code and your database. An ORM can help you write less SQL code, avoid errors and bugs, and improve the performance and security of your web application.

Some of the benefits and features of an ORM are:

It is abstract and convenient: An ORM allows you to work with your data as Python objects, rather than SQL statements and raw data. This makes your code more readable, maintainable, and reusable. An ORM also handles the low-level details of connecting, querying, and updating the database, so you don’t have to worry about them.
It is consistent and reliable: An ORM ensures that your data is consistent and valid, by enforcing the schema and constraints of the database. An ORM also protects your data from SQL injection attacks, by escaping and sanitizing the user input. An ORM also supports transactions, which are atomic, consistent, isolated, and durable (ACID) operations that ensure the integrity and reliability of the data.
It is flexible and powerful: An ORM allows you to perform complex queries and operations on the data, using Python methods and expressions. An ORM also supports multiple database engines, such as SQLite, PostgreSQL, MySQL, Oracle, and more. An ORM also supports advanced features and functionality, such as migrations, caching, events, signals, and more.

There are many ORM tools available for Python, such as SQLAlchemy, Django ORM, Peewee, PonyORM, and more. In this blog, we will use SQLAlchemy, which is one of the most popular and powerful ORM tools for Python.

If you want to learn more about ORM and SQLAlchemy, you can visit their official websites at https://www.fullstackpython.com/object-relational-mappers-orms.html and https://www.sqlalchemy.org/, where you can find the documentation, tutorials, examples, and more.

In the next section, you will learn what CRUD is and how it relates to your web application.

6. What is CRUD?

CRUD is an acronym that stands for Create, Read, Update, and Delete. CRUD operations are the basic functions that you need to perform on any data in your web application. CRUD operations can be implemented using SQL statements or ORM methods.

Some of the examples and explanations of CRUD operations are:

Create: This is the operation that allows you to insert a new record into a table. For example, you can use the SQL statement INSERT INTO users (name, email, password) VALUES ('Alice', 'alice@example.com', '123456') or the ORM method user = User(name='Alice', email='alice@example.com', password='123456') to create a new user record in the users table.
Read: This is the operation that allows you to select records from a table. For example, you can use the SQL statement SELECT * FROM users WHERE name = 'Alice' or the ORM method user = User.query.filter_by(name='Alice').first() to read the user record with the name Alice from the users table.
Update: This is the operation that allows you to modify an existing record in a table. For example, you can use the SQL statement UPDATE users SET password = '654321' WHERE name = 'Alice' or the ORM method user.password = '654321' to update the password of the user record with the name Alice in the users table.
Delete: This is the operation that allows you to remove an existing record from a table. For example, you can use the SQL statement DELETE FROM users WHERE name = 'Alice' or the ORM method user.delete() to delete the user record with the name Alice from the users table.

CRUD operations are essential for any web application that needs to store and manipulate data. By using SQL or ORM, you can perform CRUD operations on your database easily and securely.

In the next section, you will learn more about SQLAlchemy and how to use it to perform CRUD operations on your database.

7. What is SQLAlchemy?

SQLAlchemy is a popular and powerful ORM tool for Python. SQLAlchemy stands for SQL Alchemy and it is a library that provides a high-level abstraction of SQL and database operations. SQLAlchemy allows you to work with your data as Python objects, rather than SQL statements and raw data. SQLAlchemy also handles the low-level details of connecting, querying, and updating the database, so you don’t have to worry about them.

Some of the benefits and features of SQLAlchemy are:

It is modular and customizable: SQLAlchemy consists of two main components: the Core and the ORM. The Core is the lower-level component that provides a generic interface to the database and the SQL language. The ORM is the higher-level component that provides a declarative and object-oriented interface to the database and the data models. You can use either component separately or together, depending on your needs and preferences.
It is expressive and flexible: SQLAlchemy allows you to define your data models and schemas using Python classes and decorators, which enable type annotations, data validation, and serialization. SQLAlchemy also supports multiple query styles, such as SQL expression language, ORM query API, and raw SQL. SQLAlchemy also supports multiple database engines, such as SQLite, PostgreSQL, MySQL, Oracle, and more.
It is robust and efficient: SQLAlchemy ensures that your data is consistent and valid, by enforcing the schema and constraints of the database. SQLAlchemy also protects your data from SQL injection attacks, by escaping and sanitizing the user input. SQLAlchemy also supports transactions, which are atomic, consistent, isolated, and durable (ACID) operations that ensure the integrity and reliability of the data. SQLAlchemy also supports advanced features and functionality, such as migrations, caching, events, signals, and more.

If you want to learn more about SQLAlchemy, you can visit its official website at https://www.sqlalchemy.org/, where you can find the documentation, tutorials, examples, and more.

In the next section, you will learn how to set up a FastAPI project with SQLAlchemy and create your database.

8. How to set up a FastAPI project with SQLAlchemy?

In this section, you will learn how to set up a FastAPI project with SQLAlchemy and create your database. You will use SQLite as your database engine, which is a lightweight and portable relational database that stores data in a single file. You will also use Alembic, which is a tool that helps you manage database migrations with SQLAlchemy.

Before you start, you need to have Python 3.6 or higher installed on your system. You also need to install the following packages using pip:

fastapi: This is the web framework that you will use to create your API endpoints.
uvicorn: This is the ASGI server that you will use to run your FastAPI application.
sqlalchemy: This is the ORM tool that you will use to interact with your database.
sqlalchemy-utils: This is a helper library that provides some useful functions and classes for SQLAlchemy.
alembic: This is the tool that you will use to manage database migrations with SQLAlchemy.

To install the packages, you can run the following command in your terminal:

pip install fastapi uvicorn sqlalchemy sqlalchemy-utils alembic

Next, you need to create a new folder for your project and navigate to it. You can name the folder anything you want, but for this tutorial, we will call it fastapi-sqlalchemy. To create and enter the folder, you can run the following commands in your terminal:

mkdir fastapi-sqlalchemy
cd fastapi-sqlalchemy

Inside the folder, you need to create a new file called main.py, which will contain the code for your FastAPI application. You can use any text editor or IDE of your choice, but for this tutorial, we will use Visual Studio Code. To create and open the file, you can run the following command in your terminal:

code main.py

In the main.py file, you need to import the FastAPI class from the fastapi module and create an instance of it. You also need to import the SQLAlchemy and Alembic modules, which we will use later. You can write the following code in the file:

from fastapi import FastAPI
import sqlalchemy
import alembic

app = FastAPI()

This is the basic setup for your FastAPI project with SQLAlchemy. In the next section, you will learn how to create a database model with SQLAlchemy and define your data schema.

9. How to create a database model with SQLAlchemy?

A database model is a representation of the data and the relationships in your database. A database model defines the structure and constraints of the data, as well as the operations that can be performed on it. A database model can help you organize and manipulate your data efficiently and securely.

With SQLAlchemy, you can create a database model using Python classes and decorators, which enable type annotations, data validation, and serialization. SQLAlchemy uses a declarative and object-oriented approach to define the database model, which makes your code more readable, maintainable, and reusable.

To create a database model with SQLAlchemy, you need to follow these steps:

Import the SQLAlchemy modules and classes that you will use to create your database model. You will need the declarative_base class, which is the base class for all your model classes, and the Column, Integer, String, and ForeignKey classes, which are used to define the columns and the data types of your model classes. You can write the following code in your main.py file:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

Create an instance of the declarative_base class and assign it to a variable called Base. This will be the base class for all your model classes. You can write the following code in your main.py file:

Base = declarative_base()

Create a model class for each table that you want to create in your database. A model class is a Python class that inherits from the Base class and represents a table in your database. A model class has attributes that correspond to the columns of the table, and each attribute is defined using the Column class and the data type classes. For example, if you want to create a table called users that has columns such as id, name, email, and password, you can create a model class called User and write the following code in your main.py file:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    email = Column(String(50), unique=True, nullable=False)
    password = Column(String(50), nullable=False)

Create a model class for each relationship that you want to create in your database. A relationship is a connection between two tables that defines how they are related to each other. A relationship can be one-to-one, one-to-many, or many-to-many, depending on the cardinality of the association. To create a relationship, you need to use the ForeignKey class, which references the primary key of another table, and the relationship function, which defines the nature and direction of the association. For example, if you want to create a one-to-many relationship between the users and the orders tables, where each user can have many orders, but each order belongs to only one user, you can create a model class called Order and write the following code in your main.py file:

from sqlalchemy.orm import relationship

class Order(Base):
    __tablename__ = 'orders'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    user = relationship('User', back_populates='orders')

Note that you also need to add an attribute called orders to the User model class, which represents the collection of orders that belong to each user. You can write the following code in your main.py file:

class User(Base):
    # ...
    orders = relationship('Order', back_populates='user')

This is how you can create a database model with SQLAlchemy. In the next section, you will learn how to create a database session with SQLAlchemy and connect to your database.

10. How to create a database session with SQLAlchemy?

A database session is an object that allows you to communicate with your database and perform CRUD operations on your data. A database session manages the connection, the transactions, and the state of your data. A database session also provides an interface to the ORM layer, which allows you to work with your data as Python objects, rather than SQL statements and raw data.

With SQLAlchemy, you can create a database session using the sessionmaker class, which is a factory that creates and configures session objects. You can also use the scoped_session function, which is a helper that ensures that each thread has its own session object, and that the session object is automatically closed when the thread ends.

To create a database session with SQLAlchemy, you need to follow these steps:

Import the SQLAlchemy modules and classes that you will use to create your database session. You will need the create_engine function, which is used to create a connection to your database engine, and the sessionmaker and scoped_session classes, which are used to create and manage your session objects. You can write the following code in your main.py file:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

Create an instance of the create_engine function and assign it to a variable called engine. This will create a connection to your database engine, which in this case is SQLite. You need to pass the path to your database file as an argument to the create_engine function. If the file does not exist, it will be created automatically. You can write the following code in your main.py file:

engine = create_engine('sqlite:///fastapi-sqlalchemy.db')

Create an instance of the sessionmaker class and assign it to a variable called SessionLocal. This will create a factory that produces session objects that are bound to your database engine. You need to pass the engine variable as an argument to the sessionmaker class. You can write the following code in your main.py file:

SessionLocal = sessionmaker(bind=engine)

Create an instance of the scoped_session function and assign it to a variable called session. This will create a helper that manages the session objects for each thread. You need to pass the SessionLocal variable as an argument to the scoped_session function. You can write the following code in your main.py file:

session = scoped_session(SessionLocal)

This is how you can create a database session with SQLAlchemy. In the next section, you will learn how to perform CRUD operations with SQLAlchemy and manipulate your data.

11. How to perform CRUD operations with SQLAlchemy?

With SQLAlchemy, you can perform CRUD operations using the session object that you created in the previous section. The session object provides an interface to the ORM layer, which allows you to work with your data as Python objects, rather than SQL statements and raw data. The session object also manages the connection, the transactions, and the state of your data.

To perform CRUD operations with SQLAlchemy, you need to follow these steps:

Create an instance of your model class and assign it to a variable. This will create a new Python object that represents a new record or entity in your database. You can pass the values for the attributes of your model class as arguments to the constructor. For example, if you want to create a new user object, you can write the following code in your main.py file:

new_user = User(name='Alice', email='alice@example.com', password='123456')

Add the new object to the session object using the add method. This will mark the new object as pending, which means that it will be inserted into the database when the session is committed. You can write the following code in your main.py file:

session.add(new_user)

Commit the session using the commit method. This will execute the SQL statements that correspond to the pending operations and persist the changes to the database. You can write the following code in your main.py file:

session.commit()

Query the data from the database using the query method of the session object. This will return a query object that allows you to filter, order, group, and aggregate the data using various methods. You can also use the ORM query API or the SQL expression language to construct your queries. For example, if you want to get all the users from the database, you can write the following code in your main.py file:

users = session.query(User).all()

Update the data in the database using the update method of the query object. This will update the records that match the query criteria with the new values that you provide as a dictionary. You can also use the set method of the query object to update the records with an expression. For example, if you want to update the email of the user with the name ‘Alice’, you can write the following code in your main.py file:

session.query(User).filter(User.name == 'Alice').update({'email': 'alice@gmail.com'})
session.commit()

Delete the data from the database using the delete method of the query object. This will delete the records that match the query criteria from the database. You can also use the delete method of the session object to delete a specific object from the database. For example, if you want to delete the user with the name ‘Alice’, you can write the following code in your main.py file:

session.query(User).filter(User.name == 'Alice').delete()
session.commit()

This is how you can perform CRUD operations with SQLAlchemy. In the next and final section, you will learn how to conclude your blog and provide some useful resources for further learning.

12. Conclusion

In this blog, you have learned how to connect your web API to a database and perform CRUD operations using an ORM. You have used FastAPI, a modern and high-performance web framework for building APIs with Python, and SQLAlchemy, a popular and powerful ORM for working with relational databases.

You have learned how to:

Set up a FastAPI project with SQLAlchemy and create your database.
Create a database model with SQLAlchemy and define your data schema.
Create a database session with SQLAlchemy and connect to your database.
Perform CRUD operations with SQLAlchemy and manipulate your data.

By following this tutorial, you have gained some valuable skills and knowledge that can help you create and manage your web applications with ease and efficiency. You have also learned some of the benefits and features of FastAPI and SQLAlchemy, as well as some of the best practices and tips for using them effectively.

We hope you have enjoyed this blog and found it useful and informative. If you want to learn more about FastAPI, SQLAlchemy, or any other related topics, you can check out the following resources:

FastAPI official website: This is the official website of FastAPI, where you can find the documentation, tutorials, examples, and more.
SQLAlchemy official website: This is the official website of SQLAlchemy, where you can find the documentation, tutorials, examples, and more.
FastAPI: Build Python Web APIs with Modern Tech: This is a comprehensive and practical guide on how to build web APIs with FastAPI, covering topics such as authentication, testing, deployment, and more.
Python SQLite and SQLAlchemy: This is a detailed and hands-on tutorial on how to use SQLite and SQLAlchemy in Python, covering topics such as database creation, data manipulation, queries, and more.

Thank you for reading this blog and we hope to see you again soon!