Path: | lib/sequel/extensions/pg_auto_parameterize.rb |
Last Update: | Sun May 19 07:59:58 +0000 2013 |
This extension allows Sequel‘s postgres adapter to automatically parameterize all common queries. Sequel‘s default behavior has always been to literalize all arguments unless specifically using parameters (via :$arg placeholders and the prepare/call methods). This extension makes Sequel take all string, numeric, date, and time types and automatically turn them into parameters. Example:
# Default DB[:test].where(:a=>1) # SQL: SELECT * FROM test WHERE a = 1 DB.extension :pg_auto_parameterize DB[:test].where(:a=>1) # SQL: SELECT * FROM test WHERE a = $1 (args: [1])
This extension is not necessarily faster or more safe than the default behavior. In some cases it is faster, such as when using large strings. However, there are also some known issues with this approach:
DB[:table].select(:a, :b).order(2, 1)
Since it will use the following SQL (which isn‘t valid):
SELECT a, b FROM table ORDER BY $1, $2
To work around this, you can either specify the columns manually or use a literal string:
DB[:table].select(:a, :b).order(:b, :a) DB[:table].select(:a, :b).order(Sequel.lit('2, 1'))
DB[:table].insert(:interval=>'1 day')
To work around this, you can just add the necessary casts manually:
DB[:table].insert(:interval=>'1 day'.cast(:interval))
You can also work around any issues that come up by disabling automatic parameterization by calling the no_auto_parameterize method on the dataset (which returns a clone of the dataset).
It is likely there are other corner cases I am not yet aware of when using this extension, so use this extension with caution.
This extension is only compatible when using the pg driver, not when using the old postgres driver or the postgres-pr driver.