Connecting PHPStorm and Lando Databases for Highly Productive Drupal Development

October 28, 2020
Senior Principal Architect

PHPStorm is one of our favorite Integrated Development Environments (IDE) for building Drupal sites. In addition to its outstanding ability to help any PHP developer's productivity, it offers several Drupal-specific time-saving tools—like the ability to handle code completion for hook declarations and applying Drupal coding standards.

Among the many tabs that border the PHPStorm IDE window is one that offers access to one of the hardest-working components of the Drupal ecosystem...the database!

Many developers only interface with the database via Drush commands, performing database backups, or moving content from the server to their local machine. Given the Drupal database is where all content and active configuration are stored, developers should feel comfortable leveraging the database as a research and diagnostic tool when developing solutions or debugging problems. The database can provide insight into how your data is flowing throughout the system, which can help when debugging errors or when working with a new module that modifies data before saving.

In this guide, we'll show you how to connect to your local Lando environment’s Drupal database from within PHPStorm. If you’re using another local environment like DrupalVM or DDEV, you can use the following steps as a guide for how you can connect these other environments.

Obligatory warning: After you connect to the database, you'll have access to modify or delete data, tables, or the entire database. Be sure you’re not working directly with a live/production database! We suggest using the database tool on a local copy of the database that can be restored if needed.

Step 1 - Allow Lando to Receive Incoming Database Connections

By default, Lando does not allow anything but the Lando app to connect to the database server, so we need to tell Lando that it’s OK for PHPStorm to connect. In the Lando configuration file (either the project-wide .lando.yml, or in the local overrides .lando.local.yml), add the following lines:

services:
  database:
    portforward: 3307

When added to a basic .lando.yml recipe, the file will look like this:

code added to a basic .lando.yml recipe

This allows port forwarding on port 3307 to the host 'database', which is the default name of the database container in the drupal8 / drupal9 recipe in Lando. If your database hostname is different, update as needed.

Finally, for this step, rebuild the Lando environment with lando rebuild --yes.

Step 2 - Connect the Database in PHPStorm

Now that Lando has been rebuilt and is running, we can connect PHPStorm to the Drupal database. If it’s not already open, click the database tab to open the database pane.

In the database pane, click the + sign to add a new data source, select MySQL (or MariaDB).

click the + sign to add a new data source, select MySQL (or MariaDB)

In the new window that opens, enter the server information and credentials to connect:

data sources and drivers window with the server information and credentials shown

Since we’re using the default values that come with the Lando drupal8 recipe, we’ve entered:

  • Name - a name you want to call this in configuration
  • Host - defaults to localhost, you should be able to leave it as is
  • Port - 3307 (or the port you assigned in .lando.yml)
  • User - drupal8 (or the database username you assigned)
  • Password - drupal8 (or the password you assigned)
  • Database - drupal8 (or the database name you assigned)

When you click "Test Connection" you should see a green checkmark to verify that it connected successfully.

So, now what?

Step 3 - Use the Database!

The database pane should show you a tree of the database tables in your Drupal database. When you connect to the database, a console tab will open up in the main editor window. You can also browse the data in a table by double-clicking on the table name in the database tab. In the image below, we’ve opened the table block_content and have the data as a table in the main editor window.

database pane with tree of the database tables in your Drupal database

Why Use this Database Tool Over Others

The database tool within PHPStorm has most of the features of JetBrains’s DataGrip IDE. There are too many features to cover, but here are three of our favorites:

Feature 1 - Viewing the Data in a Table

This seems pretty mundane, but scanning through data tables can help you visually pick up patterns about your data. The column headers allow you to sort by one or more columns to help you review the data in the table. You can also drag to rearrange the columns to make viewing the data easier for your task.

Feature 2 - Finding Data in a Table

When you need to find a specific string in a table, you can write a query by hand or you could use some of the built-in tools to make finding the string much easier. When you’re viewing a table, you can search all rows and columns by simply pressing Cmd+F or Ctrl+F. A magical search form will appear:

search appearing to help find data in a table

As you start typing, data cells with your search string will be highlighted. You can also check the "Filter rows" box to only show the rows that have your search string in them:

data cells highlighted with the search query typed in

There are also options to search with case-sensitivity or with regular expressions, which can help you find all of the data that you’re looking for.

Feature 3 - Finding Data ANYWHERE!

This is a great tool to use when you know what you're looking for, but you aren’t sure where to find it. You no longer have to navigate a huge haystack of the SQL export text file to look for your needle!

In the database tab, right-click on the drupal8 database and pick "Full-Text Search...":

options shown after you right-click on the drupal8 database

In the new window that opens, you can enter your search term and press Search:

window for full-text search

PHPStorm will open the "Find" tab and show you how many matches were found in the tables of your database:

number of matches shown found PHPStorm

Delivering Great Digital Platforms

PHPStorm is a true workhorse of Drupal development. It allows talented people to be more productive in their efforts to create amazing features for Drupal and awesome digital experiences for users. The built-in suite of tools for PHPStorm—especially the database tools—makes this IDE my favorite when it comes to delivering great digital platforms for our clients at Bounteous.