Flutter: Effective way of inserting list of data in database

Question

I have cities table and trying to insert city, upon database creation. The table structure is pretty simple, it has just id and name column.

In onCreate method of my database class, I create table with this command:

var tblCities = 'cities';
await db.execute('CREATE TABLE $tblCities (id INTEGER PRIMARY KEY, name TEXT)');

I have Cities model class with fromMap and toMap methods.

There are about 350 cities, and I wanted to insert them in the table.

Q. What is the best and easy way to do that?

This comes in my mind:

1. creating list of city
2. using for loop to iterate entire list
3. creating map of the city using toMap method
4. calling db.insert method inside the loop

I'm not sure, but this seem dumb approach so thinking about better and optimized solution...

Solution 1

As mentioned by @chunhunghan, you can use batch to insert bulk data.

Here's step by step guideline:

1. Get ready your json file e.g cities.json (create csv file of data and use csv to json converter like this)
2. Add cities.json file in your assets directory

3. Define it in pubspec.yaml like this:

   assets:
    - assets/cities.json
   

4. Paste this code inside onCreate method of your database class (make sure its after table creation query)

   Batch batch = db.batch();
   
   String citiesJson = await rootBundle.loadString('assets/json/cities.json');
   List citiesList = json.decode(citiesJson);
   
   
   citiesList.forEach((val) {
     //assuming you have 'Cities' class defined
     Cities city = Cities.fromMap(val);
     batch.insert(tblCities, city.toMap());
   });
   
   batch.commit();
   

That's it! :)

Solution 2

There is Batch support
To avoid ping-pong between dart and native code, you can use Batch:

batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
results = await batch.commit();

official example https://github.com/tekartik/sqflite/blob/master/sqflite/example/lib/batch_test_page.dart

In your case, for loop list with batch.insert command, it's easier to maintain
for simplicity syntax, use toMap, example

batch.insert("cities", city.toMap());   

detail https://www.techiediaries.com/flutter-sqlite-crud-tutorial/

If you prefer rawInsert, please reference Insert multiple records in Sqflite