Loading...

 

Connect Laravel 5.7 To Cloud SQL

  SC     17 Dec 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.


3. Click on the CREATE INSTANCE button.


4. Select MySQL.


5. Choose the second generation.


6. 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

Follow the guide here to Install the proxy client on your local machine.

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.


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


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


4. Then, update the app.yaml with the new credentials.

// app.yaml
DB_USERNAME: laravel
DB_PASSWORD: C0mpl3xP4ssw0rd

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.