A unified interface on top of ActiveRecord for case-insensitive string-in-set and prefix querying, and full-text search on SQLite, MySQL, and PostgreSQL

* Case-insensitive string-in-set querying, prefix querying, and case-insensitive index creation. * Basic full-text search for a list of terms, and FTS index creation.

Code Quality Rank: L5
Monthly Downloads: 1,192
Programming language: Ruby
License: MIT License
Tags: Misc     ORM/ODM Extensions     Search    
Latest version: v0.3.1

DbTextSearch alternatives and similar gems

Based on the "ORM/ODM Extensions" category.
Alternatively, view DbTextSearch alternatives based on common mentions on social networks and blogs.

Do you think we are missing an alternative of DbTextSearch or a related project?

Add another 'ORM/ODM Extensions' Gem


DbTextSearch Build Status Code Climate Test Coverage

Different relational databases treat text search very differently. DbTextSearch provides a unified interface on top of ActiveRecord for SQLite, MySQL, and PostgreSQL to do:

  • Case-insensitive string-in-set querying, prefix querying, and case-insensitive index creation.
  • Basic full-text search for a list of terms, and FTS index creation.


Add this line to your application's Gemfile:

gem 'db_text_search', '~> 0.3.1'


Case-insensitive string matching

Add an index in a migration to an existing CI (case-insensitive) or CS (case-sensitive) column:

DbTextSearch::CaseInsensitive.add_index connection, :users, :username
# Options: name, unique

Or, create a new CI column:

DbTextSearch::CaseInsensitive.add_ci_text_column connection, :users, :username

Perform a search for records with column that case-insensitively equals to one of the strings in a given set:

# Find all confirmed users that have either the username Alice or Bob (case-insensitively):
DbTextSearch::CaseInsensitive.new(User.confirmed, :username).in(%w(Alice Bob))
 #=> ActiveRecord::Relation

Perform a case-insensitive prefix search:

DbTextSearch::CaseInsensitive.new(User.confirmed, :username).prefix('Jo')

See also: API documentation.

Full text search

Add an index:

DbTextSearch::FullText.add_index connection, :posts, :content
# Options: name

Perform a full-text search:

DbTextSearch::FullText.new(Post.published, :content).search('peace')
DbTextSearch::FullText.new(Post.published, :content).search(%w(love kaori))

Under the hood

Case-insensitive string matching

Case-insensitive equality methods Column typeSQLiteMySQLPostgreSQL Detected typesSearch / indexDetected typesSearch / indexDetected typesSearch / index CI always treated as CS COLLATE NOCASE default default CITEXT default CS non-ci collations LOWERno index default LOWER

Case-insensitive prefix matching (using LIKE) Column typeSQLiteMySQLPostgreSQL
CI default, cannot always use an index, even for prefix queries default cannot use an index CS cannot use an index
LOWER(column text_pattern_ops)

Full-text search


A FULLTEXT index, and a MATCH AGAINST query. MySQL v5.6.4+ is required.


A gist(to_tsvector(...)) index, and a @@ plainto_tsquery query. Methods also accept an optional pg_ts_config argument (default: "'english'") that is ignored for other databases.


No index, a LIKE %term% query for each term joined with AND.


Make sure you have a working installation of SQLite, MySQL, and PostgreSQL. After checking out the repo, run bin/setup to install dependencies. Then, run rake test_all to run the tests with all databases and gemfiles.

See the Rakefile for other available test tasks.

You can also run bin/console for an interactive prompt that will allow you to experiment.


Bug reports and pull requests are welcome on GitHub at https://github.com/thredded/db_text_search. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.


The gem is available as open source under the terms of the MIT License.

*Note that all licence references and agreements mentioned in the DbTextSearch README section above are relevant to that project's source code only.