Multi-Database Support

Ormed supports connecting to multiple databases simultaneously for read replicas, tenant databases, or different database systems.

Configuring Multiple Connections

Define DataSources

  // Primary database
  final primaryDs = DataSource(
    DataSourceOptions(
      name: 'primary',
      driver: SqliteDriverAdapter.inMemory(),
      entities: generatedOrmModelDefinitions,
    ),
  );

  // Analytics database
  final analyticsDs = DataSource(
    DataSourceOptions(
      name: 'analytics',
      driver: SqliteDriverAdapter.inMemory(),
      entities: generatedOrmModelDefinitions,
    ),
  );

Initialize

Initialize them (can be in parallel).

  await Future.wait([primaryDs.init(), analyticsDs.init()]);

Using Named Connections

Setup

Create and initialize multiple named DataSources:

  final primaryDs = DataSource(
    DataSourceOptions(
      name: 'primary',
      driver: SqliteDriverAdapter.inMemory(),
      entities: generatedOrmModelDefinitions,
    ),
  );
  await primaryDs.init();

  final analyticsDs = DataSource(
    DataSourceOptions(
      name: 'analytics',
      driver: SqliteDriverAdapter.inMemory(),
      entities: generatedOrmModelDefinitions,
    ),
  );
  await analyticsDs.init();

Queries

Use the connection instance you want to target:

  // Query from specific connection
  final users = await primaryDs.query<$User>().get();
  final analyticsData = await analyticsDs.query<$User>().get();

Transaction Caveat

Transactions cannot span multiple databases:

Coordinating Across Databases

Use compensating transactions for cross-database operations:

Why

Transactions cannot span multiple databases. If you need cross-database workflows, coordinate explicitly and design for retries.

Caveat (code)

  // Transactions are per-datasource
  await primaryDs.transaction(() async {
    await primaryDs.repo<$User>().insert(
      $User(id: 0, email: 'user@example.com'),
    );

    // This is NOT in the same transaction!
    // analyticsDs.repo<$Analytics>().insert(...)
  });

Pattern (code)

  // Coordinate operations across databases manually
  try {
    // Step 1: Primary operation
    final user = await primaryDs.repo<$User>().insert(
      $User(id: 0, email: 'user@example.com'),
    );

    // Step 2: Analytics operation
    // await analyticsDs.repo<$UserActivity>().insert(...)

    // If step 2 fails, you may need to compensate step 1
  } catch (e) {
    // Handle compensation if needed
    rethrow;
  }

Multi-Tenant Architecture

Tenant DB

class TenantDataSourceManager {
  final Map<String, DataSource> _dataSources = {};

  Future<DataSource> getForTenant(String tenantId) async {
    if (!_dataSources.containsKey(tenantId)) {
      final ds = DataSource(
        DataSourceOptions(
          name: 'tenant_$tenantId',
          driver: SqliteDriverAdapter.inMemory(),
          entities: generatedOrmModelDefinitions,
          tablePrefix: '${tenantId}_',
        ),
      );
      await ds.init();
      _dataSources[tenantId] = ds;
    }
    return _dataSources[tenantId]!;
  }

  Future<void> dispose() async {
    for (final ds in _dataSources.values) {
      await ds.dispose();
    }
    _dataSources.clear();
  }
}

Tenant scope

Future<void> tenantScopeExample() async {
  final manager = TenantDataSourceManager();

  // Get datasource for specific tenant
  final tenantDs = await manager.getForTenant('acme-corp');

  // All queries scoped to tenant
  final users = await tenantDs.query<$User>().get();
  await tenantDs.repo<$User>().insert($User(id: 0, email: 'user@acme.com'));

  await manager.dispose();
}

Connection Factory

typedef DataSourceFactory = Future<DataSource> Function(String name);

DataSourceFactory createDataSourceFactory(
  List<OrmModelDefinition> definitions,
) {
  return (String name) async {
    final ds = DataSource(
      DataSourceOptions(
        name: name,
        driver: SqliteDriverAdapter.inMemory(),
        entities: definitions,
      ),
    );
    await ds.init();
    return ds;
  };
}

ConnectionManager

class ConnectionManager {
  final Map<String, DataSource> _connections = {};

  Future<DataSource> get(String name) async {
    if (!_connections.containsKey(name)) {
      throw Exception('Connection "$name" not registered');
    }
    return _connections[name]!;
  }

  Future<void> register(String name, DataSource ds) async {
    await ds.init();
    _connections[name] = ds;
  }

  Future<void> disposeAll() async {
    for (final ds in _connections.values) {
      await ds.dispose();
    }
    _connections.clear();
  }
}

Connection resolution for static helpers follows this order:

1. A custom resolver registered via Model.bindConnectionResolver(...).
2. The default connection in ConnectionManager (set by DataSource.init() or dataSource.setAsDefault()).

If you need per-request routing, bind a resolver and return the correct QueryContext:

Model.bindConnectionResolver(
  resolveConnection: (name) => tenantResolver(name),
  connectionManager: ConnectionManager.instance,
  defaultConnection: 'primary',
);

Best Practices

1. Name connections clearly - Use descriptive names like 'primary', 'analytics', 'cache'
2. Set a default connection - Makes API cleaner for the most common case
3. Avoid cross-database transactions - They're complex and often not supported
4. Close connections properly - Use await dataSource.dispose() when shutting down
5. Monitor connection health - Track active connections and query times

Driver Compatibility

Feature	SQLite	PostgreSQL	MySQL
Multiple connections	✅	✅	✅
Read replicas	🔜	🔜	🔜
Connection pooling	Driver-specific	Driver-specific	Driver-specific
Cross-DB queries	❌	❌	❌

🔜 = Planned for future release