6

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,252
  • 5
  • 18
  • 30
  • 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

17

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,252
  • 5
  • 18
  • 30