1

I am trying to set up Alembic to work with Google Cloud Spanner, but no matter what I try I end up with one of the following errors when attempting to run a simple "alembic upgrade head":

  • sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:spanner
  • KeyError: 'spanner+spanner'

I have tried so far:

In alembic.ini for sqlalchemy.url:

spanner:///projects/<project>/instances/<instance>/databases/<db>
spanner+spanner:///projects/<project>/instances/<instance>/databases/<db>

In env.py:

config.set_main_option("sqlalchemy.url", "spanner:///projects/<project>/instances/<instance>/databases/<db>")
config.set_main_option("sqlalchemy.url", 
"spanner+spanner:///projects/<project>/instances/<instance>/databases/<db>")

I have tried with latest versions of SqlAlchemy/Alembic/SqlAlchemy-Spanner, as well as earlier versions (sqlalchemy 1.3 and matching versions of the others)

I have tried using sqlalchemy-spanner from PyPi and from GitHub (git+https://github.com/googleapis/python-spanner-sqlalchemy.git@v1.2.0#egg=sqlalchemy-spanner)

There is no code being executed so far, just trying to get Alembic to be able to connect to the instance. I have all required permissions (project owner).

From what I can see, it appears that the spanner dialect for SqlAlchemy isn't installed properly. I have tried to start a new project from scratch to elimiate old installations messing with my setup but no luck.

I've even tried to register the dialect manually through env.py with:

registry.register("spanner.spanner", "google.cloud.sqlalchemy_spanner", "SpannerDialect")

I'm by no means a Python expert (worked with Python for one year now after doing .Net/c# for 10 years), but I'm running out of ideas here. Any help would be highly appreciated.

MacBlimp
  • 11
  • 1

1 Answers1

2

I suppose the issue is solved by a talk with Google Cloud Spanner team. In case if not, and to summarize, here is the explanation.

sqlalchemy_spanner now supports SQLAlchemy 1.3 and 1.4 versions, which are using different styles of connection URLs. It's described in the docs.

If sqlalchemy_spanner was installed by pip install sqlalchemy_spanner, the dialect should be registered correctly. If the package was installed from the GitHub repo, one should manually run the package setup.py file to register the dialect.

When one is working with Alembic, connection URLs and the dialect name still have to follow the style of one's SQLAlchemy version. That means in env.py (see an example in the repo) and in alembic.ini:

[alembic]
sqlalchemy.url = spanner+spanner:///projects/{project}...

Thus, if a similar error is failing, there are three places to check first: connection URLs and Alembic files env.py and alembic.ini - all must use the same dialect id to make it all work.

Ilya Gurov
  • 46
  • 4
  • Thanks for the reply! I eventually got it working (pretty much with the same code as in your examples). When it comes to the actual Alembic migrations, these are incredibly slow (20+ sec to create a table, and up to 120 secs or more to create indexes or foreign keys). Is this normal? We are talking empty tables with less than 10 columns each, in a fresh spanner instance. – MacBlimp Sep 12 '22 at 07:37
  • No, that's not how it usually works. Right now I have a project which performs operations lighting fast. Maybe something related to a regional datacenter? Don't you know where your Spanner instance is regionally located? See the docs for more details: https://cloud.google.com/spanner/docs/instance-configurations – Ilya Gurov Sep 13 '22 at 06:41