Google Cloud Spanner dialect for SQLAlchemy is generally available
Google Cloud recently announced the general availability of Spanner dialect enabling SQLAlchemy applications to run on the SQL distributed database management and storage service.
Used by companies like drop box 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 mapping model, where classes can be mapped to the database in a decoupled way, keeping the underlying SQL and object relational details transparent. .
Built above the Spanner Database API, the Cloud Spanner dialect for SQLAlchemy is a open-source project and supports migration through Still. Shanika Kuruppu, a senior software engineer at Google, warns:
A migration script can produce many DDL statements, and if each statement is executed separately, the migration will be slow. Therefore, it is highly recommended to use the Alembic batch context function to group DDL statements into statement groups.
Cloud Spanner supports interleaved tables, generated columns, query hints, and stale reads: all read-only transactions are strong reads by default, but it is possible to perform a stale read using ‘a explicit timestamp bound. To improve performance, Kuruppu suggests:
Use explicit connections and reuse the connection in multiple queries. It is also possible to use an implicit connection but this is not recommended because the dialect will then have to establish a new connection to the database for each Connection.execute() to call.
Among the limitations of running SQLAlchemy on Cloud Spanner, the distributed database does not support creating unique constraints, does not allow DDL statements within a transaction, and does not support mutations. All features and limitations, including no auto-increment ID, are described on the GitHub page.
The cloud provider also announced the general availability of Ruby Active Record Adapter for Google Cloud Spanner, an ORM library that comes with Ruby on Rails. Pritam Shah, Director of Engineering at Google, Explain the importance of supporting different ORM frameworks:
We want to democratize Spanner – we want to make it accessible to all developers (not just big companies). For Ruby and Python developers, we released Cloud Spanner Adapter for Active Record and Spanner Dialect for SQLAlchemy.