Google Cloud recently announced the general availability of the Spanner dialect enabling SQLAlchemy applications to run on the distributed SQL database management and storage service.
Used by companies like DropBox and Reddit, SQLAlchemy is a Python SQL toolkit that gives application developers the ability to use SQL. It is known for its object-relational mapper (ORM), a component that provides the data mapper pattern, where classes can be mapped to the database in a decoupled way, keeping transparent the underlying SQL and object relational details.
Built on top of the Spanner DB API, the Cloud Spanner dialect for SQLAlchemy is an open-source project and supports migrations through Alembic. Shanika Kuruppu, senior software engineer at Google, warns:
A migration script can produce a lot of DDL statements, and if each statement is executed separately, the migration will be slow. Therefore, it is highly recommended that you use the Alembic batch context feature to pack DDL statements into groups of statements.
Cloud Spanner supports interleaved tables, generated columns, query hints and stale reads: all read-only transactions default to strong reads but it is possible to perform a stale read using an explicit timestamp bound. To improve performances, Kuruppu suggests:
Use explicit connections, and reuse the connection across multiple queries. It's also possible to use an implicit connection but this is not recommended as the dialect will then need to establish a new connection to the database for every Connection.execute() call.
Among the limitations of running SQLAlchemy on Cloud Spanner, the distributed database doesn't support the creation of unique constraints, doesn't allow DDL statements in a transaction and doesn't support mutations. All the features and limitations, including the lack of auto increment IDs, are described on the GitHub page.
The cloud provider announced as well the general availability of Ruby Active Record Adapter for Google Cloud Spanner, an ORM library bundled with Ruby on Rails. Pritam Shah, director of engineering at Google, explains the importance of supporting different ORM frameworks:
We want to democratize Spanner - we want to make it available to all developers (not just large enterprises). For the Ruby & Python developers, we launched Cloud Spanner Adapter for Active Record and Spanner dialect for SQLAlchemy.
The documentation of the Python client for Cloud Spanner and of SQLAlchemy 1.3 provide further details on how to use SQLAlchemy to query and modify Google Spanner data.
