Connect to your Platform.sh Database with an IDE (MySQL,PHPStorm)

[dman]

Goal

To directly access your hosted database(s) from your workstation IDE .

See also how-to-connect-mongodb-tunnel-with-robo-3t

Assumptions

You will need:

• Platform.sh CLI tool working on your local environment
• An active project and branch with a MariaDB(MySQL) database
• The JetBrains PHPStorm IDE (for this example - most IDEs with integrated database inspectors can probably be configured in the same way). This also works with the official MySQL Workbench

This example is done on OSX, but the equivalent tools are available on Windows and Linux.

Problems

This can be useful to investigate issues, make direct schema changes or SQL commands, or import/export the database directly from the workspace you are most comfortable in, if the direct SQL CLI access is not sufficient.

We use the Platform.sh CLI tool to open a tunnel to the site instance, connecting the database port on the database container to a local port on the local machine, over ssh.

You then point your IDE or database at that port locally, and it interacts as if it was connected directly to the DB server.

Steps

1. Open the tunnel

Change into your local checkout of the project. The project should have been download using the platform get, which means that the project ID and branch will be automatically detected.

$ platform tunnel:single 

If you have a simple project with only one database service, this is all that is needed to open the connection. Otherwise the tool will ask you to select the service.

The tunnel is now open, make a note of the connection details it displays.

2. Open the database inspector window in PHPStorm

JetBrains/PHPStorm IDE supports multiple different Database inspectors as plugins. You can choose for yourself, but here I’m using the bundled “Database Tools and SQL” plugin.

3. Create Data Source

Select “Data Source” > “MySQL”

4. Add connection details for the tunnel

Copy the Host, Port, DB Name and User Name that the CLI displayed into the appropriate places on the Data Source Configuration Form.

It is normal for the password to be blank.

Test the connection.

(Optional) Check for updates

The plugins sometimes prompt you to update. This is likely if you’ve not used this feature before. Do that when needed.

Use the IDE Database tool

You’ll now get a window allowing you to inspect and edit the DB schema, run raw SQL queries, Or dump the DB to a local file.

Restart the tunnel each session

If you want to use this feature again, you’ll probably need to re-open the tunnel again each time. The database connection will be unavailable until then.

Conclusion

You can now directly access the database using local workstation tools.

[michaelbrauner]

Ok, nice to know. That works.

BUT since PHPStorm has a tunnel feature itself - it would be nice to know,
how to configure this:

Is it possible to share it for us?

[dman]

Did you try that? it was a bit more complicated, but I found it could be made to work.

For this, you now need to know two extra bits of knowledge, plus an sneaky SSH gotcha work-around.

Define a tunnel

To set up the tunnel, you need to know or retrieve your normal ssh credentials, and provide them to the “Use SSH Tunnel” setup.
Discover that from your web console, or by going
platform ssh --pipe

EG:
43g727syo3xr4-main-ba6vxei--app@ssh.au.platform.sh

In Data Sources and Dirvers > [your data source] > SSH/SSL > Use SSH Tunnel > [Create new or edit] > “SSH Configurations” : And enter the Host and Username there, In this context, 43g727syo3xr4-main-ba6vxei--app is your username

This will define what the tunnel destination is. The rest of the ‘tunneling’ flags will be taken care of by the IDE.

Authentication is tricky.

Gotcha: on that panel, “Authentication type” cannot be “Password”, and it seems that “Open SSH config and authentication agent” would be good, but it didn’t work for me at first, even with ssh-agent running.

As the Platform.sh security layer provides a temporary session key for SSH authentication, not simple key-pair, we (can/should/must?) use that.

SO:
Find the location of your temporary session key. This gets written into your home directory when you first successfully authenticate with the CLI for each session. I found it by opening a ssh session with verbose options, and seeing which of my keys the server actually accepted during the handshake.

ssh -vv $(platform ssh --pipe) exit 2>&1 | grep "using private key"

debug2: sign_and_send_pubkey: using private key "/home/dman/.platformsh/.session/sess-cli-default/ssh/id_ed25519" for certificate

So, you need to tell the IDE to use that key for auth when opening your tunnel.

• Choose Authentication type: “Key Pair”
• Enter that path as the “Private key file”
• [Test connection]

Save and step back to the “General” tab of your data source definition.

Now you have a tunnel. Still need to connect to the DB on the other side.

• Find your remote db connection details

platform environment:relationships

database:
    -
        username: user
        port: 3306
        host: database.internal
        path: main
        password: ''
        url: 'mysql://user:@database.internal:3306/main'

^ these credentials are usually the same for everyone who started with one of the templates, but may be different if you have modified your own DB relationships.

Enter the Host, User, Password (blank), and database as appropriate.
[Test Connection]

---

At this point, you SHOULD have access to the remote DB as a data source direct from the IDE. The wires are connected now.

I found I had to refresh things and re-save a few times in the Database tab before the tables started showing up, but it did.

So, a lot more painful to get configured. This is why the platform tunnel command is supposed to help.

and a final big gotcha:.

The temporary SSH certificate is temporary - it will expire on its own by design, and it will need to be refreshed regularly.

If you use platform CLI, this will be detected and the cert will be refreshed for you automatically. This is good.
If you use the above tunnel process, to bypass the platform CLI and have your IDE make the SSH calls directly, this will not renew automatically and the certificate will suddenly stop working after an hour.

You will still manually need to run platform login (or almost any platform command that needs ssh) every so often to keep the authentication for the connection fresh. There may be a way to avoid this by tweaking the tunnel keep-alive setting, but really … all this was way to much more work than the easy original answer …
So I simply don’t want to recommend any of this process.
Just letting you know, yes, it can be done. But it’s no fun.