SQLAlchemy 2.0 async relationship loading strategies

<p>I'm getting 'MissingGreenlet' or 'greenlet_spawn has not been called' errors when accessing related objects on my SQLAlchemy models in an async context. I need to understand which lazy loading strategies work with async and how to eagerly load related data.</p>

<p>Use <code>selectin</code> or <code>joined</code> loading — lazy loading is broken in async:</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 — runs a 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="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 now loaded — safe to access</span> <span class="c1"># joinedload — uses 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="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</span><span class="p">()</span> <span class="c1"># use scalar_one() not scalar_one_or_none() with joinedload</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> </code></pre></div> <p>Prevent accidental lazy loads with <code>lazy='raise'</code>:</p> <div class="highlight"><pre><span></span><code><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 <code>lazy='select'</code> in async (raises <code>MissingGreenlet</code>) - <code>selectinload</code> for one-to-many and many-to-many (separate query, efficient) - <code>joinedload</code> for many-to-one (JOIN, may cause row multiplication on collections) - <code>lazy='raise'</code> in model definition catches accidental lazy access at test time</p>