Connecting to Google Cloud SQL from PhpStorm
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.
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:
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.
Develop with pleasure!
– JetBrains PhpStorm Team
Subscribe to Blog updates
Thanks, we've got you!
Qodana Clone Finder: Early Access Program
In December 2020, we announced the EAP for Qodana, which is rapidly evolving into a comprehensive platform that allows companies to perform multi-level evaluations of the quality of code they own, contract, or purchase. Qodana helps you detect bugs without relying on an IDE, either on a local machi…
PhpStorm 2020.2 EAP #3
The Early Access Program for PhpStorm 2020.2 is in full swing and today we’ve got the third build of the 2020.2 EAP for you. In this blog post, you can read about improvements for Git installed in WSL2 and Search Everywhere. Download PhpStorm 2020.2 EAP (more…)…
PhpStorm 2019.3.3 Preview
We’ve just rolled out a preview for the third minor update for PhpStorm 2019.3. Please give the PhpStorm 2019.3.3 Preview build 193.6494.5 a try and share your feedback with us. (more…)…
How to Upgrade to PHP 7.4 with PhpStorm
Check out our fresh video with a few nice tips and tricks on how to update your codebase to PHP 7.4. It combines materials from the PhpStorm 2019.2 and PhpStorm 2019.3 releases, with a few cool bonuses to boot. Nikita drew our attention to the fact that setters are slower than assigning prop…