Add support for mapping multiple applications to multiple databases/branches

[fatih]

A PlanetScale customer might run one or more applications (M) and might want to connect to one or more databases(N). If we also introduce the branches (O) feature, there will be a mapping of M:N:O. We need to create a simple to use mental concept of mapping applications to databases (and branches).

Google's sql_cloud_proxy does provide a mapping of N:M (applications to databases) by running the client proxy with a comma-separated list of instances. Here is the bit from the help page:

Connection:
  -instances
    To connect to a specific list of instances, set the instances parameter
    to a comma-separated list of instance connection strings. For example:

           -instances=my-project:my-region:my-instance

    For connectivity over TCP, you must specify a tcp port as part of the
    instance string. For example, the following example opens a loopback TCP
    socket on port 3306, which will be proxied to connect to the instance
    'my-instance' in project 'my-project':

            -instances=my-project:my-region:my-instance=tcp:3306

Some usage examples:

# proxies all conections from localhost:3306 to the remote database `db-id-1`
$ cloud_sql_proxy -instances=myproject:us-central1:db-id-1=tcp:3306

# proxies all conections from localhost:3306 to the remote database `db-id-1` and `db-id-2`
$ cloud_sql_proxy -instances=myproject:us-central1:db-id-1=tcp:3306,myproject:us-central1:db-id-2=tcp:3306

# proxies all conections from localhost:3306 to the remote database `db-id-1` 
# and from localhost:3307 to remote database `db-id-2`
$ cloud_sql_proxy -instances=myproject:us-central1:db-id-1=tcp:3306,myproject:us-central1:db-id-2=tcp:3307

If no --instances is provided, it triggers the Auto-Discovery mode:

Automatic instance discovery:
   If the Google Cloud SQL is installed on the local machine and no instance
   connection flags are specified, the proxy connects to all instances in the
   gcloud tool's active project. Run 'gcloud config list project' to
   display the active project

--

We could do use the same pattern with branches support in these forms:

# connect to the database with id `psdb-id-1` and branch `production` over the localhost via the 3306 port
sql-client-proxy --dbs psdb-id-1:production=tcp:3306

# connect to the database with id `psdb-id-1` and branch `production` over the localhost via the 3306 port
# connect to the database with id `psdb-id-2` and branch `development` over the localhost via the 3307 port
sql-client-proxy --dbs psdb-id-1:production=tcp:3306,psdb-2:development=tcp:3307

Even though the sql-client-proxy accepts this configuration via flags, we can easily introduce a file-based configuration. For example, a Rails application puts a file under .psdb/config.yaml. If someone runs the sql-client-proxy inside the application directory (a.k.a git repository), it could automatically read the configuration and start proxying.

This will especially come in handy for branching because a git branch could easily change the file to point to a specific PlanetScale database branch. Anyone who would checkout a Git branch would then automatically connect to the correct branch. Here are some imaginary workflows:

# checkout the main branch
git checkout main

# current psdb config file
$ cat .psdb/config.yaml:
connections:
  - ports:  3306
    database: psdb-1
    branch: production

# run the proxy manually
# connect to the database with id `psdb-id-1` and branch `production` over the localhost via the 3306 port
sql-client-proxy

Assuming I want to work on a new feature and want to test it on a new branch (both on git and psdb), I'll do the following:

# checkout a new branch
git checkout -b my-new-feature

# modify the psdb config file:
$ cat .psdb/config.yaml:
connections:
  - ports:  3306
    database: psdb-1
    branch: my-new-feature

# run the proxy manually
# connect to the database with id `psdb-id-1` and branch `my-new-feature` over the localhost via the 3306 port
sql-client-proxy

# commit the changes and push to remote branch
git add . && git commit -m "Testing a new feature."
git push origin my-new-feature

Now anyone working on this project will fetch the branch and start working. They will automatically connect to the correct database and don't have to figure it out.

Of course, there are many improvements to be made, such as running sql-client-proxy automatically when you develop locally, etc... But that's all improvement we can build on top of this idea.