Manage database schemas with Terraform in plain SQL
You can now use plain SQL to define the desired state of your database schema.
Earlier today, we released v0.5.0 of the Atlas Terraform Provider. This release includes two changes that, in my opinion, are a great improvement to the experience of working with the Atlas Provider.
In this post, I will discuss these two changes and how they can help you to manage your database schemas with Terraform:
- Support for the
docker://driver for dev-databases. - Support for defining the desired state of your database schema in plain SQL (and any other schema loader supported by Atlas).
Improving the dev-database experience with the docker:// driver
Atlas relies on a connection to an empty database which it can use to perform various calculations and operations. This database is called the "dev-database", and it allows Atlas to do things like validate the correctness of user-provided code as well as normalize user-input to the way the database actually sees it.
In previous versions of the Atlas Provider, the dev-database needed to be provided
by the user. This was a bit cumbersome, as the user needed to spin up a database (usually
by running a local Docker container), and then provide the connection string to it
in the dev_url field of the atlas_schema resource.
To improve this experience, we added support for the docker:// driver, which allows
the user to only provide the database engine and version, and Atlas will spin up an
ephemeral container with the correct database engine and version. In addition, starting
this version, users may define the dev_url on the provider scope. For example:
provider "atlas" {
dev_url = "docker://mysql/8/myapp"
}
Defining the desired state of the database schema in plain SQL
In earlier versions of the Atlas Provider, the atlas_schema resource required the user
to provide an Atlas HCL file which describes the desired
state of the database schema. Many users found this syntax, which resembles Terraform's
own, to be clean and concise. However, others disliked it and asked for a way to define
the desired state in plain SQL.
To support this use-case, and many others, we have announced
support for "schema loaders" -
components that can be used to load the desired schema from many kinds of sources
(such as plain SQL, an existing database, or the data-model of an ORM). To use this capability,
users may use the atlas_schema data source, which accepts a url field that points to
the desired schema. The scheme of this URL determines which schema loader will be used, for instance:
file://schema.sql- loads the schema from a local SQL file.mysql://root:pass@localhost:3306/myapp- loads the schema from an existing MySQL database.ent://service/ent/schema- loads the schema from the schema of an Ent project.
Managing database schemas in plain SQL using Terraform
You can find the final code for this example here.
In the following example, we will show how you can use Terraform and the Atlas provider to manage a MySQL database schema in plain SQL.
Let's start by creating a Terraform file named main.tf installing the Atlas
Terraform provider:
terraform {
required_providers {
atlas = {
source = "ariga/atlas"
version = "0.5.0"
}
}
}
In addition to installing the Atlas provider, we will also spin up a local MySQL database using Docker which will represent our target database that we will manage with Terraform. In a real-world scenario, you would probably use a managed database service such as AWS RDS or Google Cloud SQL, but for the purpose of brevity, a local database will suffice. Run:
docker run -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=myapp mysql:8
Now that we have a database to manage, we can define the desired state of the database schema. Add a file named "schema.sql" with the following content:
create table users (
id int not null auto_increment primary key,
name varchar(255) not null
);
Next, we will define an atlas_schema data source that will load the schema from the
schema.sql file:
data "atlas_schema" "sql" {
src = "file://${path.module}/schema.sql"
}
Finally, we will define an atlas_schema resource that will manage the schema in the
target database. In addition, we will configure the Atlas provider to use the docker://
driver to spin up a temporary database container:
provider "atlas" {
dev_url = "docker://mysql/8/myapp"
}
resource "atlas_schema" "mysql" {
url = "mysql://root:pass@localhost:3306/myapp"
hcl = data.atlas_schema.sql.hcl
}
Now that we have defined our Terraform configuration, we can run terraform init to
install the required providers:
terraform init
This should output something like:
Initializing provider plugins...
- Finding ariga/atlas versions matching "0.4.7"...
- Installing ariga/atlas v0.5.0...
- Installed ariga/atlas v0.5.0 (signed by a HashiCorp partner, key ID 45441FCEAAC3770C)
# ...
Terraform has been successfully initialized!
Finally, we can run terraform apply to create the database schema:
terraform apply
Terraform will print the following plan:
data.atlas_schema.sql: Reading...
data.atlas_schema.sql: Read complete after 4s [id=qnUvTyupgQzivof5LYWDOQ]
Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following
symbols:
+ create
Terraform will perform the following actions:
# atlas_schema.myapp will be created
+ resource "atlas_schema" "myapp" {
+ hcl = <<-EOT
table "hello" {
schema = schema.myapp
column "world" {
null = true
type = text
}
column "thoughts" {
null = true
type = varchar(100)
}
}
schema "myapp" {
charset = "utf8mb4"
collate = "utf8mb4_0900_ai_ci"
}
EOT
+ id = (known after apply)
+ url = (sensitive value)
}
Plan: 1 to add, 0 to change, 0 to destroy.
╷
│ Warning: Atlas Plan
│
│ with atlas_schema.myapp,
│ on main.tf line 18, in resource "atlas_schema" "myapp":
│ 18: resource "atlas_schema" "myapp" {
│
│ The following SQL statements will be executed:
│
│
│ CREATE TABLE `myapp`.`hello` (`world` text NULL, `thoughts` varchar(100) NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci
│
╵
Do you want to perform these actions?
Terraform will perform the actions described above.
Only 'yes' will be accepted to approve.
Enter a value:
Notice that the plan shows the SQL statements that will be executed to create the database schema as well
as our loaded schema in its HCL representation - this was done by the schema loader that was used to load
the schema from the schema.sql file.
If you are happy with the plan, type yes and press enter to apply the changes:
Do you want to perform these actions?
Terraform will perform the actions described above.
Only 'yes' will be accepted to approve.
Enter a value: yes
atlas_schema.myapp: Modifying... [id=mysql:///myapp]
atlas_schema.myapp: Modifications complete after 4s [id=mysql:///myapp]
Apply complete! Resources: 0 added, 1 changed, 0 destroyed.
Hooray! We have successfully created a database schema using Terraform and the Atlas provider.
Terraform's true power comes from its declarative nature - we feed it a desired state and it will
make sure that the actual state matches the desired state. Atlas is a perfect match for this paradigm.
Let's see what happens if we change the schema in the schema.sql file and run terraform apply again:
Update the contents of schema.sql to the following:
create table `groups` (
id int not null auto_increment primary key,
name varchar(255) not null
);
create table `users` (
id int not null auto_increment primary key,
name varchar(255) not null,
group_id int not null,
foreign key (group_id) references `groups` (id)
);
Re-apply the changes:
terraform apply
Observe that our plan includes the addition of the groups table as well as the foreign key constraint
on the users table:
data.atlas_schema.sql: Reading...
data.atlas_schema.sql: Read complete after 4s [id=Qhci62i6CFYRQ2CmUOjMeA]
atlas_schema.myapp: Refreshing state... [id=mysql:///myapp]
Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following
symbols:
~ update in-place
Terraform will perform the following actions:
# atlas_schema.myapp will be updated in-place
~ resource "atlas_schema" "myapp" {
~ hcl = <<-EOT
+ table "groups" {
+ schema = schema.myapp
+ column "id" {
+ null = false
+ type = int
+ auto_increment = true
+ }
+ column "name" {
+ null = false
+ type = varchar(255)
+ }
+ primary_key {
+ columns = [column.id]
+ }
+ }
table "users" {
schema = schema.myapp
column "id" {
null = false
type = int
auto_increment = true
}
column "name" {
null = false
type = varchar(255)
}
+ column "group_id" {
+ null = false
+ type = int
+ }
primary_key {
columns = [column.id]
}
+ foreign_key "users_ibfk_1" {
+ columns = [column.group_id]
+ ref_columns = [table.groups.column.id]
+ on_update = NO_ACTION
+ on_delete = NO_ACTION
+ }
+ index "group_id" {
+ columns = [column.group_id]
+ }
}
schema "myapp" {
charset = "utf8mb4"
collate = "utf8mb4_0900_ai_ci"
}
EOT
id = "mysql:///myapp"
# (1 unchanged attribute hidden)
}
Plan: 0 to add, 1 to change, 0 to destroy.
╷
│ Warning: Atlas Plan
│
│ with atlas_schema.myapp,
│ on main.tf line 18, in resource "atlas_schema" "myapp":
│ 18: resource "atlas_schema" "myapp" {
│
│ The following SQL statements will be executed:
│
│
│ CREATE TABLE `myapp`.`groups` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) CHARSET
│ utf8mb4 COLLATE utf8mb4_0900_ai_ci
│ ALTER TABLE `myapp`.`users` ADD COLUMN `group_id` int NOT NULL, ADD INDEX `group_id` (`group_id`), ADD CONSTRAINT
│ `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `myapp`.`groups` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
│
╵
Do you want to perform these actions?
Terraform will perform the actions described above.
Only 'yes' will be accepted to approve.
Enter a value:
After typing yes and pressing enter, Terraform will apply the changes, bringing
the actual state of the database schema in line with the desired state:
atlas_schema.myapp: Modifying... [id=mysql:///myapp]
atlas_schema.myapp: Modifications complete after 4s [id=mysql:///myapp]
Apply complete! Resources: 0 added, 1 changed, 0 destroyed.
Conclusion
In this tutorial, we have seen how to use Terraform to manage the schema of a MySQL database using the Atlas provider with plain SQL. Using this workflow, teams can bridge the gap between their database schema management flows and their Terraform workflows, allowing for simpler and more reliable software delivery.
How can we make Atlas better?
We would love to hear from you on our Discord server ❤️.
