Database-agnostic case-insensitive equality, full text search

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

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

DbTextSearch does all the heavy lifting under the hood, hiding the complexity of handling each database differently away, which is great for gem authors, when migrating an application from one database to another, or writing code that must support multiple databases.

Here is how db_text_search handles different types of columns in different databases for case-insensitive comparisons:

DbTextSearch case-insensitive string matching methods
Column typeSQLiteMySQLPostgreSQL
Detected typesSearch / indexDetected typesSearch / indexDetected typesSearch / index
Case-insensitive always treated as case-sensitive COLLATE NOCASE default default CITEXT default
Case-sensitive non-ci collations LOWER
no index
default LOWER

Full-text search is even more gnarly. Great that you don't have to worry about that if you use this gem! Check it out and learn more on

Share on

comments powered by Disqus