Connect to Google Cloud SQL

This guide assumes that you already have a SQL database set up in Google Cloud.

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

Setting up a service runtime

Service runtimes are Nextjournal’s way of providing long-running services (like databases) to other runtimes. Add a service runtime via the insert menu.

In your service runtime, you will need to specify a start script that the service runtime can use to connect to your Google Cloud SQL instance using a proxy service. In the start script field, paste the following code:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && 

chmod +x cloud_sql_proxy &&

./cloud_sql_proxy -credential_file /.cloud_sql-secret -dir socket-dir -instances=[YOUR INSTANCE CONNECTION NAME]=tcp:0.0.0.0:5432

You will need to replace [YOUR INSTANCE CONNECTION NAME] with your actual SQL instance’s name. You can find this name by clicking SQL in your Google Cloud account’s sidebar and then expanding the instance name row:

One you have the start script in place, click Customize in your service runtime:

This will open your service runtime’s settings panel.

First, provide a name for your service, e.g. postgres. This name can be used as host name (or network name) in the code cells that you use to connect to your database, hence, be careful that this name only consists of a-z, 0-9, and -_ characters.

Next, under Mounts, click Add secret.

Now paste your instance’s service account JSON and name the secret CLOUD_SQL.

Click Store and use secret, then click Apply and finally, back in your service runtime, click Start runtime. You should quickly see a green dot next to your runtime’s name, indicating that it’s running.

Access your database from a code cell

You can now use any other programming language runtime to connect to your database. The following example shows you a Python code cell that connects to a Postgres database using the psycopg2 library.

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