> ## Documentation Index
> Fetch the complete documentation index at: https://notes.kodekloud.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Pydantic Sqlalchemy Models
> This article clarifies the differences between Pydantic models and SQLAlchemy models in a FastAPI application.
This article clarifies the differences between a schema (Pydantic model) and an ORM (SQLAlchemy model) by demonstrating their roles within a FastAPI application. The Pydantic model validates incoming requests and outgoing responses, while the SQLAlchemy model establishes the database table structure.
## FastAPI Setup and Basic Imports
Below is an initial code snippet showing the essential imports and setup for the FastAPI application:
```python theme={null}
from random import randrange
import psycopg2
from psycopg2.extras import RealDictCursor
import time
from sqlalchemy.orm import Session
from sqlalchemy.sql.functions import mode
from fastapi import FastAPI, Depends, status
from pydantic import BaseModel
from . import models
from .database import engine, get_db
models.Base.metadata.create_all(bind=engine)
app = FastAPI()
class Post(BaseModel):
title: str
content: str
published: bool = True
while True:
# This loop demonstrates an example of a service running continuously.
pass
```
The following log output confirms that the application starts successfully and provides examples of handling PUT requests:
```text theme={null}
INFO: Application startup complete.
INFO: 127.0.0.1:54950 - "PUT /posts/1 HTTP/1.1" 200 OK
INFO: 127.0.0.1:65135 - "PUT /posts/12342324 HTTP/1.1" 404 Not Found
```
Remember that the Pydantic model is primarily used for request/response data validation, while the SQLAlchemy model is used to define the persistent data structure of your database.
## Differentiating Between Pydantic and SQLAlchemy Models
### Pydantic Model (Schema)
The Pydantic model serves to:
* **Define Data Structure:** Outline the expected structure of the request body.
* **Validate Incoming Data:** Ensure that the data received adheres to the specified types and constraints.
* **Shape Response Data:** Optionally filter and format data before sending a response.
### SQLAlchemy Model (ORM)
The SQLAlchemy model is dedicated to defining the database table structure. It includes:
* **Column Definitions:** Attributes such as post ID, title, content, published status, and creation timestamps.
* **Database Interaction Methods:** Functions to query, create, delete, and update database entries.
## Connecting to the Database
Enhance your understanding with the following code snippet that illustrates how to connect to a PostgreSQL database using psycopg2:
```python theme={null}
from random import randrange
import psycopg2
from psycopg2.extras import RealDictCursor
import time
from sqlalchemy.orm import Session
from sqlalchemy.sql.functions import mode
from fastapi import FastAPI, Depends, status
from pydantic import BaseModel
from . import models
from .database import engine, get_db
models.Base.metadata.create_all(bind=engine)
app = FastAPI()
class Post(BaseModel):
title: str
content: str
published: bool = True
while True:
try:
conn = psycopg2.connect(
host='localhost',
database='fastapi',
user='postgres',
password='password123',
cursor_factory=RealDictCursor
)
cursor = conn.cursor()
print("Database connection was successful!")
break
except Exception as error:
print("Connection failed:", error)
time.sleep(2)
```
Again, the log output confirms the successful startup of the application and its request handling capabilities:
```text theme={null}
INFO: Application startup complete.
INFO: 127.0.0.1:54950 - "PUT /posts/1 HTTP/1.1" 200 OK
INFO: 127.0.0.1:65135 - "PUT /posts/1234324 HTTP/1.1" 404 Not Found
```
## Path Operations and Request Validation
The following code snippet demonstrates how path operations use the Pydantic model to validate incoming data for fetching and creating posts:
```python theme={null}
@app.get("/posts")
def get_posts(db: Session = Depends(get_db)):
posts = db.query(models.Post).all()
return {"data": posts}
@app.post("/posts", status_code=status.HTTP_201_CREATED)
def create_posts(post: Post, db: Session = Depends(get_db)):
# The code to insert the post into the database has been omitted for brevity.
pass
```
The Pydantic model named Post validates that any request to create a new post contains the fields title, content, and (optionally) a published flag. This strict validation approach helps protect the API from malformed data and ensures consistency in the data exchange.
## SQLAlchemy Model Definition
The SQLAlchemy model is defined in the `models.py` file. It represents the actual structure of the "posts" table in the PostgreSQL database:
```python theme={null}
from sqlalchemy import Column, Integer, String, Boolean
from sqlalchemy.sql.expression import text
from sqlalchemy.sql.sqltypes import TIMESTAMP
from .database import Base
class Post(Base):
__tablename__ = "posts"
id = Column(Integer, primary_key=True, nullable=False)
title = Column(String, nullable=False)
content = Column(String, nullable=False)
published = Column(Boolean, server_default='TRUE', nullable=False)
created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text('now()'))
```
The diagram below further illustrates how the SQLAlchemy model defines the columns of the "posts" table and its role in handling operations such as querying, creating, deleting, and updating database entries:
## Summary
In summary, the Pydantic model (schema) ensures that both requests and responses comply with the expected data structures, while the SQLAlchemy model provides a detailed blueprint of the database table structure. Although adopting Pydantic models is technically optional, it is a best practice for building robust and secure APIs by enforcing strict data validation.
By carefully separating concerns—using Pydantic for data validation and SQLAlchemy for database interactions—developers can build maintainable and secure APIs that efficiently manage data flow between clients and servers.
For more details consider exploring the [FastAPI Documentation](https://fastapi.tiangolo.com/) and [SQLAlchemy Documentation](https://docs.sqlalchemy.org/en/14/).