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 thearticle
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
.