Object relational mappers (ORM) are utilized to abstract database access into your preferred object oriented programming language. On the other hand, raw SQL statements are a more direct way of communicating with a database. Usually raw SQL statements are either embedded into your database access layer or stored in your database as a stored procedure. Each approach has its advantages and disadvantages.
- ORM
- Advantages
- Switching and upgrading databases are easier.
- Great for simple CRUD.
- In theory, one object can represent an entire entity and be passed through an entire application.
- SQL injection is usually taken care of ORM frameworks.
- Database tables can be generated from objects.
- Objects can be generated from database tables.
- Easy to store in source control.
- Disadvantages
- Slower than SQL.
- More memory usage than SQL.
- Have to learn quirky bugs for the specific ORM one is utilizing.
- Advantages
- SQL Embedded Into DAL
- Advantages
- Faster and more expressive than ORM.
- Easy to store in source control.
- Disadvantages
- Slower than stored procedure due to not being compiled and having to send the raw SQL over the network.
- Manual intervention to prevent SQL injection is required.
- Advantages
- Stored Procedure
- Advantages
- Faster and more expressive than ORM.
- Faster than SQL embedded into the database access layer.
- Creates a distinct separation of database logic and application logic.
- Disadvantages
- Requires extra steps to put in source control.
- When stepping through code, debugging requires more work than other database access methods because one has to navigate to the stored procedure in order to see the database logic.
- Advantages
As one can see, each database access method has its advantages and disadvantages. Usually, the most important consideration when choosing a database access method is the complexity of the query. For simple CRUD queries, the ORM tends to be best. For very complex queries, which usually involve data manipulation and calculation, SQL tends to be the way to go. Most large applications tend to utilize both the ORM and SQL for database access.
Which method do you prefer? Feel free to comment below!
With thanks! Valuable information!