How to update version database SQFLite flutter?

Question

I need to create new tables and delete others in my local database, however changing only the database version, it does not work it is not creating the new tables that I need.

In the same way I already tried the onUpgrade but it did not work

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

  }


  initDB()async {
    Directory documentDirectory = await getApplicationDocumentsDirectory();
    String path = join( documentDirectory.path, 'DB.db' );
     var ouDb =  await openDatabase(path, version: 2,onCreate: onCreate, onUpgrade: onUpgrade);
     return ouDb;
    }

  FutureOr<void> onCreate(Database db, int version) async {

      await db.execute('CREATE TABLE nameTable0...');



      db.execute("DROP TABLE IF EXISTS NameTable1");
      db.execute("DROP TABLE IF EXISTS NameTable2");



  }

  FutureOr<void> onUpgrade(Database db, int oldVersion, int newVersion) async{
      if (oldVersion < newVersion) {
        await db.execute('CREATE TABLE NameTable3 ...');
      }
      //onCreate(db, newVersion); 
  }

Ostensible

Answer 1

I'm seeing a lot of answers that explain to perform the following check in onUpgrade in openDatabase:

 if (oldVersion < newVersion) {
      await db.execute('CREATE TABLE CUSTOMER....');
 }

This will work when performing your first database upgrade (ie version 1 to 2), but will fail when executing your second (say 2 to 3) since the table 'CUSTOMER' will already have been created. So you need logic to only call upgrades one time. In addition, you may be on version 10 of your database and the user is on version 5, so you need to handle only upgrades of 6,7,8,9 and 10. Below is the the logic I built to handle these scenarios:

  static Future<Database?> getDatabase() async {

    if (_dbInstance == null) {
      var databasesPath = await getDatabasesPath();
      String path = join(databasesPath, _databaseName);

      _dbInstance = await openDatabase(path, version: _databaseVersion,
          
      onUpgrade: (Database db, int oldVersion, int newVersion) async {
        //
        //Iterate from the current version to the latest version and execute SQL statements
        for (int version = oldVersion; version < newVersion; version++) {
          await _performDBUpgrade(db, version + 1);
        }
      }, 

      onCreate: (Database db, int newVersion) async {
        //
        //Start from version 1 to current version and create DB
        for (int version = 0; version < newVersion; version++) {
          await _performDBUpgrade(db, version + 1);
        }
      });
    }

    return _dbInstance;
  }

  //Call to upgrade the database. [upgradeToVersion] is the version of SQL statements that should be
  //executed.  A version of 1 is the initial creation of the database.   Anything higher would
  //be an upgrade of the database.  This function should be called once for every version upgrade.
  //For example, if current version is 1 and user is now performing an update and new version is
  //5, then this function should be called 4 times (from `onUpgrade`), where [upgradeToVersion] would be passed a 2, 3, 4 and 5.
  
  static Future<void> _performDBUpgrade(Database db, int upgradeToVersion) async {
    switch (upgradeToVersion) {
      //Upgrade to V1 (initial creation)
      case 1:
        await _dbUpdatesVersion_1(db);
        break;

      //Upgrades for V2
      case 2:
        //await _dbUpdatesVersion_2(db);
        break;
    }
  }

  ///Database updates for Version 1 (initial creation)
  static Future<void> _dbUpdatesVersion_1(Database db) async {
      await db.execute('CREATE TABLE Customer...)');
  }
 

Potty

This is the correct/best answer. Real-world of mobile apps is that user could be running any previous version of your app, and jumping directly to new version. Your update code has to always be able to handle update-from-X to current where X is any previously released version of the app. Code you posted is very similar to what I arrived at a couple years ago in iOS work. Kudos. This allows me to transfer this to new flutter app without having translate from my Swift code. – Katz

I almost make the mistake of writing if (oldVersion == 3 && newVersion == 4) { }. I forgot that in real life scenario, there's no guarantee that my user is installing version 4 on top version 3. Even with Play Store's auto update, there's still a possibility that they jump from version 1 to 4. – Oxime

Answer 2

You need to use onUpgrade

initDb() async {
    Directory documentDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentDirectory.path, 'maindb.db');
    var ourDb = await openDatabase(path, version: 2, onCreate: _onCreate, onUpgrade: _onUpgrade);
    return ourDb;
  }

  // UPGRADE DATABASE TABLES
  void _onUpgrade(Database db, int oldVersion, int newVersion) {
    if (oldVersion < newVersion) {
      // you can execute drop table and create table
      db.execute("ALTER TABLE tb_name ADD COLUMN newCol TEXT;");
    }
  }

Chasseur

I had already tried this, and I just tried again but it does not detect the change @Sanjay Sharma – That

What do you mean by "it does not detect the change"? – Chasseur

I put a breakpoint and it does not go beyond this line Directory documentDirectory = await getApplicationDocumentsDirectory(); because it no longer reaches the line where the onUpgrade I don't know if why on this line ´if (_database! = null) return _database;´ It validates that if there is already a bd it no longer executes the initDB, I am new to flutter, so I supported myself in a course that I am seeing and there it only said that with increasing the version number it would detect the changes in the bd @Sanjay Sharma – That

Can you post the modified code which you are using ? – Chasseur

Try to change the version to 3 and run it. I guess it's already updated by the time you wrote onUpgrade – Chasseur

it did not work for me, and even with a device with which it is installed for the first time it does not work. @Sanjay Sharma :´( – That

Onupgrade won't be called when the app is installed for the first time. The table models should be updated and as per Onupgrade – Chasseur

And then what happens if in the future they ask you to add models or even for users who are going to install the app for the first time? @Sanjay Sharma – That

you need to update it as per your latest table schema. So it should be always updated and for the old app you can have onUpgrade – Chasseur

For this to work, you have to update the version number too. – Gripper

Answer 3

I think it will help you...

Future<Database> openDatabase(String path,
    int version,
    {OnDatabaseConfigureFn? onConfigure,
      OnDatabaseCreateFn? onCreate,
      OnDatabaseVersionChangeFn? onUpgrade,
      OnDatabaseVersionChangeFn? onDowngrade,
      OnDatabaseOpenFn? onOpen,
      bool readOnly = false,
      bool singleInstance = true}) {
  final options = OpenDatabaseOptions(
      version: 4,
      onConfigure: onConfigure,
      onCreate: _createDb,
      onUpgrade: _upgradeDb,
      onDowngrade: onDowngrade,
      onOpen: onOpen,
      readOnly: readOnly,
      singleInstance: singleInstance);
  return databaseFactory.openDatabase(path, options: options);
}

Call it any where in your code.

Kati

Answer 4

Try adding Future as return type and the remove the semicolon from the query. This code works for me.

// UPGRADE DATABASE TABLES
Future _onUpgrade(Database db, int oldVersion, int newVersion) async {  
  if (oldVersion < newVersion) {
    // you can execute drop table and create table
    db.execute("ALTER TABLE tb_name ADD COLUMN newCol TEXT");
  }
}

Larrabee