How to Integrate FastAPI with PostgreSQL

Introduction

In this article, we will explore how to connect FastAPI with PostgreSQL, a popular open-source relational database management system. FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.7+ based on standard Python type hints. It provides a great developer experience and high performance, making it an excellent choice for building web applications.

Related Article: Optimizing FastAPI Applications: Modular Design, Logging, and Testing

How to Install FastAPI?

To install FastAPI, you need to have Python 3.7 or higher installed on your system. You can install FastAPI using pip, the Python package manager. Open your terminal and run the following command:

<a href="https://www.squash.io/troubleshooting-pip-install-failures-with-fastapi/">pip install fastapi</a>

How to Install PostgreSQL?

To install PostgreSQL, you can follow the official documentation for your operating system. Here are the general steps to install PostgreSQL:

1. For Linux:
– Update the package manager:

     sudo apt-get update

– Install PostgreSQL:

     sudo apt-get install postgresql

2. For macOS:
– Install Homebrew (if not already installed):

     /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

– Install PostgreSQL using Homebrew:

     brew install postgresql

3. For Windows:
– Download the PostgreSQL installer from the official website.
– Run the installer and follow the installation wizard.

How to Create a Database Connection in FastAPI?

To create a database connection in FastAPI, we will use SQLAlchemy as the ORM and psycopg2 as the PostgreSQL adapter. Here’s how you can set up a database connection in FastAPI:

1. Import the required modules:

   from fastapi import FastAPI
   from sqlalchemy import create_engine
   from sqlalchemy.orm import sessionmaker

2. Create a FastAPI application instance:

   app = FastAPI()

3. Define the database URL and create the SQLAlchemy engine:

   database_url = "postgresql://username:password@localhost/database_name"
   engine = create_engine(database_url)

4. Create a session factory using the engine:

   SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

5. Create a dependency function to provide a database session to routes:

   def get_db():
       db = SessionLocal()
       try:
           yield db
       finally:
           db.close()

6. Use the Depends decorator to inject the get_db dependency into routes:

   @app.get("/")
   async def root(db: Session = Depends(get_db)):
       # Use the database session
       pass

Now you have a database connection set up in FastAPI using SQLAlchemy and psycopg2.

Related Article: FastAPI Integration: Bootstrap Templates, Elasticsearch and Databases

What is the Difference Between Sync and Async in FastAPI?

FastAPI supports both synchronous (sync) and asynchronous (async) request handlers. The main difference between sync and async handlers is how they handle concurrent requests.

Sync handlers are executed sequentially, one after another, blocking the execution until a response is returned. This means that if a sync handler takes a long time to process, it will block other requests from being processed.

Async handlers, on the other hand, can be executed concurrently and do not block the execution of other handlers. They allow for better utilization of system resources and can handle more concurrent requests efficiently.

To define a sync handler in FastAPI, you can use the normal def keyword. For example:

@app.get("/")
def sync_handler():
    # Sync handler code
    pass

To define an async handler, you need to use the async def keyword. For example:

@app.get("/")
async def async_handler():
    # Async handler code
    pass

It is important to note that if you use async handlers, you need to use an async-compatible database adapter, such as asyncpg, instead of psycopg2, to interact with PostgreSQL.

Code Snippet – Creating a FastAPI Endpoint

Here’s an example of how to create a simple FastAPI endpoint:

from fastapi import FastAPI

app = FastAPI()

@app.get("/")
async def root():
    return {"message": "Hello, World!"}

In this example, we define a route handler for the root path (“/”). When a GET request is made to the root path, the root function is called, which returns a JSON response with the message “Hello, World!”.

Code Snippet – Retrieving Data from PostgreSQL using SQLAlchemy

To retrieve data from PostgreSQL using SQLAlchemy in FastAPI, you can use the ORM capabilities provided by SQLAlchemy. Here’s an example:

from fastapi import FastAPI
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

app = FastAPI()

# Define the database URL and create the SQLAlchemy engine
database_url = "postgresql://username:password@localhost/database_name"
engine = create_engine(database_url)

# Create a session factory using the engine
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

@app.get("/users/{user_id}")
async def get_user(user_id: int):
    # Create a new session
    db = SessionLocal()

    # Retrieve the user from the database using the user_id
    user = db.query(User).filter(User.id == user_id).first()

    # Close the session
    db.close()

    return {"user": user}

In this example, we define a route handler for retrieving a user by their ID. We create a new session using the session factory, query the database using SQLAlchemy’s query API, and return the user as a JSON response.

Related Article: Tutorial: i18n in FastAPI with Pydantic & Handling Encoding

Code Snippet – Inserting Data into PostgreSQL using SQLAlchemy

To insert data into PostgreSQL using SQLAlchemy in FastAPI, you can use the ORM capabilities provided by SQLAlchemy. Here’s an example:

from fastapi import FastAPI
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

app = FastAPI()

# Define the database URL and create the SQLAlchemy engine
database_url = "postgresql://username:password@localhost/database_name"
engine = create_engine(database_url)

# Create a session factory using the engine
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

@app.post("/users")
async def create_user(user: UserCreate):
    # Create a new session
    db = SessionLocal()

    # Create a new user object using the request data
    new_user = User(name=user.name, email=user.email)

    # Add the user to the session
    db.add(new_user)

    # Commit the session to persist the changes
    db.commit()

    # Close the session
    db.close()

    return {"message": "User created successfully"}

In this example, we define a route handler for creating a new user. We create a new session using the session factory, create a new user object using the request data, add the user to the session, commit the session to persist the changes, and return a JSON response indicating the success of the operation.

Code Snippet – Updating Data in PostgreSQL using SQLAlchemy

To update data in PostgreSQL using SQLAlchemy in FastAPI, you can use the ORM capabilities provided by SQLAlchemy. Here’s an example:

from fastapi import FastAPI
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

app = FastAPI()

# Define the database URL and create the SQLAlchemy engine
database_url = "postgresql://username:password@localhost/database_name"
engine = create_engine(database_url)

# Create a session factory using the engine
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

@app.put("/users/{user_id}")
async def update_user(user_id: int, user: UserUpdate):
    # Create a new session
    db = SessionLocal()

    # Retrieve the user from the database using the user_id
    db_user = db.query(User).filter(User.id == user_id).first()

    # Update the user object with the new data
    db_user.name = user.name
    db_user.email = user.email

    # Commit the session to persist the changes
    db.commit()

    # Close the session
    db.close()

    return {"message": "User updated successfully"}

In this example, we define a route handler for updating a user by their ID. We create a new session using the session factory, retrieve the user from the database using SQLAlchemy’s query API, update the user object with the new data, commit the session to persist the changes, and return a JSON response indicating the success of the operation.

Code Snippet – Deleting Data from PostgreSQL using SQLAlchemy

To delete data from PostgreSQL using SQLAlchemy in FastAPI, you can use the ORM capabilities provided by SQLAlchemy. Here’s an example:

from fastapi import FastAPI
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

app = FastAPI()

# Define the database URL and create the SQLAlchemy engine
database_url = "postgresql://username:password@localhost/database_name"
engine = create_engine(database_url)

# Create a session factory using the engine
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

@app.delete("/users/{user_id}")
async def delete_user(user_id: int):
    # Create a new session
    db = SessionLocal()

    # Retrieve the user from the database using the user_id
    db_user = db.query(User).filter(User.id == user_id).first()

    # Delete the user from the session
    db.delete(db_user)

    # Commit the session to persist the changes
    db.commit()

    # Close the session
    db.close()

    return {"message": "User deleted successfully"}

In this example, we define a route handler for deleting a user by their ID. We create a new session using the session factory, retrieve the user from the database using SQLAlchemy’s query API, delete the user from the session, commit the session to persist the changes, and return a JSON response indicating the success of the operation.

Related Article: FastAPI Enterprise Basics: SSO, RBAC, and Auditing

Best Practices for Using FastAPI with PostgreSQL

When using FastAPI with PostgreSQL, there are some best practices you can follow to ensure a smooth and efficient development process:

1. Use SQLAlchemy as the ORM: SQLAlchemy provides a useful and flexible API for working with databases. It allows you to work with databases using Python objects and provides a high-level interface for common database operations.

2. Use Pydantic for data validation: Pydantic allows you to define the shape (schema) of your data and validate it against that schema. It provides a convenient way to validate and parse request and response data in FastAPI.

3. Use dependency injection for database connections: FastAPI supports dependency injection, which allows you to inject dependencies such as database sessions into your route handlers. This ensures that each request gets its own database session and avoids common issues related to session management.

4. Use async handlers for better performance: If your application can benefit from concurrent request handling, consider using async handlers. Async handlers allow for better utilization of system resources and can handle more concurrent requests efficiently.

5. Use connection pooling: Connection pooling allows you to reuse existing database connections instead of creating new connections for each request. This can significantly improve the performance of your application by reducing the overhead of establishing new connections.

Troubleshooting Common Issues in FastAPI with PostgreSQL

When working with FastAPI and PostgreSQL, you may encounter some common issues. Here are a few troubleshooting tips to help you resolve them:

1. Connection issues: If you are unable to establish a connection to your PostgreSQL database, double-check your database URL and ensure that the database server is running and accessible.

2. Dependency issues: If you encounter import errors or missing dependencies, make sure that you have installed all the necessary packages and that they are compatible with the versions of FastAPI and PostgreSQL you are using.

3. Performance issues: If your application is experiencing performance issues, consider optimizing your database queries and using connection pooling to reuse existing connections. You can also use profiling tools to identify bottlenecks and optimize your code.

4. Data integrity issues: If you are experiencing data integrity issues, ensure that you have defined proper constraints, such as primary keys and foreign keys, in your database schema. Also, validate and sanitize user input to prevent SQL injection attacks.

5. Error handling: FastAPI provides built-in exception handling and error reporting. Make sure to handle exceptions gracefully and return appropriate error responses to the client. Use the built-in exception handlers in FastAPI to catch and handle specific types of errors.

Additional Resources

– FastAPI: High Performance, Easy to Learn, Fast to Code, Ready for Production
– FastAPI GitHub Repository
– PostgreSQL: The World’s Most Advanced Open Source Relational Database