Can I execute a raw sql query, leverage prepared statements, and not use ActiveRecord::Relation::QueryAttribute?

Question

I want to do an upsert. Rails doesn't support this yet. The query is something like this:

INSERT INTO foos (thing_id, bar_id) VALUES (1, 2)
ON CONFLICT (thing_id, bar_id) DO NOTHING

I can easily do this with self.class.connection.execute or exec_insert. But I want to also leverage prepared statements. I thought I can do this like so:

thing_id = ActiveRecord::Relation::QueryAttribute.new("thing_id", thing.id, ActiveRecord::Type::Integer.new)
bar_id = ActiveRecord::Relation::QueryAttribute.new("bar_id", id, ActiveRecord::Type::Integer.new)

self.class.connection.exec_insert(<<-SQL, nil, [thing_id, bar_id])
  INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2)
  ON CONFLICT (thing_id, bar_id) DO NOTHING
SQL

But when I experimented with this it seems that a prepared statement is not created.

I tried this style:

query = <<-SQL
  INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2)
  ON CONFLICT (thing_id, bar_id) DO NOTHING
SQL

connection = ActiveRecord::Base.connection.raw_connection
connection.prepare('some_name', query)
st = connection.exec_prepared('some_name', [ thing.id, id ])

And it does create a prepared statement. BUT, the second time it is run, postgres complains about creating a prepared statement with the same name. So, rails' prepared statement management happens at a level above this, and I'm unable to leverage it here. I would have to manually manage it here. I'm not confident I could do this properly, and even if I could it would be verbose.

execute and friends do not accept the ("foo=?", 1) api that where accepts.

Is there any way to leverage rails' automagic prepared statement management for raw SQL?

Answer 1

#exec_query accepts an optional keyword argument, prepare, which defaults to false. Take a look at the method definition (and here).

Given the following table definition:

CREATE TABLE foos (
    thing_id INT,
    bar_id INT,
    UNIQUE (thing_id, bar_id)
);

I tested the following:

conn = ActiveRecord::Base.connection
stmt = 'INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2) ' \
       'ON CONFLICT (thing_id, bar_id) DO NOTHING'

# "Old" bind parameters
binds = [[nil, 1], [nil, 2]]
conn.exec_query stmt, 'SQL', binds, prepare: true
conn.exec_query stmt, 'SQL', binds, prepare: true

# "New" bind parameters
type  = ActiveModel::Type::Integer.new limit: 4
binds = [
  ActiveRecord::Relation::QueryAttribute.new('thing_id', 1, type),
  ActiveRecord::Relation::QueryAttribute.new('bar_id',   2, type)
]
conn.exec_query stmt, 'SQL', binds, prepare: true
conn.exec_query stmt, 'SQL', binds, prepare: true

Both of those bind param styles worked for me with ActiveRecord 5.2.0 and pg 1.0.0. Multiple INSERT statements using the same values ultimately result in only one row being inserted with no errors raised. I checked the Postgres logs, and there was only one "parse" (before the first INSERT), so it appears that the prepared statement mechanism is being used correctly.