Building REST APIs using FastAPI, SQLAlchemy & Uvicorn

FastAPI with SQLAlchemy


  • Fast: Very high performance, on par with NodeJS and Go.
  • Fast to code: It allows for significant increases in development speed.
  • Easy: Designed to be easy to use and learn. Less time reading docs.
  • Short: Minimize code duplication. Multiple features from each parameter declaration. Fewer bugs.
  • Robust: Get production-ready code. With automatic interactive documentation.
  • Standards-based: It’s based on the open standards for APIs, OpenAPI and JSON Schema.


  • Swagger Editor — browser-based editor where you can write OpenAPI specs
  • Swagger UI — renders OpenAPI specs as interactive API documentation
  • Swagger Codegen — generates server stubs and client libraries from an OpenAPI spec



Creating the Item and Store REST API

API Design
  1. Setup and Installation
  2. Configure Database
  3. Create Database Models
  4. Create Schemas
  5. Application Entry Point
  6. Swagger UI
  7. Conclusion


1. Setup and Installation

$ cd /path/to/my/workspace/
$ mkdir python-sample-fastapi-application
$ cd python-sample-fastapi-application
pipenv shell --python 3.8
pipenv install fastapi==0.68.1
pipenv install uvicorn==0.15.0
pipenv install sqlalchemy==1.4.23
  • Pipfile contains all the names of the dependencies we just installed.
  • Pipfile.lock is intended to specify, based on the dependencies present in Pipfile, which specific version of those should be used, avoiding the risks of automatically upgrading dependencies that depend upon each other and breaking your project dependency tree.

2. Configure Database

  • PostgreSQL
  • MySQL
  • SQLite
  • Oracle
  • Microsoft SQL Server, and so on.
  • First, we have imported packages which are required to create SQLAlchemy engine and database session to connect to the SQLite database. The database file will be created in the same directory with the name data.db.
  • Then, we created the SQLAlchemy engine using the database created above.
    Note: connect_args={"check_same_thread":False} is only required for SQLite
  • SessionLocal class represents database session. The class itself is not a database session yet. But, once we create an instance of the SessionLocal class, this instance will be the actual database session. To create the SessionLocal class, we used the function sessionmaker from sqlachemy.orm .
  • Finally, we used the function declarative_base() that returns a class to create Base class. Later, we will inherit from this class to create each of the database models or classes(the ORM models).
  • We also defined a function called get_db() , which can used to create independent database session for each request. We will use the same session throughout the request and then close it after the request is finished. In the function get_db , yield is used to create a database session for each request. Close it after finishing the request.

3. Create Database Models
  • We started off by creating the Item Model class in line 6.
  • In line 7, we declared the table name items where this model will be mapped to.
  • From line 9 to 13, we defined the table columns along with their data types. We use Column from SQLAlchemy as the default value. Here, store_id acts as Foreign key reference for Stores .
  • From line 14 to 15, we added some helper methods to print the object at runtime.
  • In line 17, we declared the Store Model class and in line 18 we declared the table name stores where this model will be mapped to.
  • From line 19 to 20, we defined the stores table columns along with their data types.
  • In line 21 we define the relationship provided by SQLAlchemy ORM. This will become, more or less, a “magic” attribute that will contain the values from other tables related to this one.
  • From line 23 to 24, we added some helper methods to print the object at runtime.
  • We started off by creating the ItemRepo class in line 7 and StoreRepo class in line 38 .
  • From line 9 to 25, we defined some helper methods, which we can use to perform CRUD operations on Item database model.
  • From line 40 to 63, we defined some helper methods, which we can use to perform CRUD operations on Store database model.

4. Create Schemas
  • We started off by creating ItemBase Pydantic model(schema) in line 6and StoreBase Pydantic model(schema) in line 24. These classes contain the common attributes, which we need while creating or reading data. When a model attribute has a default value or is not required, then we can make that attribute optional. Here, we have used None as the default value for description in line 9 .
  • Then we added ItemCreate and StoreCreate classes, which inherit from ItemBase and StoreBase, respectively. Thus, they will have all the attributes of the Parent class, plus any additional data (attributes) needed for creation.
  • Finally, we created Pydantic models (schemas) Item and Store that will be used to read the data from the database and returning it from the API. In the Pydantic models for reading, Item and Store, we added an internal Config class. This Config class is used to provide configurations to Pydantic. In the Config class, we set the attribute orm_mode = True.

5. Application Entry Point

6. Swagger UI

Automatic Documentation with Pydantic
Automatic JSON Schema

7. Conclusion

  • Use path parameters to get a unique URL path per item
  • Receive JSON data in requests using Pydantic
  • Use API best practices like validation, serialization, and documentation
  • Integrate FastAPI with SQLAlchemy
  • Using both normal and async ways of handling requests.
  • Using Swagger in FastAPI

References & Useful Readings



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store