Upload and Import SQL file from Google Cloud Storage to CloudSQL using NodeJS

Hello, folks. This month I get an interesting project that require me to learn about Google Cloud or more specifically BigQuery, Google Cloud Storage (GCS) and CloudSQL. At first I was so excited having a chance to play around with Google Cloud Platform since I’m too lazy to get myself a credit card and register for the Google Cloud Platform trial. But, my excitement doesn’t last long because the documentation for using NodeJS to access google storage and CloudSQL is too unpractical for me. I mean when people say documentation, they usually means step-by-step guide that targeted to someone that never use the product so they could understand and use the product, right? But I suppose google have their own definition of documentation. Or maybe I’m not smart enough to understand the documentation. Well, honestly I don’t think the later is possible :).

For this project, I spend hours on stackoverflow and watch bunch of youtube videos just to understand how to make Google Cloud Platform works on my NodeJS. It’s so frustrating and time consuming therefore I don’t want anyone else to go through the same pain and suffering that I experienced. So, in this article I will explain step-by-step on how you can use NodeJS to upload an SQL file to GCS then import it to CloudSQL. I will include as much screenshot as possible for you visual learner out there so I hope from this article you can learn:

1. How to set up Google Cloud Storage and CloudSQL
2. How to get Google Cloud Storage and CloudSQL credential JSON file
3. How to access CloudSQL using Google Cloud Shell and Sequel Pro
4. How to upload local file to GCS using NodeJS
5. How to import SQL file from GCS to CloudSQL using NodeJS

It will a long article but I will provide a subheading so you can jump to specific part of this article that you need. Or if you are here just for the juicy code, you can check the Github repository here.

Prepare Google Storage

First, let’s see how GCP dashboard look like at this url and you will see something like this:

On the left bar you can see all the services that google provides you. For now let’s choose storage and click “Browser”. By the way, you could pin the storage button, so it will be displayed on top the next time you open the dashboard. This feature is definitely a big help for someone like me who is too lazy to scroll down and somehow still can’t find the menu I’m looking for.

After that, we will need to create new bucket for our google storage by clicking “CREATE BUCKET” on the interface as illustrated below:

Then, just fill the form depends on what you need and finally click create. At this step, you might need to wait for several minutes for GCP to create your bucket. In case you doesn’t know what bucket is, you can imagine it like a your local storage partition and the process that we’re doing now is just creating new partition in our cloud storage.

After you create a bucket, you can upload and download to google storage using the console, but you will not be able to do that using NodeJS yet. To allow NodeJS to access Google Storage we need to enable the GCS API. To do that, go to the left bar, hover to “APIs & Services” and click “Dashboard”

Now, click “ENABLE APIS AND SERVICES” and search for “Google Cloud Storage”. After that you can just click “ENABLE” to enable GCS API. Here is the enabled GCS API looks like in the dashboard:

Prepare CloudSQL

After we create our GCS bucket, now we need to set up our CloudSQL services. Let’s go to the left bar again and click “SQL” to open CloudSQL dashboard.

Now, we need to create new instance by clicking “CREATE INSTANCE” on the top of dashboard like what illustrated in the picture below:

Then we need to choose the type of database that we prefer. For this article let’s choose MySQL and then hit “Next”.

After that, we also need to choose the type of MySQL instance that we need. Let’s choose the second generation since google is kindly recommend it to us.

Now, we need to fill in some form to create our MySQL instance. Make sure your instance ID is unique and choose location closest to your current location.

Finally, click “Create” and as usual you might need to wait for several minutes for google to create your MySQL instance. Just like the GCS, we also need to enable the API for CloudSQL to allow access for NodeJS. Go to “APIs & Services” then search for “Google Cloud SQL” and click “ENABLE”. This picture below is what enable Google Cloud SQL API looks like:

Create CloudSQL Database

It is actually a simple things to create CloudSQL database. You just need to connect to the CloudSQL and use your standard query SQL to create new database, new table etc. But some people might have difficulty to connect with CloudSQL database. Well, at least for me I froze for 2 whole minutes before I figure out there is a button to connect to it. There are 2 methods that I have tried to connect which are using Google Cloud Shell and by giving authorization to my network. There are other method like using SSL to connect but I will not cover that in this article since I haven’t tried it yet.

To connect using Google Cloud Shell you just need to open your CloudSQL dashboard, click on the Instance ID that you just create and good luck finding “Connect using cloud shell”. Just kidding, it’s not that hard but the first time I open this page, somehow I just forgot that you can scroll down to see more option on this page. LOL. Anyway, after you click that button, it will open google cloud shell and you need to press Enter to run the command there. As usual when you press enter, you might need to wait for several minutes for google to whitelist your IP. After that you can type your MySQL password to login into your MySQL database.

If in the first method, the platform will automatically whitelist your IP temporarily, in the second method we will whitelist our network IP manually. By using this method you will be able to access CloudSQL using SQL application such as Sequel Pro. To authorize your network, go to authorization tab on your CloudSQL dashboard and click “Add network” as follows:

Now you will be able to access CloudSQL using your network IP. To connect using Sequel Pro or any other app you just need to use the IP address that you can find on overview tab, user name (root) and the password you set for your CloudSQL.

Finally after you manage to connect to CloudSQL, let’s create a new table as follows:

Upload SQL file to Google Cloud Storage

For this article, we will try to insert several row data to a table using this following SQL query:

To upload sql file or any file to GCS using NodeJS we need to install node package called @google-cloud/storage by using this following command:

npm install @google-cloud/storage --save

Before we start to make a code, we need to get google storage credential file from GCP dashboard. This credential is the one that will allow our NodeJS script to access our GCS. To get the credential file go to the left bar menu and hover over “APIs & Services” but now instead of clicking “Dashboard” we click “Credentials”.

Now after we see the credential dashboard, click on “Create credentials” and choose “Service account key”.

After that choose the service account that you want to use and if you have no service account just create one. Finally click “Create” to download the credential file. For this example we will use the JSON format credential.

Now let’s get our hand dirty with code and use the following code to upload the SQL file to GCS:

In the above code, “storage-credential.json” is the JSON credential file that we get from google cloud dashboard previously. You might notice that in this code we also make our sql file public. To import SQL file to CloudSQL from GCS using NodeJS, we need to allow NodeJS to access the uploaded SQL file and that’s why in this code we also make our file public. This picture below is showing how a public file looks like in GCS dashboard. Notice that there is check mark on the right column for a public file.

Import SQL file from GCS to CloudSQL

To import SQL file from GCS to CloudSQL, we need to install another node package called googleapis. Use the following command to install googleapis node package:

npm install googleapis --save

After that, we need to authorize NodeJS to access CloudSQL. Unfortunately, we can not use the previous credential or at least I can’t find any reference on using the previous credential to access CloudSQL from NodeJS. Therefore we will use another way to get credential which is by using Google Cloud SDK. Bear with me, because this will be kind of inefficient method since we need to install SDK just to get a credential. But at least this method works and that’s the most important thing for people like us, right? LOL.

Once you install Google Cloud SDK, run the following command to get the credential file:

gcloud auth application-default login

This command will open a browser that ask you to login to your google account that connected to the Google Cloud Platform. After that it will generate a default application credential file for Google Cloud Platform in your local machine. You can change the default location for the credential file by defining the new location at environment variable called GOOGLE_APPLICATION_CREDENTIALS, but let’s not make this article more complex that it already has and just use the default location.

Now we’re ready to get back to our code. Create new file and type the following code or honestly I’m sure you just gonna copy paste it anyway.

In this code we use default application credential to get the authorization to access CloudSQL then use the authorization on sqlAdmin module that will import the SQL file from Google Storage to CloudSQL. By the way, if you want to try the import API without implement it on NodeJS, you can try it on the documentation that Google provides here.

To run all the code all together from uploading to importing the SQL file, you can use the following code:

Finally you can see the result by using google cloud shell or Sequel Pro as follows:

That’s all for today, if you have further question or the Github repository doesn’t work for you, please feel free to contact me at himang@skyshi.io or if you prefer skype, contact me at live:himang27s. See you on the next article.