GCP Spanner can't set DatabaseDialect while creating a database

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"}"

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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&quot;
} ]

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?

>Solution :

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 ("").