How to access a SQL database

This guide shows you how you can access the SIP SQL (Postres, Mysql) databases as a developer.

Prerequisites: For this guide you need to install the gcloud utility and authenticate with your VSETH Google Account as well as kubectl installed and configured. See How to access Kubernetes Cluster on details of how to do this.


This method allows you to connect to production databases. Errors may result in data loss of your production system.

Cloud Sql Proxy

The connection to a database goes through the google cloud_sql_proxy:

Diagram of the Cloud SQL Auth proxy connecting from client software to SQL instance

Download the cloud_sql_proxy binary from the Github Release Page: https://github.com/GoogleCloudPlatform/cloudsql-proxy/releases

The database servers are:

  • vseth-prd-mysql for the msyql server
  • vseth-prd-pg for the postgres server

Then you can start the google cloud proxy by running (note that we start the proxy on the default port of the database):

MySQL

cloud_sql_proxy -instances=aerial-reef-202214:europe-west4:vseth-prd-mysql=tcp:0.0.0.0:3306

of if you are using a newer version

cloud-sql-proxy --port 3306 aerial-reef-202214:europe-west4:vseth-prd-mysql --gcloud-auth

Postgres

cloud_sql_proxy -instances=aerial-reef-202214:europe-west4:vseth-prd-pg=tcp:0.0.0.0:5432

or if you are using a newer version:

cloud-sql-proxy --port 5432 aerial-reef-202214:europe-west4:vseth-prd-pg --gcloud-auth

Get the credentials

Get the credentials (username and password) of the database you want to access. You can do this by using kubectl to access the secrets as described in How to access Kubernetes Cluster

For example if you want to get the database credentials of the helfertool running in the com-staging namespace

kubectl get secret -n com-staging

you find the secret with the name my-helfertool-db

which you can access by running: (the secret is base64 encoded so we need to decode it):

kubectl get secret -n com-staging my-helfertool-db -o jsonpath="{.data.username}" | base64 --decode
kubectl get secret -n com-staging my-helfertool-db -o jsonpath="{.data.password}" | base64 --decode

For your application you can just replace com-staging with the namespace of your application and helfertool with the name of your deployment.

The prefix my is determined by the database type:

  • my stands for mysql
  • pg stands for postgres


The value returned by the above commands may end with %. This character indicates that the values does not end with a newline character \n but is not part of the actual value.

Connect

Now you can connect to the database from you local machine using standard db tools, for example to connect with the mysql cli:

mysql -h 127.0.0.1 -p -u com_staging_helfertool_db

Where you use the username and password you have obtained in the previous step.


If you prefer a nice graphical user interface, you might want to take a look at Datagrip.