The best way to effectively do exec many?

[thedmdim]

Hi! Could you advice me the best way to INSERT multiple rows?

1. I could firstly compose INSERT INTO table (col1, col2) VALUES (?, ?), (?, ?), (?, ?) ... and so many (?, ?) as I have rows to write. Then Execute
2. Either I could do sqlitex.Execute(conn, "INSERT INTO table (col1, col2) VALUES (?, ?);", ...) in a loop, for all rows I need to persist

( I use sqlitex.Open())

[zombiezen]

Things to keep in mind (not specific to this package, but applicable to SQLite in general):

• Building SQL statements programmatically (as in the first option) is usually inconvenient, but can work. It usually does not permit good caching with Conn.Prep, for example.
• If you execute statements that modify the database outside a transaction (as in the second option), it will incur a synchronous disk write for each statement, since it executes each in a separate transaction. For many such statements, you should bundle them up in a single transaction for better efficiency.
• Preparing a SQLite statement and executing it with different parameters skips the overhead of re-parsing and re-planning the query each time.

Putting this all together, the sort of code I would recommend is something like this:

func myFunc(conn *sqlite.Conn) (err error) {
  endFn, err := sqlitex.ImmediateTransaction(conn)
  if err != nil {
    return err
  }
  defer endFn(&err)
  stmt := conn.Prep("INSERT INTO table (col1, col2) VALUES (?, ?);")
  for _, row := range mySlice {
    stmt.BindText(1, row.col1)
    stmt.BindText(2, row.col2)
    if err := stmt.Step(); err != nil {
      return err
    }
    if err := stmt.Reset(); err != nil {
      return err
    }
  }
  return nil
}

[thedmdim]

Thanks <3