# Creating Users Table via SQLAlchemy & FastAPI

* First we will need to define our Table model in <mark style="color:green;">`models.py`</mark>

```python
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()'))
```

{% hint style="info" %}
Note: The <mark style="color:orange;">`unique=True`</mark> constraint will restrict users from registering with the same email twice
{% endhint %}

* We should set up a schema for the user registration in the <mark style="color:green;">`schemas.py`</mark> file
* We can use the <mark style="color:orange;">`EmailStr`</mark> validation
* This requires the <mark style="color:orange;">`email_validator`</mark> Module to be installed

{% hint style="info" %}
This should already be installed if we have done:

```python
pip install fastapi[all]
```

Can be checked with <mark style="color:yellow;">`pip freeze`</mark>

```
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
```

{% endhint %}

* Documentation and Additional info: [Click here!](https://pydantic-docs.helpmanual.io/usage/types/)
* The schema should look something like this:

```python
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 <mark style="color:green;">`main.py`</mark> file

```python
@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:

```json
{
    "created_at": "2022-07-29T10:00:43.106836+03:00",
    "email": "arp@gmail.com",
    "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 <mark style="color:green;">`schemas.py`</mark>

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

    class Config:
        orm_mode = True
```

* We also need to update our response model in <mark style="color:green;">`main.py`</mark>

```python
@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:

```json
{
    "id": 6,
    "email": "john@gmail.com",
    "created_at": "2022-07-29T10:06:31.160227+03:00"
}
```

{% hint style="danger" %}
YOU SHOULD NEVER STORE THE PASSWORDS IN CLEAR TEXT AS IN THIS EXAMPLE

&#x20;

[Click here](/programming/python/frameworks/fastapi/hashing-passwords-via-fastapi.md) to see how to HASH Passwords
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.arkannis.net/programming/python/frameworks/sqlalchemy/creating-users-table-via-sqlalchemy-and-fastapi.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.