Google Cloud Spanner support

[amacneil]

Need to add support for Google Cloud Spanner

[AJCStriker]

Hey @amacneil I would be interested in picking this issue up and submitting a PR.

I took a look at the current drivers but is there anything to keep in mind whilst designing an integration?
In particular clarification about how you view Connection lifecycles would be really helpful?

[amacneil]

Last time I looked into this, I ran into the issue that dbmate has been designed around an assumption that all drivers will use database/sql, and the spanner driver did not use that. So it would require refactoring the driver interface to remove dependence on database/sql.

Long term I think this is a good direction to take dbmate, but just be aware that to achieve this feature I think you will need to refactor the interface and all other drivers to match the new interface, it won't be a simple case of adding one new driver.

From memory there also might be a problem with the spanner driver not accepting multiple sql statements in one string, so we will need to import a sql parsing library and do our own statement splitting. Again I think this is a nice feature that would benefit all drivers eventually (allowing us to give more helpful error messages, etc), but it adds complexity.

[mii9000]

any plans for this development? i am open to contribute if you can share the design on how it should be done

[dossy]

No plans, but it should be much easier now, as this suggests that there's a PostgreSQL client interface to Google Spanner:

https://cloud.google.com/spanner/docs/postgresql-interface

Developers can connect their applications to a PostgreSQL interface database using open source Spanner clients for Java, Go, Python, Node.js, Ruby, PHP, C#, and C++ environments. They can also use the open source Spanner JDBC driver and the driver for Go's database/sql package.

And, there's also a Google Cloud Spanner driver for Go's database/sql package.

https://github.com/googleapis/go-sql-spanner

I don't use and have no plans to use Google Cloud Spanner databases, but anyone who does who wants to work on support for it could discuss it here and see how much interest there is, I suppose.

[grahamhoyes]

Here's a PR with a first attempt at adding support for the postgres interface, following the details of my post below: #555

[grahamhoyes]

Spanner has two supported dialects at the moment: GoogleSQL and Postgres, the latter with some limitations and a smaller subset of supported functions. The dialect for a database is chosen at database creation, and can't be changed - so full-featured support would require separate support for both.

Supporting GoogleSQL would probably mean a new driver that uses https://github.com/googleapis/go-sql-spanner, and the queries would be different from the Postgres driver.

Supporting the Postgres interface through the PGAdapter should be relatively simple:

• The unnest(text[]) function used here isn't supported, so like in this PR that added redshift support we can do identifier quoting client-side
• Spanner doesn't allow migrations in explicit transactions. Setting transaction:false in the migrate file works, or we may want to default this to true (while still allowing setting transaction:true for data migrations and such)