Files
responder/tutorial-sqlalchemy.html
2026-07-14 04:14:56 +00:00

426 lines
39 KiB
HTML
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<!DOCTYPE html>
<html lang="en" data-content_root="./">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" /><meta name="viewport" content="width=device-width, initial-scale=1" />
<title>Using SQLAlchemy &#8212; responder 9.2.0 documentation</title>
<link rel="stylesheet" type="text/css" href="_static/pygments.css?v=5ecbeea2" />
<link rel="stylesheet" type="text/css" href="_static/basic.css?v=b08954a9" />
<link rel="stylesheet" type="text/css" href="_static/alabaster.css?v=eb4102c5" />
<link rel="stylesheet" type="text/css" href="_static/copybutton.css?v=76b2166b" />
<link rel="stylesheet" type="text/css" href="_static/sphinx-design.min.css?v=95c83b7e" />
<link rel="stylesheet" type="text/css" href="_static/design-elements.e5416f61bae5d36adc6d722a2b6f8cff.css?v=452a8e97" />
<script src="_static/documentation_options.js?v=e8b940be"></script>
<script src="_static/doctools.js?v=fd6eb6e6"></script>
<script src="_static/sphinx_highlight.js?v=6ffebe34"></script>
<script src="_static/clipboard.min.js?v=a7894cd8"></script>
<script src="_static/copybutton.js?v=fd10adb8"></script>
<script>
</script>
<script src="_static/design-tabs.js?v=f930bc37"></script>
<script src="_static/design-elements.bbdccc18c4abea9397628f9fea3d48c2.js?v=03c7770e"></script>
<link rel="index" title="Index" href="genindex.html" />
<link rel="search" title="Search" href="search.html" />
<link rel="next" title="Authentication" href="tutorial-auth.html" />
<link rel="prev" title="Building a REST API" href="tutorial-rest.html" />
<link rel="stylesheet" href="_static/custom.css" type="text/css" />
</head><body>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body" role="main">
<section id="using-sqlalchemy">
<h1>Using SQLAlchemy<a class="headerlink" href="#using-sqlalchemy" title="Link to this heading"></a></h1>
<p>Most real web applications need a database. This guide shows how to
integrate <a class="reference external" href="https://www.sqlalchemy.org/">SQLAlchemy</a> with Responder,
using async support and the lifespan pattern for connection management.</p>
<p>SQLAlchemy is the most popular Python database toolkit. It gives you an
ORM (Object-Relational Mapper) for working with databases using Python
classes instead of raw SQL, plus a powerful query builder for when you
need fine-grained control.</p>
<section id="installation">
<h2>Installation<a class="headerlink" href="#installation" title="Link to this heading"></a></h2>
<p>Install SQLAlchemy with async support and an async database driver.
We’ll use SQLite for simplicity, but the pattern works with PostgreSQL,
MySQL, and any other database SQLAlchemy supports:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ uv pip install &#39;sqlalchemy[asyncio]&#39; aiosqlite
</pre></div>
</div>
</section>
<section id="define-your-models">
<h2>Define Your Models<a class="headerlink" href="#define-your-models" title="Link to this heading"></a></h2>
<p>SQLAlchemy models map Python classes to database tables. Each attribute
becomes a column:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># models.py</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">String</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.orm</span><span class="w"> </span><span class="kn">import</span> <span class="n">DeclarativeBase</span><span class="p">,</span> <span class="n">Mapped</span><span class="p">,</span> <span class="n">mapped_column</span>
<span class="k">class</span><span class="w"> </span><span class="nc">Base</span><span class="p">(</span><span class="n">DeclarativeBase</span><span class="p">):</span>
<span class="k">pass</span>
<span class="k">class</span><span class="w"> </span><span class="nc">Book</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s2">&quot;books&quot;</span>
<span class="nb">id</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">int</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">autoincrement</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">title</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">str</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="n">author</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">str</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="n">year</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">int</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="n">isbn</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">str</span> <span class="o">|</span> <span class="kc">None</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">String</span><span class="p">,</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
</pre></div>
</div>
<p>This uses SQLAlchemy 2.0’s <code class="docutils literal notranslate"><span class="pre">Mapped</span></code> type annotations and
<code class="docutils literal notranslate"><span class="pre">mapped_column()</span></code>, which give you type checker support and cleaner
syntax than the older <code class="docutils literal notranslate"><span class="pre">Column()</span></code> style. Each model class corresponds
to a table, and each <code class="docutils literal notranslate"><span class="pre">mapped_column()</span></code> corresponds to a column.</p>
</section>
<section id="database-setup">
<h2>Database Setup<a class="headerlink" href="#database-setup" title="Link to this heading"></a></h2>
<p>Create the async engine and session factory. The <em>engine</em> manages
the connection pool. The <em>session</em> is your unit of work — you use it to
query and modify data within a transaction:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># database.py</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.ext.asyncio</span><span class="w"> </span><span class="kn">import</span> <span class="n">create_async_engine</span><span class="p">,</span> <span class="n">async_sessionmaker</span>
<span class="n">DATABASE_URL</span> <span class="o">=</span> <span class="s2">&quot;sqlite+aiosqlite:///./books.db&quot;</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_async_engine</span><span class="p">(</span><span class="n">DATABASE_URL</span><span class="p">,</span> <span class="n">echo</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">async_session</span> <span class="o">=</span> <span class="n">async_sessionmaker</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="n">expire_on_commit</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">echo=True</span></code> flag prints all SQL queries to the console — very
helpful during development, but you’ll want to disable it in production.</p>
<p>The <code class="docutils literal notranslate"><span class="pre">expire_on_commit=False</span></code> flag keeps model attributes accessible
after a commit, which is convenient for returning created objects in
API responses.</p>
</section>
<section id="lifespan-for-startup-and-shutdown">
<h2>Lifespan for Startup and Shutdown<a class="headerlink" href="#lifespan-for-startup-and-shutdown" title="Link to this heading"></a></h2>
<p>Use Responder’s lifespan context manager to create the database tables
on startup and dispose of connections on shutdown:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># app.py</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">contextlib</span><span class="w"> </span><span class="kn">import</span> <span class="n">asynccontextmanager</span>
<span class="kn">import</span><span class="w"> </span><span class="nn">responder</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">database</span><span class="w"> </span><span class="kn">import</span> <span class="n">engine</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">models</span><span class="w"> </span><span class="kn">import</span> <span class="n">Base</span>
<span class="nd">@asynccontextmanager</span>
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">lifespan</span><span class="p">(</span><span class="n">app</span><span class="p">):</span>
<span class="c1"># Startup: create tables</span>
<span class="k">async</span> <span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">conn</span><span class="p">:</span>
<span class="k">await</span> <span class="n">conn</span><span class="o">.</span><span class="n">run_sync</span><span class="p">(</span><span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">)</span>
<span class="k">yield</span>
<span class="c1"># Shutdown: close all connections</span>
<span class="k">await</span> <span class="n">engine</span><span class="o">.</span><span class="n">dispose</span><span class="p">()</span>
<span class="n">api</span> <span class="o">=</span> <span class="n">responder</span><span class="o">.</span><span class="n">API</span><span class="p">(</span><span class="n">lifespan</span><span class="o">=</span><span class="n">lifespan</span><span class="p">,</span> <span class="n">sessions</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
</pre></div>
</div>
<p>This is the proper way to manage database connections in an async
application. The lifespan context manager ensures that:</p>
<ol class="arabic simple">
<li><p>Tables are created before the first request</p></li>
<li><p>The connection pool is properly closed when the server shuts down</p></li>
<li><p>If table creation fails, the server won’t start</p></li>
</ol>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>We pass <code class="docutils literal notranslate"><span class="pre">sessions=False</span></code> because this is a stateless REST API with
no cookie sessions. Without it, Responder mints a random per-process
signing key and logs a startup warning. If your app <em>does</em> use
<code class="docutils literal notranslate"><span class="pre">req.session</span></code>, set a stable <code class="docutils literal notranslate"><span class="pre">secret_key=</span></code> (or the
<code class="docutils literal notranslate"><span class="pre">RESPONDER_SECRET_KEY</span></code> environment variable) instead — see the
<a class="reference internal" href="tour.html"><span class="doc">Feature Tour</span></a> for the full story.</p>
</div>
</section>
<section id="crud-endpoints">
<h2>CRUD Endpoints<a class="headerlink" href="#crud-endpoints" title="Link to this heading"></a></h2>
<p>First, define the Pydantic schemas Responder uses to validate the request
body and shape the response:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span><span class="w"> </span><span class="nn">pydantic</span><span class="w"> </span><span class="kn">import</span> <span class="n">BaseModel</span><span class="p">,</span> <span class="n">ConfigDict</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">select</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">responder</span><span class="w"> </span><span class="kn">import</span> <span class="n">abort</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">database</span><span class="w"> </span><span class="kn">import</span> <span class="n">async_session</span>
<span class="kn">from</span><span class="w"> </span><span class="nn">models</span><span class="w"> </span><span class="kn">import</span> <span class="n">Book</span>
<span class="k">class</span><span class="w"> </span><span class="nc">BookIn</span><span class="p">(</span><span class="n">BaseModel</span><span class="p">):</span>
<span class="n">title</span><span class="p">:</span> <span class="nb">str</span>
<span class="n">author</span><span class="p">:</span> <span class="nb">str</span>
<span class="n">year</span><span class="p">:</span> <span class="nb">int</span>
<span class="n">isbn</span><span class="p">:</span> <span class="nb">str</span> <span class="o">|</span> <span class="kc">None</span> <span class="o">=</span> <span class="kc">None</span>
<span class="k">class</span><span class="w"> </span><span class="nc">BookOut</span><span class="p">(</span><span class="n">BaseModel</span><span class="p">):</span>
<span class="n">model_config</span> <span class="o">=</span> <span class="n">ConfigDict</span><span class="p">(</span><span class="n">from_attributes</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="nb">id</span><span class="p">:</span> <span class="nb">int</span>
<span class="n">title</span><span class="p">:</span> <span class="nb">str</span>
<span class="n">author</span><span class="p">:</span> <span class="nb">str</span>
<span class="n">year</span><span class="p">:</span> <span class="nb">int</span>
<span class="n">isbn</span><span class="p">:</span> <span class="nb">str</span> <span class="o">|</span> <span class="kc">None</span> <span class="o">=</span> <span class="kc">None</span>
</pre></div>
</div>
<p><code class="docutils literal notranslate"><span class="pre">from_attributes=True</span></code> tells Pydantic to read values off SQLAlchemy
model attributes (not just dicts), so <code class="docutils literal notranslate"><span class="pre">BookOut.model_validate(book)</span></code>
turns a <code class="docutils literal notranslate"><span class="pre">Book</span></code> ORM object straight into a response model.</p>
<section id="a-session-per-request">
<h3>A Session Per Request<a class="headerlink" href="#a-session-per-request" title="Link to this heading"></a></h3>
<p>Rather than open <code class="docutils literal notranslate"><span class="pre">async</span> <span class="pre">with</span> <span class="pre">async_session()</span></code> by hand in every handler,
register the session as a <strong>dependency</strong>. Each request gets its own,
cleaned up automatically:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">dependency</span><span class="p">()</span>
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">session</span><span class="p">():</span>
<span class="k">async</span> <span class="k">with</span> <span class="n">async_session</span><span class="p">()</span> <span class="k">as</span> <span class="n">session</span><span class="p">:</span>
<span class="k">yield</span> <span class="n">session</span>
</pre></div>
</div>
<p>Responder injects this into any handler that declares a <code class="docutils literal notranslate"><span class="pre">session</span></code>
parameter. The code after <code class="docutils literal notranslate"><span class="pre">yield</span></code> — here, the <code class="docutils literal notranslate"><span class="pre">async</span> <span class="pre">with</span></code> block
closing the session — runs as teardown once the response has been sent,
even if the handler raised. That’s one session per request, with
guaranteed cleanup and no boilerplate in the views. A request-scoped
session layered on the module-level engine is the canonical dependency;
see the <a class="reference internal" href="tour.html"><span class="doc">Feature Tour</span></a> for the full dependency-injection guide.</p>
</section>
<section id="the-handlers">
<h3>The Handlers<a class="headerlink" href="#the-handlers" title="Link to this heading"></a></h3>
<p>Each handler declares <code class="docutils literal notranslate"><span class="pre">session</span></code> to receive the injected session. On
write methods, a Pydantic-typed parameter (<code class="docutils literal notranslate"><span class="pre">book:</span> <span class="pre">BookIn</span></code>) is
auto-filled with the validated request body — an invalid body returns
<code class="docutils literal notranslate"><span class="pre">422</span></code> before the handler runs, so there’s no manual <code class="docutils literal notranslate"><span class="pre">await</span> <span class="pre">req.media()</span></code>.
A <code class="docutils literal notranslate"><span class="pre">-&gt;</span> <span class="pre">BookOut</span></code> or <code class="docutils literal notranslate"><span class="pre">-&gt;</span> <span class="pre">list[BookOut]</span></code> return annotation makes Responder
validate and serialize the returned value against that contract, and both
models flow into the generated <a class="reference internal" href="tour.html"><span class="doc">OpenAPI schema</span></a> automatically.</p>
<p><strong>List all books</strong>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span><span class="s2">&quot;/books&quot;</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">&quot;GET&quot;</span><span class="p">])</span>
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">list_books</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">session</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="nb">list</span><span class="p">[</span><span class="n">BookOut</span><span class="p">]:</span>
<span class="n">result</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">select</span><span class="p">(</span><span class="n">Book</span><span class="p">))</span>
<span class="n">books</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">scalars</span><span class="p">()</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<span class="k">return</span> <span class="p">[</span><span class="n">BookOut</span><span class="o">.</span><span class="n">model_validate</span><span class="p">(</span><span class="n">b</span><span class="p">)</span> <span class="k">for</span> <span class="n">b</span> <span class="ow">in</span> <span class="n">books</span><span class="p">]</span>
</pre></div>
</div>
<p><strong>Create a book</strong>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span>
<span class="s2">&quot;/books&quot;</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">&quot;POST&quot;</span><span class="p">],</span> <span class="n">check_existing</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="n">status_code</span><span class="o">=</span><span class="mi">201</span>
<span class="p">)</span>
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">create_book</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">book</span><span class="p">:</span> <span class="n">BookIn</span><span class="p">,</span> <span class="n">session</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">BookOut</span><span class="p">:</span>
<span class="n">new</span> <span class="o">=</span> <span class="n">Book</span><span class="p">(</span><span class="o">**</span><span class="n">book</span><span class="o">.</span><span class="n">model_dump</span><span class="p">())</span>
<span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">new</span><span class="p">)</span>
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">new</span><span class="p">)</span>
<span class="k">return</span> <span class="n">BookOut</span><span class="o">.</span><span class="n">model_validate</span><span class="p">(</span><span class="n">new</span><span class="p">)</span>
</pre></div>
</div>
<p><strong>Get a single book</strong>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span><span class="s2">&quot;/books/{book_id:int}&quot;</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">&quot;GET&quot;</span><span class="p">])</span>
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">get_book</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">book_id</span><span class="p">,</span> <span class="n">session</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">BookOut</span><span class="p">:</span>
<span class="n">book</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">Book</span><span class="p">,</span> <span class="n">book_id</span><span class="p">)</span>
<span class="k">if</span> <span class="n">book</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">abort</span><span class="p">(</span><span class="mi">404</span><span class="p">,</span> <span class="n">detail</span><span class="o">=</span><span class="s2">&quot;Book not found&quot;</span><span class="p">)</span>
<span class="k">return</span> <span class="n">BookOut</span><span class="o">.</span><span class="n">model_validate</span><span class="p">(</span><span class="n">book</span><span class="p">)</span>
</pre></div>
</div>
<p><strong>Update a book</strong>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span><span class="s2">&quot;/books/{book_id:int}&quot;</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">&quot;PUT&quot;</span><span class="p">],</span> <span class="n">check_existing</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">update_book</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">book_id</span><span class="p">,</span> <span class="n">book</span><span class="p">:</span> <span class="n">BookIn</span><span class="p">,</span> <span class="n">session</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">BookOut</span><span class="p">:</span>
<span class="n">existing</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">Book</span><span class="p">,</span> <span class="n">book_id</span><span class="p">)</span>
<span class="k">if</span> <span class="n">existing</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">abort</span><span class="p">(</span><span class="mi">404</span><span class="p">,</span> <span class="n">detail</span><span class="o">=</span><span class="s2">&quot;Book not found&quot;</span><span class="p">)</span>
<span class="k">for</span> <span class="n">key</span><span class="p">,</span> <span class="n">value</span> <span class="ow">in</span> <span class="n">book</span><span class="o">.</span><span class="n">model_dump</span><span class="p">()</span><span class="o">.</span><span class="n">items</span><span class="p">():</span>
<span class="nb">setattr</span><span class="p">(</span><span class="n">existing</span><span class="p">,</span> <span class="n">key</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span>
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">refresh</span><span class="p">(</span><span class="n">existing</span><span class="p">)</span>
<span class="k">return</span> <span class="n">BookOut</span><span class="o">.</span><span class="n">model_validate</span><span class="p">(</span><span class="n">existing</span><span class="p">)</span>
</pre></div>
</div>
<p><strong>Delete a book</strong>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="nd">@api</span><span class="o">.</span><span class="n">route</span><span class="p">(</span><span class="s2">&quot;/books/{book_id:int}&quot;</span><span class="p">,</span> <span class="n">methods</span><span class="o">=</span><span class="p">[</span><span class="s2">&quot;DELETE&quot;</span><span class="p">],</span> <span class="n">check_existing</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">delete_book</span><span class="p">(</span><span class="n">req</span><span class="p">,</span> <span class="n">resp</span><span class="p">,</span> <span class="o">*</span><span class="p">,</span> <span class="n">book_id</span><span class="p">,</span> <span class="n">session</span><span class="p">):</span>
<span class="n">book</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">Book</span><span class="p">,</span> <span class="n">book_id</span><span class="p">)</span>
<span class="k">if</span> <span class="n">book</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
<span class="n">abort</span><span class="p">(</span><span class="mi">404</span><span class="p">,</span> <span class="n">detail</span><span class="o">=</span><span class="s2">&quot;Book not found&quot;</span><span class="p">)</span>
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">delete</span><span class="p">(</span><span class="n">book</span><span class="p">)</span>
<span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="n">resp</span><span class="o">.</span><span class="n">no_content</span><span class="p">()</span>
</pre></div>
</div>
<p>A couple of things worth noting. <code class="docutils literal notranslate"><span class="pre">abort(404,</span> <span class="pre">detail=&quot;Book</span> <span class="pre">not</span> <span class="pre">found&quot;)</span></code>
raises a rendered, content-negotiated HTTP error and halts the handler
immediately — no need to set <code class="docutils literal notranslate"><span class="pre">resp.status_code</span></code> and <code class="docutils literal notranslate"><span class="pre">return</span></code> by hand.
And assigning a model (or a list of them) to <code class="docutils literal notranslate"><span class="pre">resp.media</span></code> just works:
Responder serializes Pydantic models natively, so the trailing
<code class="docutils literal notranslate"><span class="pre">.model_dump()</span></code> is no longer needed.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>The <code class="docutils literal notranslate"><span class="pre">-&gt;</span> <span class="pre">BookOut</span></code> return validation only fires when <code class="docutils literal notranslate"><span class="pre">resp.media</span></code> is
a dict or a Pydantic model. A raw SQLAlchemy ORM object assigned to
<code class="docutils literal notranslate"><span class="pre">resp.media</span></code> is <strong>not</strong> auto-validated — that’s why every handler
wraps the result with <code class="docutils literal notranslate"><span class="pre">BookOut.model_validate(book)</span></code> before
assigning it to <code class="docutils literal notranslate"><span class="pre">resp.media</span></code>.</p>
</div>
</section>
</section>
<section id="run-it">
<h2>Run It<a class="headerlink" href="#run-it" title="Link to this heading"></a></h2>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">if</span> <span class="vm">__name__</span> <span class="o">==</span> <span class="s2">&quot;__main__&quot;</span><span class="p">:</span>
<span class="n">api</span><span class="o">.</span><span class="n">run</span><span class="p">()</span>
</pre></div>
</div>
<p>Start the server and you’ll see SQLAlchemy’s SQL echo in the console.
The SQLite database file <code class="docutils literal notranslate"><span class="pre">books.db</span></code> is created automatically on first
startup.</p>
</section>
<section id="using-postgresql">
<h2>Using PostgreSQL<a class="headerlink" href="#using-postgresql" title="Link to this heading"></a></h2>
<p>To switch to PostgreSQL, just change the connection URL and driver:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ uv pip install asyncpg
</pre></div>
</div>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">DATABASE_URL</span> <span class="o">=</span> <span class="s2">&quot;postgresql+asyncpg://user:pass@localhost/mydb&quot;</span>
</pre></div>
</div>
<p>Everything else stays the same. SQLAlchemy abstracts the database
differences so your application code doesn’t need to change.</p>
</section>
<section id="tips">
<h2>Tips<a class="headerlink" href="#tips" title="Link to this heading"></a></h2>
<ul class="simple">
<li><p>Give each request its own session and transaction — never share one
across requests. The <code class="docutils literal notranslate"><span class="pre">session</span></code> dependency above does this for you;
declaring <code class="docutils literal notranslate"><span class="pre">session</span></code> in a handler is all it takes.</p></li>
<li><p>For complex queries, use SQLAlchemy’s <code class="docutils literal notranslate"><span class="pre">select()</span></code> with <code class="docutils literal notranslate"><span class="pre">.where()</span></code>,
<code class="docutils literal notranslate"><span class="pre">.order_by()</span></code>, <code class="docutils literal notranslate"><span class="pre">.limit()</span></code>, and <code class="docutils literal notranslate"><span class="pre">.offset()</span></code> — it composes
naturally.</p></li>
<li><p>In production, use connection pooling (SQLAlchemy does this by
default) and set pool size limits appropriate for your database.</p></li>
<li><p>Consider <a class="reference external" href="https://alembic.sqlalchemy.org/">Alembic</a> for database
migrations — it tracks schema changes over time so you can evolve
your database without losing data.</p></li>
</ul>
</section>
</section>
</div>
</div>
</div>
<div class="sphinxsidebar" role="navigation" aria-label="Main">
<div class="sphinxsidebarwrapper"><p class="logo">
<a href="index.html">
<img class="logo" src="_static/responder.png" />
</a>
</p>
<p>
<strong>Responder</strong> — a familiar HTTP service framework for Python.
<br />
<small>v9.2.0</small>
</p>
<h3>Useful Links</h3>
<ul>
<li><a href="https://github.com/kennethreitz/responder">Responder @ GitHub</a></li>
<li><a href="https://pypi.org/project/responder/">Responder @ PyPI</a></li>
<li><a href="https://github.com/kennethreitz/responder/issues">Issue Tracker</a></li>
</ul><h3>Navigation</h3>
<p class="caption" role="heading"><span class="caption-text">User Guide</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="quickstart.html">Quick Start</a></li>
<li class="toctree-l1"><a class="reference internal" href="tour.html">Feature Tour</a></li>
<li class="toctree-l1"><a class="reference internal" href="examples.html">Examples</a></li>
<li class="toctree-l1"><a class="reference internal" href="routers.html">Composing Apps with Routers</a></li>
<li class="toctree-l1"><a class="reference internal" href="guide-config.html">Configuration</a></li>
<li class="toctree-l1"><a class="reference internal" href="clientgen.html">Generated Clients</a></li>
<li class="toctree-l1"><a class="reference internal" href="deployment.html">Deployment</a></li>
<li class="toctree-l1"><a class="reference internal" href="testing.html">Testing</a></li>
</ul>
<p class="caption" role="heading"><span class="caption-text">Tutorials</span></p>
<ul class="current">
<li class="toctree-l1"><a class="reference internal" href="tutorial-rest.html">Building a REST API</a></li>
<li class="toctree-l1 current"><a class="current reference internal" href="#">Using SQLAlchemy</a><ul>
<li class="toctree-l2"><a class="reference internal" href="#installation">Installation</a></li>
<li class="toctree-l2"><a class="reference internal" href="#define-your-models">Define Your Models</a></li>
<li class="toctree-l2"><a class="reference internal" href="#database-setup">Database Setup</a></li>
<li class="toctree-l2"><a class="reference internal" href="#lifespan-for-startup-and-shutdown">Lifespan for Startup and Shutdown</a></li>
<li class="toctree-l2"><a class="reference internal" href="#crud-endpoints">CRUD Endpoints</a></li>
<li class="toctree-l2"><a class="reference internal" href="#run-it">Run It</a></li>
<li class="toctree-l2"><a class="reference internal" href="#using-postgresql">Using PostgreSQL</a></li>
<li class="toctree-l2"><a class="reference internal" href="#tips">Tips</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="tutorial-auth.html">Authentication</a></li>
<li class="toctree-l1"><a class="reference internal" href="tutorial-websockets.html">WebSocket Tutorial</a></li>
<li class="toctree-l1"><a class="reference internal" href="tutorial-middleware.html">Writing Middleware</a></li>
<li class="toctree-l1"><a class="reference internal" href="tutorial-flask.html">Migrating from Flask</a></li>
</ul>
<p class="caption" role="heading"><span class="caption-text">Reference</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="api.html">API Reference</a></li>
<li class="toctree-l1"><a class="reference internal" href="cli.html">Command Line Interface</a></li>
<li class="toctree-l1"><a class="reference internal" href="runtime-contracts.html">Runtime Contracts</a></li>
</ul>
<p class="caption" role="heading"><span class="caption-text">Project</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="changes.html">Changelog</a></li>
<li class="toctree-l1"><a class="reference internal" href="migration-v9.html">Migrating to v9</a></li>
<li class="toctree-l1"><a class="reference internal" href="migration-v8.html">Migrating to v8</a></li>
<li class="toctree-l1"><a class="reference internal" href="migration-v7.html">Migrating to v7</a></li>
<li class="toctree-l1"><a class="reference internal" href="migration-v6.html">Migrating to v6</a></li>
<li class="toctree-l1"><a class="reference internal" href="migration-v5.html">Migrating to v5</a></li>
<li class="toctree-l1"><a class="reference internal" href="sandbox.html">Sandbox</a></li>
<li class="toctree-l1"><a class="reference internal" href="backlog.html">Backlog</a></li>
</ul>
<div class="relations">
<h3>Related Topics</h3>
<ul>
<li><a href="index.html">Documentation overview</a><ul>
<li>Previous: <a href="tutorial-rest.html" title="previous chapter">Building a REST API</a></li>
<li>Next: <a href="tutorial-auth.html" title="next chapter">Authentication</a></li>
</ul></li>
</ul>
</div>
<search id="searchbox" style="display: none" role="search">
<h3 id="searchlabel">Quick search</h3>
<div class="searchformwrapper">
<form class="search" action="search.html" method="get">
<input type="text" name="q" aria-labelledby="searchlabel" autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false"/>
<input type="submit" value="Go" />
</form>
</div>
</search>
<script>document.getElementById('searchbox').style.display = "block"</script>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="footer">
&#169;2018-2026, Kenneth Reitz.
|
<a href="_sources/tutorial-sqlalchemy.rst.txt"
rel="nofollow">Page source</a>
</div>
<a href="https://github.com/kennethreitz/responder" class="github">
<img src="_static/forkme_right_darkblue.png" alt="Fork me on GitHub" class="github"/>
</a>
</body>
</html>