Alembic migrations#

When making changes to your database schema, you have to ensure the associated search triggers and trigger functions get updated also. SQLAlchemy-Searchable offers two helper functions for this: sync_trigger() and drop_trigger().

sqlalchemy_searchable.sync_trigger(conn, table_name, tsvector_column, indexed_columns, metadata=None, options=None, schema=None, update_rows=True)[source]#

Synchronize the search trigger and trigger function for the given table and search vector column. Internally, this function executes the following SQL queries:

  • Drop the search trigger for the given table and column if it exists.

  • Drop the search function for the given table and column if it exists.

  • Create the search function for the given table and column.

  • Create the search trigger for the given table and column.

  • Update all rows for the given search vector by executing a column=column update query for the 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 the article table and then synchronize 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 a new MetaData object is initialized within this function.

  • options – Dictionary of configuration options

  • schema – The schema name for this table. Defaults to None.

  • update_rows – If set to False, the values in the vector column will remain unchanged until one of the indexed columns is updated.

sqlalchemy_searchable.drop_trigger(conn, table_name, tsvector_column, metadata=None, options=None, schema=None)[source]#

Drop the search trigger and trigger function for the given table and search vector column. Internally, this function executes the following SQL queries:

  • Drop the search trigger for the given table if it exists.

  • Drop the search function for the given table if it exists.

Example:

from alembic import op
from sqlalchemy_searchable import drop_trigger


def downgrade():
    conn = op.get_bind()

    drop_trigger(conn, 'article', 'search_vector')
    op.drop_index('ix_article_search_vector', table_name='article')
    op.drop_column('article', 'search_vector')
Parameters:
  • conn – SQLAlchemy Connection object

  • table_name – name of the table to apply search trigger dropping

  • tsvector_column – TSVector typed column which is used as the search index column

  • metadata – Optional SQLAlchemy metadata object that is being used for autoloaded Table. If None is given, then a new MetaData object is initialized within this function.

  • options – Dictionary of configuration options

  • schema – The schema name for this table. Defaults to None.