Technical Articles
Getting DB’s Graphical Interface for Cloud Foundry Databases
“Visualizing information can give us a very quick solution to problems. We can get clarity or the answer to problems very quickly”
When we are working on the containerized environment like Cloud Foundry, its important to get a visual structure of our databases deployed there.
Imagine working from the command line of 90s VS The modern intuitive Graphical interface. I can bet you will choose the Graphical Interface. It gives shape to your imagination.
In the blog, I’ll show how to get visual Interface of Databases running on cloud foundry.
I will be using SAP Cloud Platform’s cloud foundry environment to demonstrate this, Postgres as a database, Chisel to act as a proxy, and Squirrel as the UI provider.
Chisel:
Chisel is an HTTP client and server which acts as a TCP proxy, written in Go (Golang). On Cloud Foundry it can be used to map TCP endpoints of your backing services to your local workstation. It provides binaries for 64-bit OSX, Windows and Linux.
How to use
You need a Cloud Foundry (CF) account and the CF command line tool on your work station.
Let’s get Started:
- I will be assuming we have the database service instance running on the cloud foundry environment.
- Clone chisel:
- git clone https://github.com/morikat/chisel
- Goto Chisel directory using: “cd chisel”
- Create a file auth.json in the root directory for this project.
- Make sure your manifest is having correct name for the applicaiton.
- For security reasons, you should always use the authenticated Create a file auth.json in the root directory of this project for user your_cf_user and password your_cf_password like this:
{ "your_cf_user:your_CF_Password": [""] }
- Do a cf push to deploy your application.
Now you need to bind the chisel app to your databases. Use cf bind-service my-chisel-app my-DB
- Or go to your cloud platform console and bind from there.
- Now to get the DB- Credentials you need to create a service key. Use the following command to create a service key.
- cf create-service-key db_name name_for_serviceKey
- cf service-key db_name name_for_serviceKey
- This will give you the service keys that are required for further steps.
- Now Open a terminal and enter
- cf ssh -L 63306:DB_ip_from_servicekey:44405 chiesel_app_name where -L tells the local port number that will be listening to this connection.
- Once installed, you need to install psql on your machine. This is to connect with Postgres DB.
- Once done with this step use the following command :
psql -U ZwRG********r20x -d qJfNn1h*******C -h localhost -p 63306 (here -d tells you need to insert DB name from service key, -U tells to insert Username
- Enter the password generated in the service
Now you are having a connection to your DB. Its time to see what’s there inside the DB.
For the visualization, I am using Squirrel client. You can find more details here: https://squirrel-sql-client.en.lo4d.com/
- Install the Postgres JDBC for it.
- Dos users can launch it using the squirrel-sql.bat
- Click on “+” at the left top corner of squirrel, enter the details as shown in the picture.
- The URL should be like: jdbc:postgresql://localhost:63306/DB_name_from_service_key
- The port should be the local port number specified during the ssh connection.
Fireworks!!!
You have done it. You must be able to see your database now.
Notes:
1 – It’s good to keep one service key rather than creating it again and again.
2 – Make sure that all the entered values(passwords, username, port etc) are correct.
Hi Gopal,
the provided git links seem to be SAP internal. So unfortunately i was not able to clone the repository and test things out :/
Hi @Nils Lutz,
Thanks a lot for trying out and bringing this issue up, you can cone my forked version of chisel's original code from here: https://github.com/gopalanand333/chisel
All other steps will remain the same.
I have updated the document with the same repository URL.
Best Regards,
Gopal
Hi Gopal,
interesting blog post 🙂
Could you explain why you need to tunnel the jdbc traffic via the CF app to the backing service? Isn't it possible to use the information from the service key to access the DB instance directly?
Hi Gopal,
There’s also a really marvelous cf-command-line extension that someone created to automate this task. Take a look at:
https://github.com/18F/cf-service-connect
I use that “cf connect-to-service” extension routinely to open up a tunnel and connect the Database tools in Eclipse IDE to my CF databases. I expect the same technique would work with a tool like Squirrel.
Cheers,
Riley
Hi Marius,
Tunnelling the JDBC traffic via the cf app gives the ability to do GET, POST calls and helps in establishing a secure connection, also with the service key I wasn't able to achieve this, Its something I am working on.