How to create multiple table in Sqlite database at different times in Flutter

Question

So Initially I created a table named "TABLE" in the database. I wanted to add another table. So I added another query to create a table. However I get an error saying "TABLE1 does not exist" when I run the app.

I do feel like there is a flaw in my code. I think the _onCreate() method will only be called the first time I run the app. So any code I add on _onCreate() method afterwards will not run. Any help will be appreciated.

     class DBHelper {
       static Database _db;
       static const String DB_NAME = 'employeeDB';

       static const String ID = 'id';
       static const String NAME = 'name';
       static const String TABLE = 'Employee';

       static const String ID1 = 'id1';
      static const String NAME1 = 'name1';
     static const String TABLE1 = 'Employee1';


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

 initDb() async {
 io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, DB_NAME);
var db = await openDatabase(path, version: 1, onCreate: _onCreate);
return db;
  }

 _onCreate(Database db, int version) async {
 await db.execute("CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY,$NAME TEXT)");
await db.execute("CREATE TABLE $TABLE1 ($ID1 INTEGER PRIMARY KEY,$NAME1 TEXT)");

  }

   Future<Employee> save(Employee employee) async {
var dbClient = await db;
employee.id = await dbClient.insert(TABLE, employee.toMap());
return employee;

  }

  Future<Employee1> saveEmp1(Employee1 employee) async {
var dbClient = await db;
employee.id = await dbClient.insert(TABLE1, employee.toMap());
return employee;

  }


   Future<List<Employee>> getEmployees() async {
var dbClient = await db;
List<Map> maps = await dbClient.query(TABLE, columns: [ID, NAME]);


List<Employee> employees = [];
if (maps.length > 0) {
  for (int i = 0; i < maps.length; i++) {
    employees.add(Employee.fromMap(maps[i]));

  }
  }


return employees;
}


Future<List<Employee1>> getEmployees1() async {
var dbClient = await db;
List<Map> maps = await dbClient.query(TABLE1, columns: [ID1, 
 NAME1]);


List<Employee1> employees = [];
if (maps.length > 0) {
  for (int i = 0; i < maps.length; i++) {
    employees.add(Employee.fromMap(maps[i]));

  }
}
return employees;
   }

  }

Solution 1

The first time run this app and initDb() in emulator, the db file employeeDB has created.
and it will not be created again

For only test app execution, you can change

String DB_NAME = 'employeeDB'

to another name

String DB_NAME = 'employeeDB1'

or you can uninstall this app from Emulator first then run it again.

source code snippet of https://github.com/tekartik/sqflite/blob/master/sqflite/lib/sqlite_api.dart

/// Called when the database is created.
  OnDatabaseCreateFn onCreate;

For schema migration, you can use OnUpgrade, detail reference https://efthymis.com/migrating-a-mobile-database-in-flutter-sqlite/

code snippet

await openDatabase(path,
  version: 1,
  onCreate: (Database db, int version) async {
    await db.execute(initialSchema));
  },
  onUpgrade: (Database db, int oldVersion, int newVersion) async {
    await db.execute(migrationScript));
  });

Solution 2

You have to create migrations and run them against your existing database using the onUpgrade handler. Basically you need to check the existing database version and upgrade if the version is smaller than the migration number.

You can check out the detail steps/code tutorial here.