I'm trying to convert my Rails application from using Paperclip to Active Storage for file attachments. I'm following a guide/tutorial that outlines how to do this through a migration. However, when I run the migration, I get the following error:
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:
PG::SyntaxError: ERROR: syntax error at or near "active_storage_blob_statement"
LINE 1: active_storage_blob_statement
^
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:61:in `block (4 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:54:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:54:in `block (3 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:53:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:53:in `block (2 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:33:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:33:in `block in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:32:in `up'
I'm not sure what's causing this error or how to fix it. Here's the relevant code from my migration:
class ConvertToActiveStorage < ActiveRecord::Migration[6.1]
require 'open-uri'
def up
# postgres
get_blob_id = 'LASTVAL()'
# mariadb
# get_blob_id = 'LAST_INSERT_ID()'
# sqlite
# get_blob_id = 'LAST_INSERT_ROWID()'
# Prepare two insert statements for the new ActiveStorage tables
active_storage_blob_statement = ActiveRecord::Base.connection.raw_connection.prepare('active_storage_blob_statement', <<-SQL)
INSERT INTO active_storage_blobs (
key, filename, content_type, metadata, byte_size, checksum, created_at
) VALUES ($1, $2, $3, '{}', $4, $5, $6)
SQL
active_storage_attachment_statement = ActiveRecord::Base.connection.raw_connection.prepare('active_storage_attachment_statement', <<-SQL)
INSERT INTO active_storage_attachments (
name, record_type, record_id, blob_id, created_at
) VALUES ($1, $2, $3, #{get_blob_id}, $4)
SQL
# Eager load the application so that all Models are available
Rails.application.eager_load!
# Get a list of all the models in the application
models = ActiveRecord::Base.descendants.reject(&:abstract_class?)
transaction do
models.each do |model|
# If the model has a column or columns named *_file_name,
# We are assuming this is a column added by Paperclip.
# Store the name of the attachment(s) found (e.g. "avatar") in an array named attachments
attachments = model.column_names.map do |c|
if c =~ /(.+)_file_name$/
$1
end
end.compact
# If no Paperclip columns were found in this model, go to the next model
if attachments.blank?
puts ' No Paperclip attachment columns found for [' + model.to_s + '].'
puts ''
next
end
puts ' Paperclip attachment columns found for [' + model.to_s + ']: ' + attachments.to_s
# Loop through the records of the model, and then through each attachment definition within the model
model.find_each.each do |instance|
attachments.each do |attachment|
# If the model record doesn't have an uploaded attachment, skip to the next record
if instance.send(attachment).path.blank?
next
end
# Otherwise, we will convert the Paperclip data to ActiveStorage records
ActiveRecord::Base.connection.execute(
'active_storage_blob_statement', [
key(instance, attachment),
instance.send("#{attachment}_file_name"),
instance.send("#{attachment}_content_type"),
instance.send("#{attachment}_file_size"),
checksum(instance.send(attachment)),
instance.updated_at.iso8601
])
ActiveRecord::Base.connection.execute(
'active_storage_attachment_statement', [
attachment,
model.name,
instance.id,
instance.updated_at.iso8601,
])
end
end
end
end
ActiveRecord::Base.connection.execute('DEALLOCATE PREPARE active_storage_attachment_statement')
ActiveRecord::Base.connection.execute('DEALLOCATE PREPARE active_storage_blob_statement')
end
def down
raise ActiveRecord::IrreversibleMigration
end
private
def key(instance, attachment)
# SecureRandom.uuid
# Alternatively:
filename = instance.send("#{attachment}_file_name")
klass = instance.class.table_name
id = instance.id
id_partition = ("%09d".freeze % id).scan(/\d{3}/).join("/".freeze)
"#{klass}/#{attachment.pluralize}/#{id_partition}/original/#{filename}"
end
def checksum(attachment)
# local files stored on disk:
url = attachment.path
Digest::MD5.base64digest(File.read(url))
# remote files stored on another person's computer:
# url = attachment.url
# Digest::MD5.base64digest(Net::HTTP.get(URI(url)))
end
end
I've tried using exec_prepared instead of execute
ActiveRecord::Base.connection.exec_prepared(
'active_storage_blob_statement', [
key(instance, attachment),
instance.send("#{attachment}_file_name"),
instance.send("#{attachment}_content_type"),
instance.send("#{attachment}_file_size"),
checksum(instance.send(attachment)),
instance.updated_at.iso8601
])
ActiveRecord::Base.connection.exec_prepared(
'active_storage_attachment_statement', [
attachment,
model.name,
instance.id,
instance.updated_at.iso8601,
])
but that gives me the following error:
Did you mean? exec_update
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:61:in `block (4 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:54:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:54:in `block (3 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:53:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:53:in `block (2 levels) in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:33:in `each'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:33:in `block in up'
/myapp/db/migrate/20230313230800_convert_to_active_storage.rb:32:in `up'
Caused by:
NoMethodError: undefined method `exec_prepared'
Can anyone help me understand what's causing this error and how to fix it? Thanks in advance.
To prepare the statement you need to use prepare of the raw postgres connection (as you did) and then to execute, you need to use exec_prepared of the raw postgres connection again.
execute is a method on the AR connection that you can use to execute a normal SQL statement :-(
Something.connection.raw_connection.prepare('some-name', "select count(*) from somethings")
=> #<PG::Result:0x000000401f888f38 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>
irb(main):003:0> Something.connection.raw_connection.exec_prepared("some-name")
=> #<PG::Result:0x000000401fcc63c0 status=PGRES_TUPLES_OK ntuples=1 nfields=1 cmd_tuples=1>
irb(main):004:0>
In short:
Change
ActiveRecord::Base.connection.execute
to
ActiveRecord::Base.connection.raw_connection.exec_prepared