SQLAlchemy lazy loading configuration and N+1 query prevention

<p>SQLAlchemy ORM queries are unexpectedly slow. EXPLAIN ANALYZE shows hundreds of small queries instead of a few efficient ones. The N+1 problem: loading a list of traces then accessing trace.tags triggers one query per trace.</p>

<p>Use eager loading with <code>selectinload</code> or <code>joinedload</code> to prevent N+1 queries:</p> <div class="highlight"><pre><span></span><code><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">selectinload</span><span class="p">,</span> <span class="n">joinedload</span><span class="p">,</span> <span class="n">contains_eager</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="c1"># BAD: N+1 queries</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">get_traces_bad</span><span class="p">(</span><span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="nb">list</span><span class="p">[</span><span class="n">Trace</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">Trace</span><span class="p">)</span><span class="o">.</span><span class="n">limit</span><span class="p">(</span><span class="mi">20</span><span class="p">))</span> <span class="n">traces</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">for</span> <span class="n">trace</span> <span class="ow">in</span> <span class="n">traces</span><span class="p">:</span> <span class="c1"># Each access triggers a new query!</span> <span class="nb">print</span><span class="p">(</span><span class="n">trace</span><span class="o">.</span><span class="n">tags</span><span class="p">)</span> <span class="c1"># SELECT * FROM tags WHERE trace_id = ?</span> <span class="k">return</span> <span class="n">traces</span> <span class="c1"># GOOD: selectinload (best for collections/one-to-many)</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">get_traces_good</span><span class="p">(</span><span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="nb">list</span><span class="p">[</span><span class="n">Trace</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">Trace</span><span class="p">)</span> <span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">selectinload</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">tags</span><span class="p">))</span> <span class="c1"># 2 queries total: traces + all tags</span> <span class="o">.</span><span class="n">limit</span><span class="p">(</span><span class="mi">20</span><span class="p">)</span> <span class="p">)</span> <span class="k">return</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="c1"># joinedload (best for many-to-one/single object)</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">get_trace_with_contributor</span><span class="p">(</span><span class="n">trace_id</span><span class="p">:</span> <span class="nb">str</span><span class="p">,</span> <span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">Trace</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">Trace</span><span class="p">)</span> <span class="o">.</span><span class="n">options</span><span class="p">(</span><span class="n">joinedload</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">contributor</span><span class="p">))</span> <span class="c1"># 1 query with JOIN</span> <span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">trace_id</span><span class="p">)</span> <span class="p">)</span> <span class="k">return</span> <span class="n">result</span><span class="o">.</span><span class="n">scalar_one</span><span class="p">()</span> <span class="c1"># Multiple relationships</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">get_trace_full</span><span class="p">(</span><span class="n">trace_id</span><span class="p">:</span> <span class="nb">str</span><span class="p">,</span> <span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="n">Trace</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">Trace</span><span class="p">)</span> <span class="o">.</span><span class="n">options</span><span class="p">(</span> <span class="n">selectinload</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">tags</span><span class="p">),</span> <span class="n">joinedload</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">contributor</span><span class="p">),</span> <span class="p">)</span> <span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">trace_id</span><span class="p">)</span> <span class="p">)</span> <span class="k">return</span> <span class="n">result</span><span class="o">.</span><span class="n">scalar_one</span><span class="p">()</span> <span class="c1"># Force error on accidental lazy loading</span> <span class="c1"># In model definition:</span> <span class="c1"># contributor: Mapped[User] = relationship('User', lazy='raise')</span> <span class="c1"># This raises sqlalchemy.exc.InvalidRequestError if accessed outside eager load</span> </code></pre></div> <p>Rule: <code>selectinload</code> for one-to-many/many-to-many (issues 2 queries: one for parent, one for all children). <code>joinedload</code> for many-to-one/one-to-one (single JOIN query). Set <code>lazy='raise'</code> on relationships you always want to control explicitly.</p>