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.
1 | Setup DB connections | Connect to SQL Lite DB. |
2 | Define model | Define Book model. |
3 | Map the model and table | Map the Book model and books table. |
4 | Refine getBook endpoint | Replace the sample data with retrieved data from DB |
5 | Develop addBook endpoint | Develop addBook endpoint to register books. |
6 | Access 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.
https://fastapi.tiangolo.com/advanced/async-sql-databases/#import-and-set-up-sqlalchemy
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
- 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)
- 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)
- 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() try: yield db finally: db.close() async def get_context( db=Depends(get_db)): return { 'db': db }
- Get db connection from Info context
From our custom context dependency, get the database session. And, pass it to get_book() of crud.py
… … … @strawberry.type class Query: @strawberry.field 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' } … … …
- 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.
https://strawberry.rocks/docs/integrations/pydantic
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] @strawberry.experimental.pydantic.type(model=BookDto) class BookType: title: strawberry.auto author: strawberry.auto type: strawberry.auto id: strawberry.auto
- Use type in response
In main.py, use BookType as the response format.
from type import BookDto, BookType … … … @strawberry.type class Query: @strawberry.field 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.
- 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.
@strawberry.experimental.pydantic.input(model=BookDto,all_fields=True) class BookInput: pass
- Define a method to add book record.
Define a method for adding book in crud.py.
def add_book(db: Session, book: Book): db.add(book) db.commit() db.refresh(book) return book
- Define an endpoint
In main.py, define the endpoint, named add_book(), for registering new record.
@strawberry.type class Mutation: @strawberry.field 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
- Run server
Please run the server by repeating “Run the server” in
https://www.acromyanmar.com/easy-development-with-fastapi/
- 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" }) { id title author type } }
You will receive the following response.
{ "data": { "addBook": { "id": 1, "title": "A work of art", "author": "Maysonet", "type": "Young adult" } } }
- Get book record
Paste and execute following query which will call get_book().
query GetBook { getBook(id:1) { title author type } }
You will receive the following response.
{ "data": { "getBook": { "title": "A work of art", "author": "Maysonet", "type": "Young adult" } }
Conclusion
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/