Introduction:

In today's fast-paced development environment, automating repetitive tasks like database creation and testing is crucial for ensuring a smooth and efficient workflow. GitHub Actions provides a powerful platform for automating various tasks within your software development process. In this blog, we'll walk you through a GitHub Actions YAML file that accomplishes the following tasks:

Set up PostgreSQL Database:

In this step, we will set up a Postgres instance. The PostgreSQL instance is configured with specific environment variables such as POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB, POSTGRES_SCHEMA, and POSTGRES_THREAD. The container exposes port 5432 for communication with the outside world.

services:
  postgres:
    image: postgres:14
    env:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: jaffle_shop
      POSTGRES_SCHEMA: dbt_alice
      POSTGRES_THREAD: 4
    ports:
      - 5432:5432
    options: >-
      --health-cmd pg_isready
      --health-interval 10s
      --health-timeout 5s
      --health-retries 5

Prepare the Environment:

Next, we need to prepare the environment for executing our tests. This involves setting up Python 3.9 and installing the required dependencies.

steps:
  - uses: actions/checkout@v2
  - name: Set up Python 3.9
    uses: actions/setup-python@v2
    with:
      python-version: 3.9

Install Dependencies

Now, we proceed to install the necessary Python packages and dbt. The requirements.txt file is installed, followed by installing the dbt PostgreSQL adapter (dbt-postgres), dbt core (dbt-core), and pytest (pytest). Additionally, we run some dbt commands such as dbt debug, dbt seed, dbt run, and dbt test.

steps:
  # ...
  - name: Install dependencies
    run: |
      cd jaffle_shop/
      python -m pip install --upgrade pip
      pip install -r requirements.txt
      pip install dbt-postgres
      pip install dbt-core
      pip install pytest
      dbt debug
      dbt seed
      dbt run
      dbt test

So far we have built the following:

jobs:

  test:
    name: Test
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:14
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: jaffle_shop
          POSTGRES_SCHEMA: dbt_alice
          POSTGRES_THREAD: 4
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:

    - uses: actions/checkout@v2
    - name: Set up Python 3.9
      uses: actions/setup-python@v2
      with:
        python-version: 3.9
    - name: Install dependencies
      run: |
        cd jaffle_shop/
        python -m pip install --upgrade pip
        pip install -r requirements.txt
        pip install dbt-postgres
        pip install dbt-core
        pip install pytest
        dbt debug
        dbt seed
        dbt run
        dbt test

Run tests

Finally, two additional steps run after the "Test" step. These steps will run tests using pytest framework to check the results using assertions. You can add tests in this step as per your model or business logic.

Name: Run tests
      run: |
         cd jaffle_shop/
         python -m pytest tests/functional/test_example_failing.py -sv

The final output will look like the below:

Conclusion:

With this GitHub Actions YAML file, you can automate the process of creating a PostgreSQL database, setting up dbt and running tests using pytest. This automation enhances your development workflow, ensuring that you can focus more on writing code and less on manual setup and testing.

By leveraging the power of GitHub Actions and well-organized YAML files, your team can achieve a more efficient, reliable, and automated software development process, ultimately leading to faster delivery and higher-quality software products.

References:
To setup dbt, dbt test data, dbt custom tests and postgress service, I found the following references useful:

1. To know more about dbt you can refer dbt introduction

2. To setup the dbt test data used for this blog you can refer this Test data setup

3. Creating postgress service using GitHub actions

4. Example of dbt custom tests

Note:

This blog was initially published at:
Automating Data Builds with dbt and GitHub Actions - DEV Community