Jiga

IntelliJ – Connect to MySQL running in Vagrant

The Database feature of IntelliJ/PHPStorm is very powerful compared to the MySQL CLI. It allows your edit your data from the UI easily without writing any MySQL commands.

Here is how you can connect your MySQL server running in a vagrant machine to the IntelliJ’s database feature and improve your productivity during development.

Create a new MySQL Data Source with these basic configuration:

Fill the basic details such as the data source name, host, port, user name, password and the database name.

Make sure you add ?useSSL=false to the JDBC URL after the form is filled. MySQL 5.7+ has SSL enabled by default.

Full JDBC url: jdbc:mysql://localhost:3306/development_database?useSSL=false

Now, with this default configuration, the connection will not work because we need to set the SSH configuration to allow IntelliJ to talk to the MySQL server running within the vagrant VM.

Goto the SSH/SSL tab, select ‘Use SSH tunnel’ and fill-in the details like this:

The value for Private key file should be the one from IdentityFile from your project vagrant directory using this command:

~/Dropbox/Projects/myproject
❯ vagrant ssh-config
Host default
  HostName 127.0.0.1
  User vagrant
  Port 2222
  UserKnownHostsFile /dev/null
  StrictHostKeyChecking no
  PasswordAuthentication no
  IdentityFile /Users/lrajasekaran/Dropbox/Projects/myproject/.vagrant/machines/default/virtualbox/private_key/private_key
  IdentitiesOnly yes
  LogLevel FATAL

Now click ‘Test Connection’ from this dialog to ensure the SSH connection is working as expected. Once the SSH connection is setup, close this dialog, go to ‘General’ tab and click ‘Test Connection’ to test the actual database connection using the SSH connection made previously. Once this successful, goto ‘Schemas’ tab and select your database name and click ‘Apply’.

Now you can view all your tables from the Database tool window on the right. Right click on any table and click ‘Jump to Editor’ to see the table rows:

Any value can be edited directly from this UI and then saved to the DB by right clicking the row and clicking ‘Submit’