When it comes to working with a PostgreSQL database within a Python application, we will need a PostgreSQL driver, there are a handful of libraries that we can use to achieve this and more on that shortly, but first let us answer the question, What is a PostgreSQL driver?

What is a PostgreSQL Driver:

In the context of PostgreSQL and Python, a driver is a software module or library that allows Python programs to connect and interact with a PostgreSQL database. A PostgreSQL driver provides an interface between the Python programming language and the PostgreSQL database, allowing developers to perform database operations such as querying, inserting, updating, and deleting data.

The driver typically implements the standard Python Database API (Python DB-API) specification, which defines a set of methods, parameters, and exceptions for communicating with a database. This specification provides a common interface that can be used by different Python database modules, allowing developers to write portable code that can work with multiple databases.

PostgreSQL drivers for Python, such as psycopg2, asyncpg, and aiopg are specifically designed to work with PostgreSQL databases and provides a set of functions and methods for connecting to a PostgreSQL server, executing SQL statements, and managing database transactions. These drivers typically support both sync and async interfaces, allowing developers to choose the appropriate interface for their application's needs.

Libraries that Facilitate the Connection of PostgreSQL Database to FastAPI

Here are Libraries used to connect a PostgreSQL database to FastAPI:

asyncpg: asyncpg is a fast, efficient, and easy-to-use PostgreSQL database driver that is designed specifically for use with asyncio. It provides a simple and high-level interface for asynchronously working with PostgreSQL databases.
aiopg: aiopg is another asyncio-based PostgreSQL database driver that provides a high-level interface for asynchronously working with PostgreSQL databases. It supports both Python 3.5+ and PostgreSQL 9.1+.
sqlalchemy: sqlalchemy is a SQL toolkit and ORM that provides a set of high-level APIs for working with relational databases, including PostgreSQL. It supports both sync and async interfaces and provides an ORM for mapping database tables to Python objects.
psycopg2: is a popular PostgreSQL database adapter for the Python programming language. It provides a comprehensive implementation of the Python DB-API specification for interacting with PostgreSQL databases. psycopg2 is widely used in the Python community for building web applications, data analysis tools, and other data-intensive applications that require access to a PostgreSQL database.

All of these libraries enumerated above are compatible with FastAPI and can be used to connect to a PostgreSQL database in your FastAPI application.

What we will be using and why

In this quick tutorial we will be using psycopg2 library to connect our FastAPI to our database and here is why:

psycopg2 is a fast and efficient driver that provides a high level of performance and reliability. It supports a wide range of PostgreSQL features, including transaction management, stored procedures, and user-defined types. It also provides a set of advanced features, such as asynchronous support, server-side cursors, and connection pooling.

One of the benefits of using psycopg2 is its popularity and extensive documentation. There are many resources available online that guide how to use psycopg2, including official documentation, third-party tutorials, and code examples. This makes it easier for developers to get started with psycopg2 and troubleshoot any issues that they may encounter.

Prerequisites

Before we begin, I would want to assume that you have the following at your disposal

Any Code interpreter
Postgres installed on your machine
A created PostgreSQL database in PgAdmin although it comes with a default database created named ‘Postgres’. You can create yours if you want
The details used in the creation of the database (very important).

With all of the above in place, ready to get your hands dirty?

Making the Connection

To connect FastAPI to a PostgreSQL database, follow these steps:

Install the required packages: use pip install psycopg2-binary fastapi[all] to install the required library. We add the [all] to install all the sub packages, we will need. Saves you the hassle of installing them individually.
```
 pip install psycopg2-binary fastapi[all]
```

Import the required packages: we then import both the driver and fast API

 from fastapi import FastAPI
 import psycopg2
 from psycopg2.extras import RealDictCursor

Create a connection to the PostgreSQL database: Fill in the required information. The database name, password and user-required fields are all the details you used in setting up your PostgreSQL server and database in PgAdmin.
```
 conn = psycopg2.connect(
     database="your_database_name",
     user="your_username",
     password="your_password",
     host="localhost",
     port="5432",
     cursor_factory=RealDictCursor)
```
Create a cursor object to interact with the database: This is calling the cursor method and having it in a variable named 'cur'. It is used to execute SQL statements.
```
 cur = conn.cursor()
```
Define your FastAPI app: What this means is that, we are setting up a variable called app, that will represent or be equal to FastAPI(). Anytime we want to access FastAPI(), we just use 'app'. hopefully, you get the explanation.
```
 app = FastAPI()
```
Define your database endpoint and query: Much need not be said here, if you are working with FastAPI, I suppose you understand exactly what is happening here. If not then you can check out some tutorials to help.
```
 @app.get("/users")
 async def read_users():
     cur.execute("SELECT * FROM users")
     users = cur.fetchall()
     return {"users": users}
```
Close the cursor and connection objects when done
```
 cur.close()
 conn.close()
```

You should have something that looks like this

from fastapi import FastAPI
import psycopg2
from psycopg2.extras import RealDictCursor

app = FastAPI()

    # Connect to an existing database
    conn = psycopg2.connect(host='localhost', 
    dbname='fastapi', 
    user='postgres',
    password='xxxxxxxxxx.',
    cursor_factory=RealDictCursor)

    # Open a cursor to perform database operations
    cursor = conn.cursor()

#creating an endpoint/path

@app.get("/users")
async def read_users():
    cur.execute("SELECT * FROM users")
    users = cur.fetchall()
    return {"users": users}

cur.close()
conn.close()

That's it! You have successfully connected to your PostgreSQL database using psycopg2.

Best Practice

When it comes to connecting to a database the connection might fail, and so, anytime we want to make a connection to a database that has a potential of failing, it is advisable to use Exception Handling.

This is not mandatory, it is not a must that you do this but If you follow through, it will be extremely helpful. one of the many things this procedure does is that it helps you know if you successfully connected to the database and if not generates an error message stating why the connection failed.

A few things can cause the connection failure like unstable network conditions, wrong syntax and a few more. With all these in mind, let's jump right in!

First, we try the connection procedure using the try exception, if the connection is successful, it prints 'database connected'.

 from fastapi import FastAPI
 import psycopg2
 from psycopg2.extras import RealDictCursor

 app = FastAPI()

 try:
     # Connect to an existing database
     conn = psycopg2.connect(host='localhost', 
     dbname='fastapi', 
     user='postgres',
     password='xxxxxxxxxxxxx.',
     cursor_factory= RealDictCursor)

     # Open a cursor to perform database operations
     cursor = conn.cursor()
     print('database connected')

Secondly, if the connection is unsuccessful, we try to catch the error using except. Make sure to indent properly.

   except Exception as error:
     print('connecting to database unsucessful')
     print('Error: ', error)

We are almost done, next, we want this exception handling to run for as many times as necessary until the database finally connects successfully.

To do this, we use a while loop and break out of the loop if the connection is successful using the break keyword.

 from fastapi import FastAPI
 import psycopg2
 from psycopg2.extras import RealDictCursor

 app = FastAPI()

 while True:
     try:
         # Connect to an existing database
         conn = psycopg2.connect(host='localhost', 
         dbname='fastapi', 
         user='postgres',
         password='xxxxxxxxxxxxx.',
         cursor_factory= RealDictCursor)

         # Open a cursor to perform database operations
         cursor = conn.cursor()
         print('database connected')
         break 

         except Exception as error:
             print('connecting to database unsucessful')
             print('Error: ', error)

Finally, it will be a good idea to wait a few seconds before trying to reconnect to the database if the connection failed, maybe due to network issues or something else.

To do this, we import the time module and place this beneath the except keyword, time.sleep(2). After every two seconds, the Fast API attempts a connection to the database. You can put any time duration you want.

 from fastapi import FastAPI
 import psycopg2
 from psycopg2.extras import RealDictCursor

 app = FastAPI()

 while True:
     try:
         # Connect to an existing database
         conn = psycopg2.connect(host='localhost', 
         dbname='fastapi', 
         user='postgres',
         password='xxxxxxxxxxxxx.',
         cursor_factory= RealDictCursor)

         # Open a cursor to perform database operations
         cursor = conn.cursor()
         print('database connected')
         break 

         except Exception as error:
             print('connecting to database unsucessful')
             print('Error: ', error)
             time.sleep(2)

And with that, everything should work smoothly.

I hope you understood everything we did, if you don't feel free to reach out to me, drop your questions in the comment section and I will be more than happy to attend to them.

Cheers!

Connecting Fast API to PostgreSQL Database

Table of contents