Terraform Q&A — DDL Statements in Database

I often receive questions about Terraform in a single thread communication channel — private emails, private messages on social networks, even via chess.com where I play chess with random people.

I always try to answer those questions, anonymize it (remove IPs, names, secrets, etc) and publish it somewhere for other people to find them before they ask the same question again.

Let me start writing it here with something and see how it goes.

Today I got this question from Paranthaman from India, who asked for the best practice to automate the DDL statements for the database while using Terraform.

Well, Terraform is best for managing infrastructure resources in the first place.

Don’t overuse it!

Terraform can manage some of MySQL and PostgreSQL resources using official providers (read the documentation for MySQL and PostgreSQL). There are also ways to manage Oracle databases and many more using official or community-supported providers.

Terraform providers at most can be a way for the initial provision of databases, users, permissions, where schema and data should be uploaded separately.

Back to the original question. DDL statements (eg, schema migrations) are most-likely generated and managed using tools like Liquidbase or Doctrine Migrations, and belong to the application stack (not infrastructure) which requires them to be executed before the code works as expected.

It also means that the way of dealing with DDL statements relates more to the process of how the application is being deployed.

If you are using Terraform to deploy your application then you may find it easy to extend that process to run migrations during the deployment.

You can make an internal module which will create null_resource with local-exec provisioner which will do just one thing — run migrations script on specified database. This way you will have a declarative configuration for database configurations (eg, in a form of a version of migration), in addition to infrastructure resources (database instances) and application version.

If you are using containers to deploy your application, I recommend following the approach described by

Pawel Malon

in his post — Automated DB migrations for continuous delivery.

On another hand, if you are using Terraform to just provision resources and do deployments without using Terraform (eg, using Ansible playbooks to connect to an existing fleet of instances), you will be better if you extend that process and run DDL statements automatically as part of the deployment.

Despite the ways how deployment is happening, you should treat DDL statements as code, and put them in a pipeline.

If you like this question and/or my answer — click thumb up.

Follow me on twitter — https://twitter.com/antonbabenko