How to provision a PostgreSQL database on a Cloud SQL instance for a new development environment

Introduction

In this tutorial, you will learn how to provision a PostgreSQL database on a Cloud SQL instance for a new non-production environment.

What would be a typical use case? Let’s say you want to spin up a new, dynamic environment for dev, testing or staging. In this case, of course, you wouldn’t want to connect your application to the static database you are using for production. You also wouldn’t connect it to a copy of the database that might contain sensitive data.

On the other hand, if you didn’t provision a database for your application running in a new environment, it most likely just wouldn’t work.

So how do we provision a PostgreSQL database running in a Google Cloud SQL instance for our new non-production environment?

Overview

In this tutorial we will show you in detail how to:

• Step 1: Create a resource definition & define matching criteria for database provisioning
• Step 2: Create a resource dependency in the workload & set environment variables for database provisioning
• Step 3: Deploy and test the application to check whether the database was actually created

💡 In this tutorial, we will mainly describe how to configure Humanitec to achieve the defined goal. At this stage, we won’t explain underlying concepts like resources and resource drivers in detail. If you want to learn more about the concept of resource provisioning, please read this article.

Prerequisites

Before starting this tutorial, you’ll need:

• A Humanitec account
• A Google Cloud Platform account that is linked to your Humanitec account
• A Cloud SQL instance for Postgres (see GCP docs)

For this tutorial we will use a simple Node.js service, which creates a PostgreSQL connection using environment variables, tests this connection and returns database parameters and status to the HTTP response:

<p> CODE: https://gist.github.com/docktorrr/f80afaff464daa4d99dfa769e29730ce.js</p>

The service is added as a workload to a sample application which is created in Humanitec (see how to create a sample app):

Step 1: Create a resource definition & define matching criteria

First, you need to define the database (in this case a PostgreSQL on Cloud SQL) as a resource and match it to the criteria it should meet to be provisioned.

• Start on the Resources Management screen and click on the Show all resources button. An overlay with all available resource categories and types will be presented to you.
• Below Data storages select Postgres, which opens a dialog.
• Add a memorable ID for this Resource Definition and select a driver, here - postgres-cloudsql.
• Below Credentials make sure your GCP account is selected.
• Fill CloudSQL instance name and maintenance database credentials.

• To set the criteria when a new PostgreSQL database should be created, select the new resource in the overview on Resource management. Click on + Define matching criteria for this resource. Now you can specify for which Environment type the PostgreSQL should be available. Choose development to match the corresponding environment:

Step 2: Create a resource dependency in the workload & set environment variables

An application in Humanitec consists of several workloads. In this step, you will define the resource dependency for the workload which requires the database (in this case sample-service).

• Let’s go to the workload details of the sample-service that requires the database. Now we have to ensure that the database we just defined as a resource is actually provided. To do so, add the resource in the Resource dependencies section. By choosing the resource name postgres and setting a name for the resource, the dependency is defined.
• Define environment variables on the workload level using placeholders.

Step 3: Deploy and test the application

Now everything is prepared for the deployment to our new development environment.

What actually happens under the hood at deployment time?

Once a developer hits deploy, the Humanitec Platform API will check for the defined dependencies and matching criteria. As defined above, a new PostgreSQL database running on a Cloud SQL instance will be created via the Humanitec’s resource driver.

The database name, user and password will be auto generated by the driver, and they are exposed as environment variables via placeholders as defined in Step 2. Also Cloud SQL Auth proxy will be created as a side-car container in the workload, so the service could communicate with the database through it, using a local host address.

Now we can request the deployed service (see How to expose the service outside the cluster) and check the database parameters and status. The response will be:

<p> CODE: https://gist.github.com/docktorrr/79b87e6491ae0039dc87dfd68173e079.js</p>

Summary

In this tutorial, you learned how to provision a PostgreSQL database on a Cloud SQL instance for a new non-production environment.

We linked Humanitec to a Google Cloud Platform account, added PostgreSQL as a resource and defined matching criteria for the database and defined how it should be connected to the application. Finally, by deploying the application into a new dev environment, the new database was spun up and tested successfully.

The beauty of Humanitec lies in the fact that we did not just create one single database for one single event. Throughout the steps above, we set up Humanitec so it will provide a new PostgreSQL for each application, for all deployments, to all environments that meet the resource definition and matching criteria.

That’s a huge relief as you need to perform the definition only once in a few seconds and not over and over again for each application.

Once again, this is a database for a dynamic non-production environment. For a production environment, it is very likely that you need a static database. This case is covered by the Humanitec driver postgres-cloudsql-static. In an upcoming tutorial you will learn how to provision such a static database.