CODEWITHSUNDEEP
fluttersqflite
Mrunal Joshi
Mrunal Joshi

Reputation: 377

Check if table exists in flutter

I have implemented code to check if database exists in Database Helper class. This is my DatabaseHelper class.

static final DatabaseHelper _instance = new DatabaseHelper.internal();
  DatabaseHelper.internal();
  factory DatabaseHelper() => _instance;

  static Database _db;

  static final table = 'User';

  Future checkDb() async{
     Directory documentDirectory = await getApplicationDocumentsDirectory();
      //var db = await getDatabasesPath();
      var dbPath = join(documentDirectory.path,"main.db");
      bool exist = await databaseExists(dbPath);
      return exist;
  }

  

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

  initdb() async{
    Directory documentDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentDirectory.path,"main.db");
    var ourDb = await openDatabase(path,version:1,onCreate:_onCreate);
    return ourDb;

  }

  void _onCreate(Database db,int version)async {
    await db.execute("CREATE TABLE User("
          "userId INTEGER PRIMARY KEY,"
          "first_name TEXT,"
          "last_name TEXT,"
          "user_password TEXT,"
          
          ")");
    print("User Table created");
  }



  void tableIsEmpty()async{
     var db = await openDatabase('main.db');
     
      int count = Sqflite
         .firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM User'));
      
     print(count);
    
  }

The Database gets created successfully and db.checkdb() returns true when i call

var db = new DatabaseHelper();

But when i call db.tableIsEmpty() i get an error "no such table:User" even after OnCreate is being executed?

How can i solve the problem or how can i check if table exists in db?

Upvotes: 2

Views: 7379

Answers (4)

Mahmoud Kaddour
Mahmoud Kaddour

Reputation: 11

Try the following code:

String checkExistTable =
    "SELECT * FROM sqlite_master WHERE name ='$TableName' and type='table'";
var checkExist = await db.rawQuery(checkExistTable);

if (checkExist.isNotEmpty) {
  // table exist
} else {
 //Table isn't exist
}

Upvotes: 0

Code on the Rocks
Code on the Rocks

Reputation: 17586

You can also do this using a raw query on the sqlite_master table:

var tables = await db.rawQuery('SELECT * FROM sqlite_master WHERE name="TABLE_NAME";');

If the table exists, you'll see output like this:

[{type: table, name: Words, tbl_name: Words, rootpage: 4, sql: CREATE TABLE Words (id INTEGER PRIMARY KEY, word TEXT, description TEXT)}]

Here's a quick snippet to show how you can use the output of this query:

var tables = await db.rawQuery('SELECT * FROM sqlite_master WHERE name="Words";');

 if (tables.isEmpty) {
    // Create the table
  } else{
    // Do Nothing
  }

Upvotes: 3

oblomov
oblomov

Reputation: 544

You can check if sqflite table exists by querying sqlite_master

db.query('sqlite_master', where: 'name = ?', whereArgs: [table]);

Then create the table if it doesn't exist

static const table = 'User';
static const version = 1;

initdb() async{
    Directory documentDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentDirectory.path, "main.db");
    var ourDb = await openDatabase(path, version: version, onCreate:_onCreate);
    if (await db.query('sqlite_master', where: 'name = ?', whereArgs: [table]) == []) {
      _onCreate (ourDb, version);
    }
    return ourDb;
  }

Source: https://github.com/tekartik/sqflite/issues/251

Upvotes: 4

chunhunghan
chunhunghan

Reputation: 54367

Because openDatabase need full path
And in your code you have defined

Future<Database> get db

So please change from 

var db = await openDatabase('main.db');

to

var db = await db;

code snippet

void tableIsEmpty()async{
    var db = await db;
     
    int count = Sqflite
         .firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM User'));
      
     print(count);    
}

Upvotes: 3

Related Questions