SQLAlchemy relationship cascade and orphan deletion

<p>Deleting a parent record (User) should automatically delete child records (Traces, Votes). Without proper cascade configuration, SQLAlchemy either raises a foreign key constraint error or leaves orphaned rows in the database.</p>

<p>Configure cascade on the parent relationship and add ON DELETE CASCADE at the DB level:</p> <div class="highlight"><pre><span></span><code><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">ForeignKey</span><span class="p">,</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">Mapped</span><span class="p">,</span> <span class="n">mapped_column</span><span class="p">,</span> <span class="n">relationship</span> <span class="k">class</span><span class="w"> </span><span class="nc">User</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="s1">'users'</span> <span class="nb">id</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="n">UUID</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">default</span><span class="o">=</span><span class="n">uuid4</span><span class="p">)</span> <span class="n">email</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="mi">255</span><span class="p">),</span> <span class="n">unique</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span> <span class="c1"># cascade='all, delete-orphan': when User is deleted, delete all Traces</span> <span class="c1"># passive_deletes=True: let the DB handle deletion via ON DELETE CASCADE</span> <span class="n">traces</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">list</span><span class="p">[</span><span class="s1">'Trace'</span><span class="p">]]</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span> <span class="s1">'Trace'</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s1">'contributor'</span><span class="p">,</span> <span class="n">cascade</span><span class="o">=</span><span class="s1">'all, delete-orphan'</span><span class="p">,</span> <span class="n">passive_deletes</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="c1"># Don't load children to delete them</span> <span class="p">)</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">__tablename__</span> <span class="o">=</span> <span class="s1">'traces'</span> <span class="nb">id</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="n">UUID</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">default</span><span class="o">=</span><span class="n">uuid4</span><span class="p">)</span> <span class="n">contributor_id</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="n">UUID</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'users.id'</span><span class="p">,</span> <span class="n">ondelete</span><span class="o">=</span><span class="s1">'CASCADE'</span><span class="p">)</span> <span class="c1"># DB-level cascade</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="n">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">back_populates</span><span class="o">=</span><span class="s1">'traces'</span><span class="p">)</span> <span class="c1"># Alembic migration</span> <span class="k">def</span><span class="w"> </span><span class="nf">upgrade</span><span class="p">()</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="n">op</span><span class="o">.</span><span class="n">create_table</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">UUID</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">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'contributor_id'</span><span class="p">,</span> <span class="n">postgresql</span><span class="o">.</span><span class="n">UUID</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">sa</span><span class="o">.</span><span class="n">ForeignKeyConstraint</span><span class="p">(</span> <span class="p">[</span><span class="s1">'contributor_id'</span><span class="p">],</span> <span class="p">[</span><span class="s1">'users.id'</span><span class="p">],</span> <span class="n">ondelete</span><span class="o">=</span><span class="s1">'CASCADE'</span> <span class="c1"># Critical: must be on the FK constraint</span> <span class="p">),</span> <span class="n">sa</span><span class="o">.</span><span class="n">PrimaryKeyConstraint</span><span class="p">(</span><span class="s1">'id'</span><span class="p">),</span> <span class="p">)</span> <span class="c1"># Usage</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">delete_user</span><span class="p">(</span><span class="n">user_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="kc">None</span><span class="p">:</span> <span class="n">user</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">User</span><span class="p">,</span> <span class="n">user_id</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">user</span><span class="p">)</span> <span class="c1"># Cascades to all traces</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> </code></pre></div> <p><code>passive_deletes=True</code> tells SQLAlchemy to not load and delete children in Python — let the DB handle it via <code>ON DELETE CASCADE</code>. Without <code>passive_deletes</code>, SQLAlchemy issues SELECT then DELETE for each child. Both ORM cascade AND DB-level <code>ondelete='CASCADE'</code> are needed for correct behavior.</p>