# SearchCop: turning a query string into fulltext SQL on ActiveRecord

> SearchCop is a Ruby gem that lets ActiveRecord models accept search engine style query strings and hash queries, mapping them to SQL that can use MySQL or PostgreSQL fulltext indices. The interesting part is the trade-off: it keeps query parsing in your app and hands you LIKE scans unless you opt attributes into fulltext.

**mrkamel/search_cop** — Search engine like fulltext query support for ActiveRecord

- Repository: https://github.com/mrkamel/search_cop
- Stars: 838 · Forks: 36
- Language: Ruby
- License: MIT
- Published: 2026-10-20 · Updated: 2026-10-20 · Language: en
- Canonical page: https://hysenlabs.com/projects/mrkamel-search-cop

## One search method your users can drive with plain text

SearchCop extends ActiveRecord models so they accept query strings that look like a search engine rather than a hash of filters. Given a Book model with attributes such as title, author, stock, price and available, the gem accepts queries like a bare phrase, a field qualified term, or a mixed expression combining ranges, exclusions and boolean groups.

```ruby
Book.search("Joanne Rowling Harry Potter")
Book.search("author: Rowling title:'Harry Potter'")
Book.search("price > 10 AND price < 20 -stock:0 (Potter OR Rowling)")
```

The point of that syntax is organisational, not technical. A search box that hands the raw string to the model means the same grammar works for an admin panel, an internal tool and a public endpoint without each surface writing its own filter parser. SearchCop does the parsing, and the resulting relation can be chained like any other:

```ruby
Book.where(available: true).search("Harry Potter").order("books.id desc").paginate(page: params[:page])
```

Because search returns an ActiveRecord::Relation, pagination, ordering and scoping stay ordinary Rails work rather than something the gem has to wrap.

## Query strings become nodes, nodes become SQL through visitors

The architecture has two stages, and the second one is the more interesting. A query string is parsed into node objects that represent the expressions in the query, for example And, Or, Not, String and Date nodes. Each node type then needs a visitor that transforms it into SQL.

The visitor pattern is the one used in Arel, and SearchCop follows it: for every node there must be a visitor, and when no visitor exists the query builder raises an exception when it tries to visit that node. That design is why the gem is not bound to a specific RDBMS. The nodes describe intent, and a visitor translates that intent into dialect specific SQL, with MySQL and PostgreSQL fulltext indices currently supported.

Some of the translation is delegated to ActiveSupport. A query of available:yes OR created_at:2014 becomes a boolean test on available plus a date range built with ActiveSupport's beginning_of_year and end_of_year methods, so a year in the query covers that calendar year rather than an arbitrary span.

A bare multi word phrase is where the abstraction shows its cost. Harry Potter becomes a conjunction of per attribute LIKE clauses, one group per word, so a search for two words scans every exposed column twice.

## Hash queries cover the cases where a grammar is the wrong shape

SearchCop also accepts a hash form, which is what you reach for when the caller already knows which fields it wants to filter on. Nested conditions, boolean groups and negation are all expressible:

```ruby
Book.search(author: "Rowling", title: "Harry Potter")
Book.search(or: [{author: "Rowling"}, {author: "Tolkien"}])
Book.search(and: [{price: {gt: 10}}, {not: {stock: 0}}, or: [{title: "Potter"}, {author: "Rowling"}]}])
Book.search(or: [{query: "Rowling -Potter"}, {query: "Tolkien -Rings"}])
```

The last two lines show the two forms mixing, which is useful when a parameterised filter has to be combined with free text entered by a person.

JSON columns are handled in the same scope definition. MySQL json fields and PostgreSQL json, jsonb and hstore fields are supported, with field values always expected to be strings, so arrays are not supported:

```ruby
search_scope :search do
  attributes user_agent: "context->browser->user_agent"

  # ...
end
```

The scope points at a path inside the json column, and the column is expected to hold an object such as a browser key with a user_agent value. The documented limitation is worth remembering before you model a list of tags or roles in json, since the gem says arrays are not supported.

## search_scope is the boundary of what a user can reach

To enable searching on a model you include SearchCop and declare a search_scope listing the attributes you want exposed to query strings. Associations can be exposed through a nested path, and a custom label can be mapped to a different column:

```ruby
class Book < ActiveRecord::Base
  include SearchCop

  search_scope :search do
    attributes :title, :description, :stock, :price, :created_at, :available
    attributes comment: ["comments.title", "comments.message"]
    attributes author: "author.name"
    # ...
  end

  has_many :comments
  belongs_to :author
end
```

That declaration is the real security boundary. A query string can only touch attributes that a scope exposes, so two scopes over the same model produce two different search surfaces:

```ruby
search_scope :admin_search do
  attributes :title, :description, :stock, :price, :created_at, :available

  # ...
end

search_scope :user_search do
  attributes :title, :description

  # ...
end
```

The admin scope covers pricing, stock and creation date, the user scope covers title and description. Splitting scopes this way keeps a public search box from becoming a way to enumerate internal columns, which is the more likely mistake in this design.

One entry deserves attention. A title value written as my_custom_sql_query passes a hand written SQL fragment into the query instead of a quoted value, which is documented in the hash examples. That is a deliberate escape hatch for the cases the grammar cannot express, and it is also the one place where the quoting guarantees described below no longer apply.

## Fulltext indices only kick in for attributes you mark

By default the gem emits LIKE '%...%' predicates. The documentation is blunt about the consequence: unless you create a trigram index, which is PostgreSQL only, those queries cannot use SQL indices, so every row is scanned when you run a search such as Book.search("Harry Potter").

To change that, you tell SearchCop which attributes already have fulltext indices:

```ruby
class Book < ActiveRecord::Base
  # ...

  search_scope :search do
    attributes :title, :author

    options :title, :type => :fulltext
    options :author, :type => :fulltext
  end

  # ...
end
```

The generated SQL then switches shape per database. On MySQL a word becomes a MATCH against each marked column in boolean mode, on PostgreSQL a to_tsvector and to_tsquery pair. The transformation is transparent to the caller, which keeps calling the same search method.

This is the design decision to understand before adopting the gem. Fulltext support is not automatic, it depends on indices you created yourself in the database, and the databases in the current support list are MySQL and PostgreSQL. Each marked column contributes a clause per search word, so marking many columns widens the generated query.

## Installing the gem and running it against MySQL or PostgreSQL

Installation is a single gem line in the application's Gemfile, followed by a bundle install:

```ruby
    gem 'search_cop'
```

```shell
    $ bundle
```

Or install it directly:

```shell
    $ gem install search_cop
```

The repository also ships a docker-compose file used to run the databases the test suite needs, one MySQL service on port 3306 and one PostgreSQL service on port 5432. The PostgreSQL service is the one with the hstore extension image:

```yaml
  postgres:
    image: styriadigital/postgres_hstore:10
    environment:
      POSTGRES_DB: search_cop
      POSTGRES_USER: search_cop
      POSTGRES_PASSWORD: secret
    ports:
      - 5432:5432
```

Both services are configured with the database name search_cop, so pointing the test suite at them is a matter of matching credentials rather than creating schemas by hand.

The licence is MIT and the licence text sits in LICENSE.txt at the repository root. For a team considering it in a commercial product, MIT is the permissive end of the spectrum and the practical obligation is limited to retaining the copyright and licence notice, though your own counsel should confirm how it fits your distribution.

## Quoting handles input, and one escape hatch bypasses it

The security section of the documentation explains the mechanism plainly. Nodes are visited by visitors, and those visitors are responsible for sanitising user supplied input, primarily through quoting of strings, table names and columns. SearchCop uses the quoting methods provided by the ActiveRecord connection adapter, which is what prevents SQL injection in the ordinary path.

The honest caveat is in the same section: the project states it can never be fully safe from security issues, and it asks that any findings be reported to a private security address rather than a public issue. Security reporting that takes that shape is a sign of a project that expects to be deployed with untrusted input, which is the intended use case here.

The limitation to plan around is the my_custom_sql_query form, which inserts raw SQL where the quoting layer would otherwise act. If you use it, the safety of the query depends on the code that builds the fragment rather than on the gem, and that code is outside the visitor pipeline.

On maintenance, the last push was on 2026-09-16, which is recent, but the most recent tagged release is v1.4.0 from 2024-05-30, before v1.3.0 in February 2024 and v1.2.3 in July 2022. Code has moved since the last release without a new gem version, so a production upgrade means deciding whether to pin the published gem or to run from source. MIGRATION.md at the repository root is where version specific changes are recorded.

## Conclusion

SearchCop fits Ruby teams that want one search box for users and admins without standing up a separate search server, especially where the corpus already lives in MySQL or PostgreSQL fulltext indices. It is the wrong choice if you need relevance ranking beyond what MATCH and tsquery give you, if you need JSON array containment, or if you want a query grammar that cannot reach arbitrary SQL. Verify three things before adopting it: that your production database and version are the ones the gem's fulltext path targets, that the attributes you expose in each search_scope are the ones users actually need, and that the my_custom_sql_query escape hatch is either unused or wrapped in your own authorization checks.

## FAQ

### How do I install SearchCop and enable search on an ActiveRecord model?

Add gem 'search_cop' to the Gemfile and run bundle, or install it with gem install search_cop. Then include SearchCop in the model and declare a search_scope listing the attributes to expose to queries.

### Does SearchCop use my database fulltext index automatically?

No. By default it builds LIKE '%...%' predicates, which the documentation says cannot use SQL indices unless you create a trigram index on PostgreSQL. To use existing fulltext indices you mark the attributes with options :title, :type => :fulltext in the scope.

### Which databases does SearchCop support?

MySQL and PostgreSQL fulltext indices are supported, and the repository's docker-compose file provides percona:5.7 on port 3306 and styriadigital/postgres_hstore:10 on port 5432 for the test suite. JSON fields are supported for MySQL, and json, jsonb and hstore for PostgreSQL.

### Can I chain other ActiveRecord methods onto a SearchCop query?

Yes. The search method returns an ActiveRecord::Relation, so the documentation shows it combined with where, order and paginate on the same call.

### Does SearchCop support searching inside JSON columns?

It does, by pointing a scope attribute at a path such as context->browser->user_agent. The documentation states that field values are always expected to be strings and that arrays are not supported.

## Sources

- [Issues](https://github.com/mrkamel/search_cop/issues)
- [License: MIT](https://github.com/mrkamel/search_cop/blob/master/LICENSE)
- [mrkamel/search_cop on GitHub](https://github.com/mrkamel/search_cop)
- [README](https://github.com/mrkamel/search_cop/blob/master/README.md)
- [Releases](https://github.com/mrkamel/search_cop/releases)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/mrkamel-search-cop