Building a CRUD FastAPI app with SQLAlchemy

February 28, 2023

Engineering

Learning how to build web apps with FastAPI — a modern Python web framework that allows you to build high-performance web apps — is worth your time. FastAPI is easy to learn, fast to code, and ready for production.

In this tutorial, you ‘ll learn how to build the backend of a basic app using FastAPI with a database set up with SQLAlchemy. The app will be a CRUD web app in which you’ll learn the basics of how to use API requests to do the common CRUD operations: create, read, update, and delete. You will build a todo app that’s easy to build and fun to learn. It will teach you the foundations of how to access FastAPI endpoints.

Prerequisites

To follow along with this tutorial, you should understand:

  • Some basic knowledge of Python
  • What SQLAlchemy is and what an object-relational mapper (ORM) is
  • (Optional) How to set up a database, especially PostgreSQL

Be sure to fork this repo to be able to use it. You can also follow along step-by-step to produce the same result.

FastAPI setup

To set up FastAPI for a basic project, you need to install two things from pip: FastAPI and Uvicorn. FastAPI is the API we will be using and that’s why we’re here. But what is Uvicorn? It’s an asynchronous server gateway interface (ASGI). In plain English, it’s a Python channel between the server and the client, and it can communicate between the two in an asynchronous way. So it fits well with FastAPI because the latter is an async framework. Thus, a Uvicorn web server can delegate workers to process FastAPI requests concurrently.

Before installing both, you need to create a virtual environment if you’re building this app locally:

python3 -m venv venv
. venv/bin/activate
pip install --upgrade pip
Now, let's install the two with pip:
pip install fastapi uvicorn

Design the app

To picture what you’re going to build at the end of this tutorial, let’s see what the app will look like:

CRUD FastAPI app

As you can see, the backend of this app consists of five endpoints.

  • One POST request for the create operation
  • Two GET requests for the read operation
  • One PUT request for the update operation
  • And one DELETE request for the delete operation

Let’s set up the database and start configuring our data.

Set up the database

If you already have a database set up, you might need to skip this section.

On Linux

To create a PostgreSQL database on Linux, make sure the PostgreSQL service is running on your machine. To do that, open your terminal and run the following:

$ sudo systemctl status postgresql

If it’s active, you’re good to go and skip the next command. If it’s not, you may want to start the service first:

$ sudo systemctl start postgresql

Once that’s done, create a new database:

$ sudo -u postgres psql -c "CREATE DATABASE mydb;"
[sudo] password for :     
CREATE DATABASE

Assuming that the username of your Postgres database engine is postgres, a new database is created called mydb.

On Mac

Similarly, if you’re using Mac OS, you might need to run the PostgreSQL service from your terminal:

$ brew services start postgresql

Next, enter the PostgreSQL client command through the terminal-based interface psql. You can do a similar command to the one discussed in the Linux subsection or break this command down into two subcommand:

psql postgres

Now, enter the psql CLI and then create a new database inside that interface:

CREATE DATABASE mydb;

Here, we’re sure there is a database called mydb because we will use this database in the next sections. If you’d prefer, you can use your own database that you set up with your desired database engine.

With Docker

If you don’t want to run PostgreSQL directly on your machine, you could also utilize the official Docker image to the same effect. Unfortunately, configuring that setup is outside the scope of this tutorial.

Set up SQLAlchemy

One useful option for database model management in FastAPI is SQLAlchemy. I’ll use its ORM to facilitate accessing databases with writing objects that Python is familiar with. We need to install two things: SQLAlchemy and psycopg2,  which is a driver to interact with the Postgres database from Python.

pip install sqlalchemy psycopg2-binary

Note: I have installed psycopg2-binary instead of psycopg2 to avoid the warning of renaming the library. In that warning, there’s a recommendation to use the binary version.

Your first FastAPI

Let’s start building the app. Create a new folder:

mkdir todo_fastapi

Navigate into that folder and create a new file called main.py:

cd todo_fastapi
touch main.py

Open your main.py file and build your first app with the following few lines:

from fastapi import FastAPI

app = FastAPI()

@app.get("/")
def home():
    return {"message": "First FastAPI app"}

Open your terminal again and enter the following:

uvicorn main:app --reload

Note: The main here points to the main.py script, while the app is the FastAPI instance inside the main.py file. We follow this uvicorn argument with --reload to enable reloading the server after any change happens to the app.Go to the browser and enter the following URL localhost:8000. You should see the following expected JSON response:

{"message":"First FastAPI app"}

Automated documentation

FastAPI provides automated documentation created by Swagger UI which allows you to test your API right from the browser; you don’t need Postman to test your FastAPI endpoints anymore.

To check it out, head over to localhost:8000/docs URL. Here’s how it looks with the home endpoint that we created in the last Python snippet:

CRUD FastAPI app

You can try it out and test the API by clicking on the Try it out button. You’ll see no parameters needed for this endpoint as there are no arguments sent to the home() function. Click on the Execute button. You’ll get the response body as expected:

CRUD FastAPI app

SQLAlchemy session

To be able to interact with the Postgres database using SQLAlchemy, you need to set up the session:

from sqlalchemy import create_engine
from sqlalchemy.engine import URL
from sqlalchemy.orm import sessionmaker


url = URL.create(
    drivername="postgresql",
    username="postgres",
    password="",
    host="localhost",
    database="mydb",
    port=5432
)

engine = create_engine(url)
Session = sessionmaker(bind=engine)
session = Session()

As you can see, the url object is defined as the string URL of the database that you should connect to. It has a postgresql as the driver and postgres as the username. Change the password to your own. If you’re using a host other than the localhost, change it as well. The mydb is the database that we set up and the default port is 5432.

Note: You may omit the username and password fields to be default if you don’t have a specific username or password attached to your PostgreSQL. However, leaving both fields blank is not encouraged in a production environment. Just consider it for development.

The engine instance is very important. It’s the starting point of any SQLAlchemy application. We use it to instantiate a session that establishes all conversations with the database.

Model the data

An important step to deal with web apps that use data is data modeling. In our basic todo app, we need just the note/text that we want to write and whether that todo item is completed or not.

So, let’s model it that way:

from sqlalchemy import Column, Integer, String, Boolean
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class Todo(Base):
    __tablename__ = "todos"

    id = Column(Integer, primary_key=True)
    text = Column(String)
    is_done = Column(Boolean, default=False)


Base.metadata.create_all(engine)

One way to create tables using SQLAlchemy is to do a declarative mapping. You’ll map classes with specific configurations; each class should inherit from the Base class. This Base class is constructed from the declarative mapper, declarative_base().

Inside the Todo class, you’ll find the definitions of each attribute and their data types.

Finally, the Base.metadata.create_all(engine) creates all table objects. In this case, it will just create one table which is the todos table.

CRUD operations

Now, let’s see how we can link SQLAlchemy with FastAPI to build our CRUD app endpoints. First, get rid of the home endpoint; we’ll set up new functions for each operation.

Create

Let’s create a new todo:

@app.post("/create")
async def create_todo(text: str, is_complete: bool = False):
    todo = Todo(text=text, is_done=is_complete)
    session.add(todo)
    session.commit()
    return {"todo added": todo.text}

Moving forward, we’ll use async with each request to allow asynchronous calls.

This /create endpoint establishes a POST request to the API with the create_todo() function. This function has two arguments: text which is a required string data type and is_complete as an optional boolean data type with a default value of False.

Notice here when an argument of a route function does not have an = sign, it’s considered a required field.

Now, the todo object is instantiated from a Todo class having the text and is_done arguments. You’ll have an id attribute automatically generated for each request as it’s set up as a primary key when we modeled the database.

Using session.add(todo) adds that todo object to the Postgres database. But you can’t see it in your database yet until you commit it using session.commit().

Let’s open the Swagger UI using localhost:8000/docs on your browser and test this create operation.

Click on the Try it out button and enter the text that you want to do. See if you’ve already finished that task or not by selecting the value of is_done field from the dropdown.

You should see a JSON response as expected returning a key-value pair of a “todo added” with the value of the task text you entered.

Read

Let’s set up a GET request with the following:

@app.get("/")
async def get_all_todos():
    todos_query = session.query(Todo)
    return todos_query.all()

Now, todos_query is a Query object. Calling all() method on this object allows you to do a SELECT * operation. So, this request should query all records of the todos table. A response would look like a list of JSON responses.

Practice with the Swagger interface and test creating some todos and fetching them back with this GET request.

You want to do a bit more complicated request and get all completed tasks. So, send another GET request:

@app.get("/done")
async def list_done_todos():
    todos_query = session.query(Todo)
    done_todos_query = todos_query.filter(Todo.is_done==True)
    return done_todos_query.all()

This /done endpoint should list all done todos. The done_todos_query is another query object that filters all True values of the is_done attribute. Calling the all() method in the return statement should list all the done tasks.

Update

You entered a todo with a typo or you want to update the status and mark it as done. You can send a PUT request to do your desired update:

@app.put("/update/{id}")
async def update_todo(
    id: int,
    new_text: str = "",
    is_complete: bool = False
):
    todo_query = session.query(Todo).filter(Todo.id==id)
    todo = todo_query.first()
    if new_text:
        todo.text = new_text
    todo.is_done = is_complete
    session.add(todo)
    session.commit()

The new_text is the task string that you want to update the todo to say. It’s set as an empty string by default because you might need to just update the is_done attribute. We make a condition for that. If it’s an empty string, the todo.text should stay the same. But if this attribute is changed, the text attribute should have the new_text value.

The todo_query is the Query object representing the todo item with the associated id. This id is passed in the path parameter inside the endpoint /update/{id}. Calling first() method on the todo_query object converts it to a Table object and fetches the first record of that query. This record is the one associated with the passed id.

We then check for the new_text to make sure it has a non-empty string. If so, the todo.text now has that newly updated text. This means the text attribute in the todos table will be changed for that particular id.

The todo.is_done is then assigned to the is_complete value passed in the argument and the change is committed to the database. The final return is the todo response for the id passed.

Delete

In the todo app, the user may want to delete a todo item. You can implement a deletion method like so:

@app.delete("/delete/{id}")
async def delete_todo(id: int):
    todo = session.query(Todo).filter(Todo.id==id).first() # Todo object
    session.delete(todo)
    session.commit()
    return {"todo deleted": todo.text}

Like the update operation, you need a unique identifier to be able to fetch that record from the database and delete it. That’s why we pass the id argument to the path parameter /delete/{id}. After you fetch the todo object from the database, you can delete it with session.delete(todo) and then commit it to the database using session.commit().

At the end of the day, here is my todo list I experimented with:

todo list build as a CRUD FastAPI app

What’s yours?

Restructure the app

Organizing your app helps you to refactor it easily — especially when it gets bigger. Let’s restructure this app in a modular way. To do this, we will create two simple modules instead of the all-in-one main.py script we used.

We will divide the SQLAlchemy models into the models.py module, and the main entry point of the app main.py which contains the routes.

You can consult it in this repo. You’ll find an additional script, other than what we mentioned, called insert_db.py which, as the name suggests, inserts data into the database through SQLAlchemy.

Conclusion

This practical tutorial covered how to build a Todo app with FastAPI. We talked about how to do basic operations like CRUD to be able to create, read, update, and delete fields from our app. We used SQLAlchemy as an ORM to communicate with the PostgreSQL database engine. We’ve also seen how FastAPI provides automated documentation through Swagger and learned how to structure your FastAPI project to be maintainable and organized.

Now, it’s your turn to put these learnings into practice! If you like content like this, browse the Mattermost library and continue your learning.

This blog post was created as part of the Mattermost Community Writing Program and is published under the CC BY-NC-SA 4.0 license. To learn more about the Mattermost Community Writing Program, check this out.

Read More Engineering Articles