Tutorial: Set Up PostgreSQL with FastAPI

Introduction

• Presenter: Eric Roby
• Topics: Setting up PostgreSQL with FastAPI, creating a quiz game API
• Background: Eric is an instructor, software engineer, and cloud enthusiast.

Project Overview

• Objective: Create a FastAPI and PostgreSQL application
• Focus: Use FastAPI for a Python microservice framework and PostgreSQL for relational database management

Initial Setup

1. Create Virtual Environment:*

   • Open a new terminal
   • Command: python3 -m venv env
   • Activate: source env/bin/activate
   • Verify activation: see virtual environment indicator

2. Install Dependencies:*

   • Command: pip install fastapi sqlalchemy psycopg2-binary

Basic FastAPI and Pydantic Setup

1. Create main.py File:

   • Import FastAPI, HTTPException, Depends from FastAPI
   • Import BaseModel from Pydantic
   • Import List, Annotated from Typing
   • Initialize FastAPI: app = FastAPI()

2. Create Pydantic Models:

   • ChoiceBase and QuestionBase classes inheriting from BaseModel.
   • Define attributes for Choice (text, is_correct) and Question (text, choices).

Database Configuration

1. Create database.py File:

   • Import create_engine, sessionmaker from SQLAlchemy
   • Declarative Base import: from sqlalchemy.ext.declarative import declarative_base
   • Define database URL: postgresql://<username>:<password>@localhost:5432/quiz_application_yt
   • Create engine: engine = create_engine(database_url)
   • Configure session: SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)
   • Define Base: Base = declarative_base()

2. Create Database via PGAdmin:

   • Open PGAdmin
   • Create database named quiz_application_yt
   • Confirm no tables exist initially.

Models Definition and Migration

1. Create models.py File:

   • Define Questions and Choices tables/models inheriting from Base
   • Define ID, question text, choice text, is_correct, foreign keys, etc.

2. Synchronize Models to Database:

   • Use SQLAlchemy to create necessary tables with metadata creation and engine binding.

API Endpoints Creation

Questions API

1. POST /questions:

   • Definition: async def create_question(question: QuestionBase, db: Session = Depends(get_db))
   • Create DB records using SQLAlchemy ORM
   • Add, commit, refresh records

2. GET /questions/{question_id}:*

   • Definition: async def read_question(question_id: int, db: Session = Depends(get_db))
   • Query for question by ID, handle case if not found.

3. GET /choices:

   • Definition: async def read_choices(question_id: int, db: Session = Depends(get_db))
   • Query for all choices associated with a given question ID.

Testing and Validation

1. Run Uvicorn Server:*

   • Command: uvicorn main:app --reload
   • Ensure uvicorn is installed, else run: pip install uvicorn

2. Interact with API via Browser:

   • Use auto-generated FastAPI documentation to test POST and GET requests
   • Validate data persistence using PGAdmin to view tables and records.

Conclusion

• Successfully created API using FastAPI and PostgreSQL
• Showed creation of database records and querying via API endpoints
• Demonstrated the benefits of using FastAPI for rapid development