Create a Full Text Search index with SQLAlchemy on PostgreSQL

Thanks for this question and answers.

I'd like to add a bit more in case ppl using alembic to manage versions by using autogenerate which creating the index seems not be detected.

We might end up writing our own alter script which look like.

"""add fts idx

Revision ID: e3ce1ce23d7a
Revises: 079c4455d54d
Create Date: 

"""

# revision identifiers, used by Alembic.
revision = 'e3ce1ce23d7a'
down_revision = '079c4455d54d'

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.create_index('idx_content_fts', 'table_name',
            [sa.text("to_tsvector('english', content)")],
            postgresql_using='gin')


def downgrade():
    op.drop_index('idx_content_fts')

The answer from @sharez is really useful (especially if you need to concatenate columns in your index). For anyone looking to create a tsvector GIN index on a single column, you can simplify the original answer approach with something like:

from sqlalchemy import Column, Index, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func


Base = declarative_base()

class Example(Base):
    __tablename__ = 'examples'

    id = Column(Integer, primary_key=True)
    textsearch = Column(String)

    __table_args__ = (
        Index(
            'ix_examples_tsv',
            func.to_tsvector('english', textsearch),
            postgresql_using='gin'
            ),
        )

Note that the comma following Index(...) in __table_args__ is not a style choice, the value of __table_args__ must be a tuple, dictionary, or None.

If you do need to create a tsvector GIN index on multiple columns, here is another way to get there using text().

from sqlalchemy import Column, Index, Integer, String, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func


Base = declarative_base()

def to_tsvector_ix(*columns):
    s = " || ' ' || ".join(columns)
    return func.to_tsvector('english', text(s))

class Example(Base):
    __tablename__ = 'examples'

    id = Column(Integer, primary_key=True)
    atext = Column(String)
    btext = Column(String)

    __table_args__ = (
        Index(
            'ix_examples_tsv',
            to_tsvector_ix('atext', 'btext'),
            postgresql_using='gin'
            ),
        )

You could create index using Index in __table_args__. Also I use a function to create ts_vector to make it more tidy and reusable if more than one field is required. Something like below:

from sqlalchemy.dialects import postgresql

def create_tsvector(*args):
    exp = args[0]
    for e in args[1:]:
        exp += ' ' + e
    return func.to_tsvector('english', exp)

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    __ts_vector__ = create_tsvector(
        cast(func.coalesce(name, ''), postgresql.TEXT)
    )

    __table_args__ = (
        Index(
            'idx_person_fts',
            __ts_vector__,
            postgresql_using='gin'
        )
    )

---

Update: A sample query using index (corrected based on comments):

people = Person.query.filter(Person.__ts_vector__.match(expressions, postgresql_regconfig='english')).all()