How to build a FastAPI app with PostgreSQL

Introduction

This tutorial will guide you through building a FastAPI application integrated with a PostgreSQL database using SQLAlchemy and pgAdmin. By the end, you'll have a solid understanding of how to set up your development environment and create a functional web application with robust database management.

Step 1: Install Required Packages

Begin by installing the necessary packages for FastAPI and PostgreSQL.

1. Open your terminal or command prompt.
2. Install the following packages using pip:

   pip install fastapi[all] sqlalchemy psycopg2 pydantic uvicorn
   

3. Verify the installations to ensure everything is set up correctly.

Step 2: Start Your FastAPI Application

Set up a basic FastAPI application structure.

1. Create a new directory for your project and navigate into it.

   mkdir fastapi_postgresql_app
   cd fastapi_postgresql_app
   

2. Create a file named main.py. This will be your main application file.
3. Add the following code to main.py to start your FastAPI application:

   from fastapi import FastAPI
   
   app = FastAPI()
   
   @app.get("/")
   def read_root():
       return {"Hello": "World"}
   

4. Run your application using uvicorn:

   uvicorn main:app --reload
   

5. Open your browser and go to http://127.0.0.1:8000 to see your FastAPI app in action.

Step 3: Create PostgreSQL Connection

Next, set up a connection to your PostgreSQL database.

1. Install PostgreSQL if you haven't already. Refer to the official PostgreSQL website for installation instructions.
2. Open pgAdmin and create a new database.
3. In your main.py, add the following code to establish a connection:

   from sqlalchemy import create_engine
   
   DATABASE_URL = "postgresql://user:password@localhost/dbname"
   engine = create_engine(DATABASE_URL)
   

   Replace user, password, and dbname with your PostgreSQL credentials.

Step 4: Use pgAdmin for Database Management

Familiarize yourself with pgAdmin for managing your PostgreSQL database.

1. Launch pgAdmin and navigate to your database.
2. Use the GUI to create tables and manage your data.
3. Refer to the pgAdmin documentation for specific functionalities and features.

Step 5: Create PostgreSQL Tables

Create tables in your PostgreSQL database to hold application data.

1. Use the SQL command interface in pgAdmin to create a table. Example:

   CREATE TABLE items (
       id SERIAL PRIMARY KEY,
       name VARCHAR(100),
       description TEXT
   );
   

2. Ensure your table structure matches your application’s needs.

Step 6: Integrate FastAPI with SQLAlchemy

Connect FastAPI with PostgreSQL using SQLAlchemy.

1. Update your main.py to include the SQLAlchemy ORM capabilities.

   from sqlalchemy import Column, Integer, String
   from sqlalchemy.ext.declarative import declarative_base
   from sqlalchemy.orm import sessionmaker
   
   Base = declarative_base()
   
   class Item(Base):
       __tablename__ = 'items'
   
       id = Column(Integer, primary_key=True, index=True)
       name = Column(String, index=True)
       description = Column(String)
   
   SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
   

2. Create a database session to interact with your database.

Step 7: Create FastAPI API Endpoints

Define API endpoints to interact with your data.

1. In main.py, add the following route to create a new item:

   from fastapi import Depends
   from sqlalchemy.orm import Session
   
   @app.post("/items/")
   def create_item(item: Item, db: Session = Depends(SessionLocal)):
       db.add(item)
       db.commit()
       db.refresh(item)
       return item
   

2. Test your API endpoints using tools like Postman or curl.

Conclusion

In this tutorial, you learned how to set up a FastAPI application with PostgreSQL, utilizing SQLAlchemy for database interactions and pgAdmin for database management. You can now build on this foundation by adding more endpoints, refining your database schema, and implementing advanced features. Happy coding!