How to do bulk insertion in square sqldelight?

Question

Thanks square for SQLDelight and providing typesafe api's for sqlite db interactions. I am doing single insert row as below:

      MyTable.Insert_row insert_row =
    new MyTableModel.Insert_row(
      mOpenHelper.getWritableDatabase(),
      MyTable.FACTORY
    );
  MyTable.bind(insert_row, data);
  insert_row.program.executeInsert();

Is there a way to perform bulk insertion for list of data at once ?

score 3 · Accepted Answer · answered Nov 14 '16 at 18:25

Kind of. Theres two approaches. Since SQLite 3.7.11 (included in Android API 16 and above) you can insert multiple values at once from the sqlite side:

INSERT INTO myTable
VALUES (?, ?), (?, ?), (?, ?);

which is probably not what you want. As long as you are only creating the insert statement once, binding and executing multiple rows is actually really fast:

MyTable.Insert_row insert_row =
    new MyTableModel.Insert_row(
      mOpenHelper.getWritableDatabase(),
      MyTable.FACTORY
    );
for (Row row : rows) {
  MyTable.bind(insert_row, row);
  insert_row.program.executeInsert();
}

which is probably closer to what you want. At the moment there's no planned support of arbitrary-sized bulk inserts from the .sq file side.

Could you update this answer for a recent version of SQLDelight? I'm attempting something similar, but the performance is awful. What took seconds with Room, is taking longer than I care to wait. I'm sure it's an issue with my inserts. — Sean, Sep 23 '21 at 14:53

How to do bulk insertion in square sqldelight?

1 Answers1