Easy Development with FastAPI (Connecting to Database)

Tech Talk

by Engineers of Acroquest Myanmar Technology

Easy Development with FastAPI (Connecting to Database)

Hello, this is Thin Pyai from Acroquest Myanmar Technology. Now, I am working as a technical architect. 

In the previous topic, I introduced
1. what FastAPI is and
2. how to start using it

In this blog, I am going to talk how to retrieve data from SQLite database.

I will continue to develop the previous example, named “Book Store “.
You can check last contents at https://www.acromyanmar.com/easy-development-with-fastapi/

FastAPI libraries

We will do the following steps.

1Setup DB connections Connect to SQL Lite DB. 
2Define model  Define Book model. 
3Map the model and table  Map the Book model and books table. 
4Refine getBook endpoint  Replace the sample data with retrieved data from DB 
5Develop addBook endpoint  Develop addBook endpoint to register books. 
6Access endpoints  Access endpoints from GraphQL console 

1) Setup DB

Create a file named database.py where I will setup the database connections. 
I use SQLite Database, named sql_app.db. Please see detail explanation at https://fastapi.tiangolo.com/advanced/async-sql-databases/#import-and-set-up-databases 
For SQLite, you can read more at https://www.sqlite.org/index.html

 from sqlalchemy import create_engine
 from sqlalchemy.orm import sessionmaker
 SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
 engine = create_engine(
     SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
 SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) 

2) Define models

Create models.py.
I will define a book model.
For detail , please see https://fastapi.tiangolo.com/advanced/async-sql-databases/#import-and-set-up-sqlalchemy

 Import uuid
 class Book():
     id: int
     title: str
     author: str
     type: str 

3) Map the model and table

To map the class definition, new table and mapper, I used declarative_base() of SQLAlchemy. Firstly I defined Base object at database.py. Please see detail at https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/basic_use.html

 from sqlalchemy.ext.declarative import declarative_base
 Base = declarative_base() 

And, by using Base object from database.py, in models.py, I map the model (Book) and table (books). Each of the field in model becomes the column of table. FastAPI support auto increment for Id when primary_key is Integer.

 from sqlalchemy import Column, Integer, String
 from database import Base
 class Book(Base):
     __tablename__ = "books"
     id = Column(Integer, primary_key=True, index=True)
     title = Column(String, unique=True, index=True)
     author = Column(String)
     type = Column(String) 

4) Refine getBook endpoint

  1. Replace sample data with the data retrieved from database.
    In crud.py, define a function named get_book(). get_book() will retrieve data a record by id.
    I used get() because of retrieving with its primary_key. For filtering with columns, you can try filter(). https://docs.sqlalchemy.org/en/14/orm/tutorial.html#common-filter-operators
    For querying, please read in detail at https://docs.sqlalchemy.org/en/14/orm/tutorial.html#common-filter-operators
 from sqlalchemy.orm import Session
 from models import Book
 def get_book(db: Session, id: int) -> Book:
     return db.query(Book).get(id) 
  1. Define custom context_getter
    In main.py, I will define a custom context_getter() function. context_getter is optional FastAPI dependency for providing custom context value. For detail, please read at https://strawberry.rocks/docs/integrations/fastapi#context_getter .
async def get_context():
     return {
         'key': ‘value’
 graphql_app = GraphQLRouter(schema, context_getter=get_context) 
  1. Inject database connection in custom context_getter
    Let’s do dependency injection for using database that we setup in database.py. To integrate with other components with FastAPI, FastAPI provides a simple way to use. For detail, please refer to https://fastapi.tiangolo.com/tutorial/dependencies/
from fastapi import Depends, FastAPI
 def get_db():
     db = SessionLocal()
         yield db
 async def get_context( db=Depends(get_db)):
     return {
         'db': db
  1. Get db connection from Info context
    From our custom context dependency, get the database session.  And, pass it to get_book() of crud.py
 class Query:
     def get_book(self, id: int, info: Info):
         book = crud.get_book(info.context['db'], id) 
         return {
             'title': 'The End We Start From',
             'author': 'Megan Hunter',
             'type': 'fiction'
  1. Define types to return Data
    In type.py, define BookDto, which extend BaseModel of pydantic, and BookType, which is strawberry type. As BookType extends BookDto, BookType get the validation features which is supporting by pydatic. It is still an experimental feature of Strawberry. It is still difficult to apply in your project. As this is an interesting feature, I will introduce it in this blog.
    And, you can use types from main strawberry API. If so, this topic will help you. https://strawberry.rocks/docs/general/schema-basics
 from typing import Optional
 from fastapi import FastAPI
 from pydantic import BaseModel
 import strawberry
 app = FastAPI()
 class BookDto(BaseModel):
     title: str 
     author: str 
     type: str
     id: Optional[int]
 class BookType:
     title: strawberry.auto
     author: strawberry.auto
     type: strawberry.auto
     id: strawberry.auto 
  1. Use type in response
    In main.py, use BookType as the response format.
from type import BookDto, BookType
 class Query:
     def get_book(self, id: int, info: Info) -> BookType:
         return BookDto(**crud.get_book(info.context['db'], id).__dict__) 

5) Develop addBook endpoint

Let’s proceed for registering books.

  1. Define input type
    Define input to register a book in type.py
    As BookInput is extending BookDto with all_fields = True, BookInput will ask to get all fields of BookDto.
 class BookInput:
  1. Define a method to add book record.
    Define a method for adding book in crud.py.
def add_book(db: Session, book: Book):
     return book 
  1. Define an endpoint
    In main.py, define the endpoint, named add_book(), for registering new record.
 class Mutation:
     def add_book(self, book: BookInput, info: Info) -> BookType:
         book_inst = crud.add_book(info.context['db'], Book(**book.__dict__))
         return BookDto(**book_inst.__dict__) 

6) Access endpoints

  1. Run server
    Please run the server by repeating “Run the server” in
  1. Add book record
    Access by following “Access to  GraphQL console” in https://www.acromyanmar.com/easy-development-with-fastapi/ .
    Paste and execute following query which will call add_book().
mutation addbook{
   addBook(book: {
     title: "A work of art",
     author: "Maysonet",
     type: "Young adult"

You will receive the following response.

   "data": {
     "addBook": {
       "id": 1,
       "title": "A work of art",
       "author": "Maysonet",
       "type": "Young adult"
  1. Get book record
    Paste and execute following query which will call get_book().
query GetBook {

You will receive the following response.

   "data": {
     "getBook": {
       "title": "A work of art",
       "author": "Maysonet",
       "type": "Young adult"


In this blog, I introduced how to insert and retrieve data from a server easily in short time with FastAPI.

As I describe only how to insert, retrieve a record from database, I will introduce quick development for the remaining CRUD functions in the future blog.
Please look forward.

Thank you very much for reading until the end.
Stay safe everyone.

★★★We are hiring the staff who are interested in latest technologies.★★★

If you are interested in our company, please see the available job descriptions in the following links.

Senior Frontend/ Backend Developer : https://www.acromyanmar.com/senior-frontend-backend-developer/

Check our Facebook Page!