Connect Laravel 5.7 To Cloud SQL

Posted by Sky Chin on November 2, 2018

In the previous post, our Laravel 5.7 application lands on the GAE (Google App Engine) Standard Environment.

In this post, we are going to connect Laravel 5.7 application to the Cloud SQL.

Cloud SQL

Cloud SQL is a fully-managed database system that supports PostgreSQL and MySQL databases in the GCP (Google Cloud Platform).

With Cloud SQL, I don’t need to install MySQL manually or maintain the database. It matches my goal of running Laravel application on GCP with no-ops.

Prerequisites

Before you getting started, you need to prepare

  1. Create a project in the Google Cloud Platform Console.
  2. Enable billing for your project.
  3. Enable the Cloud SQL API.
  4. Install the Google Cloud SDK.

Create a new instance

First of all, create a new instance of CSQL (Cloud SQL).

  1. Login to your GCP console.
  2. Search sql from the search bar.

  1. Click on the CREATE INSTANCE button.

  1. Select MySQL.

  1. Choose the second generation.

  1. Fill up all the field then click the Create button.

There you go! You have created a new instance of CSQL.

Create a new database

The beauty of a cloud server is you can connect anywhere.

To connect to the database, you connect the CSQL by using the Cloud SQL Proxy.

Connect from the local machine

  1. Follow the guide here to Install the proxy client on your local machine.
  2. Connect to the CSQL instance via the proxy client.
// Terminal

$ ./cloud_sql_proxy -instances=running-laravel-on-gcp:asia-southeast1:running-laravel-on-gcp=tcp:3306

If you have a local database which is running on the port number 3306, you can connect with another port number. In my case, I connect with port number 33060.

// Terminal

$ ./cloud_sql_proxy -instances=running-laravel-on-gcp:asia-southeast1:running-laravel-on-gcp=tcp:33060

Create a database

The CSQL is empty right now, let’s create a new database.

// Terminal

$ mysql -h 127.0.0.1 -u root -p -e "CREATE DATABASE running-laravel-on-gcp;”

In my case, I need to specify the port number 33060.

// Terminal

$ mysql -h 127.0.0.1 -P 33060 -u root -p -e "CREATE DATABASE running-laravel-on-gcp;”

Next step is run the database migration to create all the tables.

Run the database migration for Laravel

By setting database connection in .env, your local Laravel application connects to the CSQL instance directly.

// .env

DB_DATABASE: running-laravel-on-gcp
DB_USERNAME: root
DB_PASSWORD: secret
DB_SOCKET: "/cloudsql/running-laravel-on-gcp:asia-southeast1:running-laravel-on-gcp”

If you don’t want to mess up with your local environment settings, you can run the database migration by passing the database connection as environment variables.

// Terminal

$ export DB_DATABASE=laravel DB_USERNAME=root DB_PASSWORD=secret DB_SOCKET: "/cloudsql/running-laravel-on-gcp:asia-southeast1:running-laravel-on-gcp”

Okay! Run the migration now.

// Terminal

$ php artisan migrate

Then, deploy the application.

Deployment

Like the setting in .env, you set the database connection parameters as environment variables in app.yaml.

// app.yaml

runtime: php72

# Put production environment variables here.
env_variables:
  # Applicaton key
  APP_KEY: base64:neD3pkZQV26sd9OxZ8cp3jyERMnrt0X5guevJzw65N4=
  # Storage path
  APP_STORAGE: /tmp
  ## Set these environment variables according to your CloudSQL configuration.
  DB_DATABASE: running-laravel-on-gcp
  DB_USERNAME: root
  DB_PASSWORD: secret
  DB_SOCKET: "/cloudsql/running-laravel-on-gcp:asia-southeast1:running-laravel-on-gcp”

After the setting, run the deployment.

// Terminal

$ gcloud app deploy

Tada!

Using root user in the production environment is not recommended, don’t mention about the simple password I set previously. Let’s change the root user's password and create a new user for your Laravel application.

Manage MySQL user accounts

You can manage your MySQL user accounts from GCP console.

  1. From the console, select your instance then switch to USERS tab.

  1. Create a new user named laravel and set a complex password.

  1. After creating the user, you change the password of the root user account.

  1. Then, update the app.yaml with the new credentials.
// app.yaml

  DB_USERNAME: laravel
  DB_PASSWORD: C0mpl3xP4ssw0rd
  1. And, deploy it.
// Terminal

$ gcloud app deploy

Your Laravel application on GAE should work the same as previous.

Now, your Laravel 5.7 application is connected to the GSQL.

Stay tune

Laravel has an awesome filesystem which is powered by the Flysystem PHP package by Frank de Jonge.

Next post, I’m going to write about how to manage your filesystem on the cloud.

Sign up for my next post