<h3 dir="auto">First check</h3>
<ul class="contains-task-list">
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I added a very descriptive title to this issue.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I used the GitHub search to find a similar issue and didn't find it.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I searched the FastAPI documentation, with the integrated search.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I already searched in Google "How to X in FastAPI" and didn't find any information.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I already read and followed all the tutorial in the docs and didn't find an answer.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I already checked if it is not related to FastAPI but to <a href="https://github.com/samuelcolvin/pydantic">Pydantic</a>.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I already checked if it is not related to FastAPI but to <a href="https://github.com/swagger-api/swagger-ui">Swagger UI</a>.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> I already checked if it is not related to FastAPI but to <a href="https://github.com/Redocly/redoc">ReDoc</a>.</li>
<li class="task-list-item"><input type="checkbox" id="" disabled="" class="task-list-item-checkbox" checked=""> After submitting this, I commit to one of:
<ul dir="auto">
<li>Read open issues with questions until I find 2 issues where I can help someone and add a comment to help there.</li>
<li>I already hit the "watch" button in this repository to receive notifications and I commit to help at least 2 people that ask questions in the future.</li>
<li>Implement a Pull Request for a confirmed bug.</li>
</ul>
</li>
</ul>
<h3 dir="auto">Example</h3>
<p dir="auto">Here's a self-contained, <a href="https://stackoverflow.com/help/minimal-reproducible-example" rel="nofollow">minimal, reproducible, example</a> with my use case:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="
import logging
from pydantic import BaseModel
from fastapi import FastAPI, Depends, Request


logger = logging.Logger(__name__)


class UserModel(BaseModel):
    username: str
    name: str = None


app = FastAPI()


@Depends
def get_user_model_data(model: UserModel):
    print(&quot;Running function get_user_model_data&quot;)
    return model.dict(exclude_unset=True)


@Depends
def log_new_users(request: Request, data: dict = get_user_model_data):
    logger.info(f'Got request from {request.client.host} with username {model.username}')


@app.post('/users', dependencies=[log_new_users])
def create_account(data: dict = get_user_model_data):
    return {
        &quot;status&quot;: &quot;OK&quot;,
        &quot;data&quot;: data,
    }
"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">logging</span>
<span class="pl-k">from</span> <span class="pl-s1">pydantic</span> <span class="pl-k">import</span> <span class="pl-v">BaseModel</span>
<span class="pl-k">from</span> <span class="pl-s1">fastapi</span> <span class="pl-k">import</span> <span class="pl-v">FastAPI</span>, <span class="pl-v">Depends</span>, <span class="pl-v">Request</span>


<span class="pl-s1">logger</span> <span class="pl-c1">=</span> <span class="pl-s1">logging</span>.<span class="pl-v">Logger</span>(<span class="pl-s1">__name__</span>)


<span class="pl-k">class</span> <span class="pl-v">UserModel</span>(<span class="pl-v">BaseModel</span>):
    <span class="pl-s1">username</span>: <span class="pl-s1">str</span>
    <span class="pl-s1">name</span>: <span class="pl-s1">str</span> <span class="pl-c1">=</span> <span class="pl-c1">None</span>


<span class="pl-s1">app</span> <span class="pl-c1">=</span> <span class="pl-v">FastAPI</span>()


<span class="pl-en">@<span class="pl-v">Depends</span></span>
<span class="pl-k">def</span> <span class="pl-en">get_user_model_data</span>(<span class="pl-s1">model</span>: <span class="pl-v">UserModel</span>):
    <span class="pl-en">print</span>(<span class="pl-s">"Running function get_user_model_data"</span>)
    <span class="pl-k">return</span> <span class="pl-s1">model</span>.<span class="pl-en">dict</span>(<span class="pl-s1">exclude_unset</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)


<span class="pl-en">@<span class="pl-v">Depends</span></span>
<span class="pl-k">def</span> <span class="pl-en">log_new_users</span>(<span class="pl-s1">request</span>: <span class="pl-v">Request</span>, <span class="pl-s1">data</span>: <span class="pl-s1">dict</span> <span class="pl-c1">=</span> <span class="pl-s1">get_user_model_data</span>):
    <span class="pl-s1">logger</span>.<span class="pl-en">info</span>(<span class="pl-s">f'Got request from <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">request</span>.<span class="pl-s1">client</span>.<span class="pl-s1">host</span><span class="pl-kos">}</span></span> with username <span class="pl-s1"><span class="pl-kos">{</span><span class="pl-s1">model</span>.<span class="pl-s1">username</span><span class="pl-kos">}</span></span>'</span>)


<span class="pl-en">@<span class="pl-s1">app</span>.<span class="pl-en">post</span>(<span class="pl-s">'/users'</span>, <span class="pl-s1">dependencies</span><span class="pl-c1">=</span>[<span class="pl-s1">log_new_users</span>])</span>
<span class="pl-k">def</span> <span class="pl-en">create_account</span>(<span class="pl-s1">data</span>: <span class="pl-s1">dict</span> <span class="pl-c1">=</span> <span class="pl-s1">get_user_model_data</span>):
    <span class="pl-k">return</span> {
        <span class="pl-s">"status"</span>: <span class="pl-s">"OK"</span>,
        <span class="pl-s">"data"</span>: <span class="pl-s1">data</span>,
    }</pre></div>
<h3 dir="auto">Description</h3>
<p dir="auto">Above is a simple application with a /users endpoint using a pydantic model which requires a username in the post body.<br>
when the model validation passes all is fine. But when model validation fails, the model is validated for each depends separately resulting in duplicated errors.</p>
<p dir="auto">I would expect that it should be cached even when there are errors so it is validated only once.</p>
<ul dir="auto">
<li>Run the app</li>
<li>execute <code class="notranslate">curl -X POST "http://127.0.0.1:8000/api/users" -H  "Content-Type: application/json" -d '{"username":null, "name":"string"}'</code><br>
it will result in duplicate error as follows</li>
</ul>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="{
  &quot;detail&quot;: [
    {
      &quot;loc&quot;: [
        &quot;body&quot;,
        &quot;username&quot;
      ],
      &quot;msg&quot;: &quot;none is not an allowed value&quot;,
      &quot;type&quot;: &quot;type_error.none.not_allowed&quot;
    },
    {
      &quot;loc&quot;: [
        &quot;body&quot;,
        &quot;username&quot;
      ],
      &quot;msg&quot;: &quot;none is not an allowed value&quot;,
      &quot;type&quot;: &quot;type_error.none.not_allowed&quot;
    }
  ]
}"><pre class="notranslate"><code class="notranslate">{
  "detail": [
    {
      "loc": [
        "body",
        "username"
      ],
      "msg": "none is not an allowed value",
      "type": "type_error.none.not_allowed"
    },
    {
      "loc": [
        "body",
        "username"
      ],
      "msg": "none is not an allowed value",
      "type": "type_error.none.not_allowed"
    }
  ]
}
</code></pre></div>
<h3 dir="auto">Environment</h3>
<ul dir="auto">
<li>
<p dir="auto">OS: macOS:</p>
</li>
<li>
<p dir="auto">FastAPI Version 0.63.0:</p>
</li>
<li>
<p dir="auto">Python version: 3.9.1</p>
</li>
</ul>
<p dir="auto">Really impressed with FastAPI so far... I have search docs github, tickets and googled the issue described below.</p>
<h3 dir="auto">Description</h3>
<p dir="auto">How best to work with related tables and corresponding nested pydantic models whilst persisting data in a relational database in an async application?</p>
<h3 dir="auto">Additional context</h3>
<p dir="auto">I have been attempting to extend the example in the docs<br>
<a href="https://fastapi.tiangolo.com/advanced/async-sql-databases/" rel="nofollow">https://fastapi.tiangolo.com/advanced/async-sql-databases/</a><br>
which relies on <a href="https://github.com/encode/databases">https://github.com/encode/databases</a></p>
<p dir="auto">Using three test pydantic models as an example:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class UserModel(BaseModel):
    id: int
    title: str = Field(..., min_length=2, max_length=50)
    firstname: str = Field(..., min_length=1, max_length=50)
    firstname: str = Field(..., min_length=1, max_length=50)
    username: str = Field(..., min_length=3, max_length=50)
    email: str = Field(..., min_length=3, max_length=50)
    favourite_book: int = Field(...)

class FavouriteBook(BaseModel):
    id: int
    title: str = Field(...)
    author: str = Field(...)


class ExtendedUser(BaseModel):
    id: int
    title: str = Field(..., min_length=2, max_length=50)
    firstname: str = Field(..., min_length=1, max_length=50)
    firstname: str = Field(..., min_length=1, max_length=50)
    username: str = Field(..., min_length=3, max_length=50)
    email: str = Field(..., min_length=3, max_length=50)
    favourite_book: FavouriteBook
"><pre class="notranslate"><code class="notranslate">class UserModel(BaseModel):
    id: int
    title: str = Field(..., min_length=2, max_length=50)
    firstname: str = Field(..., min_length=1, max_length=50)
    firstname: str = Field(..., min_length=1, max_length=50)
    username: str = Field(..., min_length=3, max_length=50)
    email: str = Field(..., min_length=3, max_length=50)
    favourite_book: int = Field(...)

class FavouriteBook(BaseModel):
    id: int
    title: str = Field(...)
    author: str = Field(...)


class ExtendedUser(BaseModel):
    id: int
    title: str = Field(..., min_length=2, max_length=50)
    firstname: str = Field(..., min_length=1, max_length=50)
    firstname: str = Field(..., min_length=1, max_length=50)
    username: str = Field(..., min_length=3, max_length=50)
    email: str = Field(..., min_length=3, max_length=50)
    favourite_book: FavouriteBook

</code></pre></div>
<p dir="auto">the route would ideally be along the lines of...</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="@router.get(&quot;/extended&quot;, response_model=List[ExtendedUser])
async def list():
    query =  **sqlAlchemy/databases call that works**
    return database.fetch_all(query=query)
"><pre class="notranslate"><code class="notranslate">@router.get("/extended", response_model=List[ExtendedUser])
async def list():
    query =  **sqlAlchemy/databases call that works**
    return database.fetch_all(query=query)

</code></pre></div>
<p dir="auto">How can a user create a route that returns the nested ExtendedUser from the database without resorting to performing two queries?<br>
An SQL join is a standard way to do this with a single query. However, this does not work with SQLAlchemy core as the two tables contain 'id' and 'title' columns.<br>
It is possible to work with SQLAlchemy orm - but not in an async way as far as I know. (async is my reason for using FastAPI ). I could rename the columns to something unique ( but to rename 'id' column seems like poor database design to me).</p>