How do you manually execute SQL commands in Ruby On Rails using NuoDB

sql

sql-execution

rails-activerecord

database-connections

byNikita Barsukov·Oct 3, 2024

To execute SQL in Rails with NuoDB, use the following:

ActiveRecord::Base.connection.execute("SQL_HERE")

Examples:

SELECT:

rows = ActiveRecord::Base.connection.execute("SELECT * FROM users")
rows.each { |row| puts row }

INSERT:

ActiveRecord::Base.connection.execute("INSERT INTO users (name, email) VALUES ('Jane', '[email protected]')")

You've just started raw SQL execution. Notably, this operation skips Rails' inbuilt safeguards.

Maintain lean and effective database connections

Handling database resources efficiently is crucial when executing SQL commands outside of ActiveRecord. The connection_pool.with_connection block assures that database connections are managed correctly. It facilitates resource allocation and helps in avoiding exhaustion of connection pool:

ActiveRecord::Base.connection_pool.with_connection do |conn|
  conn.execute("YOUR_SQL_COMMAND") # unleash the power of raw SQL here
end

More than just 'execute'

execute is straightforward. Wondering about handling your complex SQL results more efficiently? exec_query returns data as an ActiveRecord::Result, which is quite handy. It provides methods like to_hash and columns:

result = ActiveRecord::Base.connection.exec_query("SELECT * FROM users")
result.each do |row|
  puts row['username'] // we love hash syntax, don't we?
end

To call a NuoDB stored procedure, use the same method. Ensure your SQL syntax is aligned with NuoDB's conventions:

ActiveRecord::Base.connection.execute("EXECUTE PROCEDURE my_procedure()") // Who doesn't like shortcuts?

Everyone loves reusable code

Abstract exec_query by creating a handy method within your application_controller.rb or a suitable module. This creates cleaner and compartmentalized code. Let's see how:

class ApplicationController < ActionController::Base
  def execute_statement(sql)
    result = ActiveRecord::Base.connection.exec_query(sql)
    result.present? ? result.to_a : nil  //glamorizing code reuse
  end
end

This little utility can be a real game changer - you can call it from anywhere in your Rails app. It encourages a consistent and easy-to-debug mechanism for SQL execution.

Debugging and SQL safety 101

Going custom with SQL execution means that you're bypassing Rails’ built-in safeguards, such as SQL injection protection and data type casting, typically managed by ActiveRecord. It's paramount that all SQL statements are sanitized and secure.

Need to debug your SQL execution? Pay close attention to the query syntax and ensure a successful database connection. A simple SELECT 1 command helps:

ActiveRecord::Base.connection.execute("SELECT 1")  // SELECT 'Peace of Mind' would also be cool, right?

In case of issues, Rails throws an exception and provides detailed insight for your debugging pleasure.

Going beyond basics

Advanced tasks like batch inserts or complex joins often require specific SQL commands. Such control might bring performance advantages, but remember to stay cautious. Schema changes can create brittle code:

inserts = users.map{ |u| "('#{u.name}', '#{u.email}')" }.join(',')
ActiveRecord::Base.connection.execute("INSERT INTO users (name, email) VALUES #{inserts}") // "And there came the inserts train"