or run

tessl search
Log in

Version

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
golangpkg:golang/cloud.google.com/go/spanner@v1.87.0

docs

client.mddml.mdindex.mdkeys.mdlow-level.mdprotobuf-types.mdreads.mdtesting.mdtransactions.mdtypes.mdwrites.md
tile.json

tessl/golang-cloud-google-com--go--spanner

tessl install tessl/golang-cloud-google-com--go--spanner@1.87.2

Official Google Cloud Spanner client library for Go providing comprehensive database operations, transactions, and admin functionality

writes.mddocs/

Write Operations

This document covers write operations using mutations including Insert, Update, Delete, and Apply operations.

Overview

Spanner write operations are performed using Mutations. Mutations describe changes to rows and are applied atomically. Write operations include:

  • Insert: Add new rows
  • Update: Modify existing rows
  • InsertOrUpdate: Upsert operation
  • Replace: Replace entire row
  • Delete: Remove rows

Mutation Type

type Mutation struct {
    // Has unexported fields
}

Mutations are created using builder functions and applied using Client.Apply() or buffered in read-write transactions.

Insert Operations

Insert with Columns and Values

func Insert(table string, cols []string, vals []interface{}) *Mutation

Example:

m := spanner.Insert("Users",
    []string{"id", "name", "email"},
    []interface{}{1, "alice", "alice@example.com"})

_, err := client.Apply(ctx, []*spanner.Mutation{m})
if err != nil {
    return err
}

InsertMap

func InsertMap(table string, in map[string]interface{}) *Mutation

Example:

m := spanner.InsertMap("Users", map[string]interface{}{
    "id":    1,
    "name":  "alice",
    "email": "alice@example.com",
})

_, err := client.Apply(ctx, []*spanner.Mutation{m})

InsertStruct

func InsertStruct(table string, in interface{}) (*Mutation, error)

Example:

type User struct {
    ID    int64  `spanner:"id"`
    Name  string `spanner:"name"`
    Email string `spanner:"email"`
}

user := User{ID: 1, Name: "alice", Email: "alice@example.com"}
m, err := spanner.InsertStruct("Users", user)
if err != nil {
    return err
}

_, err = client.Apply(ctx, []*spanner.Mutation{m})

Struct tags:

  • spanner:"column_name" - Map to specific column
  • spanner:"-" - Ignore field
  • Field names map to lowercase column names by default

Update Operations

Update with Columns and Values

func Update(table string, cols []string, vals []interface{}) *Mutation

Example:

m := spanner.Update("Users",
    []string{"id", "email"},
    []interface{}{1, "newemail@example.com"})

_, err := client.Apply(ctx, []*spanner.Mutation{m})

UpdateMap

func UpdateMap(table string, in map[string]interface{}) *Mutation

Example:

m := spanner.UpdateMap("Users", map[string]interface{}{
    "id":    1,
    "email": "newemail@example.com",
})

_, err := client.Apply(ctx, []*spanner.Mutation{m})

UpdateStruct

func UpdateStruct(table string, in interface{}) (*Mutation, error)

Example:

user := User{ID: 1, Email: "newemail@example.com"}
m, err := spanner.UpdateStruct("Users", user)
if err != nil {
    return err
}

_, err = client.Apply(ctx, []*spanner.Mutation{m})

InsertOrUpdate (Upsert)

InsertOrUpdate with Columns and Values

func InsertOrUpdate(table string, cols []string, vals []interface{}) *Mutation

Inserts row if it doesn't exist, updates if it does. Preserves unspecified columns.

Example:

m := spanner.InsertOrUpdate("Users",
    []string{"id", "name", "email"},
    []interface{}{1, "alice", "alice@example.com"})

_, err := client.Apply(ctx, []*spanner.Mutation{m})

InsertOrUpdateMap

func InsertOrUpdateMap(table string, in map[string]interface{}) *Mutation

InsertOrUpdateStruct

func InsertOrUpdateStruct(table string, in interface{}) (*Mutation, error)

Replace Operations

Replace with Columns and Values

func Replace(table string, cols []string, vals []interface{}) *Mutation

Inserts row or replaces existing row. Unspecified columns become NULL (unlike InsertOrUpdate).

Example:

m := spanner.Replace("Users",
    []string{"id", "name"},
    []interface{}{1, "alice"})
// If row exists, email column becomes NULL

_, err := client.Apply(ctx, []*spanner.Mutation{m})

ReplaceMap

func ReplaceMap(table string, in map[string]interface{}) *Mutation

ReplaceStruct

func ReplaceStruct(table string, in interface{}) (*Mutation, error)

Delete Operations

Delete with KeySet

func Delete(table string, ks KeySet) *Mutation

Deletes rows matching the KeySet. Succeeds even if keys don't exist.

Example - Single Key:

m := spanner.Delete("Users", spanner.Key{1})
_, err := client.Apply(ctx, []*spanner.Mutation{m})

Example - Multiple Keys:

m := spanner.Delete("Users", spanner.KeySets(
    spanner.Key{1},
    spanner.Key{2},
    spanner.Key{3},
))
_, err := client.Apply(ctx, []*spanner.Mutation{m})

Example - Key Range:

m := spanner.Delete("Users", spanner.KeyRange{
    Start: spanner.Key{1},
    End:   spanner.Key{100},
    Kind:  spanner.ClosedOpen,
})
_, err := client.Apply(ctx, []*spanner.Mutation{m})

Example - All Keys:

m := spanner.Delete("Users", spanner.AllKeys())
_, err := client.Apply(ctx, []*spanner.Mutation{m})

Apply - Execute Mutations

Client.Apply

func (c *Client) Apply(ctx context.Context, ms []*Mutation, opts ...ApplyOption) (commitTimestamp time.Time, err error)

Apply mutations atomically in a single transaction. Returns the commit timestamp.

Example - Multiple Mutations:

mutations := []*spanner.Mutation{
    spanner.Insert("Users", []string{"id", "name"}, []interface{}{1, "alice"}),
    spanner.Insert("Users", []string{"id", "name"}, []interface{}{2, "bob"}),
    spanner.Update("Accounts", []string{"id", "balance"}, []interface{}{"A", 1000}),
}

commitTS, err := client.Apply(ctx, mutations)
if err != nil {
    return err
}
fmt.Printf("Committed at: %v\n", commitTS)

Apply Options

type ApplyOption func(*applyOption)

func ApplyAtLeastOnce() ApplyOption
func ApplyCommitOptions(co CommitOptions) ApplyOption
func Priority(priority sppb.RequestOptions_Priority) ApplyOption
func TransactionTag(tag string) ApplyOption
func ExcludeTxnFromChangeStreams() ApplyOption
func IsolationLevel(isolationLevel sppb.TransactionOptions_IsolationLevel) ApplyOption

ApplyAtLeastOnce Example:

// Remove replay protection for better latency (mutations must be idempotent)
commitTS, err := client.Apply(ctx, mutations, spanner.ApplyAtLeastOnce())

Priority Example:

commitTS, err := client.Apply(ctx, mutations,
    spanner.Priority(sppb.RequestOptions_PRIORITY_HIGH))

Transaction Tag Example:

commitTS, err := client.Apply(ctx, mutations,
    spanner.TransactionTag("user-registration"))

Commit Options Example:

commitOptions := spanner.CommitOptions{
    ReturnCommitStats: true,
}

commitTS, err := client.Apply(ctx, mutations,
    spanner.ApplyCommitOptions(commitOptions))

Exclude from Change Streams Example:

commitTS, err := client.Apply(ctx, mutations,
    spanner.ExcludeTxnFromChangeStreams())

Batch Writes

Write multiple mutation groups with independent commits:

func (c *Client) BatchWrite(ctx context.Context, mgs []*MutationGroup) *BatchWriteResponseIterator
func (c *Client) BatchWriteWithOptions(ctx context.Context, mgs []*MutationGroup, opts BatchWriteOptions) *BatchWriteResponseIterator

type MutationGroup struct {
    Mutations []*Mutation
}

type BatchWriteOptions struct {
    Priority                    sppb.RequestOptions_Priority
    TransactionTag              string
    ExcludeTxnFromChangeStreams bool
}

Example:

mutationGroups := []*spanner.MutationGroup{
    {
        Mutations: []*spanner.Mutation{
            spanner.Insert("Users", []string{"id", "name"}, []interface{}{1, "alice"}),
            spanner.Insert("Accounts", []string{"user_id", "balance"}, []interface{}{1, 100}),
        },
    },
    {
        Mutations: []*spanner.Mutation{
            spanner.Insert("Users", []string{"id", "name"}, []interface{}{2, "bob"}),
            spanner.Insert("Accounts", []string{"user_id", "balance"}, []interface{}{2, 200}),
        },
    },
}

iter := client.BatchWrite(ctx, mutationGroups)
defer iter.Stop()

for {
    resp, err := iter.Next()
    if err == iterator.Done {
        break
    }
    if err != nil {
        log.Printf("Error: %v", err)
        continue
    }
    fmt.Printf("Mutation group committed at: %v\n", resp.CommitTimestamp)
}

BatchWriteResponseIterator

type BatchWriteResponseIterator struct {
    // Has unexported fields
}

func (b *BatchWriteResponseIterator) Next() (*sppb.BatchWriteResponse, error)
func (b *BatchWriteResponseIterator) Do(f func(r *sppb.BatchWriteResponse) error) error
func (b *BatchWriteResponseIterator) Stop()

Special Values

CommitTimestamp

Use for auto-populating commit timestamp columns:

var CommitTimestamp = commitTimestamp

Example:

m := spanner.Insert("Users",
    []string{"id", "name", "last_updated"},
    []interface{}{1, "alice", spanner.CommitTimestamp})

commitTS, err := client.Apply(ctx, []*spanner.Mutation{m})
// last_updated column gets actual commit timestamp

In Struct:

type User struct {
    ID          int64     `spanner:"id"`
    Name        string    `spanner:"name"`
    LastUpdated time.Time `spanner:"last_updated"`
}

user := User{
    ID:          1,
    Name:        "alice",
    LastUpdated: spanner.CommitTimestamp,
}

m, err := spanner.InsertStruct("Users", user)

Setting NULL Values

To set a column to NULL, use nil:

With columns/values:

m := spanner.Update("Users",
    []string{"id", "email"},
    []interface{}{1, nil})  // Sets email to NULL

With map:

m := spanner.UpdateMap("Users", map[string]interface{}{
    "id":    1,
    "email": nil,  // Sets email to NULL
})

With struct:

user := User{
    ID:    1,
    Email: "",  // Empty string sets to NULL for NullString
}

// Or use spanner.NullString
type User struct {
    ID    int64              `spanner:"id"`
    Email spanner.NullString `spanner:"email"`
}

user := User{
    ID: 1,
    Email: spanner.NullString{Valid: false},  // NULL
}

Type Mappings for Writes

Go types map to Spanner types as follows:

// Scalar types
string, *string, NullString              → STRING
[]byte                                   → BYTES
int, int64, *int64, NullInt64           → INT64
bool, *bool, NullBool                   → BOOL
float64, *float64, NullFloat64          → FLOAT64
float32, *float32, NullFloat32          → FLOAT32
time.Time, *time.Time, NullTime         → TIMESTAMP
civil.Date, *civil.Date, NullDate       → DATE
big.Rat, *big.Rat, NullNumeric          → NUMERIC
NullJSON                                 → JSON
protoreflect.Enum, NullProtoEnum        → ENUM
proto.Message, NullProtoMessage         → PROTO

// Array types
[]string, []*string, []NullString        → ARRAY<STRING>
[][]byte                                 → ARRAY<BYTES>
[]int, []int64, []*int64, []NullInt64   → ARRAY<INT64>
[]bool, []*bool, []NullBool             → ARRAY<BOOL>
[]float64, []*float64, []NullFloat64    → ARRAY<FLOAT64>
[]float32, []*float32, []NullFloat32    → ARRAY<FLOAT32>
[]time.Time, []*time.Time, []NullTime   → ARRAY<TIMESTAMP>
[]civil.Date, []*civil.Date, []NullDate → ARRAY<DATE>
[]big.Rat, []*big.Rat, []NullNumeric    → ARRAY<NUMERIC>
[]NullJSON                               → ARRAY<JSON>

Wrapping Protocol Buffer Mutations

Create mutations from protobuf:

func WrapMutation(proto *sppb.Mutation) (*Mutation, error)

Error Handling

Common errors:

  • codes.AlreadyExists: Insert of existing row
  • codes.NotFound: Update/Delete of non-existent row
  • codes.InvalidArgument: Type mismatch or constraint violation
  • codes.FailedPrecondition: Schema or state issue

Example:

_, err := client.Apply(ctx, mutations)
if err != nil {
    if spanner.ErrCode(err) == codes.AlreadyExists {
        // Handle duplicate key
        return fmt.Errorf("row already exists")
    }
    return err
}

Best Practices

  1. Batch mutations: Group related changes for better performance
  2. Use InsertOrUpdate wisely: Understand difference from Replace
  3. Keep mutations small: Large mutations may timeout
  4. Use struct mutations: Type-safe and cleaner
  5. Handle errors properly: Check for specific error codes
  6. Set commit timestamp: Use CommitTimestamp for audit columns
  7. Tag transactions: Use TransactionTag for debugging
  8. Consider AtLeastOnce: For idempotent, latency-sensitive writes
  9. Validate data: Before creating mutations
  10. Use NULL appropriately: Set nil for optional fields

Complete Examples

User Registration Flow

func RegisterUser(ctx context.Context, client *spanner.Client, user User) error {
    mutations := []*spanner.Mutation{
        // Insert user record
        spanner.InsertStruct("Users", user),
        
        // Create default account
        spanner.InsertMap("Accounts", map[string]interface{}{
            "user_id":      user.ID,
            "balance":      0,
            "created_at":   spanner.CommitTimestamp,
        }),
        
        // Initialize preferences
        spanner.InsertMap("Preferences", map[string]interface{}{
            "user_id":      user.ID,
            "email_notify": true,
            "theme":        "light",
        }),
    }
    
    commitTS, err := client.Apply(ctx, mutations,
        spanner.TransactionTag("user-registration"),
        spanner.Priority(sppb.RequestOptions_PRIORITY_HIGH),
    )
    if err != nil {
        return fmt.Errorf("failed to register user: %w", err)
    }
    
    log.Printf("User registered at: %v", commitTS)
    return nil
}

Bulk Insert

func BulkInsertUsers(ctx context.Context, client *spanner.Client, users []User) error {
    const batchSize = 1000
    
    for i := 0; i < len(users); i += batchSize {
        end := i + batchSize
        if end > len(users) {
            end = len(users)
        }
        
        batch := users[i:end]
        mutations := make([]*spanner.Mutation, 0, len(batch))
        
        for _, user := range batch {
            m, err := spanner.InsertStruct("Users", user)
            if err != nil {
                return err
            }
            mutations = append(mutations, m)
        }
        
        _, err := client.Apply(ctx, mutations)
        if err != nil {
            return fmt.Errorf("batch %d-%d failed: %w", i, end, err)
        }
    }
    
    return nil
}

Conditional Update

func UpdateUserEmail(ctx context.Context, client *spanner.Client, userID int64, newEmail string) error {
    // Use read-write transaction for conditional logic
    _, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
        // Read current state
        row, err := txn.ReadRow(ctx, "Users", spanner.Key{userID}, []string{"email_verified"})
        if err != nil {
            return err
        }
        
        var verified bool
        if err := row.Column(0, &verified); err != nil {
            return err
        }
        
        // Only update if email is verified
        if !verified {
            return fmt.Errorf("cannot update email: not verified")
        }
        
        // Perform update
        m := spanner.Update("Users",
            []string{"id", "email", "email_verified"},
            []interface{}{userID, newEmail, false})  // Reset verification
        
        return txn.BufferWrite([]*spanner.Mutation{m})
    })
    
    return err
}