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: Connection, table_name: str, tsvector_column: str, indexed_columns: list[str], metadata: MetaData | None = None, options: SearchOptions | None = None, schema: str | None = None, update_rows: bool = True) → None

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
import sqlalchemy as sa
from sqlalchemy_searchable import sync_trigger


def upgrade() -> None:
    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:

from typing import Any

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


def upgrade() -> None:
    vectorizer.clear()

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

    metadata = sa.MetaData()
    articles = sa.Table('article', metadata, autoload_with=conn)

    @vectorizer(articles.c.name_translations)
    def hstore_vectorizer(
        column: sa.ColumnClause[Any],
    ) -> sa.ColumnElement[str]:
        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 – SearchOptions instance for configuration

• 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: Connection, table_name: str, tsvector_column: str, metadata: MetaData | None = None, options: SearchOptions | None = None, schema: str | None = None) → None

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() -> None:
    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 – SearchOptions instance for configuration

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