Since a reproduction requires SQLAlchemy models and an Alembic config, I created a repository for simplicity.
to see the SQL query.
➜ spanner-reference git:(main) ✗ poetry run alembic revision --autogenerate -m "init"
INFO [alembic.runtime.migration] Context impl SpannerImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.schemas
INFO [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.tables
INFO [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.types
INFO [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.constraints
INFO [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.defaults
INFO [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.comments
SELECT col.table_schema, col.table_name, col.column_name,
col.spanner_type, col.is_nullable, col.generation_expression,
col.column_default
FROM information_schema.columns as col
JOIN information_schema.tables AS t
USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
WHERE
() AND
(t.table_type = 'BASE TABLE') AND
col.table_schema = '' AND
col.table_catalog = ''
ORDER BY
col.table_catalog,
col.table_schema,
col.table_name,
col.ordinal_position
Traceback (most recent call last):
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/grpc_helpers.py", line 149, in error_remapped_callable
return _StreamingResponseIterator(
result, prefetch_first_result=prefetch_first
)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/grpc_helpers.py", line 71, in __init__
self._stored_first_result = next(self._wrapped)
~~~~^^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/grpc/_channel.py", line 538, in __next__
return self._next()
~~~~~~~~~~^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/grpc/_channel.py", line 956, in _next
raise self
grpc._channel._MultiThreadedRendezvous: <_MultiThreadedRendezvous of RPC that terminated with:
status = StatusCode.INVALID_ARGUMENT
details = "Syntax error: Unexpected \")\" [at 9:18]\n () AND \n ^"
debug_error_string = "INVALID_ARGUMENT:Syntax error: Unexpected \")\" [at 9:18]\n () AND \n ^"
>
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/bin/alembic", line 8, in <module>
sys.exit(main())
~~~~^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/config.py", line 1047, in main
CommandLine(prog=prog).main(argv=argv)
~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/config.py", line 1037, in main
self.run_cmd(cfg, options)
~~~~~~~~~~~~^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/config.py", line 971, in run_cmd
fn(
~~^
config,
^^^^^^^
*[getattr(options, k, None) for k in positional],
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
**{k: getattr(options, k, None) for k in kwarg},
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/command.py", line 309, in revision
script_directory.run_env()
~~~~~~~~~~~~~~~~~~~~~~~~^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/script/base.py", line 545, in run_env
util.load_python_file(self.dir, "env.py")
~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/util/pyfiles.py", line 116, in load_python_file
module = load_module_py(module_id, path)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/util/pyfiles.py", line 136, in load_module_py
spec.loader.exec_module(module) # type: ignore
~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^
File "<frozen importlib._bootstrap_external>", line 762, in exec_module
File "<frozen importlib._bootstrap>", line 491, in _call_with_frames_removed
File "/Users/busunkim/dev/spanner-reference/src/migrations/env.py", line 94, in <module>
run_migrations_online()
~~~~~~~~~~~~~~~~~~~~~^^
File "/Users/busunkim/dev/spanner-reference/src/migrations/env.py", line 88, in run_migrations_online
context.run_migrations()
~~~~~~~~~~~~~~~~~~~~~~^^
File "<string>", line 8, in run_migrations
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/runtime/environment.py", line 969, in run_migrations
self.get_context().run_migrations(**kw)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/runtime/migration.py", line 614, in run_migrations
for step in self._migrations_fn(heads, self):
~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/command.py", line 285, in retrieve_migrations
revision_context.run_autogenerate(rev, context)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/api.py", line 587, in run_autogenerate
self._run_environment(rev, migration_context, True)
~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/api.py", line 634, in _run_environment
compare._populate_migration_script(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
autogen_context, migration_script
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/__init__.py", line 39, in _populate_migration_script
_produce_net_changes(autogen_context, upgrade_ops)
~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/__init__.py", line 48, in _produce_net_changes
autogen_context.comparators.dispatch(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"autogenerate", qualifier=autogen_context.dialect.name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
)(autogen_context, upgrade_ops)
~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/util/langhelpers.py", line 420, in go
result = fn(*arg, **kw)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/schema.py", line 51, in _produce_net_changes
autogen_context.comparators.dispatch(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"schema", qualifier=autogen_context.dialect.name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
)(autogen_context, upgrade_ops, schemas)
~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/util/langhelpers.py", line 420, in go
result = fn(*arg, **kw)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/tables.py", line 69, in _autogen_for_tables
insp.pre_cache_tables(schema_name, tablenames, available)
~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/util.py", line 275, in pre_cache_tables
self._pre_cache(
~~~~~~~~~~~~~~~^
schema,
^^^^^^^
...<3 lines>...
meth,
^^^^^
)
^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/util.py", line 169, in _pre_cache
elements = inspector_method(
schema=schema, filter_names=optimized_filter_names
)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/sqlalchemy/engine/reflection.py", line 933, in get_multi_columns
self.dialect.get_multi_columns(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
conn,
^^^^^
...<5 lines>...
**kw,
^^^^^
)
^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 185, in wrapper
return function(self, connection, *args, **kwargs)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 1152, in get_multi_columns
columns = list(snap.execute_sql(sql))
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/streamed.py", line 177, in __iter__
self._consume_next()
~~~~~~~~~~~~~~~~~~^^
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/streamed.py", line 154, in _consume_next
response = self._response_iterator.__next__()
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/snapshot.py", line 179, in _restart_on_unavailable
raise _augment_error_with_request_id(exc, current_request_id)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/snapshot.py", line 135, in _restart_on_unavailable
iterator = CrossSync._Sync_Impl.run_if_async(
method, request=request, metadata=call_metadata
)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/aio/_cross_sync/cross_sync.py", line 336, in run_if_async
return func(*args, **kwargs)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/services/spanner/client.py", line 1540, in execute_streaming_sql
response = rpc(
request,
...<2 lines>...
metadata=metadata,
)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/gapic_v1/method.py", line 128, in __call__
return wrapped_func(*args, **kwargs)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/timeout.py", line 130, in func_with_timeout
return func(*args, **kwargs)
File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/grpc_helpers.py", line 153, in error_remapped_callable
raise exceptions.from_grpc_error(exc) from exc
google.api_core.exceptions.InvalidArgument: 400 Syntax error: Unexpected \")\" [at 9:18]\n () AND \n ^, request_id = 1.10489449469928568989.1.1.6.1 [locale: "en-US"
message: "Syntax error: Unexpected \")\" [at 9:18]\n () AND \n ^"
]
Failed to export metrics to Cloud Monitoring: 400 One or more TimeSeries could not be written: timeSeries[8] (metric.type="spanner.googleapis.com/internal/client/operation_latencies", metric.labels={"client_name": "spanner-python/3.68.0", "status": "", "client_uid": "670026d9-5e8e-4f3c-ac19-f983e7c17a1d@76524@Bus-MacBook-Pro.local"}, resource.type="spanner_instance_client", resource.labels={"location": "global", "instance_config": "unknown", "client_hash": "00023f"}): the set of resource labels is incomplete, missing (instance_id); timeSeries[4] (metric.type="spanner.googleapis.com/internal/client/operation_count", metric.labels={"client_uid": "670026d9-5e8e-4f3c-ac19-f983e7c17a1d@76524@Bus-MacBook-Pro.local", "client_name": "spanner-python/3.68.0", "status": ""}, resource.type="spanner_instance_client", resource.labels={"client_hash": "00023f", "instance_config": "unknown", "location": "global"}): the set of resource labels is incomplete, missing (instance_id); timeSeries[12] (metric.type="spanner.googleapis.com/internal/client/attempt_count", metric.labels={"client_name": "spanner-python/3.68.0", "client_uid": "670026d9-5e8e-4f3c-ac19-f983e7c17a1d@76524@Bus-MacBook-Pro.local"}, resource.type="spanner_instance_client", resource.labels={"client_hash": "00023f", "instance_config": "unknown", "location": "global"}): the set of resource labels is incomplete, missing (instance_id) [type_url: "type.googleapis.com/google.monitoring.v3.CreateTimeSeriesSummary"
value: "\010\017\020\014\032\006\n\002\010\003\020\003"
]
As we discussed, there are two issues chained together. First, the dialect fails with a syntax error when inspecting a completely empty database. We can bypass this by ensuring a table exists. However, when inspecting that existing table, the dialect currently omits the "name" tag in the Primary Key dictionary. By placing a small snippet of Python code in your Alembic setup file, we can intercept the dialect's response on the fly, inject the missing "name": None tag, and deliver the corrected dictionary to Alembic so it can proceed without crashing.
Step-by-Step Implementation:
Step 1: Bypass the initial empty-database Syntax Error
For your very first migration, the database cannot be completely empty, or you will hit the initial WHERE () AND syntax error.
Manually create a temporary table in your Spanner database via your Cloud Console (e.g., CREATE TABLE dummy_table (id INT64) PRIMARY KEY(id);).
Step 2: Apply the patch in env.py
Now that the database is not empty, Alembic will try to read the dummy_table and will hit the KeyError. To fix this on the fly, open your env.py file. Right at the top, immediately after your initial import statements and before the config = context.config line, please paste the following code block:
# --- sqlalchemy-spanner workaround for KeyError: 'name' ---
from google.cloud.sqlalchemy_spanner.sqlalchemy_spanner import SpannerDialect
original_get_multi_pk = SpannerDialect.get_multi_pk_constraint
def patched_get_multi_pk_constraint(self, connection, **kw):
"""Intercepts the PK constraint dictionary to inject the missing 'name' key."""
result = original_get_multi_pk(self, connection, **kw)
for key, pk_dict in result.items():
if "name" not in pk_dict:
pk_dict["name"] = None
return result
SpannerDialect.get_multi_pk_constraint = patched_get_multi_pk_constraint
# --- End of workaround ---
Determine this is the right repository
Summary of the issue
Context
I attempted to use sqlalchemy-spanner with alembic (as suggested in sqlalchemy-spanner's README) to generate database migrations.
Expected Behavior:
I expect
alembic revision --autogenerate -m initto run without error against a new Spanner DB in the Spanner emulator and for a deployed instance.Actual Behavior:
Migration fails to generate.
API client name and version
sqlalchemy-spanner 1.19.0
Reproduction steps: code
Since a reproduction requires SQLAlchemy models and an Alembic config, I created a repository for simplicity.
https://github.com/cdco-busunkim/spanner-sqlalchemy-issue/tree/main
poetry run alembic --autogenerate -m initReproduction steps: supporting files
N/A, see above
Reproduction steps: actual results
NOTE: I manually inserted a
printstatement in the sqlalchemy-spanner code before this linegoogle-cloud-python/packages/sqlalchemy-spanner/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py
Line 1151 in 1f93a2b
Reproduction steps: expected results
Successful Alembic migration with a new file in
migrations/that can be applied throughalembic upgrade headOS & version + platform
macOS Tahoe 26.5.1
Python environment
3.14.6
Python dependencies
Additional context
I also have an active Google Cloud support ticket, but was instructed to also open an issue in this repository. The support engineer was able to reproduce the issue and provided some workarounds. I've included those in a separate branch https://github.com/cdco-busunkim/spanner-sqlalchemy-issue/tree/with-workarounds. The diff is in cdco-busunkim/spanner-sqlalchemy-issue#1.
Here is the analysis from the support engineer about the two issues discovered: