orm - dwilson2547/wiki_demo GitHub Wiki

An ORM (Object-Relational Mapping) is a programming technique that allows developers to interact with a database using object-oriented programming (OOP) principles instead of writing raw SQL queries. It acts as a bridge between object-oriented code (e.g., Python, Java, C#) and relational databases (e.g., PostgreSQL, MySQL, SQLite), enabling developers to manipulate database records as objects in their programming language.

• 1. Why Use an ORM?
  • 1.1. Abstraction
  • 1.2. Productivity
  • 1.3. Portability
  • 1.4. Security
  • 1.5. Maintainability
  • 1.6. Caching
• 2. How ORMs Work
  • 2.1. Mapping Tables to Classes
  • 2.2. CRUD Operations
  • 2.3. Relationships
  • 2.4. Querying
  • 2.5. Transactions
• 3. ORM vs. Raw SQL
• 4. Popular ORMs by Language
• 5. Example: SQLAlchemy (Python ORM)
  • 5.1. Define Models
  • 5.2. CRUD Operations
  • 5.3. Relationships
• 6. ORM Limitations
  • 6.1. Performance Overhead
  • 6.2. Complex Queries
  • 6.3. Learning Curve
  • 6.4. Database-Specific Features
• 7. When to Use an ORM?
• 8. When to Avoid an ORM?
• 9. ORM Anti-Patterns
  • 9.1. N+1 Query Problem
  • 9.2. Overusing ORM for Complex Queries
  • 9.3. Ignoring Database Constraints
  • 9.4. Not Using Transactions
• 10. ORM Best Practices
  • 10.1. Use Eager Loading for Relationships
  • 10.2. Index Database Columns
  • 10.3. Limit Query Results
  • 10.4. Validate Data
  • 10.5. Use Migrations
  • 10.6. Avoid Business Logic in Models
  • 10.7. Cache Frequent Queries
• 11. ORM vs. Query Builders vs. Micro-ORMs
• 12. Example: Django ORM (Python)
  • 12.1. Define Models
  • 12.2. CRUD Operations
  • 12.3. Relationships
• 13. Example: Sequelize (Node.js ORM)
  • 13.1. Define Models
  • 13.2. CRUD Operations
• 14. Summary

ORMs address the "impedance mismatch" between object-oriented models (e.g., classes, objects) and relational databases (e.g., tables, rows, columns). They provide several benefits:

• Hide SQL Complexity: Developers work with Python/Java objects instead of writing SQL.
• Example:

  # Without ORM (Raw SQL)
  cursor.execute("INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')")
  
  # With ORM (SQLAlchemy)
  user = User(name="Alice", email="[email protected]")
  session.add(user)
  session.commit()

• Reduce Boilerplate: ORMs handle CRUD operations (Create, Read, Update, Delete) automatically.
• Example:

  # Fetch all users (ORM)
  users = User.query.all()  # No SQL needed

• Database-Agnostic Code: Switch databases (e.g., SQLite → PostgreSQL) with minimal changes.
• Example:

  # SQLAlchemy configuration for different databases
  app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/db'  # PostgreSQL
  # app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'  # SQLite

• Prevent SQL Injection: ORMs use parameterized queries to avoid SQL injection vulnerabilities.
• Example:

  # Safe with ORM (SQLAlchemy)
  User.query.filter_by(username=input_username).first()  # Sanitized automatically

• Cleaner Code: Business logic stays in Python/Java, not scattered across SQL scripts.
• Example:

  # Business logic in Python (not SQL)
  def promote_user(user):
      if user.points > 100:
          user.role = "premium"
          session.commit()

• Optimize Performance: ORMs can cache queries to reduce database load.
• Example:

  # SQLAlchemy caching
  from sqlalchemy.orm import scoped_session, sessionmaker
  Session = scoped_session(sessionmaker(bind=engine))

• Database Tables → Python Classes:

  # SQLAlchemy model (maps to a 'users' table)
  class User(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(80))
      email = db.Column(db.String(120))

  • User class maps to a users table.
  • id, name, email map to columns.

• Define relationships between tables (e.g., one-to-many, many-to-many).
• Example (One-to-Many):

  class User(db.Model):
      posts = db.relationship('Post', backref='author', lazy=True)
  
  class Post(db.Model):
      user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

  • A User has many Posts.
  • user.posts accesses all posts by a user.

• Build queries using the ORM’s query language.
• Example:

  # Get all users older than 18
  adults = User.query.filter(User.age > 18).all()

• Group operations into atomic transactions.
• Example:

  try:
      session.add(user)
      session.commit()  # Transaction succeeds
  except:
      session.rollback()  # Transaction fails

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80), nullable=False)
    body = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

# Create
user = User(name="Alice", email="[email protected]")
db.session.add(user)
db.session.commit()

# Read
users = User.query.all()
alice = User.query.filter_by(name="Alice").first()

# Update
alice.email = "[email protected]"
db.session.commit()

# Delete
db.session.delete(alice)
db.session.commit()

# One-to-Many: User has many Posts
post = Post(title="Hello World", body="First post!", user_id=alice.id)
db.session.add(post)
db.session.commit()

# Access posts by a user
print(alice.posts)  # [<Post 1>]

• ORMs add abstraction layers, which can slow down complex queries.
• Solution: Use raw SQL for performance-critical operations or optimize queries.

• Some SQL features (e.g., window functions, complex joins) are harder to express in ORMs.
• Solution: Use hybrid approaches (ORM for simple queries, raw SQL for complex ones).

• Developers must learn both the ORM and the underlying database concepts.
• Solution: Start with simple queries and gradually explore advanced features.

• ORMs abstract database differences, but some features (e.g., PostgreSQL’s JSONB) require workarounds.
• Solution: Use database-agnostic code where possible, and database-specific extensions when needed.

✅ Rapid Development: ORMs speed up prototyping and CRUD operations. ✅ Maintainability: Keep business logic in code, not SQL scripts. ✅ Database Portability: Switch databases with minimal code changes. ✅ Security: ORMs sanitize inputs to prevent SQL injection. ✅ Team Collaboration: ORMs provide a standardized way to interact with databases.

❌ Performance-Critical Apps: Raw SQL or micro-ORMs (e.g., Dapper, Peewee) may be faster. ❌ Complex Queries: Some SQL features (e.g., recursive CTEs) are hard to express in ORMs. ❌ Legacy Databases: ORMs may not support old or non-standard database features. ❌ Learning Overhead: For small projects, an ORM might be unnecessary.

• Issue: Fetching a list of objects and then querying each one individually.
• Example:

  # BAD: N+1 queries (1 for users, N for posts)
  users = User.query.all()
  for user in users:
      print(user.posts)  # Each access triggers a new query!

• Solution: Use eager loading (e.g., joinedload in SQLAlchemy).

  # GOOD: 1 query with joins
  users = User.query.options(joinedload(User.posts)).all()

• Issue: Trying to express complex SQL in ORM syntax.
• Example:

  # BAD: ORM struggles with window functions
  # session.query(User, func.row_number().over(...))  # Not straightforward

• Solution: Use raw SQL for complex operations.

  # GOOD: Raw SQL for window functions
  result = db.session.execute("SELECT *, ROW_NUMBER() OVER (...) FROM users")

• Issue: Relying on ORM for validation instead of database constraints.
• Example:

  # BAD: Only ORM validation (no DB constraint)
  class User(db.Model):
      email = db.Column(db.String(120))  # No uniqueness enforced

• Solution: Use both ORM and database constraints.

  # GOOD: Enforce uniqueness in DB
  email = db.Column(db.String(120), unique=True)

• Issue: Forgetting to group operations into transactions.
• Example:

  # BAD: No transaction (partial updates possible)
  user.balance -= 100
  db.session.commit()
  recipient.balance += 100
  db.session.commit()  # What if this fails?

• Solution: Use transactions to ensure atomicity.

  # GOOD: Atomic transaction
  try:
      user.balance -= 100
      recipient.balance += 100
      db.session.commit()
  except:
      db.session.rollback()

• Avoid N+1 queries by loading related objects upfront.
• Example (SQLAlchemy):

  users = User.query.options(joinedload(User.posts)).all()

• Add indexes to frequently queried columns.
• Example:

  class User(db.Model):
      email = db.Column(db.String(120), unique=True, index=True)  # Indexed

• Use pagination to avoid loading too much data.
• Example:

  users = User.query.paginate(page=1, per_page=10)

• Use ORM validation and database constraints.
• Example:

  from sqlalchemy import CheckConstraint
  
  class User(db.Model):
      age = db.Column(db.Integer, CheckConstraint('age >= 0'))

• Track schema changes with tools like Alembic (SQLAlchemy) or Django Migrations.
• Example:

  flask db init       # Initialize migrations
  flask db migrate    # Create a migration
  flask db upgrade    # Apply the migration

• Keep models lean and move logic to services or managers.
• Example:

  # BAD: Business logic in model
  class User(db.Model):
      def promote(self):
          if self.points > 100:
              self.role = "premium"
  
  # GOOD: Business logic in a service
  class UserService:
      def promote(user):
          if user.points > 100:
              user.role = "premium"
              db.session.commit()

• Cache results to reduce database load.
• Example (Flask-Caching):

  from flask_caching import Cache
  cache = Cache(app, config={'CACHE_TYPE': 'SimpleCache'})
  
  @cache.cached(timeout=60)
  def get_users():
      return User.query.all()

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=80)
    email = models.EmailField(unique=True)

    def __str__(self):
        return self.name

# Create
user = User(name="Alice", email="[email protected]")
user.save()

# Read
users = User.objects.all()
alice = User.objects.get(name="Alice")

# Update
alice.email = "[email protected]"
alice.save()

# Delete
alice.delete()

class Post(models.Model):
    title = models.CharField(max_length=80)
    body = models.TextField()
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="posts")

# Access posts by a user
alice.posts.all()  # [<Post 1>, <Post 2>]

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:');

const User = sequelize.define('User', {
  name: DataTypes.STRING,
  email: { type: DataTypes.STRING, unique: true }
});

const Post = sequelize.define('Post', {
  title: DataTypes.STRING,
  body: DataTypes.TEXT
});

User.hasMany(Post);  // One-to-Many
Post.belongsTo(User);

// Create
const alice = await User.create({ name: "Alice", email: "[email protected]" });
const post = await Post.create({ title: "Hello", body: "World", UserId: alice.id });

// Read
const users = await User.findAll();
const firstUser = await User.findOne({ where: { name: "Alice" } });

// Update
alice.email = "[email protected]";
await alice.save();

// Delete
await alice.destroy();

• ORM (Object-Relational Mapping) bridges the gap between object-oriented code and relational databases.
• Benefits: Abstraction, productivity, portability, security, and maintainability.
• Limitations: Performance overhead, complexity for advanced queries, and learning curve.
• Popular ORMs:
  • Python: SQLAlchemy, Django ORM
  • JavaScript: Sequelize, TypeORM
  • Java: Hibernate
  • Ruby: ActiveRecord
  • C#: Entity Framework
• Best Practices: Use eager loading, index columns, validate data, and avoid N+1 queries.
• Alternatives: Query builders (Knex.js) or micro-ORMs (Dapper) for performance-critical apps.

ORMs are powerful tools for modern web development, enabling developers to focus on business logic rather than database intricacies. They strike a balance between productivity and performance, making them ideal for most web applications, APIs, and microservices.