How to properly setup a multi-tenanted app on Google App Engine and Google Cloud SQL

Question

Google App Engine work fine in terms of a multi tenanted application by employing namespaces. However when you use Cloud SQL as the data persistence mechanism you start to run into issues. Specifically how would one go about connecting different databases to each namespace. Currently, the database that the entire application is using is set on the instance configuration so its pretty much hard coded into the deployment from what I can surmise.

I do realise that the exact architecture behind a multi tenanted application differs with business needs (or at least should be if it is to scale usefully), however I would be happy with some generalized solutions that specifically address building a multi tenanted application with Google Cloud SQL.

I am trying to avoid making the multi tenancy column based (unless someone can explain why the very nature of it being cloud sql negates the need for me to worry about everything being in one monolithic database). The ideal scenario, imho, would be to have one application using the namespace api attaching to different databases (all sharing the same schema) and some sort of mechanism to synchronize list data across all database tenants.

Other ideas we have had are:

1. Each tenant has their own application and therefore hardcoded database.. with the problem of synchronizing list data (publisher / broadcast, proxy?).
2. Each tenant has their own version and therefore hardcoded database.. use datastore for list data.
3. One application, One database, prefixed table names based on namespace (but we cannot figure out how to get this to work with JPA dynamically)

We are also not fully adverse to using another compatible cloud sql provider.

Finally, you may ask why use SQL at all. The single reason being the ability for more than one application to be able to read and write from it at the same time, something the datastore does not allow.

Answer 1

This shouldn't be a problem. The database that an application uses can be specified in the JDBC connection URL, so can be defined at runtime for each application:

c = DriverManager.getConnection("jdbc:google:rdbms://my_instance/my_database");