Creating Users Table via SQLAlchemy & FastAPI

• First we will need to define our Table model in models.py

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, nullable=False)
    email = Column(String, nullable=False, unique=True) # unique prevents email to be registered twice
    password = Column(String, nullable=False)

    created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text('now()'))

Note: The unique=True constraint will restrict users from registering with the same email twice

• We should set up a schema for the user registration in the schemas.py file

• We can use the EmailStr validation

• This requires the email_validator Module to be installed

This should already be installed if we have done:

pip install fastapi[all]

Can be checked with pip freeze

anyio==3.6.1
asgiref==3.5.2
certifi==2022.6.15
charset-normalizer==2.0.12
click==8.1.3
colorama==0.4.5
dnspython==2.2.1
email-validator==1.2.1
fastapi==0.78.0
greenlet==1.1.2
h11==0.13.0
httptools==0.4.0
idna==3.3
itsdangerous==2.1.2
Jinja2==3.1.2
MarkupSafe==2.1.1
orjson==3.7.5
psycopg==3.0.15
psycopg-binary==3.0.15
psycopg2==2.9.3
pydantic==1.9.1
python-dotenv==0.20.0
python-multipart==0.0.5
PyYAML==6.0
requests==2.28.0
six==1.16.0
sniffio==1.2.0
SQLAlchemy==1.4.39
starlette==0.19.1
typing_extensions==4.2.0
tzdata==2022.1
ujson==5.3.0
urllib3==1.26.9
uvicorn==0.17.6
watchgod==0.8.2
websockets==10.3

• Documentation and Additional info: Click here!

• The schema should look something like this:

from datetime import datetime
from pydantic import BaseModel, EmailStr

class UserCreate(BaseModel):
    email: EmailStr
    password: str

• Now we will have to create a new path operation in FastAPI's main.py file

@app.post("/users", status_code=status.HTTP_201_CREATED)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db), ):

    new_user = models.User(**user.dict())

    db.add(new_user)
    db.commit()
    db.refresh(new_user)

    return new_user

• Once we test this via postman we should get the response like this:

{
    "created_at": "2022-07-29T10:00:43.106836+03:00",
    "email": "[e-mail]",
    "password": "Password123",
    "id": 3
}

• These is an issue with this, we should never send the PASSWORD back to the user

• We need to define a user response in schemas.py

class UserOut(BaseModel):
    id: int
    email: EmailStr
    created_at: datetime

    class Config:
        orm_mode = True

• We also need to update our response model in main.py

@app.post("/users", status_code=status.HTTP_201_CREATED, response_model=schemas.UserOut)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db), ):

    new_user = models.User(**user.dict())

    db.add(new_user)
    db.commit()
    db.refresh(new_user)

    return new_user

• We should get a proper response now with EMAIL and ID:

{
    "id": 6,
    "email": "[e-mail]",
    "created_at": "2022-07-29T10:06:31.160227+03:00"
}

YOU SHOULD NEVER STORE THE PASSWORDS IN CLEAR TEXT AS IN THIS EXAMPLE

Click here to see how to HASH Passwords