Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?

Question

I am using SQLite3 for development and PostgreSQL for deployment. However, I am facing the following problem:

My simple search using SQLite3:

def self.search(search)
    if search
      find(:all, :conditions => ["style LIKE ? OR construction LIKE ?", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
end

However, it doesn't work for PostgreSQL, and I need to replace the LIKE for ILIKE to solve the problem:

def self.search(search)
    if search
      find(:all, :conditions => ["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])
    else
      find(:all)
    end
end

Is there a "Ruby way" to do these searches across any database?

EDIT - based on your answers I don't believe I will find a generic Ruby solution for that.

I have followed the Ruby on Rails Tutorial: Learn Rails by Example - by Michael Hartl, where the final Gemfile shows both databases... well, disappointing...

I know this doesnt answer your question per se but I think you will have a world of hurt if your dev environment (sqlite) doesnt match your production environment (postgres). If something works in dev how do you know it works in production if the databases are totally different? — Cody Caughlan, Jul 01 '12 at 01:00

Erwin Brandstetter · Accepted Answer · 2022-04-16T03:13:36.353

The root of the problem lies here:

I am using SQLite3 for development and PostgreSQL for deployment.

That's a bad idea™. You will keep running into incompatibilities - or worse: not realize some until damage is done.
Use the same RDBMS (PostgreSQL) for development and production and save yourself the pointless trouble.

While you are stuck with your unfortunate setup, there is a simple fix:

lower(style) LIKE lower(?)

Works on both platforms alike.

Drop the right-hand lower() if you provide a lower-case search-pattern.

In standard SQLite lower(X) only folds ASCII letters. Quoting the chapter Core Functions in the SQLite manual:

The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.

Bold emphasis mine.

PostgreSQL lower(X) works with UTF-8 out of the box.

As a welcome side effect, you can speed up that query in PostgreSQL with an index on the expression lower(style), which will be faster than using ILIKE and a basic index on style.

Also, since PostgreSQL 9.1 you can use a GIN or GIST index with the pg_trgm extension to speed up any LIKE and ILIKE query - trigram indexes are case-insensitive. Details:

Similar UTF-8 strings for autocomplete field

Thank you very much for that... very useful information... I will play with that. — gabrielhilal, Jul 01 '12 at 17:30
I'm in same situation as OP, since I also started learning from Hartl's rails tutorial. I'd like to change my development database to Postgresql so that it matches my development database. What do I need to know about doing this? And what are the potential consequences? — Jeff Zivkovic, Jun 24 '19 at 13:33

score 9 · Answer 2 · answered Jul 04 '12 at 05:41

I think Arel is the best way to solve this problem. It is used by Rails for active record and is database independent. Your code will work the same in sqlite3 or postgres which seems to fit your situation. Using the matches method will automatically switch to an ilike in a postgres environment. example:

users=User.arel_table
User.where(users[:style].matches("%#{search}%").or(users[:construction].matches("%#{search}%")))

You can get more information from the github: https://github.com/rails/arel/

+1, This approach is discussed in this railscast: http://railscasts.com/episodes/355-hacking-with-arel — Harish Shetty, Jul 05 '12 at 21:48

score 4 · Answer 3 · edited Sep 21 '14 at 05:40

No, there is no "ruby way" to search a database -- Ruby on Rails (specifically ActiveRecord) contains helper methods for performing CRUD operations on RDBs supported by ActiveRecord but there is not much better of a way to search using LIKE then the examples you provided.

The section of the Rails doc related to this discussion would be ActiveRecord::FinderMethods.

As a side note, instead of doing find(:all) you can just do all.

The Rails docs use the same syntax as use for doing LIKE statements, i.e.:

Person.exists?(['name LIKE ?', "%#{query}%"])

Using the method above is perfectly safe.

The reason why statements like the one below are unsafe is because the where string is passed directly into your database query without any sanitization, which leaves your database open to exploitation (i.e. a simple apostrophe in params[:first_name] could mess up your entire query and leave your database vulnerable -- specifically SQL injection). In the example above, ActiveRecord can sanitize the parameters that you are passing into the query.

Client.where("first_name LIKE '%#{params[:first_name]}%'")

score 2 · Answer 4 · answered Jun 28 '12 at 16:42

2

Unfortunately I don't think you're going to find a good solution to this. The only other syntax available to you, instead of :conditions, is to use a .where clause:

where(["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])

Unfortunately, as you've probably realized, this alternate syntax will run into the exact same problem. This is just one of the annoyances you'll encounter in having a substantially different development environment from your production environment -- there are also other fairly substantial differences between SQLite and PostgresSQL. I would recommend just installing Postgres on your development machine and using that. It'll make developing a lot easier, and make your code a lot cleaner.

answered Jun 28 '12 at 16:42

Veraticus

15,944
3
41
45

many thanks for that, but looking on the [Rails Guide](http://guides.rubyonrails.org/active_record_querying.html#pure-string-conditions), it says `Building your own conditions as pure strings can leave you vulnerable to SQL injection exploits. For example, Client.where("first_name LIKE '%#{params[:first_name]}%'") is not safe.` For this reason I thought there was a better way ("ruby way") to do that. – gabrielhilal Jun 28 '12 at 21:22
Yes that is not a good way, but the way you are doing is okay. Rails will sanitize the strings before substituting them. The code you wrote in comment is not sanitizing the string though. – rubish Jun 30 '12 at 19:22

Greg Funtusov · Answer 5 · 2012-07-01T12:55:02.167

While it's not a good practice to use different databases in production and development, It still is a good case to use the squeel gem: https://github.com/ernie/squeel/

With it, you can write your queries with a DSL that is easy and arguably much cleaner and readable than raw sql, and the gem will handle it's translation to SQL specific to the RDBMS used.

Ryan Bates has a good video about it: http://railscasts.com/episodes/354-squeel

score 2 · Answer 6 · edited Apr 09 '13 at 09:24

I was facing the same problem once, here is my solution:

I wrote a little lib which make it possible to use the function for each database:

class AdapterSpecific

  class << self
    def like_case_insensitive
      case ActiveRecord::Base.connection.adapter_name
      when 'PostgreSQL'
        'ILIKE'
      else
        'LIKE'
      end
    end    

    def random
      #something
    end
  end

To use it in your model:

def self.search(search)
    if search
      find(:all, :conditions => ["style #{AdapterSpecific.like_case_insensitive} :query OR construction #{AdapterSpecific.like_case_insensitive} :query", {:query => "%#{search}%"}])
    else
      find(:all)
    end
end

Since written, I migrated the deployment database to Postgres as this is a much better setup for several reasons (see other answers).

You may also think to use full text search for Postgres which will make your text search much more efficient, see texticle for a basic implementation or pg_search if you need more customization.

score 1 · Answer 7 · answered Jul 01 '12 at 01:03

1

Searching using LIKE can be painful on the database. Certainly it wont use an index which can be quite cost prohibitive.

A longer answer: I would recommend using Postgres in development (ditching sqlite3) and then having a Full-Text index on all your searchable fields style, construction via Postgres' tsvector type.

Full-text searches in Postgres when using an index are very fast.

answered Jul 01 '12 at 01:03

Cody Caughlan

32,456
5
63
68

postgres allows to index like and ilike subjects. – rubiii Feb 19 '13 at 21:39
@rubiii you're right, good to know. Seems you have to make sure you create the index and specify the locale/operator class to ensure PG uses it though. http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html – Cody Caughlan Feb 20 '13 at 19:47

Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?

7 Answers7

Linked