How to Deploy A Web App and a Database to Azure using GitHub Actions

[lakruzz]

Chat with ChatGPT (OpenAI)

Lakruzz

I have a java jar file which runs a web app and a MySql database which the web app connects to. What would be the simplest setup for hosting these two services and make the app publicly available using Azure technology and services?

ChatGPT

To host your Java web app and MySQL database using Azure technology and services, you can follow these steps:

1. Create an Azure account: If you don't already have an Azure account, create one by visiting the Azure website and signing up.
2. Create an Azure Web App: In the Azure portal, create a new Web App and select the appropriate runtime stack for your Java app.
3. Deploy your Java web app: You can deploy your Java web app to Azure using FTP, Git, or Azure DevOps. You can also use Azure App Service's built-in continuous deployment feature to automatically deploy your app from a Git repository.
4. Create an Azure Database for MySQL: In the Azure portal, create a new Azure Database for MySQL and configure the server and database.
5. Connect your Java app to the MySQL database: Update the configuration of your Java app to connect to the MySQL database using the database connection string provided by Azure.
6. Configure firewall rules: Configure the firewall rules for your Azure Database for MySQL to allow your Java app to access the database.
7. Configure DNS: Configure the DNS settings for your web app so that it is publicly accessible using a custom domain name.

These steps should provide you with a basic setup for hosting your Java web app and MySQL database using Azure technology and services.

Lakruzz

is the Azure Web App specifically hosting java apps - or could it be any technology?

ChatGPT

Azure Web App is a fully managed web hosting service that supports a wide range of programming languages and frameworks including Java, .NET, Node.js, Python, PHP, and Ruby. So, it can host any technology as long as it's compatible with one of the supported programming languages or frameworks.

Lakruzz

How can I deploy using git? do you mean GitHub Actions or are you referring to a git repository?

ChatGPT

Azure Web App supports deployment from a Git repository, which means that you can deploy your Java web app to Azure by pushing your code changes to a Git repository, and Azure will automatically deploy the changes to your web app. Here are the high-level steps to deploy a Java web app to Azure Web App using Git:

1. Create an Azure Web App: In the Azure portal, create a new Web App and select the appropriate runtime stack for your Java app.
2. Create a Git repository: Create a Git repository on a code hosting service such as GitHub, GitLab, or Bitbucket.
3. Configure deployment credentials: In the Azure portal, configure deployment credentials for your web app to allow Git deployment.
4. Configure Git deployment: In the Azure portal, configure Git deployment for your web app by connecting your Git repository to your web app.
5. Push your code changes: Make your code changes to your local Git repository, commit the changes, and then push the changes to the remote Git repository.
6. Monitor the deployment: Monitor the deployment progress in the Azure portal, and check the logs to ensure that the deployment was successful.

You can use any Git client, such as Git command-line, Git GUI clients, or integrated development environments (IDEs) with Git support, to push your code changes to the Git repository.

Note that Azure Web App also supports deployment from other source control systems, such as GitHub Actions, Azure DevOps, and Bitbucket Pipelines, which provide more advanced

Lakruzz

OK, Sound really smart - can the MySql database also go into the web app - so it runs in the same instance with an internal connection - or would you recommend to host it separately as a service?

ChatGPT

Azure Web App allows you to run your web app and MySQL database in the same instance with an internal connection. However, hosting your MySQL database as a separate service can provide additional benefits such as improved scalability, availability, and security.

Running your MySQL database in a separate Azure Database for MySQL service, for example, can provide high availability, automatic backups, automatic patching, and built-in security features such as network isolation and encryption at rest.

• Here are some factors to consider when deciding whether to host your MySQL database as part of your web app or as a separate service:
• Scalability: If your application has high database load, it's better to separate the database into a dedicated service to ensure it can scale independently.
• Availability: If your application requires high availability, it's better to separate the database into a dedicated service to take advantage of the automatic failover and recovery features provided by Azure Database for MySQL.
• Security: Separating the database into a dedicated service can help improve the security of your application by enforcing network isolation and providing encryption at rest.
• Cost: Running your MySQL database as a separate service may incur additional costs, but it can also provide cost savings in terms of better resource utilization and improved scalability.

In general, it's recommended to separate your database into a dedicated service for better scalability, availability, and security. However, if your application has low database load and you're looking for a simpler deployment option, you can consider hosting the database within the same Azure Web App instance.

Lakruzz

you mentioned that Azure supports deployment from other systems and mentioned GitHub Actions. Say that I have a repository that contain the java code for the app and the sql to create the database. And say that I had created the Azure Web App and the Azure Database for MySql that you recommended. What would a workflow in GitHub Actions look like if it should trigger from a push to main branch and then automatically build and deploy the jar file and also test if there were any new *.sql files in the commit and if so, then execute them agains the database?

ChatGPT

Certainly, I can help you create a GitHub Actions workflow for your Java web app and MySQL database deployment scenario. Here's an example workflow that you can modify and adapt to your specific needs:

name: Java App and MySQL DB Deployment Workflow

on:
  push:
    branches:
      - main

jobs:
  build-and-deploy:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout Code
        uses: actions/checkout@v2

      - name: Setup Java
        uses: actions/setup-java@v2
        with:
          java-version: '11'

      - name: Build and Package Java App
        run: mvn package -DskipTests=true

      - name: Deploy to Azure Web App
        uses: azure/webapps-deploy@v2
        with:
          app-name: your-web-app-name
          publish-profile: ${{ secrets.AZURE_WEBAPP_PUBLISH_PROFILE }}
          package: './target/*.jar'

      - name: Check if SQL files have been modified
        id: check-sql-files
        run: |
          SQL_FILES=$(git diff --name-only HEAD HEAD~ | grep "\.sql$")
          if [[ -z "$SQL_FILES" ]]; then
            echo "No SQL files found."
          else
            echo "SQL files found: $SQL_FILES"
            echo "::set-output name=sql-files::$SQL_FILES"
          fi

      - name: Execute SQL files against Azure Database for MySQL
        if: steps.check-sql-files.outputs.sql-files != ''
        env:
          MYSQL_SERVER_NAME: your-mysql-server-name.mysql.database.azure.com
          MYSQL_DATABASE_NAME: your-mysql-database-name
          MYSQL_USERNAME: ${{ secrets.MYSQL_USERNAME }}
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
        run: |
          for file in ${GITHUB_WORKSPACE}/${{ steps.check-sql-files.outputs.sql-files }}; do
            mysql -h $MYSQL_SERVER_NAME -u $MYSQL_USERNAME -p$MYSQL_PASSWORD $MYSQL_DATABASE_NAME < $file
          done

Let me walk you through what this workflow does:

• The on clause specifies that the workflow should be triggered on push events to the main branch.
• The jobs section defines a single job called build-and-deploy that runs on an Ubuntu environment.
• The steps section lists the steps that the job should execute in order.
• The first step checks out the code from the repository.
• The second step sets up the Java environment.
• The third step builds and packages the Java app using Maven.
• The fourth step deploys the Java app to Azure Web App using the azure/webapps-deploy@v2 action. You'll need to replace your-web-app-name with the name of your Azure Web App, and provide the publish profile credentials as a secret (AZURE_WEBAPP_PUBLISH_PROFILE) in your repository's settings.
• The fifth step checks if any SQL files have been modified in the latest commit. If there are any, it sets an output variable sql-files to a list of file paths separated by spaces.
• The sixth step executes the SQL files against the Azure Database for MySQL. It first checks if the sql-files output variable is set, and if so, it loops through each SQL file and executes it using the mysql command-line tool. You'll need to replace the your-mysql-server-name, your-mysql-database-name, MYSQL_USERNAME, and MYSQL_PASSWORD placeholders with your actual MySQL server and database names, and provide the MYSQL_USERNAME and MYSQL_PASSWORD secrets in your repository's settings.

Lakruzz

Hmm I was thinking - wouldn't it be smarter to split this workflow up in to separate jobs. The first one could do the first four java related steps, but the fifth and sixths steps could be in a separate job, which only runs if the first job is successful?

ChatGPT

Yes, that's a good point. Breaking up the workflow into separate jobs can make it more modular and easier to manage. Here's an updated version of the workflow that splits the build and deploy steps into one job, and the SQL execution steps into a separate job that only runs if the build and deploy job succeeds:

name: Java App and MySQL DB Deployment Workflow

on:
  push:
    branches:
      - main

jobs:
  build-and-deploy:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout Code
        uses: actions/checkout@v2

      - name: Setup Java
        uses: actions/setup-java@v2
        with:
          java-version: '11'

      - name: Build and Package Java App
        run: mvn package -DskipTests=true

      - name: Deploy to Azure Web App
        uses: azure/webapps-deploy@v2
        with:
          app-name: your-web-app-name
          publish-profile: ${{ secrets.AZURE_WEBAPP_PUBLISH_PROFILE }}
          package: './target/*.jar'

  execute-sql:
    runs-on: ubuntu-latest
    needs: build-and-deploy
    if: success()

    steps:
      - name: Check if SQL files have been modified
        id: check-sql-files
        run: |
          SQL_FILES=$(git diff --name-only HEAD HEAD~ | grep "\.sql$")
          if [[ -z "$SQL_FILES" ]]; then
            echo "No SQL files found."
          else
            echo "SQL files found: $SQL_FILES"
            echo "::set-output name=sql-files::$SQL_FILES"
          fi

      - name: Execute SQL files against Azure Database for MySQL
        if: steps.check-sql-files.outputs.sql-files != ''
        env:
          MYSQL_SERVER_NAME: your-mysql-server-name.mysql.database.azure.com
          MYSQL_DATABASE_NAME: your-mysql-database-name
          MYSQL_USERNAME: ${{ secrets.MYSQL_USERNAME }}
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
        run: |
          for file in ${GITHUB_WORKSPACE}/${{ steps.check-sql-files.outputs.sql-files }}; do
            mysql -h $MYSQL_SERVER_NAME -u $MYSQL_USERNAME -p$MYSQL_PASSWORD $MYSQL_DATABASE_NAME < $file
          done

In this updated workflow, the build-and-deploy job performs the first four steps, while the execute-sql job performs the last two steps. The execute-sql job specifies that it should only run if the build-and-deploy job succeeds (using the needs and if clauses). This ensures that the SQL execution steps only happen if the Java app has been successfully built and deployed to Azure Web App.

Lakruzz

Nice, but what if I tell you that my sql files are always located in the directory src/sql. Then I could take the execute-sql job to a separate workflow that would trigger only an a specific path - could that work? Maybe we wouldn't have to do the first step at all then, since the job would only trigger if there were altered files in the path. And also the sql update wouldn't have to be dependent on the java build at all - what would that new sql workflow look like? - and how do you like this approach?

ChatGPT

Yes, that's a good approach too. If your SQL files are always located in the src/sql directory, you can use a separate workflow that triggers only when changes are made to that directory.

Here's what the new workflow would look like:

name: Execute SQL Workflow

on:
  push:
    paths:
      - 'src/sql/*.sql'

jobs:
  execute-sql:
    runs-on: ubuntu-latest

    steps:
      - name: Get Changed SQL Files
        id: get-changed-sql-files
        run: |
          CHANGED_FILES=$(git diff --name-only HEAD HEAD~ | grep "^src/sql/.*\.sql$")
          if [[ -z "$CHANGED_FILES" ]]; then
            echo "No SQL files found."
            exit 1
          else
            echo "Changed SQL files found: $CHANGED_FILES"
            echo "::set-output name=changed-sql-files::$CHANGED_FILES"
          fi

      - name: Execute SQL Files against Azure Database for MySQL
        env:
          MYSQL_SERVER_NAME: your-mysql-server-name.mysql.database.azure.com
          MYSQL_DATABASE_NAME: your-mysql-database-name
          MYSQL_USERNAME: ${{ secrets.MYSQL_USERNAME }}
          MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}
        run: |
          for file in ${GITHUB_WORKSPACE}/${{ steps.get-changed-sql-files.outputs.changed-sql-files }}; do
            mysql -h $MYSQL_SERVER_NAME -u $MYSQL_USERNAME -p$MYSQL_PASSWORD $MYSQL_DATABASE_NAME < $file
          done

In this new workflow, we are using the paths clause in the on trigger to specify that the workflow should only run when changes are made to files in the src/sql directory. The execute-sql job has two steps. The first step uses the git diff command to get a list of SQL files that have been changed since the last commit, and sets the list as an output variable (changed-sql-files). If no files are found, the step fails and the workflow stops. If files are found, the list is printed to the console and the output variable is set. The second step uses a for loop to execute each changed SQL file against the Azure Database for MySQL.

You can now use this separate workflow to execute SQL updates against your database whenever changes are made to SQL files in the src/sql directory.

Lakruzz

OK thanks but isn't the first step in the job irrelevant now? Since the workflow only trigger when there are changes to the path you specified? And also it should still only run on the main branch - the new workflow you came up with will run on any branch - which would have the unintended side effect that even changes made on feature branches would be deployed to production - before they were integrated to main - sounds dangerous!

ChatGPT

You're right, my apologies for the oversight. You can remove the first step in the execute-sql job since the workflow will only run when there are changes to the src/sql directory.

To limit the workflow to only run on the main branch, you can add the branches clause to the on trigger

Here's the updated workflow:

name: Execute SQL Workflow

on:
  push:
    branches:
      - main
    paths:
      - 'src/sql/*.sql'

jobs:
  execute-sql:
    runs-on: ubuntu-latest

    env:
      MYSQL_SERVER_NAME: your-mysql-server-name.mysql.database.azure.com
      MYSQL_DATABASE_NAME: your-mysql-database-name
      MYSQL_USERNAME: ${{ secrets.MYSQL_USERNAME }}
      MYSQL_PASSWORD: ${{ secrets.MYSQL_PASSWORD }}

    steps:
      - name: Execute SQL Files against Azure Database for MySQL
        run: |
          for file in ${GITHUB_WORKSPACE}/src/sql/*.sql; do
            mysql -h $MYSQL_SERVER_NAME -u $MYSQL_USERNAME -p$MYSQL_PASSWORD $MYSQL_DATABASE_NAME < $file
          done      
      

This will ensure that the workflow only runs when there are changes to SQL files on the main branch.

Thanks for catching that oversight!

Lakruzz

You're welcome and thanks for your help - we're good together. It's like having a really skilled partner in a paired programming session - Although I've been at this for many years now, I still learn a lot from coding with you!

I'll give our code a spin and see how it behaves!

Stay safe!

ChatGPT

You're welcome! It was my pleasure helping you out. I'm glad to hear that our collaboration was productive and that you learned something new. Don't hesitate to reach out if you have any more questions or if you need further assistance in the future. Have a great day and stay safe too!