如何通过Terraform或GitHub Actions自动化创建Azure SQL Database表

方案一：在Terraform中直接集成SQL脚本执行 #

你可以通过Terraform的原生资源或扩展能力，在数据库部署完成后自动执行建表SQL，无需额外手动操作：

方式1：使用azurerm_sql_script资源（推荐） #

AzureRM Provider提供了专门的azurerm_sql_script资源，会自动依赖SQL Server和Database资源，确保部署顺序正确，直接在目标数据库中执行指定SQL脚本。示例代码：

resource "azurerm_sql_server" "example" {
  name                         = "example-sql-server"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "sqladmin"
  administrator_login_password = "H@shPass123"
}

resource "azurerm_sql_database" "example" {
  name           = "example-sql-db"
  resource_group_name = azurerm_resource_group.example.name
  location       = azurerm_resource_group.example.location
  server_name    = azurerm_sql_server.example.name
  sku_name       = "GP_Gen5_2"
}

resource "azurerm_sql_script" "create_tables" {
  name                = "create-tables-script"
  resource_group_name = azurerm_resource_group.example.name
  server_name         = azurerm_sql_server.example.name
  database_name       = azurerm_sql_database.example.name
  script_content      = <<EOF
CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Username NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE Orders (
    Id INT PRIMARY KEY IDENTITY(1,1),
    UserId INT FOREIGN KEY REFERENCES Users(Id),
    OrderDate DATETIME NOT NULL DEFAULT GETDATE()
);
EOF
}

方式2：使用null_resource配合local-exec #

如果需要调用外部SQL文件或自定义执行逻辑，可通过null_resource结合local-exec，使用Azure CLI或sqlcmd执行建表命令：

resource "null_resource" "create_tables" {
  depends_on = [azurerm_sql_database.example]

  provisioner "local-exec" {
    command = <<EOT
az sql db execute-script \
  --resource-group ${azurerm_resource_group.example.name} \
  --server ${azurerm_sql_server.example.name} \
  --database ${azurerm_sql_database.example.name} \
  --admin-user ${azurerm_sql_server.example.administrator_login} \
  --admin-password ${azurerm_sql_server.example.administrator_login_password} \
  --file ./create_tables.sql
EOT
  }
}

注意：敏感信息（如管理员密码）建议通过Terraform变量或Azure Key Vault存储，避免硬编码。

方案二：通过GitHub Actions实现端到端自动化 #

如果需要将资源部署与表创建纳入CI/CD流程，GitHub Actions可实现完整的自动化流水线，分为Terraform部署和SQL脚本执行两个核心步骤：

示例GitHub Actions工作流文件（.github/workflows/azure-sql-deploy.yml） #

name: Deploy Azure SQL and Create Tables

on:
  push:
    branches: [ main ]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Azure Login
        uses: azure/login@v2
        with:
          creds: ${{ secrets.AZURE_CREDENTIALS }}

      - name: Setup Terraform
        uses: hashicorp/setup-terraform@v3

      - name: Terraform Init
        run: terraform init

      - name: Terraform Apply
        run: terraform apply -auto-approve
        env:
          ARM_CLIENT_ID: ${{ secrets.ARM_CLIENT_ID }}
          ARM_CLIENT_SECRET: ${{ secrets.ARM_CLIENT_SECRET }}
          ARM_TENANT_ID: ${{ secrets.ARM_TENANT_ID }}
          ARM_SUBSCRIPTION_ID: ${{ secrets.ARM_SUBSCRIPTION_ID }}

      - name: Get SQL Resource Names from Terraform Output
        id: tf-output
        run: |
          echo "server_name=$(terraform output -raw sql_server_name)" >> $GITHUB_OUTPUT
          echo "db_name=$(terraform output -raw sql_db_name)" >> $GITHUB_OUTPUT

      - name: Execute SQL Script to Create Tables
        run: |
          az sql db execute-script \
            --resource-group your-resource-group \
            --server ${{ steps.tf-output.outputs.server_name }} \
            --database ${{ steps.tf-output.outputs.db_name }} \
            --admin-user sqladmin \
            --admin-password ${{ secrets.SQL_ADMIN_PASSWORD }} \
            --file ./create_tables.sql

关键注意事项： #

• 提前在GitHub仓库的Secrets中配置AZURE_CREDENTIALS、ARM_*系列Azure凭证，以及SQL_ADMIN_PASSWORD等敏感信息。
• 使用terraform output动态获取SQL资源名称，避免硬编码，提升流水线灵活性。

内容的提问来源于stack exchange，提问作者bizimunda