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

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.

Screen Shot 2020-11-03 at 11.03.00

3. Create Data Source

Select “Data Source” > “MySQL”

Screen Shot 2020-11-03 at 11.03.34

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.