go 42 lines · 7 steps

Batch inserting rows in a Go transaction

How to insert many rows atomically by chunking them into multi-row INSERTs inside one transaction.

Explained by highlit
Intermediate transactions batching database error-handling sql
1func (r *EventRepository) BatchInsert(ctx context.Context, events []Event) error {
2 if len(events) == 0 {
3 return nil
4 }
5 
6 tx, err := r.db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
7 if err != nil {
8 return fmt.Errorf("begin tx: %w", err)
9 }
10 defer tx.Rollback()
11 
12 const batchSize = 500
13 for start := 0; start < len(events); start += batchSize {
14 end := start + batchSize
15 if end > len(events) {
16 end = len(events)
17 }
18 chunk := events[start:end]
19 
20 values := make([]string, 0, len(chunk))
21 args := make([]interface{}, 0, len(chunk)*4)
22 for i, e := range chunk {
23 n := i * 4
24 values = append(values, fmt.Sprintf("($%d, $%d, $%d, $%d)", n+1, n+2, n+3, n+4))
25 args = append(args, e.AggregateID, e.Type, e.Payload, e.OccurredAt)
26 }
27 
28 query := fmt.Sprintf(
29 "INSERT INTO events (aggregate_id, type, payload, occurred_at) VALUES %s",
30 strings.Join(values, ", "),
31 )
32 
33 if _, err := tx.ExecContext(ctx, query, args...); err != nil {
34 return fmt.Errorf("insert batch [%d:%d]: %w", start, end, err)
35 }
36 }
37 
38 if err := tx.Commit(); err != nil {
39 return fmt.Errorf("commit tx: %w", err)
40 }
41 return nil
42}
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Chunking large inserts caps how many parameters land in any single query, avoiding placeholder and packet limits.
  2. 2A deferred rollback plus an explicit commit guarantees all-or-nothing writes even when a batch fails midway.
  3. 3Building placeholders and args in lockstep keeps positional parameters aligned and safe from injection.

Related explainers

ruby 
class Order
  class InvalidTransition < StandardError; end
 
  TRANSITIONS = {

A state machine for order transitions in Ruby

state-machine data-driven error-handling
Intermediate 8 steps
go 
package theme
 
import (
	"fmt"

Per-tenant HTML templates with Gin's renderer

multi-tenancy concurrency html-templates
Advanced 8 steps
go 
package auth
 
import (
	"net/http"

Setting and reading secure session cookies in Go

cookies session-management security
Intermediate 6 steps
rust 
use std::cmp::Ordering;
use std::str::FromStr;
 
#[derive(Debug, Clone, PartialEq, Eq)]

Parsing and ordering semantic versions in Rust

parsing trait-implementation ordering
Intermediate 8 steps
javascript 
'use client';
 
import { useEffect } from 'react';
import * as Sentry from '@sentry/nextjs';

How a Next.js error boundary recovers

error-boundary error-handling observability
Intermediate 8 steps
ruby 
class Registration < ApplicationRecord
  belongs_to :event
 
  validates :email, presence: true, format: { with: URI::MailTo::EMAIL_REGEXP }

Validating registrations in Rails

validations i18n error-handling
Intermediate 8 steps

Share this explainer

Here's the card — post it anywhere.

Batch inserting rows in a Go transaction — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code