Welcome to this week's newsletter!

As I'm finishing the C# 302 module on data access in C#, I've been working with SQL and ORMs. ORM stands for Object-Relational Mapping, a technique that lets you use your programming language to manage a database instead of writing SQL code directly. Popular examples include Entity Framework for .NET and Hibernate for Java.

ORMs have gained popularity for making database interactions more straightforward and less technical.

Benefits of ORMs

One of the biggest perks of using an ORM is productivity. ORMs allow you to work with database data in a way that feels natural in your chosen programming language, bypassing the need to be an expert in SQL, the standard language for managing databases.

A properly configured ORM can significantly reduce the lines of code to perform common query tasks. They also help keep your code clean and readable, a boon for maintenance and future updates.

This productivity boost makes them ideal for proof-of-concept and minimum viable product (MVP) builds where getting feedback on the software product is more important than formally building the software using an enterprise architecture pattern.

As an example, consider these two code snippets:

---

---

As you can see, the second code snippet is significantly longer and requires writing SQL, handling database connections and commands, etc.

Challenges of ORMs

However, ORMs are not without their challenges. When you must execute complex queries or deal with intricate database structures, ORMs can lose performance. There's also a concept known as "Impedance Mismatch" – a conflict between how data is represented in the database versus in a programming language.

The ORM generates SQL based on code, so it tries to be a general solution to a specific problem. This means that ORMs sometimes generate slower queries compared to finely-tuned SQL. Furthermore, ORMs typically work best with direct table access, which could have security implications.

Don't Make the False Choice

It's important to remember that choosing ORM doesn't mean abandoning SQL completely. In scenarios where ORM falls short, you can still write direct SQL queries. This flexibility is especially valuable in handling complex database operations efficiently.

The key challenge as a software developer is figuring out when an ORM makes sense, and direct SQL is a better solution. As a general rule of thumb:

Use ORMs When

• The database schema is simple.
• The data is not secret or sensitive. For example, an online storefront with mostly product and price information that users of the site should have access to.
• When development speed is a critical requirement.

Use Direct SQL When

• The database schema is complex, or the queries require many joins, subqueries, or other advanced SQL features.
• The database contains data that is secret or sensitive. With direct SQL paired with views and stored procedures, you can more granularly control what data is accessed by the application.
• When performance is a critical requirement. To be clear, ORMs have become very fast, even equivalent in basic query scenarios, but queries tuned by a database professional will generally outperform what an ORM generates, especially in complex scenarios.

Should Beginners Use ORMs?

Many tutorials and bootcamps dive right into ORMs because they are trying to cram a lot of topics into a small space. However, I strongly recommend going deeper into SQL and relational database modeling. Most of the corporate world runs on databases, and SQL remains one of the top languages sought in job postings. If you do your research, you'll see that SQL is frequently paired with other mainstream programming languages like C#, Java, JavaScript, and Python.

Understanding SQL and relational database modeling will give you more flexibility and options in your coding career. Too often, I see beginners using ORMs as a crutch because they don't understand how databases work. You should approach an ORM as a tool that enhances productivity, not a substitute for understanding how databases work.

Databases are a Key Part of the Journey

I hope this overview has helped you better understand the ORM versus SQL debate. Learning about databases and SQL is a valuable part of your coding journey. It opens up a world of possibilities and empowers you to make informed decisions about the tools you use.

If you want to go deeper, check out the SQL 100 series and the upcoming C# 302 release on Skill Foundry!