Creating tables in FastAPI

Git Commit: create tables in fastapi
Before We talk about databases tables and all, I want to share a very simplistic view of the FastAPI request-response cycle. A request-response cycle explains the journey of the browser making a request and FastAPI sending back a response. It is actually a very popular interview question. During my job search, I was asked this question around 90% of the time. "Explain the request-response cycle of Django?". It's a good chance to showcase that you know middleware, its usage, and advanced stuff !!
Below is a demonstration of the request flow. 

It might so happen that you don't understand Schemas, Routers, and Models but don't panic. We will understand in the next posts. I just wanted to bring to your notice that we need to query data from database tables in almost all web apps.
So, let's begin to create database tables. Make sure you have this folder structure. Create the models directory to hold the class equivalent of DB tables.
 

backend/
├─.env
├─alembic/
├─core/
│ └─config.py
├─db/
│ ├─base.py
│ ├─base_class.py
│ ├─models/          #new folder
│ │ ├─blog.py        #new file
│ │ └─user.py       #new file
│ └─session.py
├─alembic.ini
├─main.py
├─requirements.txt

Type in the following code in db > models > blog.py , type,🫣 no cheating, no copy-paste!

from datetime import datetime
from sqlalchemy import Column, Integer, Text, String, Boolean, DateTime, ForeignKey
from sqlalchemy.orm import relationship

from db.base_class import Base


class Blog(Base):
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    slug = Column(String, nullable=False)
    content = Column(Text, nullable=True)
    author_id =  Column(Integer,ForeignKey("user.id"))
    author = relationship("User",back_populates="blogs")
    created_at = Column(DateTime, default=datetime.now)
    is_active = Column(Boolean, default=False)

Ok, now let's understand what we just did and why:

• Remember in the last post I told you a story that it's we use raw SQL queries like "Select * from Blog". It will not work with all the databases. Because each database has a different set of protocols/rules. e.g. in Postgres " double quotes are not recognized and we have to use ' single quotes.
• We have our Base class in 'base_class.py' and we are inheriting it to have a class-based representation of tables. Each property or attribute of this class is translated into a column in the table.
• The 'title' column represents the blog title and it can store strings. Its value in the table can't be NULL.
• is_active columns will be used to control if the blog post will be visible on the website or not.
• The blog table will have a foreign key to the User table and these foreign keys will be used to identify who is the blog poster. This will be used to authorize if a person can update/delete a blog post or not.

Now, we will also type in the code to have a User table which will be used to hold users' data obviously. Type in the below code in db > models > users.py

from db.base_class import Base
from sqlalchemy import Boolean
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.orm import relationship


class User(Base):
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, nullable=False, unique=True, index=True)
    password = Column(String, nullable=False)
    is_superuser = Column(Boolean(), default=False)
    is_active = Column(Boolean(), default=True)
    blogs = relationship("Blog",back_populates="author")

Done? Nope, 😅 we still have a few configurations left. Let's put the import of all these models in one single file named 'base.py'. It will be helpful to create all the tables at once in our web app.

from db.base_class import Base
from db.models.user import User
from db.models.blog import Blog

Ok, now just one last thing. Remember we were importing 'Base' in the main.py file and creating db tables. Now we won't import Base from base_class.py but instead from base.py(if you haven't done it earlier). So, change the import statement in main.py to:

###
from db.session import engine
from db.base import Base      # now import Base from db.base not db.base_class



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

Ok, time to restart the unicorn server. Now, check your DB tables. In case you are using SQLite use a tool named Downloads - DB Browser for SQLite (sqlitebrowser.org). Postgres guys refresh the tables in PgAdminDownload (pgadmin.org) and you should see the tables.
However, if you execute the command alembic revision --autogenerate -m "first migration"  This would create an empty migration file inside alembic/versions. This is because running uvicorn server creates tables because of the line Base.metadata.create_all(bind=engine). So, I would suggest you to delete the empty migration file with an empty upgrade and downgrade method. Make sure your versions directory is now empty. At this point execute the below 2 commands.

alembic revision --autogenerate -m "create user and blog table migrations"  #analyzes tables and creates a migration file
alembic upgrade head  #executes the migration files to make actual changes in db