Connect to Google Cloud SQL

This guide assumes that you already have a SQL database set up in Google Cloud. If you already used Cloud SQL with another notebook environment, e.g. Jupyter, and already have a Google Cloud service account, you are most likely good to go and can skip to Connecting your notebook.

Set up your SQL instance for Nextjournal

To be able to connect to your instance from Nextjournal, you will have to enable its Public IP setting. This is enabled by default but if that’s not the case for you, go to your Google Cloud project’s SQL console and select the SQL instance you want to connect to:

Click Edit next to Instance details to show your instance’s configuration options.

Under Connectivity, check the Public IP checkbox and click Done at the bottom of the page.

Create a service account JSON

I you don’t have a Google Cloud service account yet, you will have to create one in order to authenticate your notebook with your SQL instance. I you already have one, you can skip this section but keep your service account JSON ready.

Create a service account

From your Google Cloud’s home screen, select IAM and admin in the sidebar and then Service accounts. Then, on the Service accounts page click Create service account.

On the Create service account page, add an account name and ID and click Create.

Once your account is created, select the Cloud SQL Client permissions from the Select a role input field and submit to proceed to Grant users access to this service account.

On this page, click Create key and select JSON as key type.

Your browser will now prompt you to download a JSON file containing a private key and other information necessary to connect to your SQL instance. Keep this JSON handy as you will later need it provide it as as secret to Nextjournal.

Enabling the Cloud SQL Admin API

Next up, you will need enable the Cloud SQL Admin API. In your Google Cloud accounts sidebar, you can select APIs & Services and then Dashboard to get an overview of enabled APIs.

You can skip the rest of this section if the list already includes Cloud SQL Admin API. If that’s not case, click Enable APIs and services.

Next, search and select the Cloud SQL Admin API and click Enable.

Once this is enabled, you can open the Nextjournal notebook you want to connect to your SQL instance.

Connecting your notebook

Open Nextjournal’s Google Cloud SQL template and click Remix in the upper right corner.

In the freshly remixed template, click Customize... in the gcloud-sql-proxy runtime to show its settings.

Under mounts, click Click to add next to the /etc/gcloud.json mount.

A panel opens that lets you assign a secret to this mount. Under Store new, use GCLOUD_SQL_SERVICE_ACCOUNT as name and paste the contents of your Google Cloud service account JSON file into the Secret textfield. Then click Store and use secret and close the runtime settings panel.

Back in your gcloud-sql-proxy runtime, click into the start script textfield and replace cloud-db-demo:europe-west1:demo with your actual instance’s connection name.

You can find this name by clicking SQL in your Google Cloud account’s sidebar and then expanding the instance name row:

Next, click Start runtime. After booting, the runtime will indicate that it’s running with a green dot next to its name. You can now proceed to the Python code cell that has the code to connect to your database. There, replace all instances of postgres with your db’s name and credentials and run it with Shift+Enter.

You should now be connected to your database. Running the next code cell should print some of your db’s rows as a test. You can now add more code cells for more complex analytics.

Mind that the template uses Python with psycopg2 as an example to connect to a db. Once you have set up your gcloud-sql-proxy runtime, you can use any language or library to connect to your db.

Environments

Python psycopg2

pip install psycopg2-binary

SQL Proxy

This provides the Google Cloud SQL Proxy environment.

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy && chmod +x /usr/local/cloud_sql_proxy