Rails SQL Example: Raw SQL Querying
When it comes to querying data in a Rails application, ActiveRecord provides a number of convenient methods for generating SQL queries. However, there may be situations where you need to execute raw SQL queries to interact with your database.
Rails provides a
connection method on the
ActiveRecord::Base class that returns a handle to the database connection. You can use this handle to execute raw SQL queries.
<% sql = "SELECT * FROM users WHERE age > 18" %> <% result = ActiveRecord::Base.connection.execute(sql) %> <% result.each do |row| %> <p><%= row["name"] %></p> <% end %>
In the example above, we select all users from the database where their age is greater than 18 using raw SQL. The result of the query is then iterated over to display the names of each user.
When using raw SQL queries, it's important to be aware of the potential for SQL injection attacks. To mitigate this risk, you should use parameterized queries.
<% sql = "SELECT * FROM users WHERE age > ? AND email = ?" %> <% result = ActiveRecord::Base.connection.execute(sql, 18, "firstname.lastname@example.org") %> <% result.each do |row| %> <p><%= row["name"] %></p> <% end %>
In the example above, we use parameterized queries to select users where their age is greater than a specified value and their email matches a specified string. The values for the parameters are passed as additional arguments to the
Raw SQL querying can be a powerful tool when working with a Rails application. However, it's important to use caution and ensure that your queries are safe from SQL injection attacks. By using the
connection method and parameterized queries, you can safely execute raw SQL queries in your Rails application.