sql - Prepared Statement on Postgresql in Rails

Question

Right now I am in the middle of migrating from SQLite to Postgresql and I came across this problem. The following prepared statement works with SQLite:

id = 5
st = ActiveRecord::Base.connection.raw_connection.prepare("DELETE FROM my_table WHERE id = ?")
st.execute(id)
st.close

Unfortunately it is not working with Postgresql - it throws an exception at line 2. I was looking for solutions and came across this:

id = 5
require 'pg'
conn = PG::Connection.open(:dbname => 'my_db_development')
conn.prepare('statement1', 'DELETE FROM my_table WHERE id = $1')
conn.exec_prepared('statement1', [ id ])

This one fails at line 3. When I print the exception like this

rescue => ex

ex contains this

{"connection":{}}

Executing the SQL in a command line works. Any idea what I am doing wrong?

Thanks in advance!

Answer 1

If you want to use prepare like that then you'll need to make a couple changes:

1. The PostgreSQL driver wants to see numbered placeholders ($1, $2, ...) not question marks and you need to give your prepared statement a name:

    ActiveRecord::Base.connection.raw_connection.prepare('some_name', "DELETE FROM my_table WHERE id = $1")
   

2. The calling sequence is prepare followed by exec_prepared:

   connection = ActiveRecord::Base.connection.raw_connection
   connection.prepare('some_name', "DELETE FROM my_table WHERE id = $1")
   st = connection.exec_prepared('some_name', [ id ])
   

The above approach works for me with ActiveRecord and PostgreSQL, your PG::Connection.open version should work if you're connecting properly.

Another way is to do the quoting yourself:

conn = ActiveRecord::Base.connection
conn.execute(%Q{
    delete from my_table
    where id = #{conn.quote(id)}
})

That's the sort of thing that ActiveRecord is usually doing behind your back.

Directly interacting with the database tends to be a bit of a mess with Rails since the Rails people don't think you should ever do it.

If you really are just trying to delete a row without interference, you could use delete:

delete()

[...]

The row is simply removed with an SQL DELETE statement on the record’s primary key, and no callbacks are executed.

So you can just say this:

MyTable.delete(id)

and you'll send a simple delete from my_tables where id = ... into the database.