Github Actions job template that automates IaSQL code reviews

[depombo]

suggested by @yrobla. the Github actions job should take a pg connection string and assumes it will run on the repo it is in. It should have a button via workflow_dispatch hook that accepts a branch name to generate a PR on. markdown goes in transactions/ folder. Merging the PR triggers a CI job that calls iasql_commit with the URL to the PR which includes #

[mtp1376]

• https://iasql.com/docs/review/
• https://iasql.com/docs/envs/
• https://github.com/iasql/iasql/blob/main/rfcs/007%20-%20Peer%20review%20infra%20changes.md

---

Also, accepting a message in iasql_commit is in the scope of this issue.

[depombo]

• https://iasql.com/docs/review/
• https://iasql.com/docs/envs/
• https://github.com/iasql/iasql/blob/main/rfcs/007%20-%20Peer%20review%20infra%20changes.md

Also, accepting a message in iasql_commit is in the scope of this issue.

#2331 should be happen separately before or after this

[mtp1376]

suggested by @yrobla. the Github actions job should take a pg connection string and assumes it will run on the repo it is in. It should have a button via workflow_dispatch hook that accepts a branch name to generate a PR on. markdown goes in transactions/ folder. Merging the PR triggers a CI job that calls iasql_commit with the URL to the PR which includes #

It's not possible to pass variables (Postgres database connection string in this case) to the PR merged event. Therefore we need to use a Github secret for this purpose.

The user will need to set an IASQL_PG_CONN_STR secret in their Github repo so that we can use it in both workflow_dispatch and PR merge events.

[mtp1376]

1. Make changes in the IaSQL database, for example:

SELECT iasql_begin();
INSERT INTO bucket (name) VALUES ('mtp-test-x-15');

2. Create a Github Actions secret named IASQL_PG_CONN_STR and paste the connection string into it (don't include ?sslmode=noverify.
3. Trigger "IaSQL Create PR" job

It will create a PR as follows:

4. Merge the PR. It will run a job that will execute `iasql_commit`:

[mtp1376]

create-pr.yml

name: 'IaSQL Create PR'

on:
  workflow_dispatch:
    inputs:
      prTitle:
        description: 'Title for the PR'
        required: true
      prDescription:
        description: 'Description for the PR'
        required: false

jobs:
  create-pr:
    name: Create PR - ${{ inputs.prTitle }}
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Create markdown file
        id: create-markdown
        run: |
          PR=$(psql "$CON_STR" -AXqtc "SELECT * FROM iasql_create_review('$PR_TITLE', '$PR_DESCRIPTION');")
          mkdir -p transactions
          cd transactions
          echo "$PR" > "$PR_TITLE.md"
          echo 'pr<<EOF' >> $GITHUB_OUTPUT
          echo "$PR" >> $GITHUB_OUTPUT
          echo 'EOF' >> $GITHUB_OUTPUT
        env:
          CON_STR: ${{ secrets.IASQL_PG_CONN_STR }}
          PR_TITLE: ${{ inputs.prTitle }}
          PR_DESCRIPTION: ${{ inputs.prDescription }}
      - name: Create Pull Request
        id: cpr
        uses: peter-evans/create-pull-request@v4
        with:
          commit-message: ${{ inputs.prTitle }}
          delete-branch: false
          title: ${{ inputs.prTitle }}
          body: ${{ steps.create-markdown.outputs.pr }}
          branch: iasql/code-review
          branch-suffix: timestamp
          base: 'main'
      - name: Check outputs
        if: ${{ steps.cpr.outputs.pull-request-number }}
        run: |
          echo "Pull Request Number - ${{ steps.cpr.outputs.pull-request-number }}"
          echo "Pull Request URL - ${{ steps.cpr.outputs.pull-request-url }}"

pr-merged.yml

name: 'IaSQL Commit Changes on PR Merge'

on:
  pull_request:
    types:
      - closed

jobs:
  commit_changes:
    name: Commit changes to IaSQL from merged PR
    if: github.event.pull_request.merged == true && contains(github.head_ref, 'iasql/code-review-')

    runs-on: ubuntu-latest
    steps:
      - name: Run iasql_commit
        run: |
          echo "Running iasql_commit"
          COMMIT_RESULT=$(psql "$CON_STR" -AXqtc "SELECT * FROM iasql_commit('$PR_URL');")
          echo "Commit result: $COMMIT_RESULT"
        env:
          CON_STR: ${{ secrets.IASQL_PG_CONN_STR }}
          PR_URL: ${{ github.event.pull_request.url }}

[depombo]

This looks great to me!

1. Can we call the secret IASQL_PG_CONN_STR to make sure it is clear it is just a vanilla Postgres string?
2. How much work would it entail to put it up in the marketplace? Would prefer to do it in this task or a followup?
3. Does the correct error message show up in the job run of create-pr if there is no ongoing transaction ?

[aguillenv]

2. Create a Github Actions secret named IASQL_CONNECTION_STRING and paste the connection string into it (don't include ?sslmode=noverify.

why need to remove the sslmode?

[mtp1376]

2. Create a Github Actions secret named IASQL_CONNECTION_STRING and paste the connection string into it (don't include ?sslmode=noverify.

why need to remove the sslmode?

Because the default psql installed on the runner does not accept that arg.

[aguillenv]

3. Create a Github Actions secret named IASQL_CONNECTION_STRING and paste the connection string into it (don't include ?sslmode=noverify.

why need to remove the sslmode?

Because the default psql installed on the runner does not accept that arg.

Hmm I wonder if we should worry if it is not connecting with the db using SSL

[mtp1376]

This looks great to me!

1. Can we call the secret IASQL_PG_CONN_STR to make sure it is clear it is just a vanilla Postgres string?
2. How much work would it entail to put it up in the marketplace? Would prefer to do it in this task or a followup?
3. Does the correct error message show up in the job run of create-pr if there is no ongoing transaction ?

1. Changed to the more clear variable name you propsed and updated the original message.
2. I think it's going to be an async task with a lot of waiting, and I'm not sure that the current two-jobs approach will completely fit into Github Marketplace's structure. Anyway, I think it'd be better to make it another follow-up task.
3. This is how it will fail in that case:

success:

[depombo]

Sounds good, let's close this out and we can create another task to publish to Github Marketplace. I think it probably makes sense to publish each job separately to the GitHub marketplace?

[mtp1376]

Sounds good, let's close this out and we can create another task to publish to Github Marketplace. I think it probably makes sense to publish each job separately to the GitHub marketplace?

Yes. We should publish them separately.

[depombo]

#2404