3

I'm trying to correctly setup my database migration files and haven't found explicit syntax for the downgrade part. Using Flask-SQLAlchemy, Postgres and Flask-Migrate which uses Alembic

My (reduced) code looks like this:

from alembic import op
import sqlalchemy as sa
import sqlalchemy_utils
from sqlalchemy_searchable import sync_trigger

# revision identifiers, used by Alembic....(cut for space)

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('project_note',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('text', sa.Text(), nullable=False),
    sa.Column('search_vector', sqlalchemy_utils.types.ts_vector.TSVectorType(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_project_note_search_vector', 'project_note', ['search_vector'], unique=False, postgresql_using='gin')
    # ### end Alembic commands ###
    # Manually added commands
    conn = op.get_bind()
    sync_trigger(conn, 'project_note', 'search_vector', ['text'])


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('ix_project_note_search_vector', table_name='project_note')
    op.drop_index(op.f('ix_project_note_project_id'), table_name='project_note')
    op.drop_table('project_note')
    # ### end Alembic commands ###

Is there anything I need to do in the downgrade to cleanup the trigger or does dropping the table do that for me?

I found the documentation and relevant code

from alembic import op
from sqlalchemy_searchable import sync_trigger


def upgrade():
    conn = op.get_bind()
    op.add_column('article', sa.Column('content', sa.Text))

    sync_trigger(conn, 'article', 'search_vector', ['name', 'content'])

# ... same for downgrade

The part # ... same for downgrade is unclear. Does it mean the exact same code like this (with the drop instead of add)?

def downgrade():
        op.drop_column('article', 'content')
        conn = op.get_bind()
        sync_trigger(conn, 'article', 'search_vector', ['name', 'content'])

I had more trouble when starting a new table. If I put

sync_trigger(conn, 'project_note', 'search_vector', ['text'])

before the Alembic commands then the trigger function is left behind. If it's after the Alembic generated commands then you get an error stating the table doesn't exist. I haven't found the parameter for the indexed_columns parameter of sync_trigger that drops everything without making anything new. An empty list doesn't work.

Sobigen
  • 2,038
  • 15
  • 23

1 Answers1

0

The answer is yes, you should call sync_trigger in the exact same way as in upgrade. The documentation of sync_trigger reads:

1. Drops search trigger for given table (if it exists)
2. Drops search function for given table (if it exists)
3. Creates search function for given table
4. Creates search trigger for given table

Notice the first two points.

AdamGold
  • 4,941
  • 4
  • 29
  • 47
  • Been a while since you answered but I hadn't had the need to try this for a while. I'm working now with new table with just one column indexed. If I put `sync_trigger(conn, 'process', 'search_vector', ['text'])` before the Alembic commands the trigger function stays behind and the trigger on the table is dropped when the table is dropped. If I put it after then you get 'sqlalchemy.exc.NoSuchTableError' error. If I leave the list in the last parameter empty then the error is `AttributeError: 'TSVECTOR' object has no attribute 'columns'` I'll try to format this better in the question part – Sobigen Dec 05 '19 at 18:30