1"use strict";(self.webpackChunkwebsite=self.webpackChunkwebsite||[]).push([[9325],{13239:(e,a,n)=>{n.r(a),n.d(a,{assets:()=>o,contentTitle:()=>l,default:()=>h,frontMatter:()=>i,metadata:()=>s,toc:()=>d});var s=n(53902),t=n(74848),r=n(71184);const i={title:"Manage database schemas with Terraform in plain SQL",authors:"rotemtam",tags:["schema","migration","terraform"],slug:"/2023/04/21/terraform-v050"},l=void 0,o={authorsImageUrls:[void 0]},d=[{value:"Improving the dev-database experience with the <code>docker://</code> driver",id:"improving-the-dev-database-experience-with-the-docker-driver",level:3},{value:"Defining the desired state of the database schema in plain SQL",id:"defining-the-desired-state-of-the-database-schema-in-plain-sql",level:3},{value:"Managing database schemas in plain SQL using Terraform",id:"managing-database-schemas-in-plain-sql-using-terraform",level:3},{value:"Conclusion",id:"conclusion",level:2},{value:"How can we make Atlas better?",id:"how-can-we-make-atlas-better",level:4}];function c(e){const a={a:"a",admonition:"admonition",code:"code",em:"em",h2:"h2",h3:"h3",h4:"h4",li:"li",mdxAdmonitionTitle:"mdxAdmonitionTitle",p:"p",pre:"pre",ul:"ul",...(0,r.R)(),...e.components};return(0,t.jsxs)(t.Fragment,{children:[(0,t.jsxs)(a.admonition,{title:"TL;DR",type:"info",children:[(0,t.jsx)(a.p,{children:"You can now use plain SQL to define the desired state of your database schema."}),(0,t.jsx)(a.p,{children:(0,t.jsx)(a.a,{href:"#managing-database-schemas-in-plain-sql-using-terraform",children:"See an example"})})]}),"\n",(0,t.jsxs)(a.p,{children:["Earlier today, we released ",(0,t.jsx)(a.a,{href:"https://github.com/ariga/terraform-provider-atlas/releases/tag/v0.5.0",children:"v0.5.0"}),"\nof the ",(0,t.jsx)(a.a,{href:"https://registry.terraform.io/providers/ariga/atlas/latest/docs",children:"Atlas Terraform Provider"}),".\nThis release includes two changes that, in my opinion, are a great improvement to the\nexperience of working with the Atlas Provider."]}),"\n",(0,t.jsx)(a.p,{children:"In this post, I will discuss these two changes and how they can help you to manage your database\nschemas with Terraform:"}),"\n",(0,t.jsxs)(a.ul,{children:["\n",(0,t.jsxs)(a.li,{children:["\n",(0,t.jsxs)(a.p,{children:["Support for the ",(0,t.jsx)(a.code,{children:"docker://"})," driver for dev-databases."]}),"\n"]}),"\n",(0,t.jsxs)(a.li,{children:["\n",(0,t.jsxs)(a.p,{children:["Support for defining the desired state of your database schema in plain SQL (and\nany other ",(0,t.jsx)(a.a,{href:"/blog/2023/03/13/atlas-v010#schema-loaders",children:"schema loader"})," supported by Atlas)."]}),"\n"]}),"\n"]}),"\n",(0,t.jsxs)(a.h3,{id:"improving-the-dev-database-experience-with-the-docker-driver",children:["Improving the dev-database experience with the ",(0,t.jsx)(a.code,{children:"docker://"})," driver"]}),"\n",(0,t.jsxs)(a.p,{children:['Atlas relies on a connection to an empty database which it can use to perform\nvarious calculations and operations. This database is called the "',(0,t.jsx)(a.a,{href:"/concepts/dev-database",children:"dev-database"}),'",\nand it allows Atlas to do things like validate the correctness of user-provided code as well as\nnormalize user-input to the way the database ',(0,t.jsx)(a.em,{children:"actually"})," sees it."]}),"\n",(0,t.jsxs)(a.p,{children:["In previous versions of the Atlas Provider, the dev-database needed to be provided\nby the user. This was a bit cumbersome, as the user needed to spin up a database (usually\nby running a local Docker container), and then provide the connection string to it\nin the ",(0,t.jsx)(a.code,{children:"dev_url"})," field of the ",(0,t.jsx)(a.code,{children:"atlas_schema"})," resource."]}),"\n",(0,t.jsxs)(a.p,{children:["To improve this experience, we added support for the ",(0,t.jsx)(a.code,{children:"docker://"})," driver, which allows\nthe user to only provide the database engine and version, and Atlas will spin up an\nephemeral container with the correct database engine and version. In addition, starting\nthis version, users may define the ",(0,t.jsx)(a.code,{children:"dev_url"})," on the provider scope. For example:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-hcl",children:'provider "atlas" {\n dev_url = "docker://mysql/8/myapp"\n}\n'})}),"\n",(0,t.jsx)(a.h3,{id:"defining-the-desired-state-of-the-database-schema-in-plain-sql",children:"Defining the desired state of the database schema in plain SQL"}),"\n",(0,t.jsxs)(a.p,{children:["In earlier versions of the Atlas Provider, the ",(0,t.jsx)(a.code,{children:"atlas_schema"})," resource required the user\nto provide an ",(0,t.jsx)(a.a,{href:"/atlas-schema/hcl",children:"Atlas HCL"})," file which describes the desired\nstate of the database schema. Many users found this
1syntax, which resembles Terraform's\nown, to be clean and concise. However, others disliked it and asked for a way to define\nthe desired state in plain SQL."]}),"\n",(0,t.jsxs)(a.p,{children:["To support this use-case, and many others, we have announced\n",(0,t.jsx)(a.a,{href:"/blog/2023/03/13/atlas-v010#schema-loaders",children:'support for "schema loaders"'})," -\ncomponents that can be used to load the desired schema from many kinds of sources\n(such as plain SQL, an existing database, or the data-model of an ORM). To use this capability,\nusers may use the ",(0,t.jsx)(a.code,{children:"atlas_schema"})," data source, which accepts a ",(0,t.jsx)(a.code,{children:"url"})," field that points to\nthe desired schema. The scheme of this URL determines which schema loader will be used, for instance:"]}),"\n",(0,t.jsxs)(a.ul,{children:["\n",(0,t.jsxs)(a.li,{children:[(0,t.jsx)(a.code,{children:"file://schema.sql"})," - loads the schema from a local SQL file."]}),"\n",(0,t.jsxs)(a.li,{children:[(0,t.jsx)(a.code,{children:"mysql://root:pass@localhost:3306/myapp"})," - loads the schema from an existing MySQL database."]}),"\n",(0,t.jsxs)(a.li,{children:[(0,t.jsx)(a.code,{children:"ent://service/ent/schema"})," - loads the schema from the schema of an ",(0,t.jsx)(a.a,{href:"https://entgo.io/",children:"Ent"}),"\nproject."]}),"\n"]}),"\n",(0,t.jsx)(a.h3,{id:"managing-database-schemas-in-plain-sql-using-terraform",children:"Managing database schemas in plain SQL using Terraform"}),"\n",(0,t.jsxs)(a.admonition,{type:"info",children:[(0,t.jsx)(a.mdxAdmonitionTitle,{}),(0,t.jsxs)(a.p,{children:["You can find the final code for this example ",(0,t.jsx)(a.a,{href:"https://gist.github.com/rotemtam/5454bb4b3647bbb854446fd01b15ced7",children:"here"}),"."]})]}),"\n",(0,t.jsx)(a.p,{children:"In the following example, we will show how you can use Terraform and the Atlas provider to manage\na MySQL database schema in plain SQL."}),"\n",(0,t.jsxs)(a.p,{children:["Let's start by creating a Terraform file named ",(0,t.jsx)(a.code,{children:"main.tf"})," installing the Atlas\nTerraform provider:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-hcl",children:'terraform {\n required_providers {\n atlas = {\n source = "ariga/atlas"\n version = "0.5.0"\n }\n }\n}\n'})}),"\n",(0,t.jsx)(a.p,{children:"In addition to installing the Atlas provider, we will also spin up a local MySQL database\nusing Docker which will represent our target database that we will manage with Terraform.\nIn a real-world scenario, you would probably use a managed database service such as AWS\nRDS or Google Cloud SQL, but for the purpose of brevity, a local database will suffice. Run:"}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{children:"docker run -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=pass -e MYSQL_DATABASE=myapp mysql:8\n"})}),"\n",(0,t.jsx)(a.p,{children:'Now that we have a database to manage, we can define the desired state of the database schema.\nAdd a file named "schema.sql" with the following content:'}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-sql",children:"create table users (\n id int not null auto_increment primary key,\n name varchar(255) not null\n);\n"})}),"\n",(0,t.jsxs)(a.p,{children:["Next, we will define an ",(0,t.jsx)(a.code,{children:"atlas_schema"})," data source that will load the schema from the\n",(0,t.jsx)(a.code,{children:"schema.sql"})," file:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-hcl",children:'data "atlas_schema" "sql" {\n src = "file://${path.module}/schema.sql"\n}\n'})}),"\n",(0,t.jsxs)(a.p,{children:["Finally, we will define an ",(0,t.jsx)(a.code,{children:"atlas_schema"})," resource that will manage the schema in the\ntarget database. In addition, we will configure the Atlas provider to use the ",(0,t.jsx)(a.code,{children:"docker://"}),"\ndriver to spin up a temporary database container:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-hcl",children:'provider "atlas" {\n dev_url = "docker://mysql/8/myapp"\n}\n\nresource "atlas_schema" "mysql" {\n url = "mysql://root:pass@localhost:3306/myapp"\n hcl = data.atlas_schema.sql.hcl\n}\n'})}),"\n",(0,t.jsxs)(a.p,{children:["Now that we have defined our Terraform configuration, we can run ",(0,t.jsx)(a.code,{children:"terraform init"})," to\ninstall the required providers:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-console",children:"terraform init\n"})}),"\n",(0,t.jsx)(a.p,{children:"This should output something like:"}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{children:'Initializing provider plugins...\n- Finding ariga/atlas versions matching "0.4.7"...\n- Installing ariga/atlas v0.5.0...\n- Installed ariga/atlas v0.5.0 (signed by a HashiCorp partner, key ID 45441FCEAAC3770C)\n\n# ...\n\nTerraform has been successfully initialized!\n'})}),"\n",(0,t.jsxs)(a.p,{children:["Finally, we can run ",(0,t.jsx)(a.code,{children:"terraform apply"})," to create the database schema:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-console",children:"terraform apply\n"})}),"\n",(0,t.jsx)(a.p,{children:"Terraform will print the following plan:"}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{children:'data.atlas_schema.sql: Reading...\ndata.atlas_schema.sql: Read complete after 4s [id=qnUvTyupgQzivof5LYWDOQ]\n\nTerraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following\nsymbols:\n + create\n\nTerraform will perform the following actions:\n\n # atlas_schema.myapp will be created\n + resource "atlas_schema" "myapp" {\n + hcl = <<-EOT\n table "hello" {\n schema = schema.myapp\n column "world" {\n null = true\n type = text\n }\n column "thoughts" {\n null = true\n type = varchar(100)\n }\n }\n schema "myapp" {\n charset = "utf8mb4"\n collate = "utf8mb4_0900_ai_ci"\n }\n EOT\n + id = (known after apply)\n + url = (sensitive value)\n }\n\nPlan: 1 to add, 0 to change, 0 to destroy.\n\u2577\n\u2502 Warning: Atlas Plan\n\u2502 \n\u2502 with atlas_schema.myapp,\n\u2502 on main.tf line 18, in resource "atlas_schema" "myapp":\n\u2502 18: resource "atlas_schema" "myapp" {\n\u2502 \n\u2502 The following SQL statements will be executed:\n\u2502 \n\u2502 \n\u2502 C
1REATE TABLE `myapp`.`hello` (`world` text NULL, `thoughts` varchar(100) NULL) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci\n\u2502 \n\u2575\n\nDo you want to perform these actions?\n Terraform will perform the actions described above.\n Only \'yes\' will be accepted to approve.\n\n Enter a value: \n'})}),"\n",(0,t.jsxs)(a.p,{children:["Notice that the plan shows the SQL statements that will be executed to create the database schema as well\nas our loaded schema in its HCL representation - this was done by the schema loader that was used to load\nthe schema from the ",(0,t.jsx)(a.code,{children:"schema.sql"})," file."]}),"\n",(0,t.jsxs)(a.p,{children:["If you are happy with the plan, type ",(0,t.jsx)(a.code,{children:"yes"})," and press enter to apply the changes:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{children:"Do you want to perform these actions?\n Terraform will perform the actions described above.\n Only 'yes' will be accepted to approve.\n\n Enter a value: yes\n\natlas_schema.myapp: Modifying... [id=mysql:///myapp]\natlas_schema.myapp: Modifications complete after 4s [id=mysql:///myapp]\n\nApply complete! Resources: 0 added, 1 changed, 0 destroyed.\n"})}),"\n",(0,t.jsx)(a.p,{children:"Hooray! We have successfully created a database schema using Terraform and the Atlas provider."}),"\n",(0,t.jsxs)(a.p,{children:["Terraform's true power comes from its declarative nature - we feed it a desired state and it will\nmake sure that the actual state matches the desired state. Atlas is a perfect match for this paradigm.\nLet's see what happens if we change the schema in the ",(0,t.jsx)(a.code,{children:"schema.sql"})," file and run ",(0,t.jsx)(a.code,{children:"terraform apply"})," again:"]}),"\n",(0,t.jsxs)(a.p,{children:["Update the contents of ",(0,t.jsx)(a.code,{children:"schema.sql"})," to the following:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-sql",children:"create table `groups` (\n id int not null auto_increment primary key,\n name varchar(255) not null\n);\n\ncreate table `users` (\n id int not null auto_increment primary key,\n name varchar(255) not null,\n group_id int not null,\n foreign key (group_id) references `groups` (id)\n);\n"})}),"\n",(0,t.jsx)(a.p,{children:"Re-apply the changes:"}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{className:"language-console",children:"terraform apply\n"})}),"\n",(0,t.jsxs)(a.p,{children:["Observe that our plan includes the addition of the ",(0,t.jsx)(a.code,{children:"groups"})," table as well as the foreign key constraint\non the ",(0,t.jsx)(a.code,{children:"users"})," table:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{children:'data.atlas_schema.sql: Reading...\ndata.atlas_schema.sql: Read complete after 4s [id=Qhci62i6CFYRQ2CmUOjMeA]\natlas_schema.myapp: Refreshing state... [id=mysql:///myapp]\n\nTerraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following\nsymbols:\n ~ update in-place\n\nTerraform will perform the following actions:\n\n # atlas_schema.myapp will be updated in-place\n ~ resource "atlas_schema" "myapp" {\n ~ hcl = <<-EOT\n + table "groups" {\n + schema = schema.myapp\n + column "id" {\n + null = false\n + type = int\n + auto_increment = true\n + }\n + column "name" {\n + null = false\n + type = varchar(255)\n + }\n + primary_key {\n + columns = [column.id]\n + }\n + }\n table "users" {\n schema = schema.myapp\n column "id" {\n null = false\n type = int\n auto_increment = true\n }\n column "name" {\n null = false\n type = varchar(255)\n }\n + column "group_id" {\n + null = false\n + type = int\n + }\n primary_key {\n columns = [column.id]\n }\n + foreign_key "users_ibfk_1" {\n + columns = [column.group_id]\n + ref_columns = [table.groups.column.id]\n + on_update = NO_ACTION\n + on_delete = NO_ACTION\n + }\n + index "group_id" {\n + columns = [column.group_id]\n + }\n }\n schema "myapp" {\n charset = "utf8mb4"\n collate = "utf8mb4_0900_ai_ci"\n }\n EOT\n id = "mysql:///myapp"\n # (1 unchanged attribute hidden)\n }\n\nPlan: 0 to add, 1 to change, 0 to destroy.\n\u2577\n\u2502 Warning: Atlas Plan\n\u2502 \n\u2502 with atlas_schema.myapp,\n\u2502 on main.tf line 18, in resource "atlas_schema" "myapp":\n\u2502 18: resource "atlas_schema" "myapp" {\n\u2502 \n\u2502 The following SQL statements will be executed:\n\u2502 \n\u2502 \n\u2502 C
1REATE TABLE `myapp`.`groups` (`id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)) CHARSET\n\u2502 utf8mb4 COLLATE utf8mb4_0900_ai_ci\n\u2502 ALTER TABLE `myapp`.`users` ADD COLUMN `group_id` int NOT NULL, ADD INDEX `group_id` (`group_id`), ADD CONSTRAINT\n\u2502 `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `myapp`.`groups` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION\n\u2502 \n\u2575\n\nDo you want to perform these actions?\n Terraform will perform the actions described above.\n Only \'yes\' will be accepted to approve.\n\n Enter a value: \n'})}),"\n",(0,t.jsxs)(a.p,{children:["After typing ",(0,t.jsx)(a.code,{children:"yes"})," and pressing enter, Terraform will apply the changes, bringing\nthe actual state of the database schema in line with the desired state:"]}),"\n",(0,t.jsx)(a.pre,{children:(0,t.jsx)(a.code,{children:"atlas_schema.myapp: Modifying... [id=mysql:///myapp]\natlas_schema.myapp: Modifications complete after 4s [id=mysql:///myapp]\n\nApply complete! Resources: 0 added, 1 changed, 0 destroyed.\n"})}),"\n",(0,t.jsx)(a.h2,{id:"conclusion",children:"Conclusion"}),"\n",(0,t.jsx)(a.p,{children:"In this tutorial, we have seen how to use Terraform to manage the schema of a MySQL database\nusing the Atlas provider with plain SQL. Using this workflow, teams can bridge the gap between\ntheir database schema management flows and their Terraform workflows, allowing for simpler and\nmore reliable software delivery."}),"\n",(0,t.jsx)(a.h4,{id:"how-can-we-make-atlas-better",children:"How can we make Atlas better?"}),"\n",(0,t.jsxs)(a.p,{children:["We would love to hear from you ",(0,t.jsx)(a.a,{href:"https://discord.gg/zZ6sWVg6NT",children:"on our Discord server"})," ","\u2764\ufe0f","."]})]})}function h(e={}){const{wrapper:a}={...(0,r.R)(),...e.components};return a?(0,t.jsx)(a,{...e,children:(0,t.jsx)(c,{...e})}):c(e)}},53902:e=>{e.exports=JSON.parse('{"permalink":"/blog/2023/04/21/terraform-v050","source":"@site/blog/2023-04-21-terraform-v050.md","title":"Manage database schemas with Terraform in plain SQL","description":"You can now use plain SQL to define the desired state of your database schema.","date":"2023-04-21T00:00:00.000Z","tags":[{"inline":true,"label":"schema","permalink":"/blog/tags/schema"},{"inline":true,"label":"migration","permalink":"/blog/tags/migration"},{"inline":true,"label":"terraform","permalink":"/blog/tags/terraform"}],"readingTime":8.16,"hasTruncateMarker":true,"authors":[{"name":"Rotem Tamir","title":"Building Atlas","url":"https://github.com/rotemtam","twitter":"_rtam","imageURL":"https://s.gravatar.com/avatar/36b3739951a27d2e37251867b7d44b1a?s=80","key":"rotemtam","page":null}],"frontMatter":{"title":"Manage database schemas with Terraform in plain SQL","authors":"rotemtam","tags":["schema","migration","terraform"],"slug":"/2023/04/21/terraform-v050"},"unlisted":false,"prevItem":{"title":"Announcing v0.11.0: Manage database schemas with Kubernetes and Atlas","permalink":"/blog/2023/05/08/atlas-v011-kubernetes-operator"},"nextItem":{"title":"Troubleshooting schema migration issues with Atlas","permalink":"/blog/2023/04/10/troubleshooting-migrations"}}')}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.