alembic batch migration for SQLite and PostgreSQL compatibility

<p>Alembic migration needs to modify an existing column (change type, add constraint) in a way that works on both PostgreSQL (production) and SQLite (tests/development). PostgreSQL supports ALTER COLUMN but SQLite does not.</p>

<p>Use Alembic's batch operations which work on both databases:</p> <div class="highlight"><pre><span></span><code><span class="c1"># alembic/versions/0003_change_status_column.py</span> <span class="sd">"""change status to varchar(20) with constraint</span> <span class="sd">Revision ID: 0003</span> <span class="sd">Downs revision: '0002'</span> <span class="sd">"""</span> <span class="kn">from</span><span class="w"> </span><span class="nn">alembic</span><span class="w"> </span><span class="kn">import</span> <span class="n">op</span> <span class="kn">import</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="nn">sa</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="c1"># batch_alter_table works on SQLite (which doesn't support ALTER COLUMN)</span> <span class="k">with</span> <span class="n">op</span><span class="o">.</span><span class="n">batch_alter_table</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">,</span> <span class="n">schema</span><span class="o">=</span><span class="kc">None</span><span class="p">)</span> <span class="k">as</span> <span class="n">batch_op</span><span class="p">:</span> <span class="c1"># Change column type</span> <span class="n">batch_op</span><span class="o">.</span><span class="n">alter_column</span><span class="p">(</span> <span class="s1">'status'</span><span class="p">,</span> <span class="n">existing_type</span><span class="o">=</span><span class="n">sa</span><span class="o">.</span><span class="n">String</span><span class="p">(),</span> <span class="n">type_</span><span class="o">=</span><span class="n">sa</span><span class="o">.</span><span class="n">String</span><span class="p">(</span><span class="mi">20</span><span class="p">),</span> <span class="n">existing_nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="p">)</span> <span class="c1"># Add index within batch context</span> <span class="n">batch_op</span><span class="o">.</span><span class="n">create_index</span><span class="p">(</span><span class="s1">'idx_traces_status'</span><span class="p">,</span> <span class="p">[</span><span class="s1">'status'</span><span class="p">])</span> <span class="c1"># Add a column with a default value</span> <span class="k">with</span> <span class="n">op</span><span class="o">.</span><span class="n">batch_alter_table</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">)</span> <span class="k">as</span> <span class="n">batch_op</span><span class="p">:</span> <span class="n">batch_op</span><span class="o">.</span><span class="n">add_column</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">'confirmation_count'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Integer</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">server_default</span><span class="o">=</span><span class="s1">'0'</span><span class="p">)</span> <span class="p">)</span> <span class="n">batch_op</span><span class="o">.</span><span class="n">drop_column</span><span class="p">(</span><span class="s1">'old_column'</span><span class="p">)</span> <span class="k">def</span><span class="w"> </span><span class="nf">downgrade</span><span class="p">()</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="k">with</span> <span class="n">op</span><span class="o">.</span><span class="n">batch_alter_table</span><span class="p">(</span><span class="s1">'traces'</span><span class="p">)</span> <span class="k">as</span> <span class="n">batch_op</span><span class="p">:</span> <span class="n">batch_op</span><span class="o">.</span><span class="n">drop_index</span><span class="p">(</span><span class="s1">'idx_traces_status'</span><span class="p">)</span> <span class="n">batch_op</span><span class="o">.</span><span class="n">alter_column</span><span class="p">(</span> <span class="s1">'status'</span><span class="p">,</span> <span class="n">existing_type</span><span class="o">=</span><span class="n">sa</span><span class="o">.</span><span class="n">String</span><span class="p">(</span><span class="mi">20</span><span class="p">),</span> <span class="n">type_</span><span class="o">=</span><span class="n">sa</span><span class="o">.</span><span class="n">String</span><span class="p">(),</span> <span class="p">)</span> <span class="n">batch_op</span><span class="o">.</span><span class="n">add_column</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">'old_column'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">String</span><span class="p">()))</span> <span class="n">batch_op</span><span class="o">.</span><span class="n">drop_column</span><span class="p">(</span><span class="s1">'confirmation_count'</span><span class="p">)</span> <span class="c1"># alembic.ini or env.py — configure for SQLite in tests</span> <span class="c1"># context.configure(</span> <span class="c1"># render_as_batch=True, # Enable batch mode globally</span> <span class="c1"># ...</span> <span class="c1"># )</span> </code></pre></div> <p>SQLite doesn't support <code>ALTER TABLE ... ALTER COLUMN</code> — batch operations work around this by creating a new table, copying data, and renaming. On PostgreSQL, batch operations issue direct DDL. Use <code>render_as_batch=True</code> in <code>env.py</code> to apply this automatically based on the database dialect.</p>