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:

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

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

see https://pub.dev/documentation/sqflite/latest/sqlite_api/DatabaseExecutor/batch.html — pskink, Sep 24 '19 at 07:07
So I should use `rawInsert`? If so then what would I pass it? (`Map` OR `List`) — Alena, Sep 24 '19 at 07:20
no, you can use "normal" `insert`, why would you want to use `rawInsert`? — pskink, Sep 24 '19 at 07:23
I was thinking that maybe `rawInsert` is to insert bulk data. — Alena, Sep 24 '19 at 15:00
thanks @pskink for your help, I was able to make it work by following an answer. — Alena, Sep 24 '19 at 15:01

score 12 · Accepted Answer · answered Sep 24 '19 at 10:43

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

Here's step by step guideline:

Get ready your json file e.g cities.json (create csv file of data and use csv to json converter like this)
Add cities.json file in your assets directory
Define it in pubspec.yaml like this:
```
assets:
 - assets/cities.json
```

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! :)

thank you so much for your answer, can you please look into [this](https://stackoverflow.com/questions/58074121/flutter-selected-value-doesnt-display-in-the-dropdown?noredirect=1#comment102544629_58074121)? — Alena, Sep 24 '19 at 14:58

chunhunghan · Answer 2 · 2019-09-24T07:43:37.923

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

any clue why I hitting this error ? https://stackoverflow.com/questions/66565018/get-issue-in-select-query — John Joe, Mar 11 '21 at 02:28

score 0 · Answer 3 · answered Sep 24 '19 at 07:20

0

You can write a raw query to insert all the data at once into the database.

answered Sep 24 '19 at 07:20

Kushagra Saxena

671
5
17

Flutter: Effective way of inserting list of data in database

3 Answers3