Sqlite with two attached databases

[sheredinovs]

I have a flutter application with one database file(bundled file from assets folder) contains data to display in the application. I'm trying to add a second database file. The second database created upon initialization in UserDBProvider. After this I call DBProvider.attachUserDb. I didn’t get any errors during this operation.

import 'dart:async';
import 'dart:io';
import 'dart:typed_data';

import 'package:flutter/services.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

class DBProvider {
  DBProvider._();
  static DBProvider _db = DBProvider._();
  static Database _database;

  factory DBProvider() {
    return _db ??= new DBProvider();
  }

  Future<Database> get database async {
    if (_database != null) return _database;

    _database = await initDB();
    return _database;
  }

  initDB() async {
    var databasesPath = await getDatabasesPath();
    var path = join(databasesPath, "samples.db");

    var exists = await databaseExists(path);

    if (!exists) {
      try {
        await Directory(dirname(path)).create(recursive: true);
      } catch (_) {}

      ByteData data = await rootBundle.load(join("assets", "app_database"));
      List<int> bytes =
          data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);

      await File(path).writeAsBytes(bytes, flush: true);
    } else {}

    return await openDatabase(path, readOnly: false);
  }

  Future<Database> attachUserDb({Database db, String databaseName}) async {
    Directory documentDirectory = await getApplicationDocumentsDirectory();
    String absoluteEndPath = join(documentDirectory.path, databaseName);
    await db
        .rawQuery("ATTACH DATABASE '$absoluteEndPath' as '$databaseName'")
        .catchError((e) {
      print(e);
    }).whenComplete(() {
      print("complete");
    });
    return db;
  }
}

class UserDBProvider {
  UserDBProvider._();
  static UserDBProvider _db = UserDBProvider._();
  static Database _database;
  static final String DATABASE_NAME = "user_database";

  factory UserDBProvider() {
    return _db ??= new UserDBProvider();
  }

  Future<Database> get database async {
    if (_database != null) return _database;

    _database = await initDB();
    DBProvider().attachUserDb(db: _database, databaseName: DATABASE_NAME);
    return _database;
  }

  initDB() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, DATABASE_NAME);
    return await openDatabase(path,
        readOnly: false,
        version: 1,
        onOpen: (db) {}, onCreate: (Database db, int version) async {
      await db.execute("CREATE TABLE favorites (user_id INTEGER NOT NULL, "
          "insert_date INTEGER NOT NULL )");
    });
  }
}

But after this I'm trying to join 2 tables from source and attached databases and I get exception

getFavosites() async {
    Database database = await DBProvider().database;
    String query = "select usr.user_id, "
        "usr.user_title "
        "from user_database.favorites fav, "
        "    users usr "
        "where usr.user_id = fav.user_id ";
    return database.rawQuery(query);
  }

E/flutter (15688): [ERROR:flutter/lib/ui/ui_dart_state.cc(148)] Unhandled Exception: DatabaseException(no such table: user_database.favorites (code 1): , while compiling: select usr.user_id, usr.user_title from user_database.favorites fav, users usr where usr.user_id = fav.user_id) sql 'select usr.user_id, usr.user_title from user_database.favorites fav, users usr where usr.user_id = fav.user_id ' args []}

Full stack-trace

E/SQLiteLog(15688): (1) no such table: user_database.favorites
E/flutter (15688): [ERROR:flutter/lib/ui/ui_dart_state.cc(148)] Unhandled Exception: DatabaseException(no such table: user_database.favorites (code 1): , while compiling: select usr.user_id, usr.user_title from user_database.favorites fav,     users usr where usr.user_id = fav.user_id) sql 'select usr.user_id, usr.user_title from user_database.favorites fav,     users usr where usr.user_id = fav.user_id ' args []}
E/flutter (15688): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
E/flutter (15688): <asynchronous suspension>
E/flutter (15688): #1      SqfliteDatabaseFactoryImpl.wrapDatabaseException (package:sqflite/src/factory_impl.dart:29:7)
E/flutter (15688): #2      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.safeInvokeMethod (package:sqflite/src/database_mixin.dart:184:15)
E/flutter (15688): #3      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnRawQuery.<anonymous closure> (package:sqflite/src/database_mixin.dart:366:36)
E/flutter (15688): <asynchronous suspension>
E/flutter (15688): #4      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnSynchronized.<anonymous closure> (package:sqflite/src/database_mixin.dart:300:22)
E/flutter (15688): #5      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:31:26)
E/flutter (15688): <asynchronous suspension>
E/flutter (15688): #6      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnSynchronized (package:sqflite/src/database_mixin.dart:296:43)
E/flutter (15688): <asynchronous suspension>
E/flutter (15688): #7      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin.txnRawQuery (package:sqflite/src/database_mixin.dart:365:12)
E/flutter (15688): #8      _SqfliteDatabaseBase&Object&SqfliteDatabaseMixin&SqfliteDatabaseExecutorMixin.rawQuery (package:sqflite/src/database_mixin.dart:115:15)
E/flutter (15688): #9      UiHelper.getFavosites (package:csharp_samples_pro/utils/ui_helper.dart:97:21)
E/flutter (15688): <asynchronous suspension>
E/flutter (15688): #10     FavoritesState.inflateData (package:csharp_samples_pro/pages/favorites.dart:23:38)
E/flutter (15688): <asynchronous suspension>
E/flutter (15688): #11     FavoritesState.initState (package:csharp_samples_pro/pages/favorites.dart:19:5)
E/flutter (15688): #12     StatefulElement._firstBuild (package:flutter/src/widgets/framework.dart:4033:58)
E/flutter (15688): #13     ComponentElement.mount (package:flutter/src/widgets/framework.dart:3902:5)
E/flutter (15688): #14     Element.inflateWidget (package:flutter/src/widgets/framework.dart:3084:14)
E/flutter (15688): #15     Element.updateChild (package:flutter/src/widgets/framework.dart:2887:12)
E/flutter (15688): #16     SingleChildRenderObjectElement.mount (package:flutter/src/widgets/framework.dart:5092:14)
E/flutter (15688): #17     Element.inflateWidget (package:flutter/src/widgets/framework.dart:3084:14)
E/flutter (15688): #18     Element.updateChild (package:flutter/src/widgets/framework.dart:2887:12)
E/flutter (15688): #19     SingleChildRenderObjectElement.mount (package:flutter/src/widgets/framework.dart:5092:14)
E/flutter (15688): #20     Element.inflateWidget (package:flutter/src/widgets/framework.dart:3084:14)
E/flutter (15688): #21     Element.updateChild (package:flutter/src/widgets/framework.dart:2887:12)
E/flutter (15688): #22     ComponentElement.performRebuild (package:flutter/src/widgets/framework.dart:3935:16)
E/flutter (15688): #23     Element.rebuild (package:flutter/src/widgets/framework.dart:3721:5)
E/flutter (15688): #24     ComponentElement._firstBuild (package:flutter/src/widgets/framework.dart:3907:5)
E/flutter (15688): #25     ComponentElement.mount (package:flutter/src/widgets/framework.dart:3902:5)
E/flutter (15688): #26     Element.inflateWidget (package:flutter/src/widgets/framework.dart:3084:14)
E/flutter (15688): #27     Element.updateChild (package:flutter/src/widgets/framework.dart:2887:12)
E/flutter (15688): #28     ComponentElement.performRebuild (package:flutter/src/widgets/framework.dart:3935:16)
E/flutter (15688): #29     Element.rebuild (package:flutter/src/widgets/framework.dart:3721:5)
E/flutter (15688): #30     ComponentElement._firstBuild (package:flutter/src/widgets/framework.dart:3907:5)
E/flutter (15688): #31     ComponentElement.mount (package:flutter/src/widgets/framework.dart:3902:5)
E/flutter (15688): #32     ParentDataElement.mount (package:flutter/src/widgets/framework.dart:4279:11)
E/flutter (15688): #33     Element.inflateWidget (package:flutter/src/widgets/framework.dart:3084:14)
E/flutter (15688): #34     Element.updateChild (package:flutter/src/widgets/framework.dart:2887:12)
E/flutter (15688): #35     ComponentElement.performRebuild (package:flutter/src/widgets/framework.dart:3935:16)
E/flutter (15688): #36     Element.rebuild (package:flutter/src/widgets/framework.dart:3721:5)
E/flutter (15688): #37     ComponentElement._firstBuild (package:flutter/src/widgets/framework.

Does flutter support multiple databases?

Target Platform:Mac OS Mojave 10.14.5
Devise: Nexus 6P API 22(Emulator)

my flutter doctor
[✓] Flutter (Channel stable, v1.7.8+hotfix.4, on Mac OS X 10.14.5 18F132, locale ru-UA)

[✓] Android toolchain - develop for Android devices (Android SDK version 29.0.0)
[✓] Xcode - develop for iOS and macOS (Xcode 11.1)
[✓] iOS tools - develop for iOS devices
[✓] Android Studio (version 3.4)

[stuartmorgan-g]

sqflite is a third-party plugin, not part of the Flutter project. You'll need to file this in the sqflite issue tracker.

[AgutuSam]

I needed to create more than just multiple tables; but multiple database. I have since developed a dart package; sqlite_at_runtime that stretches to as far as creating all these entities at run time.

Below is an example code that creates three tables with similar attributes.

await Sqlartime.tableCreate(['sample1','sample2','sample3'],['name TEXT','age INTEGER','temp REAL']);

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. If you are still experiencing a similar issue, please open a new bug, including the output of flutter doctor -v and a minimal reproduction of the issue.