10 : Creating tables in FastAPI



Before We talk about databases tables and all, I want to share a very simplistic view of FastAPI request-response cycle. A request-response cycle basically means to understand what happens in between, the browser makes a request and FastAPI sends backs a response. It is actually a very popular interview question. During my freshers' job search, I was asked this question around 90% of the time. "Explain the request-response cycle of Django?".
This will also help us where does database connection comes into play and why we need to create and connect with DB tables.

It might so happen that you don't understand Schemas, Routers, 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 to hold the class equivalent of DB tables.

│ └─general_pages/
│   └─route_homepage.py
│ └─config.py
│ ├─base.py
│ ├─base_class.py
│ ├─models/          #new folder
│ │ ├─jobs.py        #new file
│ │ └─users.py       #new file
│ └─session.py
│ └─images/
│   └─logo.png
  │ └─navbar.html
  │ └─homepage.html

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

from sqlalchemy import Column, Integer, String, Boolean,Date, ForeignKey
from sqlalchemy.orm import relationship

from db.base_class import Base

class Job(Base):
    id = Column(Integer,primary_key = True, index=True)
    title = Column(String,nullable= False)
    company = Column(String,nullable=False)
    company_url = Column(String)
    location = Column(String,nullable = False)
    description = Column(String,nullable=False)
    date_posted = Column(Date)
    is_active = Column(Boolean(),default=True)
    owner_id =  Column(Integer,ForeignKey("user.id"))
    owner = relationship("User",back_populates="jobs")

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

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 sqlalchemy import Column,Integer, String,Boolean, ForeignKey
from sqlalchemy.orm import relationship

from db.base_class import Base

class User(Base):
    id = Column(Integer,primary_key=True,index=True)
    username = Column(String,unique=True,nullable=False)
    email = Column(String,nullable=False,unique=True,index=True)
    hashed_password = Column(String,nullable=False)
    is_active = Column(Boolean(),default=True)
    is_superuser = Column(Boolean(),default=False)
    jobs = relationship("Job",back_populates="owner")

Done? Nope, 😅 we still have few configurations left. Lets 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.

#db > base.py
from db.base_class import Base
from db.models.jobs import Job 
from db.models.users import User

Ok now just one last thing. Remeber we were importing 'Base' in main.py file and creating db tables. Now we won't import Base from base_class.py but instead from base.py. So, change the import statement in main.py to:

from fastapi import FastAPI
from fastapi.staticfiles import StaticFiles
from core.config import settings
from apis.general_pages.route_homepage import general_pages_router
from db.session import engine
from db.base import Base      # now import Base from db.base not db.base_class


def create_tables():


Ok, time to restart the uvicorn 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.


Prev: 09 : FastAPI … Next: 11 : Creating …

Brige the gap between Tutorial hell and Industry. We want to bring in the culture of Clean Code, Test Driven Development.

We know, we might make it hard for you but definitely worth the efforts.



Refund Policy

Follow us on our social media channels to stay updated.

© Copyright 2022-23 Team FastAPITutorial