Alembic autogenerate with custom types and constraints

<p>Alembic's autogenerate misses some schema changes: custom PostgreSQL types, check constraints, and index changes. I need to configure autogenerate to detect more changes and add manual DDL for unsupported features.</p>

<p>Alembic autogenerate configuration and limitations:</p> <div class="highlight"><pre><span></span><code><span class="c1"># migrations/env.py -- configure comparison behavior</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">context</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">event</span> <span class="k">def</span><span class="w"> </span><span class="nf">run_migrations_online</span><span class="p">():</span> <span class="n">connectable</span> <span class="o">=</span> <span class="n">async_engine_from_config</span><span class="p">(</span><span class="o">...</span><span class="p">)</span> <span class="k">with</span> <span class="n">connectable</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">connection</span><span class="p">:</span> <span class="n">context</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span> <span class="n">connection</span><span class="o">=</span><span class="n">connection</span><span class="p">,</span> <span class="n">target_metadata</span><span class="o">=</span><span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span> <span class="c1"># Include schema objects autogenerate compares:</span> <span class="n">compare_type</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="c1"># Detect column type changes</span> <span class="n">compare_server_default</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="c1"># Detect server default changes</span> <span class="n">include_schemas</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="c1"># Multi-schema support</span> <span class="c1"># Render custom types in migrations:</span> <span class="n">render_as_batch</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="p">)</span> </code></pre></div> <p>For features autogenerate misses, use manual op.execute():</p> <div class="highlight"><pre><span></span><code><span class="c1"># migrations/versions/0010_add_check_constraints.py</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="k">def</span><span class="w"> </span><span class="nf">upgrade</span><span class="p">():</span> <span class="c1"># Check constraints (autogenerate misses these):</span> <span class="n">op</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"""</span> <span class="s2"> ALTER TABLE traces</span> <span class="s2"> ADD CONSTRAINT ck_trust_score_range</span> <span class="s2"> CHECK (trust_score &gt;= 0.0 AND trust_score &lt;= 1.0)</span> <span class="s2"> """</span><span class="p">)</span> <span class="c1"># Partial indexes (autogenerate misses these):</span> <span class="n">op</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"""</span> <span class="s2"> CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_traces_validated</span> <span class="s2"> ON traces (created_at DESC)</span> <span class="s2"> WHERE status = 'validated'</span> <span class="s2"> """</span><span class="p">)</span> <span class="c1"># Enum types:</span> <span class="n">op</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"CREATE TYPE vote_type AS ENUM ('confirmed', 'disputed')"</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="n">op</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"ALTER TABLE traces DROP CONSTRAINT ck_trust_score_range"</span><span class="p">)</span> <span class="n">op</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"DROP INDEX CONCURRENTLY IF EXISTS ix_traces_validated"</span><span class="p">)</span> <span class="n">op</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"DROP TYPE IF EXISTS vote_type"</span><span class="p">)</span> </code></pre></div> <p>Key points: - compare_type=True detects VARCHAR -&gt; TEXT changes - Partial indexes, HNSW indexes, custom functions -- always manual - Run autogenerate and review the output -- don't blindly apply - Add IF NOT EXISTS / IF EXISTS for idempotent manual migrations - Always test downgrade() -- it is used in rollbacks</p>