Driver Capabilities

Ormed supports multiple database drivers and integrations (SQLite, PostgreSQL, MySQL, Cloudflare D1, and Drift-backed executors), but not all databases support the same features. The Driver Capabilities system allows you to query what features are available at runtime.

Overview

Drivers advertise supported features via DriverAdapter.capabilities and supportsCapability(...). Use this to:

• Guard optional features (e.g. returning, rawSQL, schemaIntrospection)
• Skip incompatible tests in shared driver suites
• Provide fallbacks when a feature is unavailable

Available Capabilities

// Ormed exposes a fixed set of driver capabilities:
//
// joins
// insertUsing
// queryDeletes
// schemaIntrospection
// returning
// transactions
// threadCount
// adHocQueryUpdates
// advancedQueryBuilders
// sqlPreviews
// increment
// rawSQL
// relationAggregates
// caseInsensitiveLike
// rightJoin
// distinctOn
// databaseManagement
// foreignKeyConstraintControl

Checking Capabilities at Runtime

void checkCapabilitiesExample(QueryContext context) async {
  final adapter = context.driver;

  // Check single capability
  if (adapter.supportsCapability(DriverCapability.rawSQL)) {
    print('Can use raw SQL fragments');
  }

  // Check multiple capabilities
  final canDoComplexQuery =
      adapter.supportsCapability(DriverCapability.joins) &&
      adapter.supportsCapability(DriverCapability.rawSQL);

  // Get all supported capabilities
  final supported = adapter.capabilities;
  print('Driver supports: ${supported.map((c) => c.name).join(', ')}');
}

Driver-Specific Behavior

Different drivers can expose different capability sets. Prefer runtime checks over hard-coded assumptions.

Writing Cross-Database Code

Strategy 1: Capability Checks

Future<List<$Post>> getTopPostsWithCapabilityCheck(QueryContext context) async {
  var query = context.query<$Post>();

  if (context.driver.supportsCapability(DriverCapability.rawSQL)) {
    query = query.selectRaw(
      'posts.*, (SELECT COUNT(*) FROM comments WHERE post_id = posts.id) AS comment_count',
    );
  } else {
    // Prefer query builder fallbacks when raw SQL isn't supported.
    query = query.select(['id', 'title', 'views']);
  }

  return query.orderBy('created_at', descending: true).limit(10).get();
}

Strategy 2: Prefer Query Builder Over Raw

The query builder API works across all drivers:

Future<void> queryBuilderVsRaw(QueryContext context) async {
  // ✅ Works everywhere
  final posts1 = await context
      .query<$Post>()
      .whereEquals('status', 'published')
      .whereGreaterThan('views', 100)
      .orderBy('created_at', descending: true)
      .get();

  // ⚠️ Only works on SQL databases
  final posts2 = await context
      .query<$Post>()
      .whereRaw('status = ? AND views > ?', ['published', 100])
      .orderByRaw('created_at DESC')
      .get();
}

Strategy 3: Feature Detection

SearchStrategy selectStrategy(QueryContext context) {
  return context.driver.supportsCapability(DriverCapability.rawSQL)
      ? SqlSearchStrategy()
      : BasicSearchStrategy();
}

Testing Across Drivers

Skipping Incompatible Tests

void skipIncompatibleTestsExample(DriverAdapter adapter) {
  // test('raw expression in select', () async {
  //   final posts = await context.query<$Post>()
  //       .selectRaw('*, (SELECT COUNT(*) FROM comments WHERE post_id = posts.id) as count')
  //       .get();
  //
  //   expect(posts.first.getAttribute<int>('count'), greaterThan(0));
  // }, skip: !adapter.supportsCapability(DriverCapability.rawExpressions));
}

Migration Considerations

Conditional Migrations

class AddFullTextSearchMigration extends Migration {
  @override
  Future<void> up(SchemaBuilder schema) async {
    if (schema.driver.supportsCapability(DriverCapability.rawSQL)) {
      await schema.rawStatement(
        "CREATE VIRTUAL TABLE posts_fts USING fts5(title, content, content=posts);",
      );
    }
  }

  @override
  Future<void> down(SchemaBuilder schema) async {
    if (schema.driver.supportsCapability(DriverCapability.rawSQL)) {
      await schema.rawStatement('DROP TABLE IF EXISTS posts_fts');
    }
  }
}

Best Practices

1. Use Query Builder First

void bestPracticeQueryBuilder(QueryContext context) {
  // ✅ Cross-database compatible
  context
      .query<$Post>()
      .whereEquals('status', 'published')
      .orderBy('created_at', descending: true);

  // ❌ SQL-specific
  context
      .query<$Post>()
      .whereRaw('status = ?', ['published'])
      .orderByRaw('created_at DESC');
}

2. Check Capabilities, Don't Assume

Future<void> bestPracticeCheckCapabilities(
  DriverAdapter adapter,
  Query query,
) async {
  // ❌ Bad - assumes raw expressions work
  query.selectRaw('COUNT(*)');

  // ✅ Good - checks capability first
  if (adapter.supportsCapability(DriverCapability.rawExpressions)) {
    query.selectRaw('COUNT(*)');
  } else {
    query.withCount(['items']);
  }
}

3. Document Driver Requirements

/// Performs full-text search on posts.
///
/// **Requirements:**
/// - Driver must support [DriverCapability.rawSQL]
Future<List<$Post>> fullTextSearch(DriverAdapter adapter, String term) async {
  if (!adapter.supportsCapability(DriverCapability.rawSQL)) {
    throw UnsupportedError('Full-text search requires raw SQL support');
  }
  // ... implementation
  return [];
}

4. Fallback to Compatible Alternatives

Future<List<$Post>> getPostsWithStats(QueryContext context) async {
  if (context.driver.supportsCapability(DriverCapability.windowFunctions)) {
    return context
        .query<$Post>()
        .selectRaw('*, ROW_NUMBER() OVER (ORDER BY views DESC) as rank')
        .get();
  } else {
    // Fallback: compute rank in memory
    final posts = await context
        .query<$Post>()
        .orderBy('views', descending: true)
        .get();

    return posts.asMap().entries.map((entry) {
      final post = entry.value;
      post.setAttribute('rank', entry.key + 1);
      return post;
    }).toList();
  }
}

Summary

• Driver capabilities let you detect database feature support at runtime
• Query builder API provides maximum cross-database compatibility
• Capability checks enable graceful feature degradation
• For most applications, sticking to the standard query builder API provides excellent cross-database portability without manual capability checks