Best Practices

Recommended patterns, common pitfalls, and optimization strategies for using Ormed effectively.

Query Optimization

Avoid N+1 Queries

❌ Bad: Lazy loading in loops

// BAD: N+1 problem - one query per user
Future<void> nPlusOneBad(DataSource dataSource) async {
  final users = await dataSource.query<$User>().get();

  for (final user in users) {
    // This triggers a query for EACH user!
    await user.load(['posts']);
    print('${user.name} has ${user.posts?.length} posts');
  }
}

✅ Good: Eager load relations upfront

// GOOD: Eager loading - just 2 queries total
Future<void> nPlusOneGood(DataSource dataSource) async {
  final users = await dataSource
      .query<$User>()
      .with_(['posts']) // Load all posts in one query
      .get();

  for (final user in users) {
    // Already loaded, no additional query
    print('${user.name} has ${user.posts?.length} posts');
  }
}

Use Aggregate Loaders

❌ Bad: Loading full collections to count

// BAD: Loads all related records just to count
Future<void> aggregateBad(DataSource dataSource) async {
  final user = await dataSource.query<$User>().with_(['posts']).first();
  final postCount = user?.posts?.length ?? 0;
}

✅ Good: Use aggregate loaders

// GOOD: Uses database to count
Future<void> aggregateGood(DataSource dataSource) async {
  final user = await dataSource.query<$User>().first();
  await user?.loadCount(['posts']);
  final postCount = user?.getAttribute<int>('posts_count') ?? 0;
}

Select Only What You Need

❌ Bad: Selecting all columns

// BAD: Loads all columns when you only need emails
Future<void> selectBad(DataSource dataSource) async {
  final users = await dataSource.query<$User>().get();
  final emails = users.map((u) => u.email).toList();
}

✅ Good: Use pluck() for single columns

// GOOD: Only select what you need
Future<void> selectGood(DataSource dataSource) async {
  final emails = await dataSource.query<$User>().pluck<String>('email');
}

Use Pagination

Future<void> paginationExample(DataSource dataSource) async {
  // Use pagination for large datasets
  final page = 1;
  final perPage = 20;

  final users = await dataSource
      .query<$User>()
      .orderBy('id')
      .limit(perPage)
      .offset((page - 1) * perPage)
      .get();
}

Index Frequently Queried Columns

// Ensure indexes on frequently queried columns
// In your migration:
// schema.create('users', (table) {
//   table.index(['email']);
//   table.index(['created_at']);
//   table.index(['status', 'created_at']); // Composite for common filters
// });

Relation Loading Strategies

When to Use Eager Loading

✅ Use when you know you'll need the relations:

• List views showing related data
• APIs returning nested resources
• Reports and dashboards

// Use eager loading when:
// - Processing multiple records
// - You KNOW you need the relation data
// - Building API responses with nested data
Future<void> whenToUseEagerLoading(DataSource dataSource) async {
  final posts = await dataSource.query<$Post>().with_([
    'author',
    'comments',
  ]).get();

  // Build response with all data already loaded
  for (final post in posts) {
    print('${post.title} by ${post.author?.name}');
  }
}

When to Use Lazy Loading

✅ Use when relations are conditionally needed:

• Detail views with permission-based data
• Optional expansions based on request parameters

// Use lazy loading when:
// - You might not need the relation
// - Processing single records
// - Conditional logic determines if relation is needed
Future<void> whenToUseLazyLoading(DataSource dataSource) async {
  final post = await dataSource.query<$Post>().find(1);

  if (post != null && post.getAttribute<bool>('featured') == true) {
    // Only load if needed
    await post.load(['author', 'comments']);
  }
}

Use loadMissing for Hybrid Scenarios

// Use loadMissing to avoid reloading
Future<void> loadMissingExample(DataSource dataSource) async {
  final post = await dataSource.query<$Post>().with_(['author']).first();

  if (post != null) {
    // Won't reload author, only loads comments
    await post.loadMissing(['author', 'comments']);
  }
}

Prevent Lazy Loading in Development

// Enable lazy loading prevention during development
void preventLazyLoadingExample() {
  // In main.dart or test setup
  // if (kDebugMode) {
  //   Model.preventLazyLoading();
  // }

  // Now accessing unloaded relations throws an exception
  // This helps catch N+1 issues during development
}

Model Design

Extend Model for Full Features

// Add business logic to your models
// In user.dart:
// extension UserExtensions on User {
//   String get displayName => name ?? email.split('@').first;
//   bool get isVerified => emailVerifiedAt != null;
//   bool get canPost => isVerified && active;
// }

Benefits:

• Lazy loading: await user.load(['posts'])
• Relation mutations: user.associate('role', role)
• Persistence: await user.save()
• Attributes: user.getAttribute('computed_value')

Use Immutable Models

// Models should be immutable - use tracked models for changes
Future<void> immutableModelExample(DataSource dataSource) async {
  final user = await dataSource.query<$User>().find(1);

  if (user != null) {
    // Don't modify original, use setAttribute on tracked model
    user.setAttribute('name', 'New Name');
    await dataSource.repo<$User>().update(user);

    // Or use update DTO
    await dataSource.repo<$User>().update(
      UserUpdateDto(name: 'New Name'),
      where: {'id': 1},
    );
  }
}

Benefits: Thread-safe, predictable, easy to test.

Use Soft Deletes Wisely

// Use soft deletes wisely
// Good for:
// - Audit trails
// - Recoverable deletions
// - Legal/compliance requirements

// Avoid for:
// - High-volume ephemeral data
// - Storage-constrained environments
// - Data without recovery needs

Error Handling

Use Typed Exceptions

Future<void> typedExceptionsExample(DataSource dataSource, String email) async {
  try {
    final user = await dataSource
        .query<$User>()
        .whereEquals('email', email)
        .firstOrFail();
  } on ModelNotFoundException catch (e) {
    print('User not found: $e');
  }
}

Validate Before Save

Define validation rules on your model:

@OrmModel(table: 'validated_users')
class ValidatedUser extends Model<ValidatedUser> {
  const ValidatedUser({
    required this.id,
    required this.email,
    required this.age,
  });

  @OrmField(isPrimaryKey: true, autoIncrement: true)
  final int id;
  final String email;
  final int age;

  void validate() {
    if (!email.contains('@')) {
      throw ArgumentError('Invalid email format');
    }
    if (age < 0 || age > 150) {
      throw ArgumentError('Invalid age');
    }
  }
}

Call validation before persistence:

Future<void> validateBeforeSaveExample(
  DataSource dataSource,
  ValidatedUser user,
) async {
  // Before inserting
  user.validate();
  await dataSource.repo<$ValidatedUser>().insert(user);
}

Use Transactions for Critical Operations

Future<void> transactionExample(
  DataSource dataSource,
  int fromAccountId,
  int toAccountId,
  double amount,
  double sourceBalance,
  double destBalance,
) async {
  await dataSource.transaction(() async {
    await dataSource.query<$User>().whereEquals('id', fromAccountId).update({
      'balance': sourceBalance - amount,
    });

    await dataSource.query<$User>().whereEquals('id', toAccountId).update({
      'balance': destBalance + amount,
    });
  });
}

Testing

Use In-Memory Databases

// Use in-memory databases for fast, isolated tests
// driver: SqliteDriverAdapter.inMemory()

Use Factories for Test Data

Future<void> testFactoriesExample(DataSource dataSource) async {
  final userFactory = Model.factory<User>();

  for (var i = 0; i < 100; i++) {
    await userFactory
        .seed(i)
        .withField('email', 'user_$i@test.com')
        .create(context: dataSource.context);
  }

  // expect(await dataSource.query<$User>().count(), equals(100));
}

Security

Never Use Raw User Input

❌ Bad: SQL injection risk

// BAD: SQL injection risk
Future<void> sqlInjectionBadExample(
  DataSource dataSource,
  String userInput,
) async {
  // DON'T DO THIS!
  // final users = await dataSource.query<$User>()
  //     .whereRaw("email = '\$userInput'")
  //     .get();
}

✅ Good: Parameterized queries

// GOOD: Parameterized queries
Future<void> sqlInjectionGoodExample(
  DataSource dataSource,
  String userInput,
) async {
  final users = await dataSource
      .query<$User>()
      .whereEquals('email', userInput)
      .get();
}

Validate Relations Before Mutations

Future<void> validateRelationsExample(
  DataSource dataSource,
  Post post,
  User author,
) async {
  final validAuthor = await dataSource
      .query<$User>()
      .whereEquals('id', author.id)
      .whereEquals('active', true)
      .firstOrNull();

  if (validAuthor == null) {
    throw Exception('Invalid author');
  }

  post.associate('author', validAuthor);
  await post.save();
}

Use Scopes for Multi-Tenancy

void scopesMultitenancyExample(QueryContext context, int currentTenantId) {
  context.scopeRegistry.registerScope<$Post>((query) {
    query.whereEquals('tenant_id', currentTenantId);
  });

  // All queries automatically filtered
  // final posts = await dataSource.query<$Post>().get();
  // SQL: SELECT * FROM posts WHERE tenant_id = ?
}

Limit Exposed Fields

Create DTOs for API responses:

class UserDto {
  final int id;
  final String email;
  // No passwordHash or apiToken

  UserDto({required this.id, required this.email});

  factory UserDto.fromModel(User user) =>
      UserDto(id: user.id, email: user.email);
}

Future<List<UserDto>> getUserDtosExample(DataSource dataSource) async {
  final users = await dataSource.query<$User>().get();
  return users.map(UserDto.fromModel).toList();
}

Summary Checklist

Query Performance

• Use eager loading instead of lazy loading in loops
• Use aggregate loaders instead of loading full collections
• Add database indexes for frequently queried columns
• Use pagination for large result sets

Model Design

• Extend Model<T> for full feature support
• Use immutable models
• Add validation methods
• Use soft deletes only when needed

Error Handling

• Catch specific exceptions
• Use transactions for critical operations
• Validate data before persistence

Security

• Never concatenate user input in queries
• Validate relations before mutations
• Use scopes for multi-tenancy
• Create DTOs for API responses