GitHub Actions CI/CD (6/10): Integration Tests — Real Dependencies in CI

Summary: Add a database-backed feature to helloci, write an integration test that talks to a real Postgres instance, and run it in CI using GitHub Actions service containers. This is where CI proves it can test more than just pure logic.

Key	Value
Package name	helloci
Working directory	~/projects/helloci
Database	PostgreSQL 16
Database name	helloci_db
DB user / password	helloci_user / helloci_pass
pytest marker	integration
Service container	postgres:16

0. Prerequisites

• The helloci project with matrix CI from Part 5
• Basic familiarity with SQL (just CREATE TABLE, INSERT, SELECT)
• Docker installed locally if you want to run integration tests on your machine (optional — CI handles this for you)

---

1. Add a Database Feature

Create the file src/helloci/db.py. This module stores greetings in a Postgres database.

"""Database operations for helloci."""

import psycopg


def store_greeting(conninfo: str, name: str) -> int:
    """Store a greeting in the database and return its ID."""
    with psycopg.connect(conninfo) as conn:
        with conn.cursor() as cur:
            cur.execute(
                """
                CREATE TABLE IF NOT EXISTS greetings (
                    id SERIAL PRIMARY KEY,
                    name TEXT NOT NULL,
                    message TEXT NOT NULL
                )
                """
            )
            cur.execute(
                "INSERT INTO greetings (name, message) VALUES (%s, %s) RETURNING id",
                (name, f"Hello, {name}!"),
            )
            row = cur.fetchone()
            conn.commit()
            return row[0]


def get_greeting(conninfo: str, greeting_id: int) -> str | None:
    """Retrieve a greeting message by ID."""
    with psycopg.connect(conninfo) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT message FROM greetings WHERE id = %s",
                (greeting_id,),
            )
            row = cur.fetchone()
            return row[0] if row else NoneCode language: Python (python)

The conninfo parameter is a Postgres connection string like postgresql://user:pass@host:5432/dbname. Passing it as a parameter (instead of hardcoding or reading from environment) makes the functions easy to test.

psycopg is the modern PostgreSQL adapter for Python. It replaced the older psycopg2.

---

2. Add psycopg as a Dependency

Update pyproject.toml to add psycopg as an optional dependency, since not every user of helloci needs database features.

[project.optional-dependencies]
db = [
    "psycopg[binary]>=3.1",
]
test = [
    "pytest>=8.0",
    "ruff>=0.4",
    "psycopg[binary]>=3.1",
]Code language: TOML, also INI (ini)

Install the updated dependencies.

pip install -e ".[test]"Code language: Shell Session (shell)

Note: psycopg[binary] includes a bundled copy of the Postgres client library so you do not need to install system-level libpq packages.

---

3. Write the Integration Test

Create the file tests/test_db.py.

"""Integration tests for database operations."""

import os

import pytest

from helloci.db import get_greeting, store_greeting

CONNINFO = os.environ.get(
    "DATABASE_URL",
    "postgresql://helloci_user:helloci_pass@localhost:5432/helloci_db",
)


@pytest.mark.integration
def test_store_and_retrieve_greeting():
    greeting_id = store_greeting(CONNINFO, "Alice")
    assert greeting_id is not None
    assert greeting_id > 0

    message = get_greeting(CONNINFO, greeting_id)
    assert message == "Hello, Alice!"


@pytest.mark.integration
def test_store_multiple_greetings():
    id_1 = store_greeting(CONNINFO, "Bob")
    id_2 = store_greeting(CONNINFO, "Carol")
    assert id_1 != id_2

    assert get_greeting(CONNINFO, id_1) == "Hello, Bob!"
    assert get_greeting(CONNINFO, id_2) == "Hello, Carol!"


@pytest.mark.integration
def test_get_nonexistent_greeting():
    result = get_greeting(CONNINFO, 999999)
    assert result is NoneCode language: Python (python)

Key details:

• @pytest.mark.integration — marks these tests so you can run them separately from unit tests.
• DATABASE_URL — read from environment, with a local default. CI will set this environment variable.
• Real database calls — these tests connect to Postgres, create tables, and insert rows. They are not mocked.

---

4. Register the Custom Marker

Add the marker to pyproject.toml so pytest does not warn about unknown marks.

[tool.pytest.ini_options]
markers = [
    "integration: marks tests that need a running database",
]Code language: TOML, also INI (ini)

---

5. Run Unit Tests and Integration Tests Separately

Run only unit tests (skip integration).

pytest -v -m "not integration"Code language: Shell Session (shell)

11 passed, 3 deselected

Run only integration tests.

pytest -v -m integrationCode language: Shell Session (shell)

This requires a running Postgres instance. If you have Docker locally:

docker run -d --name helloci-pg \
  -e POSTGRES_USER=helloci_user \
  -e POSTGRES_PASSWORD=helloci_pass \
  -e POSTGRES_DB=helloci_db \
  -p 5432:5432 \
  postgres:16Code language: Shell Session (shell)

Wait a few seconds for Postgres to start, then run the integration tests.

pytest -v -m integrationCode language: Shell Session (shell)

3 passed

Stop and remove the container when done.

docker stop helloci-pg && docker rm helloci-pgCode language: Shell Session (shell)

Tip: You do not need Docker locally to benefit from integration tests. CI will handle the database for you. Local Docker is just convenient for debugging.

---

6. Add a Service Container to the CI Workflow

Update .github/workflows/ci.yml to add a Postgres service container and an integration test step.

name: CI

on:
  push:
    branches: [main]
  pull_request:
    branches: [main]

jobs:
  lint:
    runs-on: ubuntu-latest
    steps:
      - name: Check out code
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.12"

      - name: Install dependencies
        run: pip install -e ".[test]"

      - name: Lint
        run: ruff check .

      - name: Check formatting
        run: ruff format . --check

  unit-tests:
    runs-on: ubuntu-latest
    strategy:
      matrix:
        python-version: ["3.10", "3.11", "3.12"]
      fail-fast: false

    steps:
      - name: Check out code
        uses: actions/checkout@v4

      - name: Set up Python ${{ matrix.python-version }}
        uses: actions/setup-python@v5
        with:
          python-version: ${{ matrix.python-version }}

      - name: Install dependencies
        run: pip install -e ".[test]"

      - name: Run unit tests
        run: pytest -v -m "not integration"

  integration-tests:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: helloci_user
          POSTGRES_PASSWORD: helloci_pass
          POSTGRES_DB: helloci_db
        ports:
          - 5432:5432
        options: >-
          --health-cmd="pg_isready -U helloci_user -d helloci_db"
          --health-interval=10s
          --health-timeout=5s
          --health-retries=5

    steps:
      - name: Check out code
        uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.12"

      - name: Install dependencies
        run: pip install -e ".[test]"

      - name: Run integration tests
        env:
          DATABASE_URL: postgresql://helloci_user:helloci_pass@localhost:5432/helloci_db
        run: pytest -v -m integrationCode language: YAML (yaml)

This is a significant upgrade from the single-job workflow in Part 5. There are now three jobs.

---

7. Understand Service Containers

The services block:

services:
  postgres:
    image: postgres:16
    env:
      POSTGRES_USER: helloci_user
      POSTGRES_PASSWORD: helloci_pass
      POSTGRES_DB: helloci_db
    ports:
      - 5432:5432
    options: >-
      --health-cmd="pg_isready -U helloci_user -d helloci_db"
      --health-interval=10s
      --health-timeout=5s
      --health-retries=5Code language: YAML (yaml)

A service container is a Docker container that GitHub Actions starts alongside your job. It runs on the same network as the runner, so your code connects to localhost:5432 just like a local Docker container.

Key	What it does
image: postgres:16	Pulls the official Postgres 16 Docker image
env	Sets environment variables inside the container (user, password, database)
ports	Maps container port 5432 to the runner’s port 5432
options	Docker health check — the job waits until pg_isready succeeds

The health check is critical. Without it, your test might try to connect before Postgres has finished starting. The --health-retries=5 setting gives Postgres up to 50 seconds to become ready.

Note: Service containers are ephemeral. They are created fresh for every job run and destroyed afterward. No data persists between runs.

---

8. Push and Watch the Three Jobs

Commit and push.

git add .
git commit -m "Add integration tests with Postgres service container"
git pushCode language: Shell Session (shell)

Open the Actions tab. You now see three jobs running in parallel.

lint ✓ unit-tests (3.10) ✓ unit-tests (3.11) ✓ unit-tests (3.12) ✓ integration-tests ✓

The lint job finishes first (under 30 seconds). The unit-tests matrix runs in parallel. The integration-tests job takes slightly longer because it waits for Postgres to start.

---

Summary

You added a database feature to helloci, wrote integration tests that talk to a real Postgres instance, and ran them in CI using GitHub Actions service containers.

• src/helloci/db.py — stores and retrieves greetings from Postgres
• tests/test_db.py — three integration tests with @pytest.mark.integration
• pytest -m "not integration" — runs only fast unit tests
• pytest -m integration — runs only database tests
• Service containers start a fresh Postgres alongside your CI runner
• Health checks ensure the database is ready before tests start

The CI workflow now has three jobs: lint, unit tests (matrix), and integration tests. In Part 7 you will protect the main branch so that these checks must pass before anyone can merge a pull request.