Captured prepared statements

[jcarlson]

I have a migration that effectively performs an UPDATE ALL command. Something like:

User.where(favorite_color: 'blue').update_all(theme_color: 'blue')

The captured SQL migration cannot be run, as it looks something like:

UPDATE "users" SET "theme_color" = 'blue' WHERE "users"."favorite_color" = $1;

Any suggestions on how to capture the actual values, not $1 in the script?

Or better yet, how to stop regenerating the .sql version if I run rails db:drop db:setup on my local box?

[R167]

You may be able to fix the issue by wrapping your query in an unprepared_statement block like:

User.connection.unprepared_statement do
  User.where(favorite_color: 'blue').update_all(theme_color: 'blue')
end

Don't have an applicable setup to test this right now, but based on how AR handles prepared statements internally, I would think this should solve your issue.

Also, could you clarify what you meant by/what's the use case for the below?

Or better yet, how to stop regenerating the .sql version if I run rails db:drop db:setup on my local box?

[jcarlson]

Is this solution something that could be baked into the gem itself?

Or better yet, how to stop regenerating the .sql version if I run rails db:drop db:setup on my local box?

My app already had a dozen or two migrations in place when I added this gem. I ran rake db:drop db:setup locally to create a clean database and run each migration. Doing so conveniently back-filled the SQL version of each migration from the beginning. I need SQL versions of all my migrations for integration with an automated deployment pipeline that can run SQL scripts, but not rake tasks (specifically, a tool called flyway).

Because one of those existing migrations performs an update_all, as referenced above, the captured SQL contained the prepared statement, not the actual values. After generating all the SQL files from rake db:drop db:setup, I manually edited the afflicted SQL files and committed them to Git.

Now, whenever I drop and re-create the database on my local machine (a relatively frequent occurrence, because it forces me to keep my database seeds up to date), this gem re-generates the SQL versions along the way, overwriting any hand-edits I had made.

So, having back-filled all the SQL versions for migrations, and hand editing some to align with the .rb version, how can I prevent this gem from overwriting my hand-edits of subsequent db:migrate calls that start from an empty database?

[dradetsky]

In my case, none of the prepared statements to be emitted were much of a concern for me, I just needed the database not to break on the 120th out of 200 migrations or whatever. So I hacked together a solution for my case here.

I suspect it won't be of use to most others, and I wasn't planning to PR it, but it might help somebody else find out where to start making changes in order to create a proper, pr-worthy fix. Especially, somebody who's a halfway-decent rails programmer (I'm not) and knows how to use the higher-level facilities available to produce a clean solution (I don't).