This page describes how to insert, update, and delete data using mutations. A mutation represents a sequence of inserts, updates, and deletes that Spanner applies atomically to different rows and tables in a Spanner database.
Although you can commit mutations by using gRPC or REST, it is more common to access the APIs through the client libraries.
This page shows the basic tasks of insert, update, and delete. You can find more examples in the Getting started tutorials.
If you need to commit a large number of blind writes, but don't require an atomic transaction, you can bulk modify your Spanner tables using batch write. For more information, see Modify data using batch writes.
Insert new rows in a table
C++
You write data using the InsertMutationBuilder()
function.
Client::Commit()
adds new rows to a table. All inserts in
a single batch are applied atomically.
This code shows how to write the data:
C#
You can insert data using the
connection.CreateInsertCommand()
method, which
creates a new SpannerCommand
to insert rows into a table. The
SpannerCommand.ExecuteNonQueryAsync()
method adds new
rows to the table.
This code shows how to insert data:
Go
You write data using a
Mutation
. A Mutation
is
a container for mutation operations. A Mutation
represents a sequence of
inserts, updates, deletes, and so on that can be applied atomically to
different rows and tables in a Spanner database.
Use Mutation.InsertOrUpdate()
to construct an INSERT_OR_UPDATE
mutation, which adds a new row or updates
column values if the row already exists. Alternatively, use
Mutation.Insert()
method to construct an INSERT
mutation, which adds a new row.
Client.Apply()
applies
mutations atomically to a database.
This code shows how to write the data:
Java
You write data using a Mutation
object. A Mutation
object is a container for mutation operations. A Mutation
represents a
sequence of inserts, updates, and deletes that Spanner applies
atomically to different rows and tables in a Spanner database.
The newInsertBuilder()
method in the Mutation
class constructs an INSERT
mutation, which inserts a new
row in a table. If the row already exists, the write fails. Alternatively,
you can use the newInsertOrUpdateBuilder
method
to construct an INSERT_OR_UPDATE
mutation, which
updates column values if the row already exists.
The write()
method in the DatabaseClient
class writes
the mutations. All mutations in a single batch are applied atomically.
This code shows how to write the data:
Node.js
You write data using a
Table
object. The
Table.insert()
method adds new rows to the table. All inserts in a single batch are applied
atomically.
This code shows how to write the data:
PHP
You write data using the
Database::insertBatch
method. insertBatch
adds new rows to a table. All inserts in a single
batch are applied atomically.
This code shows how to write the data:
Python
You write data using a Batch
object. A Batch
object is
a container for mutation operations. A mutation represents a sequence of
inserts, updates, deletes, and so on that can be applied atomically to
different rows and tables in a Spanner database.
The insert()
method in the Batch
class is used to
add one or more insert mutations to the batch. All mutations in a single
batch are applied atomically.
This code shows how to write the data:
Ruby
You write data using a
Client
object. The
Client#commit
method creates and commits a transaction for writes that execute atomically
at a single logical point in time across columns, rows, and tables in a
database.
This code shows how to write the data:
Update rows in a table
Suppose that sales of Albums(1, 1)
are lower than expected. As a result, you
want to move $200,000 from the marketing budget of Albums(2, 2)
to
Albums(1, 1)
, but only if the money is available in the budget of
Albums(2, 2)
.
Because you need to read the data in the tables to determine whether to write new values, you should use a read-write transaction to perform the reads and writes atomically.
C++
Use the Transaction()
function to run a transaction for a client.
Here's the code to run the transaction:
C#
For .NET Standard 2.0 (or .NET 4.5) and above, you can use the .NET framework's
TransactionScope()
to run a transaction. For all supported
versions of .NET, you can create a transaction by setting the result of
SpannerConnection.BeginTransactionAsync
as the Transaction
property of
SpannerCommand
.
Here are the two ways to run the transaction:
.NET Standard 2.0
.NET Standard 1.5
Go
Use the ReadWriteTransaction
type for executing a body of work in the context of a read-write transaction.
Client.ReadWriteTransaction()
returns a ReadWriteTransaction
object.
The sample uses ReadWriteTransaction.ReadRow()
to retrieve a row of data.
The sample also uses ReadWriteTransaction.BufferWrite()
,
which adds a list of mutations to the set of updates that will be applied
when the transaction is committed.
The sample also uses the Key
type, which represents a row key in a Spanner table or index.
Java
Use the TransactionRunner
interface for executing
a body of work in the context of a read-write transaction. This interface
contains the method run()
, which is used to execute a read-
write transaction, with retries as necessary. The
readWriteTransaction
method of the DatabaseClient
class returns a TransactionRunner
object for executing a single logical
transaction.
The TransactionRunner.TransactionCallable
class contains a run()
method for performing a single attempt of a
transaction. run()
takes a
TransactionContext
object, which is a context
for a transaction.
The sample uses the Struct
class, which is handy for
storing the results of the readRow()
calls. The sample also uses the
Key
class, which represents a row key in a Spanner
table or index.
Here's the code to run the transaction:
Node.js
Use Database.runTransaction()
to run a transaction.
Here's the code to run the transaction:
PHP
Use Database::runTransaction
to run a transaction.
Here's the code to run the transaction:
Python
Use the run_in_transaction()
method of the Database
class to run a transaction.
Here's the code to run the transaction:
Ruby
Use the transaction
method of the Client
class to run a transaction.
Here's the code to run the transaction:
Delete rows in a table
Each client library provides multiple ways to delete rows:
- Delete all the rows in a table.
- Delete a single row by specifying the key column values for the row.
- Delete a group of rows by creating a key range.
- Delete rows in an interleaved table by deleting the parent rows, if the
interleaved table includes
ON DELETE CASCADE
in its schema definition.
C++
Delete rows using the DeleteMutationBuilder()
function for a client.
This code shows how to delete the data:
C#
Delete rows using the
connection.CreateDeleteCommand()
method, which
creates a new SpannerCommand
to delete rows. The
SpannerCommand.ExecuteNonQueryAsync()
method deletes the
rows from the table.
This example deletes the rows in the Singers
table individually. The rows
in the Albums
table are deleted because the Albums
table is interleaved
in the Singers
table and is defined with ON DELETE CASCADE
.
Go
Delete rows using a Mutation
. Use the
Mutation.Delete()
method to construct a DELETE
mutation,
which deletes a row. The Client.Apply()
method applies
mutations atomically to the database.
This example deletes the rows in the Albums
table individually, and then
deletes all the rows in the Singers
table using a KeyRange.
Java
Delete rows using the Mutation.delete()
method.
This examples uses the KeySet.all()
method to delete all
the rows in the Albums
table. After deleting the rows in the Albums
table, the example deletes the rows in the Singers
table individually
using keys created with the KeySet.singleKey()
method.
Node.js
Delete rows using the table.deleteRows()
method.
This example uses the table.deleteRows()
method to delete all the rows
from the Singers
table. The rows in the Albums
table are deleted because
the Albums
table is interleaved in Singers
table and is defined with ON
DELETE CASCADE
.
PHP
Delete rows using the Database::delete() method
. The
Database::delete()
method page includes an example.
Python
Delete rows using the Batch.delete()
method.
This example deletes all the rows in the Albums
and Singers
tables
individually using a KeySet
object.
Ruby
Delete rows using the Client#delete
method. The
Client#delete
page includes an example.