Divio vs. Google SQL: Database Management Made Easy

If you have ever struggled with Google Cloud SQL, configuring service workers and setting up network connections, the Divio approach to automated service configuration offers an opinionated alternative that gets your web deployed quickly with production-ready infrastructure that is secure by default. 

In order to better understand how Divio provisions an SQL database, let's first look at the Divio platform-as-a-service proposition. 

Divio uses a container-based approach for local development and when running in the cloud. When creating a web application, your application is automatically containerized and seamlessly pre-configured with the various services you might want to utilise. 

Your application runs in a container on Divio and locally using the popular Docker platform. This has several essential benefits, such as ensuring a unified development environment across teams and encouraging best practices, but also that your applications environment remains consistent across local and cloud-based deployments. 

As a developer, containers are composed automatically locally on your machine or when deploying to Divio, using the Divio CLI, a powerful and intuitive command line tool.

This has some important implications for how you use a database associated with your web project, considering that your web application resides within a container and is effectively isolated from the hosting environment.

All Divio plans include a generous amount of database storage by default.

Adding a database for local development 

The Divio CLI (Command Line) serves both to help you quickly deploy and manage your web apps on the Divio PaaS and also manage a local development environment. The nature of containers means that deploying to the Divio PaaS or your local development environment is, by design, very similar. This is important to consider in your workflow – your web app effectively moves seamlessly between a local and cloud environment. 

Depending on your chosen web app configuration, the docker-compose.yml configuration file could reference a Postgres container image which will be run on the Docker platform locally and be associated with your web app. The docker-compose.yml configuration is set aside when deploying to the Divio PaaS since the Divio platform automatically configures your web app environment. 

In contrast to Google Cloud, which also uses containers, the Google Cloud CLI, or gcloud CLI, is intended to manage and administer cloud resources and is used in conjunction with other local tools, such as sql cloud proxy.

How does the Divio platform compare to the Google Cloud SQL offering? 

The Divio platform is a multi-cloud platform, able to orchestrate multiple cloud vendors and, in doing so, offer exceptional redundancy and abstract away cloud vendor specializations. Divio provisions databases ready-to-go, whereas Google Cloud leans more toward offering granular components to bring together and assemble a suitable cloud infrastructure. 

When you create your web application on the Divio platform, a SQL database is automatically set up and made available under the services menu. 

Before you can use your database, you must first provision it. This initiates the database instance and attaches it to your chosen environment. 

The Divio platform uses some common defaults – a live and test environment. You can easily add more environments to reflect your working preferences, such as adding a staging or external testing environment.

As Divio is intelligently orchestrating multiple cloud vendors, the specific SQL database that your web app is provisioned with, typically Postgres or MySQL, depends on other factors, such as your preferred region. Useful extensions, such as PostGIS for geolocation data with Postgres, are available upon request. 

Following best practices that encourage the use of environment variables, a new variable is created for you within each environment. The DATABASE_URL variable can be used within your web app, like when working locally.

When working with a database on Google Cloud, the service account responsible for running the web application must first be granted access as a client towards the database resource ahead of the SQL user credentials. The web application can connect using Unix sockets or a public/private IP address. 

The most notable difference between Google Cloud and Divio is how a database is conceptualized. The Divio platform automatically does the setup tasks in the background – assigning a service or worker account, assigning SQL credentials to the service account and making the database accessible.

Accessing a Cloud SQL database on Google Cloud from your local machine is typically done using the standalone sql cloud proxy tool, which handles IAM authentication and token refresh to establish a tunnelled connection to the database. In turn, local tools can be used to interact with Cloud SQL as if it were running locally. Exposing a Cloud SQL database using a public IP is possible but has other security considerations.

Divio rigorously follows the conventions of working within a container environment and opts to enforce a secure working style. In order to connect to your database, an SSH session is established to your web apps environment, and from the shell, the usual SQL client tools such as psql can be used to connect to the database.

A DATABASE_URL environmental variable is created and populated on your behalf by the Divio platform. The separation of the application and its services in combination with the dynamic coupling of those via environment variables allows the Divio platform to shift resources across clouds and choose the best-fit database infrastructure for your web app.

Migrating data between local and cloud instances

The Divio CLI tool can manage a database locally and on the Divio PaaS. 

A common approach is for the web app itself to manage the creation and initial data in a database, removing the manual steps of deploying the web app and then creating an accompanying database schema before it can be used in the app.

For example, a Node.js app might use Sequelize, a popular ORM, to manage the underlying data. During initialization, when given a database connection using the DATABASE_URL environmental variable, Sequelize will create tables accordingly.

However, it is often helpful to grab a copy of a database to have a more useful testing environment or use it locally with up-to-date data that might be required to implement or test a feature.

The Divio CLI addresses this with the divio app pull db command with parameters that indicate which environment, such as live or test, the database should be exported from.

Inversely, the divio app push db command deploys a local export of the database onto the Divio platform towards the database associated with the specified environment. 

Following the convention of access to the database using SSH and a preferred database client, you can use the Postgres pg_dump command line from within your web apps container.

Google Cloud SQL encourages using SQL dump files towards Cloud Storage, a separate Google product for storing unstructured data. Local tools can be used when connecting to the database IP directly or utilising the sql cloud proxy tool, but this is only practical when dealing with small datasets.

The suggested Google Cloud SQL approach involves assigning relevant export credentials, cloudsql.instances.export and cloudsql.instances.get to a service or user account, and also storage.buckets.create and storage.objects.create credentials to create and store on Cloud Storage.

The gcloud sql export tool or the web-based console is used to specify the database instance and target Cloud Storage bucket that should hold the exported data.

Managing backups 

The Divio PaaS closely associates a database with a web app and applies an appropriate backup strategy accordingly. Backups contain both the contents of the environment database and also any media assets used within your web app. This might include images or videos used by the web app – everything you need to re-create or migrate your web app in one step.

Backups can be initiated on-demand, for example, before deploying a new version of a web app and are scheduled with a typical default strategy. 

The scheduled backup scheme includes one daily backup, where the oldest daily backup becomes a weekly backup, and the oldest weekly backup becomes a monthly backup. Different subscription tiers offer increased frequency and bespoke backup possibilities. 

Google Cloud offers Cloud SQL backups encompassing a specific database instance with similar on-demand and scheduled backups. In contrast to the Divio PaaS, where the database backup can be exported, Google Cloud uses an approach of restoring a complete Cloud SQL instance from a backup. As such, the backup is not intended to be exported but rather to re-create a Cloud SQL instance and associated data.

Summary

The Divio PaaS and Google Cloud SQL offerings provide SQL databases with different propositions and needs in mind. 

Through the Divio CLI and Divio Control Panel, databases are automatically created and associated with a web app environment, following best practices and offering an opinionated approach to rapid and secure cloud database operations. The underlying configuration, such as service account creation, assigning just-enough credentials and connecting databases with apps, are all handled in the background by the Divio platform. 

The Divio platform aims to strike a balance in handling the most typical use cases – saving time, reducing security risks through misconfiguration and automating away infrastructure management.

This has implications on how teams can be structured – moving resources towards web app development and increasing velocity while leveraging the Divio platform for more traditional DevOps tasks.