How do I batch sql statements with package database/sql

Question

How do I batch sql statements with Go's database/sql package?

In Java I would do it like this :

// Create a prepared statement String sql = "INSERT INTO my_table VALUES(?)"; PreparedStatement pstmt = connection.prepareStatement(sql);  // Insert 10 rows of data for (int i=0; i<10; i++) {     pstmt.setString(1, ""+i);     pstmt.addBatch(); }  // Execute the batch int [] updateCounts = pstmt.executeBatch(); 

How would I achieve the same in Go?

Answer 1

Since the db.Exec function is variadic, one option (that actually does only make a single network roundtrip) is to construct the statement yourself and explode the arguments and pass them in.

Sample code:

func BulkInsert(unsavedRows []*ExampleRowStruct) error {     valueStrings := make([]string, 0, len(unsavedRows))     valueArgs := make([]interface{}, 0, len(unsavedRows) * 3)     for _, post := range unsavedRows {         valueStrings = append(valueStrings, "(?, ?, ?)")         valueArgs = append(valueArgs, post.Column1)         valueArgs = append(valueArgs, post.Column2)         valueArgs = append(valueArgs, post.Column3)     }     stmt := fmt.Sprintf("INSERT INTO my_sample_table (column1, column2, column3) VALUES %s",                          strings.Join(valueStrings, ","))     _, err := db.Exec(stmt, valueArgs...)     return err } 

In a simple test I ran, this solution is about 4 times faster at inserting 10,000 rows than the Begin, Prepare, Commit presented in the other answer - though the actual improvement will depend a lot on your individual setup, network latencies, etc.

Answer 2

If you’re using PostgreSQL then pq supports bulk imports.