Skip to content

Commit 6431932

Browse files
sakthivelmaniisakthivelmanii
authored
feat(sqlalchemy-spanner): native UUID data type support in dialect (googleapis#17913)
Adds native Spanner UUID type support to SQLAlchemy dialect: - Registers `"UUID": types.UUID` in `_type_map` for reflection and `_type_map_inv`. - `types.UUID` (all-caps) always emits native `UUID` DDL. - `types.Uuid` (CamelCase) defaults to emitting `STRING(36)` for backward compatibility with legacy string-backed UUID columns. - **Native UUID Flag:** To enable native `UUID` DDL for generic `types.Uuid` columns, set `supports_native_uuid = True` on the dialect: ```python engine = create_engine(...) engine.dialect.supports_native_uuid = True
1 parent 2d1372b commit 6431932

3 files changed

Lines changed: 479 additions & 0 deletions

File tree

‎packages/sqlalchemy-spanner/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,9 @@ def process(value):
123123
"TOKENLIST": types.String,
124124
}
125125

126+
if hasattr(types, "UUID"):
127+
_type_map["UUID"] = types.UUID
128+
126129

127130
_type_map_inv = {
128131
types.Boolean: "BOOL",
@@ -141,6 +144,12 @@ def process(value):
141144
types.NullType: "INT64",
142145
}
143146

147+
if hasattr(types, "UUID"):
148+
_type_map_inv[types.UUID] = "UUID"
149+
150+
if hasattr(types, "Uuid"):
151+
_type_map_inv[types.Uuid] = "UUID"
152+
144153
_compound_keywords = {
145154
selectable.CompoundSelect.UNION: "UNION DISTINCT",
146155
selectable.CompoundSelect.UNION_ALL: "UNION ALL",
@@ -765,6 +774,12 @@ class SpannerTypeCompiler(GenericTypeCompiler):
765774
Maps SQLAlchemy types to Spanner data types.
766775
"""
767776

777+
def visit_uuid(self, type_, **kw):
778+
if not type_.native_uuid or not self.dialect.supports_native_uuid:
779+
return self.visit_CHAR(types.CHAR(36), **kw)
780+
else:
781+
return self.visit_UUID(type_, **kw)
782+
768783
def visit_INTEGER(self, type_, **kw):
769784
return "INT64"
770785

@@ -847,6 +862,7 @@ class SpannerDialect(DefaultDialect):
847862
supports_identity_columns = True
848863
supports_native_boolean = True
849864
supports_native_decimal = True
865+
supports_native_uuid = False
850866
supports_statement_cache = True
851867
# Spanner uses protos for enums. Creating a column like
852868
# Column("an_enum", Enum("A", "B", "C")) will result in a String

‎packages/sqlalchemy-spanner/samples/uuid_sample.py‎

Lines changed: 187 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,187 @@
1+
# Copyright 2026 Google LLC All rights reserved.
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
from __future__ import annotations
16+
17+
from uuid import UUID, uuid4
18+
19+
from sample_helper import run_sample
20+
from sqlalchemy import create_engine, select, types
21+
from sqlalchemy.dialects import registry
22+
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
23+
24+
registry.register(
25+
"spanner",
26+
"google.cloud.sqlalchemy_spanner.sqlalchemy_spanner",
27+
"SpannerDialect",
28+
)
29+
30+
31+
class Base(DeclarativeBase):
32+
pass
33+
34+
35+
# -----------------------------------------------------------------------------
36+
# GUIDELINES FOR USERS / CUSTOMERS:
37+
#
38+
# 1. types.Uuid() (CamelCase - Recommended):
39+
# - supports_native_uuid = False (default): Compiles to `STRING(36)`.
40+
# - supports_native_uuid = True: Compiles to `UUID`.
41+
# - Works seamlessly for inserts and queries across both configurations.
42+
#
43+
# 2. types.UUID() (All-Caps - Native Spanner Type):
44+
# - IMPORTANT: Use `types.UUID()` ONLY when `supports_native_uuid = True`
45+
# is enabled on your dialect/engine for native Cloud Spanner UUID columns.
46+
#
47+
# 3. types.Uuid(native_uuid=False) (Explicit Override):
48+
# - Forces `STRING(36)` storage even when `supports_native_uuid = True`.
49+
# -----------------------------------------------------------------------------
50+
51+
52+
class CustomerLegacy(Base):
53+
"""Uses types.Uuid() -> Compiles to STRING(36) by default."""
54+
55+
__tablename__ = "customers_legacy"
56+
id: Mapped[UUID] = mapped_column(types.Uuid(), primary_key=True, default=uuid4)
57+
name: Mapped[str] = mapped_column()
58+
59+
60+
class CustomerNative(Base):
61+
"""Uses types.UUID() -> ALWAYS compiles to native UUID type in Spanner DDL,
62+
63+
even when supports_native_uuid=False.
64+
"""
65+
66+
__tablename__ = "customers_native"
67+
id: Mapped[UUID] = mapped_column(types.UUID(), primary_key=True, default=uuid4)
68+
name: Mapped[str] = mapped_column()
69+
70+
71+
class CustomerExplicitString(Base):
72+
"""Uses types.Uuid(native_uuid=False) -> ALWAYS compiles to STRING(36)."""
73+
74+
__tablename__ = "customers_explicit_string"
75+
id: Mapped[UUID] = mapped_column(
76+
types.Uuid(native_uuid=False),
77+
primary_key=True,
78+
default=uuid4,
79+
)
80+
name: Mapped[str] = mapped_column()
81+
82+
83+
def run_with_flag_disabled():
84+
"""Scenario 1: supports_native_uuid = False (Default).
85+
86+
- types.Uuid() emits STRING(36) in DDL.
87+
- types.Uuid(native_uuid=False) emits STRING(36) in DDL.
88+
"""
89+
sep = "=" * 73
90+
print(f"\n{sep}")
91+
print("SCENARIO 1: Flag Disabled (supports_native_uuid=False)")
92+
print(sep)
93+
engine = create_engine(
94+
"spanner:///projects/sample-project/"
95+
"instances/sample-instance/"
96+
"databases/sample-database",
97+
echo=True,
98+
)
99+
engine.dialect.supports_native_uuid = False
100+
Base.metadata.create_all(engine)
101+
102+
with Session(engine) as session:
103+
cust_legacy = CustomerLegacy(name="Alice_Legacy_FlagDisabled")
104+
cust_override = CustomerExplicitString(name="Diana_String_FlagDisabled")
105+
session.add_all([cust_legacy, cust_override])
106+
session.commit()
107+
108+
fetched_legacy = session.scalars(
109+
select(CustomerLegacy).filter_by(id=cust_legacy.id)
110+
).one()
111+
print(
112+
"--> [Flag Disabled] Legacy Customer ID "
113+
f"({type(fetched_legacy.id).__name__}): {fetched_legacy.id}"
114+
)
115+
116+
fetched_override = session.scalars(
117+
select(CustomerExplicitString).filter_by(id=cust_override.id)
118+
).one()
119+
print(
120+
"--> [Flag Disabled] Explicit STRING Customer ID "
121+
f"({type(fetched_override.id).__name__}): {fetched_override.id}"
122+
)
123+
124+
125+
def run_with_flag_enabled():
126+
"""Scenario 2: supports_native_uuid = True.
127+
128+
- types.Uuid() emits UUID in DDL when flag enabled.
129+
- types.UUID() ALWAYS emits UUID in DDL.
130+
- types.Uuid(native_uuid=False) explicitly emits STRING(36).
131+
"""
132+
sep = "=" * 73
133+
print(f"\n{sep}")
134+
print("SCENARIO 2: Flag Enabled (supports_native_uuid=True)")
135+
print(sep)
136+
engine = create_engine(
137+
"spanner:///projects/sample-project/"
138+
"instances/sample-instance/"
139+
"databases/sample-database",
140+
echo=True,
141+
)
142+
engine.dialect.supports_native_uuid = True
143+
Base.metadata.create_all(engine)
144+
145+
with Session(engine) as session:
146+
cust_legacy = CustomerLegacy(name="Charlie_Legacy_FlagEnabled")
147+
cust_native = CustomerNative(name="Bob_Native_FlagEnabled")
148+
cust_override = CustomerExplicitString(name="Diana_String_FlagEnabled")
149+
session.add_all([cust_legacy, cust_native, cust_override])
150+
session.commit()
151+
152+
fetched_legacy = session.scalars(
153+
select(CustomerLegacy).filter_by(id=cust_legacy.id)
154+
).one()
155+
print(
156+
"--> [Flag Enabled] Legacy Customer ID "
157+
f"({type(fetched_legacy.id).__name__}): {fetched_legacy.id}"
158+
)
159+
160+
fetched_native = session.scalars(
161+
select(CustomerNative).filter_by(id=cust_native.id)
162+
).one()
163+
print(
164+
"--> [Flag Enabled] Native Customer ID "
165+
f"({type(fetched_native.id).__name__}): {fetched_native.id}"
166+
)
167+
168+
fetched_override = session.scalars(
169+
select(CustomerExplicitString).filter_by(id=cust_override.id)
170+
).one()
171+
print(
172+
"--> [Flag Enabled] Explicit STRING Customer ID "
173+
f"({type(fetched_override.id).__name__}): {fetched_override.id}"
174+
)
175+
176+
177+
def uuid_sample():
178+
"""Demonstrates how to use types.Uuid vs types.UUID with Spanner SQLAlchemy
179+
180+
under both supports_native_uuid=False and supports_native_uuid=True.
181+
"""
182+
run_with_flag_disabled()
183+
run_with_flag_enabled()
184+
185+
186+
if __name__ == "__main__":
187+
run_sample(uuid_sample)

0 commit comments

Comments
 (0)