Alembic migrationsΒΆ

When making changes to your database schema you have to make sure the associated search triggers and trigger functions get updated also. SQLAlchemy-Searchable offers a helper function called sync_trigger for this.

sqlalchemy_searchable.sync_trigger(conn, table_name, tsvector_column, indexed_columns, metadata=None, options=None)[source]ΒΆ

Synchronizes search trigger and trigger function for given table and given search index column. Internally this function executes the following SQL queries:

  • Drops search trigger for given table (if it exists)
  • Drops search function for given table (if it exists)
  • Creates search function for given table
  • Creates search trigger for given table
  • Updates all rows for given search vector by running a column=column update query for given table.

Example:

from sqlalchemy_searchable import sync_trigger


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

This function is especially useful when working with alembic migrations. In the following example we add a content column to article table and then sync the trigger to contain this new column.

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

If you are using vectorizers you need to initialize them in your migration file and pass them to this function.

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import HSTORE
from sqlalchemy_searchable import sync_trigger, vectorizer


def upgrade():
    vectorizer.clear()

    conn = op.get_bind()
    op.add_column('article', sa.Column('name_translations', HSTORE))

    metadata = sa.MetaData(bind=conn)
    articles = sa.Table('article', metadata, autoload=True)

    @vectorizer(articles.c.name_translations)
    def hstore_vectorizer(column):
        return sa.cast(sa.func.avals(column), sa.Text)

    op.add_column('article', sa.Column('content', sa.Text))
    sync_trigger(
        conn,
        'article',
        'search_vector',
        ['name_translations', 'content'],
        metadata=metadata
    )

# ... same for downgrade
Parameters:
  • conn – SQLAlchemy Connection object
  • table_name – name of the table to apply search trigger syncing
  • tsvector_column – TSVector typed column which is used as the search index column
  • indexed_columns – Full text indexed column names as a list
  • metadata – Optional SQLAlchemy metadata object that is being used for autoloaded Table. If None is given then new MetaData object is initialized within this function.
  • options – Dictionary of configuration options