How to insert metadata records automatically in SQlite (Flutter App), just after the DB initialize & table creation

Question

We are developing a Flutter APP with a SQLite Database. Our requirement is to insert metadata records automatically at the time of Database creation / after table initialization. Metadata should be inserted automatically only once in the App lifecycle. We don't have these records in query format, but they will be read from JSON files & inserted into the SQLite database tables.

To achieve this functionality, we have added the method call at the end of initDb ( ) just after the table initialization, but then table initialization does not happen. When we load few pages, the getSetting( )  get started in an infinite loop. After every few seconds, getSetting() gets called, but no table is created & no records are inserted.

Even after adding Try catch, no error logs were found. Interestingly, the below code executes in a loop & does not move forward. SettingModel? setting = await getSetting("metaDataLoaded");

The insertInitialMetaData() works perfectly when triggered through a button.

Can somebody give me some guidance on from where to trigger insertInitialMetaData () and what the problem is with the current implementation? Also please sugget correct approch for this problem.(

Note : Metadat is a data which is required to execute the Application logic & it can not be added by the user or by any event like button click.

Below is our DbHelper.dart code

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

class DbHelper {
  static Database? _db;


  Future<Database> get db async {
    if (_db != null) {
      return _db!;
    }
    _db = await initDb();
    return _db!;
  }

  Future<Database> initDb() async {
    final path = join(await getDatabasesPath(), DB_Name);
    return await openDatabase(
      path,
      version: Version,
      onCreate: (db, version) async {
        try {
          
          //Create other tables

          // Create AppSetting table
          db.execute(
            '''
          CREATE TABLE $Table_AppSettings (
            $C_settingId INTEGER PRIMARY KEY AUTOINCREMENT,          
            $C_key TEXT,
            $C_value TEXT,
            $C_profile TEXT,
            $C_isActive INTEGER NOT NULL DEFAULT 1 ,                 
            $C_createdDate TEXT                 
          )
          ''',
          );
         
          try { // check if the settings metadata preent in the DB
            SettingModel? setting = await getSetting("metaDataLoaded");
            print('Setting response received.');
            if (setting != null) {
              //metadata is already exist             
              print('Setting found: ${setting.key} - ${setting.value}');

            } else { // metadata not present 
               print('Initializing metadata..');
**              _handleInsertInitialMetaData(); //method to insert metadata records
**            }
          }catch (ex) {
            print('Error while pulling Settings : $ex');
          }
        } catch (e) {
          print('Error creating tables: $e');
        }
      },
    );
  }

  
  //return only the required setting
  Future<SettingModel?> getSetting(String key) async {  
    try{
    final dbClient = await db;
    final List<Map<String, dynamic>> result = await dbClient.query(
        Table_AppSettings,
        where: 'isActive = ? AND key = ? AND isActive IS NOT NULL',
        whereArgs: [1,key]);
    return result.isNotEmpty ? SettingModel.fromMap(result.first) : null;
    }catch(e){
      print("Error while Getting metadata setting : $e");
      rethrow;
    }
  }

  void _handleInsertInitialMetaData() async {
    final dbClient = await db;
      await insertInitialMetaData(dbClient);
  }


  Future<void> insertInitialMetaData(Database db) async {
    print("Initializing the metaData loading from Json file ..");
    try {
     
      //Read JSON data from settings file
      String settingsMetaFilePath =
          'assets/metadataFiles/settings_initialization.json';
      String settingsMetaJsonString =
      await rootBundle.loadString(settingsMetaFilePath);
      print("Load : settingsMetaJsonString");
      var settingsJsonList = json.decode(settingsMetaJsonString) as List<dynamic>;
       int j = 0;
   
    
      // Iterate through the JSON list and insert each setting into the settings table
      for (var jsonData in settingsJsonList) {       
        await saveSettingData(SettingModel.fromJson(jsonData));
      }
      print("Settings records saved.");
    } catch (e) {
      print("Error while initialization of data from JSON: $e");
      rethrow;
    }
  }

  
  Future<void> closeDb() async {
    if (_db != null) {
      await _db!.close();
      _db = null;
    }
  }
}

**

Debug logs with infinite loop :**

I/flutter (12688): Getting metadata setting I/chatty (12688): uid=10125(com.example.app) 1.ui identical 285 lines I/flutter (12688): Getting metadata setting I/flutter (12688): Getting metadata setting I/chatty (12688): uid=10125(com.example.app) 1.ui identical 69 lines I/flutter (12688): Getting metadata setting I/flutter (12688): Getting metadata setting I/chatty (12688): uid=10125(com.example.app) 1.ui identical 579 lines I/flutter (12688): Getting metadata setting I/mple.sheti_nex(12688): Background concurrent copying GC freed 36008(2196KB) AllocSpace objects, 0(0B) LOS objects, 49% free, 2342KB/4685KB, paused 5.076ms total 72.742ms

I/flutter (12688): Getting metadata setting

I/chatty (12688): uid=10125(com.example.app) 1.ui identical 363 lines I/flutter (12688): Getting metadata setting

I/mple.sheti_nex(12688): IncrementDisableThreadFlip blocked for 6.051ms

I/mple.sheti_nex(12688): Background concurrent copying GC freed 41559(2492KB) AllocSpace objects, 0(0B) LOS objects, 49% free, 2243KB/4487KB, paused 5.402ms total 35.193ms

I/flutter (12688): Getting metadata setting

Answer 1

I found a related answer on stack page, I followed the steps & issue resolved. The main problem was - while creating the database, we were trying to use database object which hasn't actually finished creating.

https://stackoverflow.com/a/58525549/8232579