8

I am using Sqlite database in Flutter by using sqflite plugin. Following the below mentioned links I am able to successfully create database and perform CRUD operations in it.

Sqflite Plugin

Sqflite Tutorial - Youtube Playlist

The problem is, Once the database is created I cannot modify old tables. In order to get a new column in "tabEmployee" table, I must delete the database and recreate it.

void _onCreate(Database db, int newVersion) async {
    await db.execute(
        "CREATE TABLE tabEmployee($idPk INTEGER PRIMARY KEY, employeeName TEXT)");
  }

What should I do in order to modify previously created table in sqflite without deleting database ?

Zain SMJ
  • 1,492
  • 7
  • 19
  • 33
  • You can add a column to a table with [ALTER TABLE](https://www.sqlite.org/lang_altertable.html). – Shawn Dec 26 '18 at 08:00
  • 1
    make a similar function and call this function only once to alter the table, later you can remove this function `void alterTable() async { await db.execute("ALTER TABLE tabEmployee ADD employeePhone TEXT"); }` – dlohani Dec 26 '18 at 09:01
  • It's a nice hack. This would be my last approach though. There must be another way also. – Zain SMJ Dec 26 '18 at 09:44
  • Altering a table in a live application is an anti-pattern. What are you trying to accomplish? – Randal Schwartz Aug 18 '19 at 18:14

1 Answers1

21

Well I managed to resolve my issue. With some help from dlohani comment and How to add new Column to Android SQLite Database link.

I created a new method "_onUpgrade" and call it as parameter of "openDatabase" and changed the version number. Following is my relevant code:

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) {
      db.execute("ALTER TABLE tabEmployee ADD COLUMN newCol TEXT;");
    }
  }
Zain SMJ
  • 1,492
  • 7
  • 19
  • 33
  • what does ALERT do in a query ? – Mohammad Seyedmahmudi Apr 05 '20 at 10:20
  • It's "ALTER" not "ALERT". This command is used to add, delete or modify columns in an existing table. – Zain SMJ Apr 18 '20 at 12:03
  • 1
    @ZainSMJ it needs to add `newCol TEXT` column when you are `CREATE TABLE`. right? Otherwise new user's table doesn't include `newCol` column right? – BIS Tech Feb 10 '21 at 11:51
  • @ZainSMJ The condition `if (newVersion > oldVersion)` is always `true` and could cause problems if you use multiple migrations. You should use exact version checks like `if (oldVersion < 2)` Example from the docs: https://github.com/tekartik/sqflite/blob/master/sqflite/doc/migration_example.md – Robin Nov 09 '22 at 11:24
  • 1
    Yes @RobDev back then I was doing that way lol. But now as you pointed out the right approach for several migrations we must use exact version checks. – Zain SMJ Nov 10 '22 at 15:34