Microsoft SQL Server Tutorial Series: Beginner to Expert
Follow-Up: ORMs & Abstraction Layers in SQL Server
In this follow-up post, we explore Object-Relational Mappers (ORMs) and abstraction layers that simplify database interactions in modern applications.
📌 What You’ll Learn in This Post:
- What is an ORM and why use it
- Popular ORMs for Python and .NET
- Basic examples of using ORMs
- Pros and cons of ORMs vs raw SQL queries
🔍 What is an ORM?
An Object-Relational Mapper (ORM) lets you work with databases using programming language constructs instead of writing raw SQL queries.
Instead of writing SQL like SELECT * FROM Users WHERE Id = 1, you interact with database tables as if they were regular objects in your code. This can speed up development and reduce errors.
🛠 Popular ORMs
| Platform | ORM | Description |
|---|---|---|
| Python | SQLAlchemy | Powerful and flexible ORM supporting multiple databases including SQL Server. |
| .NET | Entity Framework (EF Core) | Microsoft’s official ORM for .NET applications. |
🐍 Python Example Using SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine("mssql+pyodbc://username:password@server/dbname?driver=ODBC+Driver+17+for+SQL+Server")
Base = declarative_base()
class User(Base):
__tablename__ = 'Users'
id = Column(Integer, primary_key=True)
name = Column(String)
Session = sessionmaker(bind=engine)
session = Session()
# Add a new user
new_user = User(name='Alice')
session.add(new_user)
session.commit()
# Query users
users = session.query(User).filter_by(name='Alice').all()
print(users)
💻 .NET Example Using Entity Framework Core
using Microsoft.EntityFrameworkCore;
public class User {
public int Id { get; set; }
public string Name { get; set; }
}
public class AppDbContext : DbContext {
public DbSet Users { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options) {
options.UseSqlServer("Server=server;Database=dbname;User Id=username;Password=password;");
}
}
// Usage
using(var context = new AppDbContext()) {
var user = new User { Name = "Alice" };
context.Users.Add(user);
context.SaveChanges();
var users = context.Users.Where(u => u.Name == "Alice").ToList();
}
⚖️ Pros and Cons of Using ORMs
| Pros | Cons |
|---|---|
| Faster development, less boilerplate code | Less control over generated SQL |
| Helps avoid SQL injection with parameterized queries | Can be slower for complex queries |
| Improves code readability | Learning curve if unfamiliar |
📌 Summary
- ORMs provide a higher-level way to interact with databases
- Popular ORMs include SQLAlchemy (Python) and EF Core (.NET)
- Great for most CRUD operations but sometimes raw SQL is needed
Ready to try ORMs? Start with simple CRUD operations to see the benefits!