Bulk Endpoints in Go APIs and Handling Partial Failure
Design bulk endpoints in Go: atomic versus partial modes, per-item results by index, savepoints, idempotent refs, limits, and 100 creates in 1.2 ms not 18.
Single-resource endpoints are the right shape for people clicking buttons. They are the wrong shape for programs that need to create ten thousand records, update a hundred prices or look up two hundred ids. Such a client has two bad options: send one request at a time and pay the round trip, the headers, the authentication and the transaction ten thousand times, or give up on the API and ask for a database dump. A bulk endpoint is the third option, and it is one of the places where a small design mistake costs the most, because the failure modes multiply. What does a batch do when item 37 is invalid? What does the client see? What happens when the whole thing is retried?
Tested with Go 1.25.6 and modernc.org/sqlite v1.50.0, macOS on Apple Silicon (arm64)
This article answers those with a working Go endpoint: a batch create with a limit, two modes (partial and atomic), per-item results, idempotent items, and honest numbers for the saving. It builds on the CRUD API, reuses the idempotency idea from idempotency keys, and charges for it using usage metering. Everything ran on Go 1.25.6 with modernc.org/sqlite v1.50.0.
What a bulk endpoint costs and saves
The saving is per-request overhead: the TCP and TLS round trips (amortised by keep-alive, but never free), the HTTP parsing, authentication, middleware and, above all, one database transaction and commit per item. The numbers are in the benchmark below. The cost is in the contract: a bulk request has a result per item, and the API must say what a partial result means.
The shape of the endpoint
Four decisions come first.
The route. POST /v1/tasks/batch (some APIs use a colon, /v1/tasks:batchCreate) is explicit and leaves POST /v1/tasks for one item. Accepting an array at the collection URL is tempting and makes the response type depend on the request type, which complicates clients and documentation. A generic /batch endpoint that takes an arbitrary list of operations is the trap at the far end: it reimplements routing, authentication and error handling inside one handler, and every middleware assumption breaks. Make bulk operations specific to a resource and an action.
The request body. An object with an items array, not a bare array, for the same reason a list response is an object: you will add fields. Decode strictly with an unknown-field check and a size cap.
Limits. A maximum item count (100 here) and a maximum body size (1 MiB), checked before any work. Without them one request can hold a transaction open for minutes or exhaust memory. The limit is also how clients know how to chunk; document it, and return a 413 that states it.
Item identity. Each item carries a client-chosen ref, unique per resource. It is what makes a retried batch safe: the server inserts with ON CONFLICT(ref) DO NOTHING, and an item whose ref already exists is reported as existing, not created again. The client does not need a response to know that the earlier attempt worked.
package main
import (
"context"
"database/sql"
"encoding/json"
"fmt"
"net/http"
"slices"
"strings"
"sync/atomic"
_ "modernc.org/sqlite"
)
const (
maxItems = 100
maxBytes = 1 << 20
)
type Item struct {
Ref string `json:"ref"` // chosen by the client; makes each item idempotent
Title string `json:"title"`
Priority string `json:"priority"`
}
type Problem struct {
Title string `json:"title"`
Fields map[string]string `json:"fields,omitempty"`
}
type Result struct {
Index int `json:"index"`
Status int `json:"status"`
Ref string `json:"ref,omitempty"`
ID int64 `json:"id,omitempty"`
Error *Problem `json:"error,omitempty"`
}
type Summary struct {
Created int `json:"created"`
Existing int `json:"existing"`
Failed int `json:"failed"`
NotApplied int `json:"not_applied,omitempty"` // valid items that were rolled back because another item failed
}
type Response struct {
Results []Result `json:"results"`
Summary Summary `json:"summary"`
}
type Store struct{ db *sql.DB }
var dbs atomic.Int64
func Open() *Store {
db, err := sql.Open("sqlite", fmt.Sprintf("file:bulk%d?mode=memory&cache=shared", dbs.Add(1)))
if err != nil {
panic(err)
}
db.SetMaxOpenConns(1)
if _, err := db.Exec(`CREATE TABLE tasks (id INTEGER PRIMARY KEY, ref TEXT NOT NULL UNIQUE, title TEXT NOT NULL, priority TEXT NOT NULL)`); err != nil {
panic(err)
}
return &Store{db}
}
func (s *Store) Count() (n int) { s.db.QueryRow(`SELECT COUNT(*) FROM tasks`).Scan(&n); return }
func validate(it Item) *Problem {
f := map[string]string{}
if strings.TrimSpace(it.Ref) == "" {
f["ref"] = "is required"
}
if n := len([]rune(it.Title)); n < 1 || n > 120 {
f["title"] = "must be 1 to 120 characters"
}
if !slices.Contains([]string{"low", "normal", "high"}, it.Priority) {
f["priority"] = "must be low, normal or high"
}
if len(f) == 0 {
return nil
}
return &Problem{"Invalid item", f}
}
// Apply processes the items against one transaction. In atomic mode any failure rolls everything back; in partial mode
// each item runs inside a savepoint, so a failure undoes only that item.
func (s *Store) Apply(ctx context.Context, items []Item, atomic bool) (Response, error) {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return Response{}, err
}
defer tx.Rollback()
resp := Response{Results: make([]Result, 0, len(items))}
for i, it := range items {
r := Result{Index: i, Ref: it.Ref}
if p := validate(it); p != nil {
r.Status, r.Error = 422, p
} else if _, err := tx.Exec(`SAVEPOINT item`); err != nil {
return Response{}, err
} else {
res, err := tx.Exec(`INSERT INTO tasks (ref, title, priority) VALUES (?, ?, ?) ON CONFLICT(ref) DO NOTHING`, it.Ref, it.Title, it.Priority)
switch n, _ := res.RowsAffected(); {
case err != nil:
tx.Exec(`ROLLBACK TO item`)
r.Status, r.Error = 500, &Problem{Title: "Could not store the item"}
case n == 0: // the ref already exists: a retry of an earlier create
tx.QueryRow(`SELECT id FROM tasks WHERE ref = ?`, it.Ref).Scan(&r.ID)
r.Status = 200
default:
id, _ := res.LastInsertId()
r.Status, r.ID = 201, id
}
tx.Exec(`RELEASE item`)
}
switch r.Status {
case 201:
resp.Summary.Created++
case 200:
resp.Summary.Existing++
default:
resp.Summary.Failed++
}
resp.Results = append(resp.Results, r)
}
if atomic && resp.Summary.Failed > 0 {
for i, r := range resp.Results {
if r.Status == 201 || r.Status == 200 {
resp.Results[i] = Result{Index: r.Index, Ref: r.Ref, Status: 424, Error: &Problem{Title: "Not applied: another item in the batch failed"}}
resp.Summary.NotApplied++
}
}
resp.Summary.Created, resp.Summary.Existing = 0, 0
return resp, errRolledBack
}
return resp, tx.Commit()
}
var errRolledBack = fmt.Errorf("batch rolled back")
func Handler(s *Store) http.Handler {
mux := http.NewServeMux()
mux.HandleFunc("POST /v1/tasks/batch", func(w http.ResponseWriter, r *http.Request) {
var body struct {
Items []Item `json:"items"`
}
dec := json.NewDecoder(http.MaxBytesReader(w, r.Body, maxBytes))
dec.DisallowUnknownFields()
if err := dec.Decode(&body); err != nil {
status := 400
if strings.Contains(err.Error(), "too large") {
status = 413
}
problem(w, status, "Could not read the batch: "+err.Error())
return
}
switch n := len(body.Items); {
case n == 0:
problem(w, 422, "A batch needs at least one item")
return
case n > maxItems:
problem(w, 413, fmt.Sprintf("A batch holds at most %d items, got %d", maxItems, n))
return
}
atomicMode := r.URL.Query().Get("mode") == "atomic"
resp, err := s.Apply(r.Context(), body.Items, atomicMode)
w.Header().Set("Content-Type", "application/json")
switch {
case err == errRolledBack:
w.WriteHeader(422) // nothing was applied; the per-item results say why
case err != nil:
problem(w, 500, "Internal error")
return
default:
w.WriteHeader(200) // the request worked; read the per-item statuses
}
json.NewEncoder(w).Encode(resp)
})
return mux
}
func problem(w http.ResponseWriter, status int, title string) {
w.Header().Set("Content-Type", "application/problem+json")
w.WriteHeader(status)
json.NewEncoder(w).Encode(map[string]any{"title": title, "status": status})
}Partial or atomic
The central question is what happens when some items are bad.
Partial means each item succeeds or fails on its own. The valid ones are stored, the invalid ones are reported. It suits imports where progress matters more than purity: loading a customer's product list, where 4,990 good rows should not be held hostage by ten bad ones. The client must handle a response that is a mix.
Atomic means all or nothing. If any item fails, nothing is applied. It suits operations whose items belong together, such as the lines of an order. The client gets one failure and fixes the input.
The handler takes both, as ?mode=atomic, with partial as the default. In both modes it runs the whole batch in a single transaction, and Apply wraps each item in a SAVEPOINT. In partial mode, a failing insert rolls back to the savepoint, undoing that item only, and the transaction continues. In atomic mode the transaction is rolled back at the end when any item failed. Validation runs in Go before any SQL, so most failures never reach the database, and the savepoint is there for the failures that do (a constraint violated by a concurrent insert, say).
The response
Every result carries the item's index, its ref, an HTTP-style status and either the new id or an error. The results are in the same order as the request, so a client can match them by position or by ref. A summary gives counts.
The statuses per item reuse the meanings clients already know: 201 created, 200 already existed, 422 invalid, 500 failed for a reason that is not the client's fault. The summary adds not_applied.
The top-level HTTP status needs a choice. In partial mode the request itself succeeded even when items failed, so it is a 200, and the per-item statuses carry the detail. Some APIs use 207 Multi-Status, from WebDAV; clients and proxies that do not know it treat it as a generic 2xx, and its meaning is the same, so use it if your consumers expect it. In atomic mode a failed batch is a 422, because the request as a whole was refused. Either way, never answer 200 with a body that hides the fact that nothing was stored.
That last point shapes the atomic response. When a batch is rolled back, the valid items must not claim success. They are reported as 424 with the title "Not applied: another item in the batch failed", and the summary counts them in not_applied. My first version returned the original 201 statuses for rolled-back items, which was a lie a client would act on; a test now fails if a rolled-back item reports success.
What it did
package main
import (
"bytes"
"encoding/json"
"fmt"
"net/http"
"net/http/httptest"
"strings"
)
func post(h http.Handler, path string, body any) (int, Response, string) {
b, _ := json.Marshal(body)
req := httptest.NewRequest("POST", path, bytes.NewReader(b))
rec := httptest.NewRecorder()
h.ServeHTTP(rec, req)
var resp Response
json.Unmarshal(rec.Body.Bytes(), &resp)
return rec.Code, resp, strings.TrimSpace(rec.Body.String())
}
func show(label string, code int, r Response) {
fmt.Printf("%s -> HTTP %d summary=%+v\n", label, code, r.Summary)
for _, x := range r.Results {
line := fmt.Sprintf(" #%d %d ref=%s", x.Index, x.Status, x.Ref)
if x.ID != 0 {
line += fmt.Sprintf(" id=%d", x.ID)
}
if x.Error != nil {
line += fmt.Sprintf(" error=%s %v", x.Error.Title, x.Error.Fields)
}
fmt.Println(line)
}
}
func main() {
batch := map[string]any{"items": []Item{
{"a-1", "write the post", "high"},
{"a-2", "", "normal"},
{"a-3", "review it", "urgent"},
{"a-4", "publish", "low"},
}}
s := Open()
h := Handler(s)
code, r, _ := post(h, "/v1/tasks/batch", batch)
show("partial mode (default)", code, r)
fmt.Println("rows stored:", s.Count())
code, r, _ = post(h, "/v1/tasks/batch", batch)
show("\nthe same batch retried", code, r)
fmt.Println("rows stored:", s.Count(), "(the two valid items were not duplicated)")
s2 := Open()
h2 := Handler(s2)
code, r, _ = post(h2, "/v1/tasks/batch?mode=atomic", batch)
show("\natomic mode", code, r)
fmt.Println("rows stored:", s2.Count())
fmt.Println("\n== limits")
var many []Item
for i := 0; i < 101; i++ {
many = append(many, Item{fmt.Sprint("r", i), "t", "low"})
}
code, _, body := post(h, "/v1/tasks/batch", map[string]any{"items": many})
fmt.Println("101 items:", code, body)
code, _, body = post(h, "/v1/tasks/batch", map[string]any{"items": []Item{}})
fmt.Println("0 items: ", code, body)
code, _, body = post(h, "/v1/tasks/batch", map[string]any{"items": []Item{{"x", "t", "low"}}, "surprise": true})
fmt.Println("unknown field:", code, body)
}partial mode (default) -> HTTP 200 summary={Created:2 Existing:0 Failed:2 NotApplied:0}
#0 201 ref=a-1 id=1
#1 422 ref=a-2 error=Invalid item map[title:must be 1 to 120 characters]
#2 422 ref=a-3 error=Invalid item map[priority:must be low, normal or high]
#3 201 ref=a-4 id=2
rows stored: 2
the same batch retried -> HTTP 200 summary={Created:0 Existing:2 Failed:2 NotApplied:0}
#0 200 ref=a-1 id=1
#1 422 ref=a-2 error=Invalid item map[title:must be 1 to 120 characters]
#2 422 ref=a-3 error=Invalid item map[priority:must be low, normal or high]
#3 200 ref=a-4 id=2
rows stored: 2 (the two valid items were not duplicated)
atomic mode -> HTTP 422 summary={Created:0 Existing:0 Failed:2 NotApplied:2}
#0 424 ref=a-1 error=Not applied: another item in the batch failed map[]
#1 422 ref=a-2 error=Invalid item map[title:must be 1 to 120 characters]
#2 422 ref=a-3 error=Invalid item map[priority:must be low, normal or high]
#3 424 ref=a-4 error=Not applied: another item in the batch failed map[]
rows stored: 0
== limits
101 items: 413 {"status":413,"title":"A batch holds at most 100 items, got 101"}
0 items: 422 {"status":422,"title":"A batch needs at least one item"}
unknown field: 400 {"status":400,"title":"Could not read the batch: json: unknown field \"surprise\""}Partial mode. The four-item batch had two valid items and two bad ones. The response is HTTP 200, with a summary of two created and two failed. Item 1 (empty title) is a 422 with title: must be 1 to 120 characters, and item 2 (priority urgent) is a 422 with the allowed values. Two rows were stored.
Retry. Sending the identical batch again returned 200 for the two valid items (existing, with the same ids), the same two failures, and the row count stayed at 2. This is the property that lets a client that timed out resend the whole batch without thinking.
Atomic mode. The same input with mode=atomic returned HTTP 422. The two bad items carry their field errors, the two good ones are 424 and the summary says Failed:2 NotApplied:2. Zero rows were stored.
Limits. 101 items returned a 413 naming the limit, an empty batch a 422, and a body with an unknown field a 400 naming it.
Tests
package main
import (
"context"
"fmt"
"testing"
)
func mixed() []Item {
return []Item{{"r1", "ok", "low"}, {"r2", "", "low"}, {"r3", "ok", "high"}}
}
func TestPartialModeKeepsTheGoodItems(t *testing.T) {
s := Open()
resp, err := s.Apply(context.Background(), mixed(), false)
if err != nil || resp.Summary != (Summary{Created: 2, Failed: 1}) || s.Count() != 2 {
t.Fatalf("%v %+v rows=%d", err, resp.Summary, s.Count())
}
if resp.Results[1].Status != 422 || resp.Results[1].Index != 1 {
t.Fatalf("%+v", resp.Results[1])
}
}
func TestAtomicModeStoresNothingOnAnyFailure(t *testing.T) {
s := Open()
resp, err := s.Apply(context.Background(), mixed(), true)
if err != errRolledBack || s.Count() != 0 {
t.Fatalf("err=%v rows=%d", err, s.Count())
}
if resp.Summary != (Summary{Failed: 1, NotApplied: 2}) || resp.Results[0].Status != 424 {
t.Fatalf("a rolled-back item must not report success: %+v %+v", resp.Summary, resp.Results[0])
}
}
func TestRetryingABatchCreatesNoDuplicates(t *testing.T) {
s := Open()
s.Apply(context.Background(), mixed(), false)
resp, _ := s.Apply(context.Background(), mixed(), false)
if s.Count() != 2 || resp.Summary.Existing != 2 || resp.Summary.Created != 0 {
t.Fatalf("rows=%d %+v", s.Count(), resp.Summary)
}
}
func TestResultsAreAlignedByIndex(t *testing.T) {
s := Open()
var items []Item
for i := 0; i < 50; i++ {
items = append(items, Item{fmt.Sprint("r", i), "t", "low"})
}
resp, _ := s.Apply(context.Background(), items, false)
for i, r := range resp.Results {
if r.Index != i || r.Ref != items[i].Ref {
t.Fatalf("result %d is for %q at index %d", i, r.Ref, r.Index)
}
}
}=== RUN TestPartialModeKeepsTheGoodItems
--- PASS: TestPartialModeKeepsTheGoodItems (0.01s)
=== RUN TestAtomicModeStoresNothingOnAnyFailure
--- PASS: TestAtomicModeStoresNothingOnAnyFailure (0.00s)
=== RUN TestRetryingABatchCreatesNoDuplicates
--- PASS: TestRetryingABatchCreatesNoDuplicates (0.00s)
=== RUN TestResultsAreAlignedByIndex
--- PASS: TestResultsAreAlignedByIndex (0.02s)
PASS
ok example.test/lab 1.445sThe tests encode the contract: partial mode keeps the good items and reports the bad one by index; atomic mode stores nothing and does not report rolled-back items as created; a retry creates no duplicates and reports the earlier items as existing; and fifty results come back in request order with matching refs. Add a test for each limit when you set yours.
What it saves
package main
import (
"bytes"
"encoding/json"
"fmt"
"net/http"
"net/http/httptest"
"testing"
)
func items(n, round int) []Item {
out := make([]Item, n)
for i := range out {
out[i] = Item{fmt.Sprintf("b%d-%d", round, i), "benchmark task", "normal"}
}
return out
}
// 100 single creates over real HTTP, each its own request and transaction.
func BenchmarkHundredSingleRequests(b *testing.B) {
srv := httptest.NewServer(Handler(Open()))
defer srv.Close()
b.ResetTimer()
round := 0
for b.Loop() {
round++
for _, it := range items(100, round) {
body, _ := json.Marshal(map[string]any{"items": []Item{it}})
res, _ := http.Post(srv.URL+"/v1/tasks/batch", "application/json", bytes.NewReader(body))
res.Body.Close()
}
}
}
// The same 100 creates in one request and one transaction.
func BenchmarkOneBatchOfHundred(b *testing.B) {
srv := httptest.NewServer(Handler(Open()))
defer srv.Close()
b.ResetTimer()
round := 0
for b.Loop() {
round++
body, _ := json.Marshal(map[string]any{"items": items(100, round)})
res, _ := http.Post(srv.URL+"/v1/tasks/batch", "application/json", bytes.NewReader(body))
res.Body.Close()
}
}goos: darwin
goarch: arm64
pkg: example.test/lab
cpu: Apple M1 Max
BenchmarkHundredSingleRequests-10 64 18246594 ns/op 2654640 B/op 21330 allocs/op
BenchmarkOneBatchOfHundred-10 1120 1178714 ns/op 153089 B/op 3087 allocs/op
PASS
ok example.test/lab 2.814sBoth benchmarks create 100 tasks over real HTTP to a test server. One sends 100 requests of one item each, the other sends one request with 100 items. On an Apple M1 Max, the 100 single requests took 18.2 ms and 21,330 allocations; the single batch took 1.18 ms and 3,087 allocations. That is about fifteen times faster, and the gap grows with network latency, since the batch pays one round trip and the singles pay a hundred. Over a link with 40 ms round trips, 100 sequential requests take at least four seconds before the server does anything.
Retrying the right items
A client that gets a mixed response must decide what to resend. The statuses make the rule mechanical. Items that came back 201 or 200 are done. Items that came back 422 will fail again with the same input, so retrying them is pointless; they go to a dead-letter list or back to a person. Items that came back 5xx, or that were never reached because the request timed out, are worth retrying, and the ref makes that safe even if the earlier attempt secretly succeeded. In atomic mode, a 424 item is not at fault: fix the 422 items and resend the whole batch.
Write this into the API reference as a table. It is the part developers will otherwise get wrong, and the consequences are duplicated records or silently dropped ones.
Authorization is per item
A bulk endpoint is a good place to leak data. If the handler checks once that the caller may use the endpoint, and then acts on a list of ids, a caller can include ids belonging to someone else. Every item needs the same ownership check a single request would get, and an item that fails it should be reported as 403 or 404 in the results, not allowed and not allowed to fail the whole batch in partial mode. The check also belongs inside the transaction for updates and deletes, so the permission and the change cannot be separated by a concurrent edit (broken access control).
Bulk reads
The same idea applies to reads, where it is simpler. GET /v1/tasks?ids=1,2,3 returns the tasks that exist, in a data array, and an optional missing array of the ids that do not. Cap the number of ids, validate each, and use a single WHERE id IN (...) query with bound parameters, never string-built (filtering and sorting). For a large set of ids, accept a POST with a body, since URLs have length limits that vary between proxies.
When a batch is too big
There is a ceiling on what one synchronous request can do. A file of a million rows should not be a JSON array in a request body at all. Two alternatives: upload the file and return 202 Accepted with a job to poll (async jobs), or accept newline-delimited JSON and process it as a stream (streaming JSON), writing per-line results back. Both bound memory and transaction time. The batch endpoint stays for the case it is good at: up to a few hundred items, answered immediately.
Further design points
Transaction size. One transaction for 100 small inserts is fine. A batch of 10,000 holds locks for a long time and blocks others. Keep the item limit low and make clients chunk; for really large loads, use an asynchronous job (async jobs with 202) or an import file.
Ordering and dependencies. Process items in order and document it. If items can refer to each other (a parent created earlier in the same batch), say so, and decide what happens when the parent fails: the child should fail with a clear reason rather than create an orphan.
Updates and deletes. The same shape works: each item says which resource and what change, and each result says what happened. For updates add the version or If-Match per item, so one stale item fails without hiding the others (CRUD with ETags).
Rate limits and quotas. A batch of 100 should cost 100 units, not one request. Charge by items, or a client can bypass every limit by batching (rate limiting).
Observability. Log the batch size, the mode and the summary counts. A rising failure ratio on a client's batches is the first sign of a bug in their integration.
Client helper. Ship (or document) a function that chunks a slice into batches under the limit, runs them, and merges the results by ref. Most clients will write it badly otherwise.
The standard library pieces involved are database/sql's Tx and the savepoint syntax documented by SQLite; PostgreSQL and MySQL support the same statements. The status codes are defined in RFC 9110, and 424 in RFC 4918.
