Inserting records on sqflite database creation

Question

I'm trying to create Sqflite database in android, and insert upon creation some records in it. I don't want to have an asset_db, because I have to insert only 5-6 records in one table.

I tried to do something like this

initDatabase() async {
  String path = join(await getDatabasesPath(), DATABASE_NAME);

  print('Init Database');

  return await openDatabase(
    path,
    version: DATABASE_VERSION,
    onOpen: (db) async {
      print("Opened Database $DATABASE_NAME");
    },
    onCreate: _onCreate,
  );
}

_onCreate(Database db, int version) async {
 print("Creating new database with version $version");

  await db.execute("CREATE TABLE IF NOT EXISTS categories ("
      "id INTEGER PRIMARY KEY,"
      "name VARCHAR(50) NOT NULL,"
      ")");

  await _createDefaultCategories();
}

In _createDefaultCategories I do a couple of inserts using db.

The problem I have is that when I use the database and the init is called I got into an infinite loop printing forever:

I/flutter (10003): Creating new database with version 1
I/flutter (10003): Init Database
I/flutter (10003): Creating new database with version 1
I/flutter (10003): Init Database
I/flutter (10003): Creating new database with version 1
I/flutter (10003): Init Database
I/flutter (10003): Creating new database with version 1

What am I doing wrong ?

LE: The _createDefaultCategories is something like that:

_createDefaultCategories() async {
    var database = await DBHelper.instance.database;

    await database.insert('Category', Category(id: 1).toMap());
}

And the helper where I'm initializing the database is:

class DBHelper {
    DBHelper._();

    static final DBHelper instance = DBHelper._();

    static Database _database;

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

        _database = await initDatabase();

        return _database;
    }

    initDatabase() { 
      ...above code...
    }
}

Can you share your code for _createDefaultCategories? It looks like you're calling initDatabase from _createDefaultCategories? — Tom Alabaster
– Tom Alabaster, Commented Oct 21, 2019 at 22:04

Tom Alabaster · Accepted Answer · 2019-10-23 14:48:44Z

4

This issue looks like while you're creating the database, you're trying to use your database object which hasn't actually finished creating.

Instead of having your _createDefaultCategories method get the database instance, have it passed in as a parameter instead like this:

Future<void> _createDefaultCategories(Database database) async {
  await database.insert('Category', Category(id: 1).toMap());
}

And call this from your _onCreate method like so:

await _createDefaultCategories(db);

Further to this, your database property is slightly wrong. It should have an async keyword before the body.

Here's a complete example of what you're likely trying to achieve:

class DBHelper {
  DBHelper._();

  static final DBHelper instance = DBHelper._();

  static Database _database;

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

    _database = await initDatabase();

    return _database;
  }

  initDatabase() async { 
    String path = join(await getDatabasesPath(), DATABASE_NAME);

    print('Init Database');

    return await openDatabase(
      path,
      version: DATABASE_VERSION,
      onOpen: (db) async {
        print("Opened Database $DATABASE_NAME");
      },
      onCreate: _onCreate,
    );
  }

  _onCreate(Database db, int version) async {
    print("Creating new database with version $version");

    // this could also be part of some kind of seed method
    await db.execute("CREATE TABLE IF NOT EXISTS categories ("
        "id INTEGER PRIMARY KEY,"
        "name VARCHAR(50) NOT NULL,"
        ")");

    await _createDefaultCategories(db);
  }

  Future<void> _createDefaultCategories(Database database) async {
    await database.insert('Category', Category(id: 1).toMap());
  }
}

answered Oct 23, 2019 at 14:48

Tom Alabaster

9856 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Razvan Curcudel Over a year ago

The async missing from getter was a mistake. It was in my code, I just forgot to put it here. I tested your suggestion, it seems to work somehow. But, I wanted to have ,for creating new records, a Repository class which will have an insert static function that will get the database from helper (await DatabaseHelper.instance.database) and then do the insert (await database.insert). So, I don't want to send the database sent as parameter to this class.

Jaczura Zoltán Over a year ago

"I don't want to send the database sent as parameter to this class". That is my main problem, to. Future _onCreate(Database db, int version) async { var batch = db.batch(); batch.execute('"CREATE TABLE1 IF NOT EXISTS ....'); batch.execute('"CREATE TABLE2 IF NOT EXISTS ....'); await batch.commit(); print("Tables created"); await seedDb(); } I using batch, that runs correctly, and seedDb is next, but it calls create table section again. Why? They are awaitable and stepping the code the execution runs in correct order.

Tom Alabaster Over a year ago

Why do you both not want to have to send the db to the method? Your initDatabase method hasn't finished executing the openDatabase function yet, because it is still executing onCreate. It won't return a database object until onCreate has finished, but because onCreate is calling a getter which is calling initDatabase again because the previous call didn't finish and assign, you get the infinite loop.

VincentH Over a year ago

Thanks Tom. I was having exactly the same issue. I was trying to populate the database with some data once it was created, but it was going in an infinite loop, with the onCreate being called over and over. Came across your post and implemented your recommended changes. Worked perfectly, thank you - great solution.

Collectives™ on Stack Overflow

Inserting records on sqflite database creation

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related