Postgres Full Text Search in SQLAlchemy

By Jake Robers on September 17th 2018

Text search is often a core feature of web APIs. Postgres and SQLAlchemy can fill the need without having to add additional technology (such as ElasticSearch or Solr). We can lean on Postgres' pg_trgm to provide "good enough" search in early stages of API development.

We will be adding the trigram index on a text column for quicker searches.

We are using Alembic and SQLAlchemy for the database migration. The table name is books. The column that will be searchable is title.

"""alter books trgm title
Revision ID: some_revision_id
Revises: some_down_revision_id
Create Date: 2018-07-17 13:47:00.348610+00:00
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision = 'some_revision_id'
down_revision = 'some_down_revision_id'
branch_labels = None
depends_on = None
def upgrade():
    # creating the extension should only occur once. No need to include for future trigram indicies
    op.execute('CREATE EXTENSION pg_trgm;')
    op.create_index(op.f('ix_books_trgm_title'), 'book', ['title'], postgresql_using='gin',
        postgresql_ops={
            'title': 'gin_trgm_ops',
        }, unique=False)
def downgrade():
    op.drop_index(op.f('ix_books_trgm_title'), table_name='books')

From the above link, the Postgres documentation tells us:

These index types support ... trigram-based index searches for LIKE, ILIKE, ~ and ~* queries.

So let's implement a search with ILIKE in Flask-SQLAlchemy:

# Add this classmethod to our Book model.
@classmethod
def list_books_by_title(self, title_query):
  books = self.query
  books = books.filter(self.title.ilike('%{}%'.format(title_query)))
  return books.all()

Happy Coding!

RokkinCat

is a software engineering agency. We build applications and teach businesses how to use new technologies like machine learning and virtual reality. We write about software, business, and business software culture.