How to Connect to a PostgreSQL Database in FastAPI: Python FastAPI Tutorial #21

In this tutorial, we will learn how to connect to a PostgreSQL database in FastAPI. PostgreSQL is a popular open-source relational database management system that is widely used in web development. By integrating a PostgreSQL database with FastAPI, we can create powerful and efficient web applications that can handle large amounts of data.

To get started, make sure you have PostgreSQL installed on your machine. You can download and install PostgreSQL from the official website: https://www.postgresql.org/download/

Next, we will need to install the necessary packages to connect to PostgreSQL from our FastAPI application. We can do this by running the following command:

pip install databases[postgresql]

This will install the required packages for connecting to a PostgreSQL database using the databases library.

Now, let’s create a new FastAPI application and set up the database connection. We will create a new file called main.py and add the following code:

from fastapi import FastAPI
from databases import Database

app = FastAPI()

DATABASE_URL = "postgresql://username:password@localhost/database"

database = Database(DATABASE_URL)

@app.on_event("startup")
async def startup():
    await database.connect()
    print("Database connected")

@app.on_event("shutdown")
async def shutdown():
    await database.disconnect()
    print("Database disconnected")

In this code, we have defined a DATABASE_URL variable that contains the connection string for our PostgreSQL database. Replace username, password, and database with your PostgreSQL credentials.

We have also created an instance of the Database class and connected to the database in the startup event. We have also added a shutdown event to disconnect from the database when the FastAPI application is shutdown.

Next, let’s create a route to retrieve data from our PostgreSQL database. We will add the following code to main.py:

@app.get("/users")
async def get_users():
    query = "SELECT * FROM users"
    users = await database.fetch_all(query)
    return users

In this code, we have defined a route /users that queries the users table from our PostgreSQL database and returns the result. The fetch_all method is used to execute the query and fetch all the results.

Finally, let’s run our FastAPI application and test the database connection. You can start the FastAPI application by running the following command:

uvicorn main:app --reload

Open your web browser and navigate to http://localhost:8000/users to see the data retrieved from your PostgreSQL database.

Congratulations! You have successfully connected to a PostgreSQL database in FastAPI. This allows you to build powerful web applications that can interact with a PostgreSQL database to store and retrieve data. Experiment with different queries and database operations to explore the full potential of integrating PostgreSQL with FastAPI.