The PhpStorm Blog

The Lightning-Smart IDE for PHP Programming

Download

Features Releases

Connecting to Google Cloud SQL from PhpStorm

Maarten Balliauw

When creating Google App Engine applications in PHP using PhpStorm, chances are that we are also using Google Cloud SQL. From our PHP code, we can easily do this by creating a new connection to our Google Cloud SQL instance using PDO:

Now what if we want to connect to this database using the Database Tools available in PhpStorm?

Note that Google Cloud SQL is not the only database platform we can make use of from within PhpStorm. MySQL, Microsoft SQL Server, Microsoft’s Windows Azure SQL Database (another cloud!), Oracle, PostgreSQL and many more providers are available.

This functionality is available in IntelliJ IDEA, PyCharm, PhpStorm, and RubyMine.

Database support can be found on the right-hand side of the IDE or by pressing Ctrl+Alt+A (Cmd+Alt+A on Mac) and searching for “Database”.

Opening the database pane, we can create a new connection or Data Source. We’ll have to specify the JDBC database driver to be used to connect to our database. Google Cloud SQL uses its own JDBC driver which comes bundled with the command line tool from the App Engine Java SDK. After downloading and extracting this tool, we can use the extracted google_sql.jar file as the JDBC driver we want to use in PhpStorm.

After selecting the Google Cloud SQL JDBC driver, we can select the driver class from the dropdown. Let’s go with the com.google.cloud.sql.Driver class and enter our database URL. The database URL comes in the following form:

jdbc:google:rdbms://<instance_name>/<database_name>

where instance_name is the name of our Google Cloud SQL instance and database_name is the name of the database we want to connect to. I’ve created an instance named mblgae in which I have a database named test, making the database URL jdbc:google:rdbms://mblgae:mblgae/test.

For database credentials, we have several options. The easiest way is to create a new database user through the Google API Console and issuing the following SQL DDL:

CREATE USER 'user_here'@'%' IDENTIFIED BY 'password_here';
GRANT ALL PRIVILEGES ON *.* TO 'user_here'@'%' WITH GRANT OPTION;

We can now make use of this newly created user to connect to our database(s).

Another approach is making use of an OAuth 2.0 user which maps to a Google Account. If we enable database access to a specific Google Account through the Google API Console, we can also use OAuth 2.0 tokens to authorize applications to connect to our database instance on our behalf. We can obtain such a token by running the google_sql.cmd or google_sql.sh script which came bundled with the JDBC driver downloaded earlier.

The google_sql tool will present us with a URL which we have to open in our browser. After granting access through this URL, we can copy the authorization code in the tool and continue:

The authorization code received will also be the username we can use in PhpStorm for connecting to our Google Cloud SQL database. After entering it, we can connect to our database.

After saving connection information, we can run various queries and insert, update or delete data. We can also use the built-in tools to create new tables.