CODEWITHSUNDEEP
ruby-on-rails
Jerome
Jerome

Reputation: 6217

Rails ActiveRecord::Base.connection to execute upon an external file

To run a one-off procedure of loading data that is already formatted for loading into postgresql

BEGIN;
INSERT INTO "public"."optionsets" [...]
COMMIT;

Rails has the command that allows to execute raw sql

ActiveRecord::Base.connection.execute()

However, the syntax alludes to placing the syntax within the parenthesis and the documentation confirms this: "Executes the SQL statement in the context of this connection". The following fail

ActiveRecord::Base.connection.execute(/Users/main/optionsets.sql)
SyntaxError ((irb):3: unknown regexp options - ma)

ActiveRecord::Base.connection.execute("/Volumes/main/optionsets.sql")
ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "/")
LINE 1: /Users/main/optionsets.sql

location = "/Volumes/main/optionsets.sql"
ActiveRecord::Base.connection.execute(location)
ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "/")
LINE 1: /Users/main/optionsets.sql

How can this be effected instead by calling an .sql file /[path_to]/optionsets.sql ?

Upvotes: 0

Views: 308

Answers (2)

Kewei Jiang
Kewei Jiang

Reputation: 163

If run from the command line, then Rails also has a "dbconsole" command with a "db" alias, so you can do something like this

rails db < /Volumes/main/optionsets.sql

https://github.com/rails/rails/blob/bbd6d82dc4e00b00f07193a0c7ac3e266bce410a/guides/source/command_line.md#binrails-dbconsole

Upvotes: 1

Jerome
Jerome

Reputation: 6217

Reading the file is not effected in the above scenarios, so it must be executed beforehand.

file = File.read('/[path_to]/optionsets.sql')     
ActiveRecord::Base.connection.execute(file)

Upvotes: 1

Related Questions