Insert sqlite flutter without freezing the interface

Question

I'm trying to insert a lot of rows (about 12k or more) in a sqlite memory database using flutter.

I get data from the API and use a compute function in order to process data from Json. Now I need to add these data to a database in memory, in order to do so I use a transaction with a batch.

batchInsertEventSong(List<EventSong> rows) async {
   Database db = await instance.database;
   db.transaction((txn) async {
      Batch batch = txn.batch();
      for (var song in rows) {
         Map<String, dynamic> row = {
           DatabaseHelper.columnIdSong: song.id,
           DatabaseHelper.columnTitle: song.title,
           DatabaseHelper.columnInterpreter: song.interpreter
         };
         batch.insert(table, row);
       }
      batch.commit();
   }
}

But this function is blocking my UI during insertions, I tried also with compute but I can't pass the class db or the class batch. I hadn't clear how to execute this process in another thread or (since I can't use isolates) executing without blocking my UI.

Any advice?

The for is taking about 2 seconds, the commit about 4 seconds. With add to `batch` in smaller chunks what do you mean? executing `batch.commit();` not at the end but after some cycles? — PsyKoWebMari, May 28 '19 at 15:51
I tried adding a `if(i % 1000 == 0) batch.commit(noResult: true);` so it commit rows every 1000 items and at the end of the loop. But this keeps blocking my UI for now 12 seconds more or less... Is there in flutter a sleep or processEvents (like in Qt) to let the main thread a little air to execute? — PsyKoWebMari, May 28 '19 at 16:24

score 9 · Accepted Answer · edited Jun 20 '20 at 09:12

Update 2020 - 05 - 15

this will not work see :

Unable to call a platform channel method from another isolate · Issue #13937 · flutter/flutter

Note : full code available on the end

Step 1 : make your method static and make it void

  static batchInsertEventSong(List<EventSong> rows) {
    Database db = await instance.database;
    db.transaction((txn) async {
        Batch batch = txn.batch();
        for (var song in rows) {
            Map<String, dynamic> row = {
            DatabaseHelper.columnIdSong: song.id,
            DatabaseHelper.columnTitle: song.title,
            DatabaseHelper.columnInterpreter: song.interpreter
            };
            batch.insert(table, row);
        }
        batch.commit();
    }
  }

Step 2 : create new method (usually but not required add Async for the same name)

  Future batchInsertEventSongAsync(List<EventSong> rows) {

  }

Step 3 : call compute with your method static and return

  return compute(batchInsertEventSong, rows);

Step [1,2,3] code review

  Future batchInsertEventSongAsync(List<EventSong> rows) {
    return compute(_batchInsertEventSong, rows);
  }

  static _batchInsertEventSong(List<EventSong> rows) {
    Database db = await instance.database;
    db.transaction((txn) async {
        Batch batch = txn.batch();
        for (var song in rows) {
            Map<String, dynamic> row = {
            DatabaseHelper.columnIdSong: song.id,
            DatabaseHelper.columnTitle: song.title,
            DatabaseHelper.columnInterpreter: song.interpreter
            };
            batch.insert(table, row);
        }
        batch.commit();
    }
  }

Full Code

import 'dart:async';
import 'package:flutter/foundation.dart';
import 'package:flutter/material.dart';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: Scaffold(body: MyHomePage(title: 'Flutter Demo Home Page')),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            FlatButton.icon(
              icon: Icon(Icons.backup),
              label: Text("Long Opreation"),
              onPressed: () async {
                var rows = await RsetApi.getRawsAsync();
                await Database._saveRaws(rows);
              },
            ),
            FlatButton.icon(
              icon: Icon(Icons.backup),
              label: Text("Short Opreation"),
              onPressed: () {
                Scaffold.of(context).hideCurrentSnackBar();
                Scaffold.of(context).showSnackBar(new SnackBar(
                  content: new Text(DateTime.now().toIso8601String()),
                ));
              },
            ),
          ],
        ),
      ),
    );
  }
}

class RsetApi {
  static Future<List<EventSong>> getRawsAsync() {
    return compute(_getRaws, null);
  }

  static List<EventSong> _getRaws(pram1) {
    var rows = List<EventSong>();
    for (var i = 1; i < 12000; i++) {
      rows.add(EventSong(i));
      print("fetching raws " + (i / 12000).toString());
    }
    return rows;
  }
}

class Database {
  static Future saveRawsAsync(List<EventSong> rows) {
    return compute(_saveRaws, rows);
  }

  static _saveRaws(List<EventSong> rows) {
    for (var i = 1; i < rows.length; i++) {
      print("saving raws " + (i / rows.length).toString());
    }
  }
}

class EventSong {
  int id;
  EventSong(this.id);
}

Ref:

Parse JSON in the background - Flutter

non-important Ref:

I thought that PlatformChannels (`sqflite` `insert`) don't work with isolates (`compute`). Does this example work? — Martyns, Jul 22 '19 at 07:00
@Martyns do you have the a refrence for this fact (a url), so i can check it later , it will help me alot — Mohamed Elrashid, Jul 22 '19 at 07:06
There are a couple issues on GH, this is the main one https://github.com/flutter/flutter/issues/13937 — Martyns, Jul 22 '19 at 07:11
@MohamedElrashid Thanks for the answer, I don't know why yours works, but I tried the same way and still get this error `error: native function 'Window_sendPlatformMessage' (4 arguments) cannot be found`. I think it is because `compute` still does not support flutter plugins — Jama Mohamed, Jul 22 '19 at 08:07
Can you post your full code with the batchInsertEventSong in compute call ? I can't get this example to work. — live-love, May 15 '20 at 19:17
@live-love this will not work see : [Unable to call a platform channel method from another isolate · Issue #13937 · flutter/flutter](https://github.com/flutter/flutter/issues/13937) — Mohamed Elrashid, May 15 '20 at 19:32
@jama-mohamed this will not work see : [Unable to call a platform channel method from another isolate · Issue #13937 · flutter/flutter](https://github.com/flutter/flutter/issues/13937) — Mohamed Elrashid, May 15 '20 at 19:32