Quickstart: Use GitHub Actions to connect to Azure PostgreSQL

APPLIES TO: Azure Database for PostgreSQL - Single Server

APPLIES TO: Azure Database for PostgreSQL - Single Server Azure Database for PostgreSQL - Flexible Server

Get started with GitHub Actions by using a workflow to deploy database updates to Azure Database for PostgreSQL.

Prerequisites

You will need:

Workflow file overview

A GitHub Actions workflow is defined by a YAML (.yml) file in the /.github/workflows/ path in your repository. This definition contains the various steps and parameters that make up the workflow.

The file has two sections:

Section Tasks
Authentication 1. Generate deployment credentials.
Deploy 1. Deploy the database.

Generate deployment credentials

Create a service principal with the az ad sp create-for-rbac command in the Azure CLI. Run this command with Azure Cloud Shell in the Azure portal or by selecting the Try it button.

az ad sp create-for-rbac --name "myML" --role contributor \
                            --scopes /subscriptions/<subscription-id>/resourceGroups/<group-name> \
                            --sdk-auth

In the example above, replace the placeholders with your subscription ID, resource group name, and app name. The output is a JSON object with the role assignment credentials that provide access to your App Service app similar to below. Copy this JSON object for later.

  {
    "clientId": "<GUID>",
    "clientSecret": "<GUID>",
    "subscriptionId": "<GUID>",
    "tenantId": "<GUID>",
    (...)
  }

Copy the PostgreSQL connection string

In the Azure portal, go to your Azure Database for PostgreSQL server and open Settings > Connection strings. Copy the ADO.NET connection string. Replace the placeholder values for your_database and your_password. The connection string will look similar to this.

Important

  • For Single server use user=adminusername@servername . Note the @servername is required.
  • For Flexible server , use user= adminusername without the @servername.
psql host={servername.postgres.database.azure.com} port=5432 dbname={your_database} user={adminusername} password={your_database_password} sslmode=require

You will use the connection string as a GitHub secret.

Configure the GitHub secrets

  1. In GitHub, go to your repository.

  2. Select Security > Secrets and variables > Actions.

    Screenshot of adding a secret

  3. Select New repository secret.

  4. Paste the entire JSON output from the Azure CLI command into the secret's value field. Give the secret the name AZURE_CREDENTIALS.

  5. Select Add secret.

Add your workflow

  1. Go to Actions for your GitHub repository.

  2. Select Set up your workflow yourself.

  3. Delete everything after the on: section of your workflow file. For example, your remaining workflow may look like this.

    name: CI
    
    on:
    push:
        branches: [ main ]
    pull_request:
        branches: [ main ]
    
  4. Rename your workflow PostgreSQL for GitHub Actions and add the checkout and login actions. These actions will checkout your site code and authenticate with Azure using the GitHub secret(s) you created earlier.

    name: PostgreSQL for GitHub Actions
    
    on:
    push:
        branches: [ main ]
    pull_request:
        branches: [ main ]
    
    jobs:
    build:
        runs-on: ubuntu-latest
        steps:
        - uses: actions/checkout@v1
        - uses: azure/login@v1
        with:
            creds: ${{ secrets.AZURE_CREDENTIALS }}
    
  5. Use the Azure PostgreSQL Deploy action to connect to your PostgreSQL instance. Replace POSTGRESQL_SERVER_NAME with the name of your server. You should have a PostgreSQL data file named data.sql at the root level of your repository.

     - uses: azure/postgresql@v1
       with:
        connection-string: ${{ secrets.AZURE_POSTGRESQL_CONNECTION_STRING }}
        server-name: POSTGRESQL_SERVER_NAME
        sql-file: './data.sql'
    
  6. Complete your workflow by adding an action to logout of Azure. Here is the completed workflow. The file will appear in the .github/workflows folder of your repository.

    name: PostgreSQL for GitHub Actions
    
    on:
    push:
        branches: [ main ]
    pull_request:
        branches: [ main ]
    
    
    jobs:
    build:
        runs-on: ubuntu-latest
        steps:
        - uses: actions/checkout@v1
        - uses: azure/login@v1
        with:
            client-id: ${{ secrets.AZURE_CREDENTIALS }}
    
    - uses: azure/postgresql@v1
      with:
        server-name: POSTGRESQL_SERVER_NAME
        connection-string: ${{ secrets.AZURE_POSTGRESQL_CONNECTION_STRING }}
        sql-file: './data.sql'
    
        # Azure logout
    - name: logout
      run: |
         az logout
    

Review your deployment

  1. Go to Actions for your GitHub repository.

  2. Open the first result to see detailed logs of your workflow's run.

    Log of GitHub Actions run

Clean up resources

When your Azure PostgreSQL database and repository are no longer needed, clean up the resources you deployed by deleting the resource group and your GitHub repository.

Next steps