Attaching two databases?

[alexiswbr]

Hello

We are using two databases for performance purposes.
Is it possible to attach two databases through Moor this way?

attach 'database1.db' as db1;
attach 'database2.db' as db2;

If it's not possible I still can achieve it in pure SQL, but how can I use one of the referenced database using Moor?

SELECT * FROM db2.table_name

If that's really not possible, our last chance is to use a single database and to open two differents Isolates on the same database. But an alert message says the database may be corrupted when I try. Scary.

[davidmartos96]

@alexiswbr I had the requirement of attaching long ago and I solved it with the following.
Moor creates the tables for you when you define the DataClasses or moor files in your app, so what I did is make moor ignore the creation of the entities that belong to the attached database.
So instead of doing migrator.createAll() I have the following method, which is equivalent:

const Set<Type> kSchemaEntitiesToIgnore = {
  AttachedDbTable1,
  AttachedDbTable2,
};

Future<void> createAllSchemaEntities(Migrator m) async {
    for (final entity in allSchemaEntities) {
      if (kSchemaEntitiesToIgnore.contains(entity.runtimeType)) {
          continue;
      }

      if (entity is TableInfo) {
        await m.createTable(entity);
      } else if (entity is Trigger) {
        await m.createTrigger(entity);
      } else if (entity is Index) {
        await m.createIndex(entity);
      } else if (entity is OnCreateQuery) {
        await customStatement(entity.sql, const <Object>[]);
      } else {
        throw AssertionError('Unknown entity: $entity');
      }
    }
  }
}

Then for the attachment itself you probably have it figured out already. The only gotcha I had is that I have to try to detach it before attaching so that it plays nice with Hot Restarts

Future<void> _attachToDb() async {
    try {
      // Detach database before attaching it, otherwise it can fail on attaching.
      // For example in a Hot Restart
      await customStatement("DETACH DATABASE 'attachedDb'");
    } catch (e) {
      // Ignore, it fails if it is not attached (first time open)
    }

    await customStatement("ATTACH DATABASE '/dbpath' AS attachedDb;");
  }

I have been using that for quite some time without any issues. Hope that works for you!

I think it may be too niche for a feature on moor, but somehow there are times where @simolus3 is able to impress us all 😄

[davidmartos96]

My queries don't need the alias because there is no naming conflict, but a query like the one you showed should work fine. Did you attach the database? Note that you are getting a sqlite error, not a moor error, so the query is getting through just fine. The problem seems to be that the database with the stats table is not attached.

[davidmartos96]

Oh, sorry the table that is not found is the users one. Then make sure that you are creating the database tables with the method above.
In your moor main database class:

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    onCreate: (Migrator m) async {
      await createAllSchemaEntities(m);
  ...

[alexiswbr]

Hoo ok I thought it was mandatory to use the chosen alias!
It works great, at the moment it looks like I don't need to detach before reattaching. Do you know when it is needed?

Eventually I have splitted the databases in two differentes classes (AppDatabase and StatsDatabase), this way I don't need to exclude anything during the createAllSchemaEntities process and Moor is generating the necessary code for both of them!

Thank you very much for your time 👍

[davidmartos96]

@alexiswbr For my case, since I am using sqflite under the hood (based on platform channels), the db connection is kept open on hot restart, that is probably not the case with VmDatabase.
About having two different db classes, how does that work on a dao which needs to import tables from 2 databases?

[alexiswbr]

We have two singletons DBService() and DBStatsService(). Each of them have a property containing a database instance.
This is surely not ideal but here is an example from our ProductStatDao.

@UseDao(tables: [ProductStats])
class ProductStatDao extends DatabaseAccessor<StatsDatabase> with _$ProductStatDaoMixin {
  StatsDatabase db;

  ProductStatDao(this.db) : super(db);

  myQuery() {
    AppDatabase mainDb = DBService().db;
    
    select(db.productStats)
      ..join([
        innerJoin(mainDb.user, mainDb.user.id.equalsExp(db.productStats.user_id), useColumns: false),
      ])
      ..where(db.productStats.user_id.equals(42));
  }
}