SQLAlchemy ORM relationship loading and N+1 prevention

<p>I keep getting MissingGreenlet errors or N+1 queries when accessing related objects on SQLAlchemy models in an async context. I need to understand which loading strategies work with async and how to eagerly load.</p>

<p>Eager loading strategies for async SQLAlchemy:</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="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"># selectinload -- separate SELECT IN query (best for to-many)</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"># Separate: SELECT * FROM tags WHERE id IN (...)</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="n">trace</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">scalar_one_or_none</span><span class="p">()</span> <span class="c1"># trace.tags is loaded -- safe to access</span> <span class="c1"># joinedload -- LEFT OUTER JOIN (best for to-one)</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"># JOIN users ON ...</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="c1"># Use scalar_one() not scalar_one_or_none() with joinedload (avoids unique row issues)</span> <span class="n">trace</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">unique</span><span class="p">()</span><span class="o">.</span><span class="n">scalar_one</span><span class="p">()</span> <span class="c1"># Nested loading:</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">User</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">User</span><span class="o">.</span><span class="n">traces</span><span class="p">)</span><span class="o">.</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="p">)</span> <span class="c1"># Prevent accidental lazy loads:</span> <span class="k">class</span><span class="w"> </span><span class="nc">Trace</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">contributor</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="s1">'User'</span><span class="p">]</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s1">'User'</span><span class="p">,</span> <span class="n">lazy</span><span class="o">=</span><span class="s1">'raise'</span><span class="p">)</span> </code></pre></div> <p>Key points: - Never use default lazy='select' in async -- raises MissingGreenlet - selectinload for one-to-many and many-to-many (separate query, efficient) - joinedload for many-to-one (JOIN, efficient for single item) - lazy='raise' in model definition catches accidental lazy access in tests - scalars().unique().all() deduplicates rows when using joins</p>