Why Using Raw SQL in a Ruby on Rails Application Is Considered a Code Smell
Why Raw SQL in Ruby on Rails Is Usually Frowned Upon, But There Are Times When It’s Justifiable
What is a “code smell”?
A code smell is a term used in software development to describe a characteristic or symptom in the code that indicates a deeper problem. It is an indication that there may be an issue with the design or implementation of the code that could lead to further problems down the line.
Code smells are not necessarily errors, but they do suggest that the code may be hard to maintain, difficult to understand, or prone to bugs. They can be thought of as warning signs that indicate a potential problem.
Examples of code smells include long methods or classes, repeated code blocks, excessive comments, excessive indentation, and many others. These smells are often an indication that the code could be improved through refactoring, which involves restructuring the code to make it more readable, maintainable, and efficient.
Identifying and addressing code smells is an important part of software development because it can lead to better quality code that is easier to maintain and update over time.
Why is adding raw SQL to a Ruby on Rails application considered a code smell?
Adding raw SQL to a Ruby on Rails application is considered a code smell because it violates some of the key principles of the Rails framework, which include convention over configuration, testability, and security.
Convention over configuration
Ruby on Rails is a web development framework that follows the principle of Convention over Configuration (CoC). CoC is a software design paradigm that emphasizes convention and configuration rather than hard-coded configurations to reduce the amount of code needed to build applications.
In Rails, these conventions are embodied in a set of naming and directory structure conventions, which help developers to organize and structure their code. For example, by following these conventions, Rails can infer the names and locations of models, controllers, and views, reducing the amount of boilerplate code that developers need to write.
When developers use raw SQL in their Rails applications, they are bypassing these conventions and potentially creating code that is difficult to understand and maintain. This is because SQL code is not inherently structured according to Rails conventions, and developers may need to write more code to integrate it with the rest of their Rails application.
In addition, using raw SQL can make it more challenging to maintain the application over time. For example, if the database schema changes, developers will need to update any raw SQL queries that rely on that schema. In contrast, if developers use ActiveRecord, the ORM provided by Rails, the schema can be updated automatically without needing to modify any application code.
Overall, by using Rails conventions and ActiveRecord, developers can create more maintainable and scalable applications. This can help to reduce technical debt, improve code quality, and make the application easier to extend and modify over time.
Testability
When writing SQL queries directly in code, it can be challenging to test the code effectively because the SQL statements are tightly coupled to the application code. This can lead to several problems, including:
Test data management: When writing tests for code that uses raw SQL, developers need to manually set up the test data in the database. This can be time-consuming and error-prone, especially if the data requirements for the query are complex.
Code maintainability: If the SQL query is embedded in the application code, any changes to the database schema or the query itself require changes to the application code. This can make the code more difficult to maintain, as it may require updates in multiple places.
Testing complexity: Writing tests for SQL code can be more complex than testing application code that uses ActiveRecord. Developers need to ensure that the test data is in the correct state, that the query is being executed correctly, and that the results are what is expected.
In contrast, ActiveRecord provides a clean separation between the application code and the database. This separation makes it easier to write and run tests because:
ActiveRecord includes built-in mechanisms for generating test data, making it easier to set up the test environment.
Since ActiveRecord is an ORM, it maps database tables to Ruby objects, providing a more natural way of accessing and modifying data. This makes it easier to write and maintain the code.
ActiveRecord provides a query interface that abstracts away the need to write SQL queries directly in code. This interface makes it easier to write tests for database interactions because the query code is separated from the application code.
Overall, by using ActiveRecord, developers can write code that is more testable, more maintainable, and less error-prone. This can help to improve the overall quality of the application and reduce the risk of bugs and errors in the code.
Security
Raw SQL can be vulnerable to SQL injection attacks if not written carefully. ActiveRecord provides built-in protections against these types of attacks, making the application more secure.
A SQL injection attack is a type of security vulnerability that can occur in web applications that use a database. The attack involves an attacker using malicious input to manipulate the SQL statements that are executed by the database, potentially allowing the attacker to access or modify sensitive data.
For example, if a web application uses user input to construct a SQL query without proper validation or sanitization, an attacker could input specially crafted code to modify the query’s behavior. This can allow the attacker to bypass authentication mechanisms, access sensitive data, or modify the database.
ActiveRecord, the Object-Relational Mapping (ORM) framework included in Ruby on Rails, provides several built-in protections against SQL injection attacks.
First, ActiveRecord uses parameterized queries, also known as prepared statements, which allow the database to treat user input as a parameter rather than as part of the SQL statement. This makes it more difficult for attackers to inject SQL code into the query.
Second, ActiveRecord automatically sanitizes user input to prevent the injection of malicious SQL code. It does this by escaping special characters in user input, such as quotes and semicolons, that could be used to modify the SQL statement.
Finally, ActiveRecord provides a query interface that abstracts away the need to write SQL queries directly in code. This reduces the risk of developers accidentally introducing SQL injection vulnerabilities into their code.
Overall, these protections provided by ActiveRecord can help prevent SQL injection attacks and make Ruby on Rails applications more secure. However, it is important to use these protections correctly and to follow secure coding practices to ensure the application is as secure as possible.
What are some reasons you may want to add raw SQL to a Ruby on Rails application?
While it is generally recommended to use the Object-Relational Mapping (ORM) provided by Ruby on Rails, there may be situations where it is appropriate to use raw SQL in a Rails application. Here are a few examples:
Complex queries: In some cases, a query may be too complex or too specific to be handled by the ORM. In these cases, it may be appropriate to write the query in raw SQL to achieve the desired results.
Performance optimization: In some cases, raw SQL may be faster than using the ORM, particularly for queries that involve large amounts of data or complex joins. However, it is important to carefully benchmark and test any performance optimizations to ensure they are effective and don’t introduce new issues.
Database-specific features: Some databases offer features that are not supported by the ORM. In these cases, it may be necessary to use raw SQL to take advantage of those features.
Migration scripts: When migrating data from one database schema to another, it may be necessary to write custom SQL scripts to modify the data.
However, when using raw SQL in a Rails application, it is important to be cautious and ensure that the code is secure and maintainable. Developers should be familiar with SQL injection vulnerabilities and take steps to prevent them, such as using prepared statements and sanitizing user input. Additionally, raw SQL code should be well-documented and tested to ensure that it can be easily maintained over time.
Summary
In summary, adding raw SQL to a Ruby on Rails application is a code smell because it goes against some of the key principles of the framework and can lead to code that is difficult to understand, test, and secure. Instead, it is recommended to use the ORM provided by Rails, ActiveRecord, which abstracts away the need to write SQL queries directly in code.
