GCP Spanner can't set DatabaseDialect while creating a database

Question

I have Python method to create a database in GCP's Spanner which in I want to set the database dialect to PostgreSql:

from google.cloud import spanner
from google.cloud.spanner_admin_database_v1.types import spanner_database_admin, DatabaseDialect

def create_database(instance_id, database_id, extra_statements=None, database_dialect=DatabaseDialect.POSTGRESQL.value):
    """Create a new database"""
    if extra_statements is None:
        extra_statements = []
    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.CreateDatabaseRequest(
        parent=database_admin_api.instance_path(
            spanner_client.project, instance_id
        ),
        create_statement=f"CREATE DATABASE `{database_id}`",
        extra_statements=extra_statements,
        database_dialect=database_dialect,
    )

    operation = database_admin_api.create_database(request=request)
    database = operation.result(OPERATION_TIMEOUT_SECONDS)

But no matter what value I set for database_dialect parameter, I always get this error:

Traceback (most recent call last): File "/home/ghasem/dayrize-cloud/.venv/lib/python3.10/site-packages/google/api_core/grpc_helpers.py", line 76, in error_remapped_callable return callable_(*args, **kwargs) File "/home/ghasem/dayrize-cloud/.venv/lib/python3.10/site-packages/grpc/_channel.py", line 1161, in call return _end_unary_response_blocking(state, call, False, None) File "/home/ghasem/dayrize-cloud/.venv/lib/python3.10/site-packages/grpc/_channel.py", line 1004, in _end_unary_response_blocking raise _InactiveRpcError(state) # pytype: disable=not-instantiable grpc._channel._InactiveRpcError: <_InactiveRpcError of RPC that terminated with: status = StatusCode.INVALID_ARGUMENT details = "Invalid create statement. Database ids should be 2-30 characters long, contain only lowercase letters, numbers, underscores or hyphens, start with a letter and cannot end with an underscore or hyphen. Example of valid create statement: CREATE DATABASE "my-database"" debug_error_string = "UNKNOWN:Error received from peer ipv4:142.250.145.95:443 {grpc_message:"Invalid create statement. Database ids should be 2-30 characters long, contain only lowercase letters, numbers, underscores or hyphens, start with a letter and cannot end with an underscore or hyphen. Example of valid create statement: CREATE DATABASE "my-database"", grpc_status:3, created_time:"2024-04-11T15:00:12.70067553+00:00"}"

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "", line 1, in File "/home/ghasem/dayrize-cloud/dayrize-backend/src/dayrize_backend/helper/spanner.py", line 31, in create_database operation = database_admin_api.create_database(request=request) File "/home/ghasem/dayrize-cloud/.venv/lib/python3.10/site-packages/google/cloud/spanner_admin_database_v1/services/database_admin/client.py", line 821, in create_database response = rpc( File "/home/ghasem/dayrize-cloud/.venv/lib/python3.10/site-packages/google/api_core/gapic_v1/method.py", line 131, in call return wrapped_func(*args, **kwargs) File "/home/ghasem/dayrize-cloud/.venv/lib/python3.10/site-packages/google/api_core/timeout.py", line 120, in func_with_timeout return func(*args, **kwargs) File "/home/ghasem/dayrize-cloud/.venv/lib/python3.10/site-packages/google/api_core/grpc_helpers.py", line 78, in error_remapped_callable raise exceptions.from_grpc_error(exc) from exc google.api_core.exceptions.InvalidArgument: 400 Invalid create statement. Database ids should be 2-30 characters long, contain only lowercase letters, numbers, underscores or hyphens, start with a letter and cannot end with an underscore or hyphen. Example of valid create statement: CREATE DATABASE "my-database" [links {
description: "The rules of Cloud Spanner database IDs." url: "https://cloud.google.com/spanner/docs/data-definition-language#database-id-names" } ]

These are the values I set for database_dialect based on the documentations:

database_dialect=DatabaseDialect.POSTGRESQL.value
database_dialect=DatabaseDialect.POSTGRESQL
database_dialect=2

I know the database name is fine as if I remove the database_dialect from the statement, it will create the database without any problem.

What am I missing here?

Answer 1

The problem is that you are using GoogleSQL style quoting for the database name. Change this to the following:

OLD: create_statement=f"CREATE DATABASE `{database_id}`"
NEW: create_statement=f"CREATE DATABASE \"{database_id}\"",

(Note the double quotes instead of backticks!)

GoogleSQL uses backticks (``) to quote identifiers. PostgreSQL uses double quotes ("").