Is your feature request related to a problem? Please describe.
Autoincrementing serogate integer surrogate IDs are common among databases over the decades, and strongly recommended against within Cloud Spanner (https://cloud.google.com/spanner/docs/whitepapers/optimizing-schema-design [1]). I realize this is not an optimal solution, but it is a very compatible solution when schema migration is difficult for workloads with low latency / throughput requirements (this is python afterall).
Cloud spanner recommends a lot of application side boilerplate for generating autoincrementing IDs: https://cloud.google.com/solutions/sequence-generation-in-cloud-spanner#sequence_generators_using_database_table_rows [2]. TL;DR: Select the highest ID, increment, and insert all within a transaction is the simplest, most robust, and slowest option. I predict a lot of users migrating workloads to spanner will run into this issue and have to write their own code.
Describe the solution you'd like
sqlalchemy supports sa.Column('col1', sa.Integer, autoincrement=True), but this is ignored by python-spanner-sqlalchemy (and an Error is raised during insertion that 'col1' was not supplied). Another option is sa.Column('col1', sa.Integer, sa.Sequence('col1_auto_inc')), but table creation fails for lack of available sequences
Note: Both these tests were performed on the spanner emulator, not the actual thing. These might be emulator errors?
I'd like python-spanner-sqlalchemy to support either of the above solutions, or something similar.
Describe alternatives you've considered
The workaround is using a transaction, selecting the highest id, incrementing, and inserting within the same transaction (as listed in [2]). This requires reinventing the wheel for every insert for every user and application. It's not horrible, but it seems like adding it to the library would be ergonomic
Is your feature request related to a problem? Please describe.
Autoincrementing serogate integer surrogate IDs are common among databases over the decades, and strongly recommended against within Cloud Spanner (https://cloud.google.com/spanner/docs/whitepapers/optimizing-schema-design [1]). I realize this is not an optimal solution, but it is a very compatible solution when schema migration is difficult for workloads with low latency / throughput requirements (this is python afterall).
Cloud spanner recommends a lot of application side boilerplate for generating autoincrementing IDs: https://cloud.google.com/solutions/sequence-generation-in-cloud-spanner#sequence_generators_using_database_table_rows [2]. TL;DR: Select the highest ID, increment, and insert all within a transaction is the simplest, most robust, and slowest option. I predict a lot of users migrating workloads to spanner will run into this issue and have to write their own code.
Describe the solution you'd like
sqlalchemy supports
sa.Column('col1', sa.Integer, autoincrement=True), but this is ignored by python-spanner-sqlalchemy (and an Error is raised during insertion that 'col1' was not supplied). Another option issa.Column('col1', sa.Integer, sa.Sequence('col1_auto_inc')), but table creation fails for lack of available sequencesNote: Both these tests were performed on the spanner emulator, not the actual thing. These might be emulator errors?
I'd like python-spanner-sqlalchemy to support either of the above solutions, or something similar.
Describe alternatives you've considered
The workaround is using a transaction, selecting the highest id, incrementing, and inserting within the same transaction (as listed in [2]). This requires reinventing the wheel for every insert for every user and application. It's not horrible, but it seems like adding it to the library would be ergonomic