FastAPI: Connecting to Database

Git Commit: https://github.com/sourabhsinha396/fastapi-blog/tree/db01258f62c7b125948734bc69958ca45560cb1a

Super, So, far we have done a lot. Now, its time to add persistence. We are going to connect a database to fastapi. I am opting for PostgreSQL. You might need to download Postgres and DBeaver/PgAdmin(for monitoring Postgres). In case you just want to play and learn on your local computer, You may skip downloading Postgres. We can use SQLite, It is a file system-based, easy-to-use database and is supported by Python.

We will be using an ORM called sqlalchemy, ORM is a mapper which helps translate our database table records to a class object. Below are some benefits of using an ORM:

Fewer context switch: We write Python statements that are translated to database queries by ORMs.
Switching databases does not requires changes in the database, all it needs is to change the connection object.
Goodies: Migrations, connection pooling, and transactions are supported out of the box.
Optimized for performance: ORMs know how to optimize our queries (most of the time!)

Ok let's add the following lines in requirements.txt: and run pip install -r requirements.txt

fastapi==0.95.1 
uvicorn==0.22.0 

#new
SQLAlchemy==2.0.13
psycopg2==2.9.6    #linux/mac user use: psycopg2-binary==2.9.6

python-dotenv==1.0.0

Now, what we want is to have information on the database. I won't suggest storing this information in raw form. Instead, I would suggest keeping this information in environment variables. To make this process of creating environment variables super easy we will use python-dotenv. Let's create a new file named .env

In the '.env' file we are going to store critical information for our applications like API-keys, API-secret-key, and database URLs. So, let's put this info. in the '.env' file.

POSTGRES_USER=nofoobar
POSTGRES_PASSWORD=supersecret
POSTGRES_SERVER=localhost
POSTGRES_PORT=5432
POSTGRES_DB=blogdb

Now, I am going to modify our config file, this is because we don't want to directly communicate with the '.env' file and instead we want to arrange all our project configurations in one specific place. In our case, it is a config.py file. So, let's modify the config.py file to read from the '.env; file and keep our configurations ready.

import os
from dotenv import load_dotenv

from pathlib import Path
env_path = Path('.') / '.env'
load_dotenv(dotenv_path=env_path)

class Settings:
    PROJECT_NAME:str = "Job Board"
    PROJECT_VERSION: str = "1.0.0"

    POSTGRES_USER : str = os.getenv("POSTGRES_USER")
    POSTGRES_PASSWORD = os.getenv("POSTGRES_PASSWORD")
    POSTGRES_SERVER : str = os.getenv("POSTGRES_SERVER","localhost")
    POSTGRES_PORT : str = os.getenv("POSTGRES_PORT",5432) # default postgres port is 5432
    POSTGRES_DB : str = os.getenv("POSTGRES_DB","tdd")
    DATABASE_URL = f"postgresql://{POSTGRES_USER}:{POSTGRES_PASSWORD}@{POSTGRES_SERVER}:{POSTGRES_PORT}/{POSTGRES_DB}"

settings = Settings()

Almost there, just stick with me for some time. This I am doing to make our project more maintainable. What I have learned in my little experience is "Software development is more about maintenance and less about development".
Ok, now we can move to database setup, type the following lines in db > session.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from core.config import settings


SQLALCHEMY_DATABASE_URL = settings.DATABASE_URL
print("Database URL is ",SQLALCHEMY_DATABASE_URL)
engine = create_engine(SQLALCHEMY_DATABASE_URL)

#if you don't want to install postgres or any database, use sqlite, a file system based database, 
# uncomment below lines if you would like to use sqlite and comment above 2 lines of SQLALCHEMY_DATABASE_URL AND engine

# SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
# engine = create_engine(
#     SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
# )

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

We are creating a sqlalchemy engine with postgres database URL. As stated earlier, you can create an engine with sqllite too. just uncomment the commented lines and comment out SQLALCHEMY_DATABASE_URL = settings.DATABASE_URL engine = create_engine(SQLALCHEMY_DATABASE_URL)
Then, we are creating a SessionLocal. Once we create an instance of the SessionLocal class, this instance will be the actual database session. Remember this thing, we will create an actual database session for each request later.

A model class is the Pythonic representation of a database table. Alright, now we are going to create a super grandfather class. Every model will inherit this 'Base' class and we will utilize this base class to create all the database tables. Also, we will keep all common logic related to tables in this 'Base' class. For instance, all our table tables will have an id field. This will be used to uniquely identify each row/record. Let's create this Base class in a file db > base_class.py

from typing import Any
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import as_declarative

@as_declarative()
class Base:
    id: Any
    __name__: str

    #to generate tablename from classname
    @declared_attr
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

That was a lot, but there is one big thing missing. Think think 😁
Our app is in the main.py file and It has no idea of whatever we are typing in other files! So, we have to tell our app to create our database tables for us. So, add the following code in main.py

from fastapi import FastAPI
from core.config import settings
from db.session import engine 
from db.base_class import Base

def create_tables():         
	Base.metadata.create_all(bind=engine)
        

def start_application():
    app = FastAPI(title=settings.PROJECT_NAME,version=settings.PROJECT_VERSION)
    create_tables()
    return app


app = start_application()


@app.get("/")
def home():
    return {"msg":"Hello FastAPI🚀"}

If you are in Linux/Mac machine use the below command to create a database and user with the necessary permissions.

sudo -u postgres psql   # for linux/mac
# psql -U postgres      # for windows users

\l  # means show all databases

CREATE DATABASE blogdb;
CREATE USER nofoobar WITH ENCRYPTED PASSWORD 'supersecret';
GRANT ALL PRIVILEGES ON DATABASE blogdb TO nofoobar;

# \c dbname : to connect to a database
# \dt : to see tables in the database

Now, If you are using Postgres, open up PgAdmin and create a new database, the same as that mentioned by you in the '.env' file. (yourdbname_eg_debug) and restart the server. Our database and server are now connected to each other. In case we are using SQLite, we don't even need to create a database manually. Just restart the server. Here is a gif of my Postgres db connection establishment using PGAdmin.

Done, now let's meet in the next post. I am all tired of typing so much. Good night 😴