Add column

Update a schema by adding a column.

Documentation pages that include this code sample

To view the code sample used in context, see the following documentation:

Code sample

C#

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

// Initialize request argument(s).
string connectionString =
    $"Data Source=projects/{projectId}/instances/"
    + $"{instanceId}/databases/{databaseId}";
string alterStatement =
    "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64";
// Make the request.
using (var connection = new SpannerConnection(connectionString))
{
    var updateCmd = connection.CreateDdlCommand(alterStatement);
    await updateCmd.ExecuteNonQueryAsync();
}
Console.WriteLine("Added the MarketingBudget column.");

C++

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

void AddColumn(google::cloud::spanner::DatabaseAdminClient client,
               std::string const& project_id, std::string const& instance_id,
               std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata =
      client
          .UpdateDatabase(
              database, {"ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"})
          .get();
  if (!metadata) throw std::runtime_error(metadata.status().message());
  std::cout << "Added MarketingBudget column\n";
}

Go

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.


import (
	"context"
	"fmt"
	"io"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

func addNewColumn(ctx context.Context, w io.Writer, db string) error {
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: db,
		Statements: []string{
			"ALTER TABLE Albums ADD COLUMN MarketingBudget INT64",
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}
	fmt.Fprintf(w, "Added MarketingBudget column\n")
	return nil
}

Java

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

static void addMarketingBudget(DatabaseAdminClient adminClient, DatabaseId dbId) {
  OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
      adminClient.updateDatabaseDdl(
          dbId.getInstanceId().getInstance(),
          dbId.getDatabase(),
          Arrays.asList("ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"),
          null);
  try {
    // Initiate the request which returns an OperationFuture.
    op.get();
    System.out.println("Added MarketingBudget column");
  } catch (ExecutionException e) {
    // If the operation failed during execution, expose the cause.
    throw (SpannerException) e.getCause();
  } catch (InterruptedException e) {
    // Throw when a thread is waiting, sleeping, or otherwise occupied,
    // and the thread is interrupted, either before or during the activity.
    throw SpannerExceptionFactory.propagateInterrupt(e);
  }
}

Node.js

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

const request = ['ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'];

// Creates a new index in the database
try {
  const [operation] = await database.updateSchema(request);

  console.log('Waiting for operation to complete...');
  await operation.promise();

  console.log('Added the MarketingBudget column.');
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

use Google\Cloud\Spanner\SpannerClient;

/**
 * Adds a new column to the Albums table in the example database.
 * Example:
 * ```
 * add_column($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function add_column($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $operation = $database->updateDdl(
        'ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'
    );

    print('Waiting for operation to complete...' . PHP_EOL);
    $operation->pollUntilComplete();

    printf('Added the MarketingBudget column.' . PHP_EOL);
}

Python

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

def add_column(instance_id, database_id):
    """Adds a new column to the Albums table in the example database."""
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    operation = database.update_ddl(
        ["ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"]
    )

    print("Waiting for operation to complete...")
    operation.result(120)

    print("Added the MarketingBudget column.")

Ruby

To learn how to install and use the client library for Cloud Spanner, see Cloud Spanner client libraries.

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner  = Google::Cloud::Spanner.new project: project_id
instance = spanner.instance instance_id
database = instance.database database_id

job = database.update statements: [
  "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"
]

puts "Waiting for database update to complete"

job.wait_until_done!

puts "Added the MarketingBudget column"

What's next

To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser