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.
backend/ ├─.env ├─apis/ │ └─general_pages/ │ └─route_homepage.py ├─core/ │ └─config.py ├─db/ │ ├─base.py │ ├─base_class.py │ ├─models/ #new folder │ │ ├─jobs.py #new file │ │ └─users.py #new file │ └─session.py ├─main.py ├─requirements.txt ├─static/ │ └─images/ │ └─logo.png └─templates/ ├─components/ │ └─navbar.html ├─general_pages/ │ └─homepage.html └─shared/ └─base.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(): print("create_tables") Base.metadata.create_all(bind=engine) ...... .....
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.