Create and manage databases

This page describes creating and managing Cloud Spanner databases.

This page describes how to manage a Cloud Spanner database, including how to create a database, modify database options, and delete a database. To learn how to update a database schema, see Make schema updates.

Create a database

The following examples show how to create a database in an existing instance.

For GoogleSQL-dialect databases, you can define the database schema either at the time of database creation, or after the database has been created. For PostgreSQL-dialect databases you must define the schema after creation.

Schemas are defined using the Database Definition Language, which is documented for GoogleSQL and PostgreSQL. Refer to the following links for more information about creating and updating schemas:

• Schema and data model
• Schema design best practices
• Schema updates

After you create your database, you can safeguard databases that are important to your applications and services by enabling database deletion protection. For more information, see Prevent accidental database deletion.

Console

1. Go to the Instances page in the Google Cloud console.

   Instances

2. Select the instance to create the database in.

3. Click Create database.

4. Enter the following values:

   • A database name to display in the Google Cloud console.
   • The dialect to use for this database.
   • For GoogleSQL-dialect databases, optionally provide a set of DDL statements that define your schema. Use the DDL templates to pre-fill common elements. If there are errors in your DDL statements, the Google Cloud console returns an error when you try to create the database.
   • Optionally, select a customer-managed encryption key to use for this database.

5. Click Create to create the database.

gcloud

Use the gcloud spanner databases create command.

gcloud spanner databases create DATABASE \
  --instance=INSTANCE \
  [--async] \
  [--database-dialect=DATABASE_DIALECT] \
  [--ddl=DDL] \
  [--ddl-file=DDL_FILE] \
  [--kms-key=KMS_KEY : --kms-keyring=KMS_KEYRING --kms-location=KMS_LOCATION --kms-project=KMS_PROJECT] \
  [GCLOUD_WIDE_FLAG …]

The following options are required:

DATABASE

ID of the database or fully qualified identifier for the database. If specifying the fully qualified identifier, the --instance flag can be omitted.

--instance=INSTANCE

The Cloud Spanner instance for the database.

The following options are optional:

--async

Return immediately, without waiting for the operation in progress to complete.

--database-dialect=DATABASE_DIALECT

The SQL dialect of the Cloud Spanner Database. Must be one of: POSTGRESQL, GOOGLE_STANDARD_SQL.

--ddl=DDL

Semi-colon separated DDL (data definition language) statements to run inside the newly created database. If there is an error in any statement, the database is not created. This flag is ignored if --ddl_file is set. Not supported by PostgreSQL-dialect databases.

--ddl-file=DDL_FILE

Path of a file that contains semicolon separated DDL (data definition language) statements to run inside the newly created database. If there is an error in any statement, the database is not created. If --ddl_file is set, --ddl is ignored. Not supported by PostgreSQL-dialect databases.

If you're specifying a Cloud Key Management Service key to use when creating the database, include the following options:

--kms-key=KMS_KEY

ID of the key or fully qualified identifier for the key.

This flag must be specified if any of the other arguments in this group are specified. The other arguments may be omitted if the fully qualified identifier is provided.

--kms-keyring=KMS_KEYRING

Cloud KMS keyring ID of the key.

--kms-location=KMS_LOCATION

Cloud location for the key.

--kms-project=KMS_PROJECT

Cloud project ID for the key.

Client (GoogleSQL)

C++

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

To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

View on GitHub Feedback

void CreateDatabase(google::cloud::spanner_admin::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);
  google::spanner::admin::database::v1::CreateDatabaseRequest request;
  request.set_parent(database.instance().FullName());
  request.set_create_statement("CREATE DATABASE `" + database.database_id() +
                               "`");
  request.add_extra_statements(R"""(
      CREATE TABLE Singers (
          SingerId   INT64 NOT NULL,
          FirstName  STRING(1024),
          LastName   STRING(1024),
          SingerInfo BYTES(MAX),
          FullName   STRING(2049)
              AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED
      ) PRIMARY KEY (SingerId))""");
  request.add_extra_statements(R"""(
      CREATE TABLE Albums (
          SingerId     INT64 NOT NULL,
          AlbumId      INT64 NOT NULL,
          AlbumTitle   STRING(MAX)
      ) PRIMARY KEY (SingerId, AlbumId),
          INTERLEAVE IN PARENT Singers ON DELETE CASCADE)""");
  auto db = client.CreateDatabase(request).get();
  if (!db) throw std::move(db).status();
  std::cout << "Database " << db->name() << " created.\n";
}

Go

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

To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

View on GitHub Feedback

import (
	"context"
	"fmt"
	"io"
	"regexp"

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

func createDatabase(ctx context.Context, w io.Writer, db string) error {
	matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)
	if matches == nil || len(matches) != 3 {
		return fmt.Errorf("Invalid database id %s", db)
	}

	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	op, err := adminClient.CreateDatabase(ctx, &adminpb.CreateDatabaseRequest{
		Parent:          matches[1],
		CreateStatement: "CREATE DATABASE `" + matches[2] + "`",
		ExtraStatements: []string{
			`CREATE TABLE Singers (
				SingerId   INT64 NOT NULL,
				FirstName  STRING(1024),
				LastName   STRING(1024),
				SingerInfo BYTES(MAX),
				FullName   STRING(2048) AS (
					ARRAY_TO_STRING([FirstName, LastName], " ")
				) STORED
			) PRIMARY KEY (SingerId)`,
			`CREATE TABLE Albums (
				SingerId     INT64 NOT NULL,
				AlbumId      INT64 NOT NULL,
				AlbumTitle   STRING(MAX)
			) PRIMARY KEY (SingerId, AlbumId),
			INTERLEAVE IN PARENT Singers ON DELETE CASCADE`,
		},
	})
	if err != nil {
		return err
	}
	if _, err := op.Wait(ctx); err != nil {
		return err
	}
	fmt.Fprintf(w, "Created database [%s]\n", db)
	return nil
}

Java

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

To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

View on GitHub Feedback

import com.google.api.gax.longrunning.OperationFuture;
import com.google.cloud.spanner.Database;
import com.google.cloud.spanner.DatabaseAdminClient;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerException;
import com.google.cloud.spanner.SpannerExceptionFactory;
import com.google.cloud.spanner.SpannerOptions;
import com.google.spanner.admin.database.v1.CreateDatabaseMetadata;
import java.util.Arrays;
import java.util.concurrent.ExecutionException;

public class CreateDatabaseWithDefaultLeaderSample {

  static void createDatabaseWithDefaultLeader() {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "my-project";
    final String instanceId = "my-instance";
    final String databaseId = "my-database";
    final String defaultLeader = "my-default-leader";
    createDatabaseWithDefaultLeader(projectId, instanceId, databaseId, defaultLeader);
  }

  static void createDatabaseWithDefaultLeader(
      String projectId, String instanceId, String databaseId, String defaultLeader) {
    try (Spanner spanner = SpannerOptions
        .newBuilder()
        .setProjectId(projectId)
        .build()
        .getService()) {
      final DatabaseAdminClient databaseAdminClient = spanner.getDatabaseAdminClient();
      final OperationFuture<Database, CreateDatabaseMetadata> operation = databaseAdminClient
          .createDatabase(
              instanceId,
              databaseId,
              Arrays.asList(
                  "CREATE TABLE Singers ("
                      + "  SingerId   INT64 NOT NULL,"
                      + "  FirstName  STRING(1024),"
                      + "  LastName   STRING(1024),"
                      + "  SingerInfo BYTES(MAX)"
                      + ") PRIMARY KEY (SingerId)",
                  "CREATE TABLE Albums ("
                      + "  SingerId     INT64 NOT NULL,"
                      + "  AlbumId      INT64 NOT NULL,"
                      + "  AlbumTitle   STRING(MAX)"
                      + ") PRIMARY KEY (SingerId, AlbumId),"
                      + "  INTERLEAVE IN PARENT Singers ON DELETE CASCADE",
                  "ALTER DATABASE " + "`" + databaseId + "`"
                      + " SET OPTIONS ( default_leader = '" + defaultLeader + "' )"
              )
          );
      final Database database = operation.get();
      System.out.println("Created database [" + database.getId() + "]");
      System.out.println("\tDefault leader: " + database.getDefaultLeader());
    } 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 Spanner, see Spanner client libraries.

To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

View on GitHub Feedback

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

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

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and a database. The database does not need to exist.
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

async function createDatabaseWithDefaultLeader() {
  // Create a new database with an extra statement which will alter the
  // database after creation to set the default leader.
  console.log(`Creating database ${database.formattedName_}.`);
  const createSingersTableStatement = `
    CREATE TABLE Singers (
      SingerId   INT64 NOT NULL,
      FirstName  STRING(1024),
      LastName   STRING(1024),
      SingerInfo BYTES(MAX)
    ) PRIMARY KEY (SingerId)`;
  const createAlbumsStatement = `
    CREATE TABLE Albums (
      SingerId     INT64 NOT NULL,
      AlbumId      INT64 NOT NULL,
      AlbumTitle   STRING(MAX)
    ) PRIMARY KEY (SingerId, AlbumId),
      INTERLEAVE IN PARENT Singers ON DELETE CASCADE`;

  // Default leader is one of the possible values in the leaderOptions field of the
  // instance config of the instance where the database is created.
  const setDefaultLeaderStatement = `
    ALTER DATABASE \`${databaseId}\`
    SET OPTIONS (default_leader = '${defaultLeader}')`;
  const [, operation] = await database.create({
    extraStatements: [
      createSingersTableStatement,
      createAlbumsStatement,
      setDefaultLeaderStatement,
    ],
  });

  console.log(`Waiting for creation of ${database.id} to complete...`);
  await operation.promise();
  console.log(
    `Created database ${databaseId} with default leader ${defaultLeader}.`
  );
}
createDatabaseWithDefaultLeader();

PHP

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

To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

View on GitHub Feedback

use Google\Cloud\Spanner\SpannerClient;

/**
 * Creates a database and tables for sample data.
 * Example:
 * ```
 * create_database($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function create_database(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);

    if (!$instance->exists()) {
        throw new \LogicException("Instance $instanceId does not exist");
    }

    $operation = $instance->createDatabase($databaseId, ['statements' => [
        'CREATE TABLE Singers (
            SingerId     INT64 NOT NULL,
            FirstName    STRING(1024),
            LastName     STRING(1024),
            SingerInfo   BYTES(MAX),
            FullName     STRING(2048) AS
            (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED
        ) PRIMARY KEY (SingerId)',
        'CREATE TABLE Albums (
            SingerId     INT64 NOT NULL,
            AlbumId      INT64 NOT NULL,
            AlbumTitle   STRING(MAX)
        ) PRIMARY KEY (SingerId, AlbumId),
        INTERLEAVE IN PARENT Singers ON DELETE CASCADE'
    ]]);

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

    printf('Created database %s on instance %s' . PHP_EOL,
        $databaseId, $instanceId);
}

Python

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

To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

View on GitHub Feedback

def create_database(instance_id, database_id):
    """Creates a database and tables for sample data."""
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)

    database = instance.database(
        database_id,
        ddl_statements=[
            """CREATE TABLE Singers (
            SingerId     INT64 NOT NULL,
            FirstName    STRING(1024),
            LastName     STRING(1024),
            SingerInfo   BYTES(MAX),
            FullName   STRING(2048) AS (
                ARRAY_TO_STRING([FirstName, LastName], " ")
            ) STORED
        ) PRIMARY KEY (SingerId)""",
            """CREATE TABLE Albums (
            SingerId     INT64 NOT NULL,
            AlbumId      INT64 NOT NULL,
            AlbumTitle   STRING(MAX)
        ) PRIMARY KEY (SingerId, AlbumId),
        INTERLEAVE IN PARENT Singers ON DELETE CASCADE""",
        ],
    )

    operation = database.create()

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

    print("Created database {} on instance {}".format(database_id, instance_id))

Ruby

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

To authenticate to Spanner, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

View on GitHub Feedback

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

require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"

database_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

instance_path = database_admin_client.instance_path project: project_id, instance: instance_id

job = database_admin_client.create_database parent: instance_path,
                                            create_statement: "CREATE DATABASE `#{database_id}`",
                                            extra_statements: [
                                              "CREATE TABLE Singers (
      SingerId     INT64 NOT NULL,
      FirstName    STRING(1024),
      LastName     STRING(1024),
      SingerInfo   BYTES(MAX)
    ) PRIMARY KEY (SingerId)",

                                              "CREATE TABLE Albums (
      SingerId     INT64 NOT NULL,
      AlbumId      INT64 NOT NULL,
      AlbumTitle   STRING(MAX)
    ) PRIMARY KEY (SingerId, AlbumId),
    INTERLEAVE IN PARENT Singers ON DELETE CASCADE"
                                            ]

puts "Waiting for create database operation to complete"

job.wait_until_done!

puts "Created database #{database_id} on instance #{instance_id}"

Update database schema or options

You can update your database schema and options using DDL statements.

For example, to add a column to a table, use the following DDL statement:

GoogleSQL

ALTER TABLE Songwriters ADD COLUMN Publisher STRING(10);

PostgreSQL

ALTER TABLE Songwriters ADD COLUMN Publisher VARCHAR(10);

To update the query optimizer version, use the following DDL statement:

GoogleSQL

ALTER DATABASE Music SET OPTIONS(optimizer_version=null);

PostgreSQL

ALTER DATABASE DB_NAME SET spanner.optimizer_version TO DEFAULT;

For more information about supported options, refer to the ALTER DATABASE DDL reference for GoogleSQL or PostgreSQL.

For information about schema updates, see Make schema updates.

Console

1. Go to the Instances page in the Google Cloud console.

   Instances

2. Select the instance containing the database to alter.

3. Select the database.

4. Click Write DDL.

5. Enter the DDL statements to apply. Use the DDL templates to pre-fill common statements.

6. Click Submit to apply the updates. If there are errors in your DDL, the Google Cloud console returns an error and the database is not altered.

gcloud

To alter a database with the gcloud command-line tool, use gcloud spanner databases ddl update.

gcloud spanner databases ddl update \
(DATABASE : --instance=INSTANCE) \
[--async] \
[--ddl=DDL] \
[--ddl-file=DDL_FILE] \

Refer to the gcloud reference for details about the available options.

Pass the database updates to the command with either the --ddl flag, or the --ddl-file flag. If a DDL file is specified, the --ddl flag is ignored.

Refer to the ALTER DATABASE DDL reference for GoogleSQL or PostgreSQL for the DDL statements to include.

DDL

Refer to the ALTER DATABASE DDL reference for GoogleSQL or PostgreSQL for details.

Delete a database

Deleting a database permanently removes the database and all its data. Database deletion can't be undone. If database deletion protection is enabled on a database, you can't delete that database until you disable its deletion protection.

Existing backups are not deleted when a database is deleted. For more information, see Backup and restore.

Console

1. Go to the Instances page in the Google Cloud console.

   Instances

2. Select the instance containing the database to delete.

3. Select the database.

4. Click Delete database. A confirmation appears.

5. Type the database name and click Delete.

gcloud

To delete a database with the gcloud command-line tool, use gcloud spanner databases delete.

gcloud spanner databases delete \
  (DATABASE : --instance=INSTANCE)

The following options are required:

DATABASE

ID of the database or fully qualified identifier for the database. If the fully qualified identifier is provided, the --instance flag should be omitted.

--instance=INSTANCE

The Cloud Spanner instance for the database.

For more details refer to the gcloud reference.

DDL

DDL does not support database deletion syntax.

More information

• Learn more about GoogleSQL DDL reference.
• Learn more about PostgreSQL DDL reference.
• Learn how to backup and restore a database.
• Learn how to prevent accidental database deletion.
• Learn how to make schema updates.