postgresql - Check if a table column exists in the database using SQLAlchemy and Alembic

Question

I'm using Alembic as migration tool and I'm launching the following pseudo script on an already updated database (no revision entries for Alembic, the database schema is just up to date).

revision = '1067fd2d11c8'
down_revision = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.add_column('box', sa.Column('has_data', sa.Boolean, server_default='0'))


def downgrade():
    pass

It gives me the following error only with PostgreSQL behind (it's all good with MySQL):

INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [root] (ProgrammingError) ERREUR:  la colonne ? has_data ? de la relation ? box ? existe déjà

Last line means the column has_data already exists.

I want to check that the column exists before op.add_column.

Answer 1

The easiest answer is not to try to do this. Instead, make your Alembic migrations represent the full layout of the database. Then any migrations you make will be based off the changes to the existing database.

To make a starting migration if you already have a database, temporarily point at an empty database and run alembic revision --autogenerate -m "base". Then, point back at the actual database and run alembic stamp head to say that the current state of the database is represented by the latest migration, without actually running it.

If you don't want to do that for some reason, you can choose not to use --autogenerate and instead generate empty revisions that you fill in with the operations you want. Alembic won't stop you from doing this, it's just much less convenient.