IntegreSQL

IntegreSQL manages isolated PostgreSQL databases for your integration tests.

Do your engineers a favour by allowing them to write fast executing, parallel and deterministic integration tests utilizing real PostgreSQL test databases. Resemble your live environment in tests as close as possible.

sequenceDiagram
    You->>Testrunner: Start tests

    Testrunner->>IntegreSQL: New template database
    IntegreSQL->>PostgreSQL: 
    PostgreSQL-->>IntegreSQL: 
    IntegreSQL-->>Testrunner: 

    Testrunner->>PostgreSQL: Connect to template database, apply all migrations, seed all fixtures, ..., disconnect.
    PostgreSQL-->>Testrunner: 

    Testrunner->>IntegreSQL: Finalize the template database
    IntegreSQL-->>Testrunner: 

    Note over Testrunner,PostgreSQL: Your test runner can now get isolated test databases for this hash from the pool!

    loop Each test
    Testrunner->>IntegreSQL: Get test database (looks like template database)
    Testrunner->>PostgreSQL: 
    Note over Testrunner,PostgreSQL: Run your test code in an isolated test database!

    Testrunner-xPostgreSQL: Disconnect from the test database.
    end

• IntegreSQL
• Install
• Usage
  • Run using Docker (preferred)
  • Run locally (not recommended)
  • Run within your CI/CD
  • GitHub Actions
• Integrate
  • Integrate by client lib
  • Integrate by RESTful JSON calls
  • Once per test runner/process
    • Testrunner creates a new template database
    • Testrunner reuses an existing template database
    • Failure modes while template database setup: 503
  • Per each test
    • New test database per test
    • Optional: Manually unlocking a test database after a readonly test
    • Optional: Manually recreating a test database
    • Failure modes while getting a new test database
    • StatusNotFound 404
    • StatusGone 410
    • StatusServiceUnavailable 503
  • Demo
• Configuration
• Architecture
  • TestDatabase states
  • Pool structure
• Background
  • Approach 0: Leaking database mutations for subsequent tests
  • Approach 1: Isolating by resetting
  • Approach 2a: Isolation by transactions
  • Approach 2b: Isolation by mocking
  • Approach 3a: Isolation by templates
  • Approach 3b: Isolation by cached templates
  • Approach 3c: Isolation by cached templates and pool
  • Approach 3c benchmark 1: Baseline
  • Approach 3c benchmark 2: Small project
  • Final approach: IntegreSQL
• Benchmarks
  • Benchmark v1.1.0 vs v1.0.0
• Contributing
  • Development setup
  • Development quickstart
• Maintainers
  • Previous maintainers
• License

Install

A minimal Docker image is published on GitHub Packages. See GitHub Releases.

docker pull ghcr.io/allaboutapps/integresql:<TAG>

Usage

IntegreSQL is a RESTful JSON API distributed as Docker image and go cli. It's language agnostic and manages multiple PostgreSQL templates and their separate pool of test databases for your tests. It keeps the pool of test databases warm (as it's running in the background) and is fit for parallel test execution with multiple test runners / processes.

Run using Docker (preferred)

Simply start a Docker (19.03 or above) container, provide the required environment variables and expose the server port:

docker run -d --name integresql -e INTEGRESQL_PORT=5000 -p 5000:5000 ghcr.io/allaboutapps/integresql:<TAG>

The container can also be included in your project via Docker Compose (1.25 or above):

version: "3.4"
services:

  # Your main service image
  service:
    depends_on:
      - postgres
      - integresql
    environment:
      PGDATABASE: &PGDATABASE "development"
      PGUSER: &PGUSER "dbuser"
      PGPASSWORD: &PGPASSWORD "9bed16f749d74a3c8bfbced18a7647f5"
      PGHOST: &PGHOST "postgres"
      PGPORT: &PGPORT "5432"
      PGSSLMODE: &PGSSLMODE "disable"

      # optional: env for integresql client testing
      # see https://github.com/allaboutapps/integresql-client-go
      # INTEGRESQL_CLIENT_BASE_URL: "http://integresql:5000/api"

      # [...] additional main service setup

  integresql:
    image: ghcr.io/allaboutapps/integresql:<TAG>
    ports:
      - "5000:5000"
    depends_on:
      - postgres
    environment: 
      PGHOST: *PGHOST
      PGUSER: *PGUSER
      PGPASSWORD: *PGPASSWORD

  postgres:
    image: postgres:12.2-alpine # should be the same version as used live
    # ATTENTION
    # fsync=off, synchronous_commit=off and full_page_writes=off
    # gives us a major speed up during local development and testing (~30%),
    # however you should NEVER use these settings in PRODUCTION unless
    # you want to have CORRUPTED data.
    # DO NOT COPY/PASTE THIS BLINDLY.
    # YOU HAVE BEEN WARNED.
    # Apply some performance improvements to pg as these guarantees are not needed while running locally
    command: "postgres -c 'shared_buffers=128MB' -c 'fsync=off' -c 'synchronous_commit=off' -c 'full_page_writes=off' -c 'max_connections=100' -c 'client_min_messages=warning'"
    expose:
      - "5432"
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: *PGDATABASE
      POSTGRES_USER: *PGUSER
      POSTGRES_PASSWORD: *PGPASSWORD
    volumes:
      - pgvolume:/var/lib/postgresql/data

volumes:
  pgvolume: # declare a named volume to persist DB data

You may also refer to our go-starter docker-compose.yml.

Run locally (not recommended)

Installing IntegreSQL locally requires a working Go (1.14 or above) environment. Install the integresql executable to your Go bin folder:

# This installs the latest version of IntegreSQL into your $GOBIN
go install github.com/allaboutapps/integresql/cmd/server@latest

# you may want to rename the binary to integresql after installing:
mv $GOBIN/server $GOBIN/integresql

Running the IntegreSQL server locally requires configuration via exported environment variables (see below).

export INTEGRESQL_PORT=5000
export PGHOST=127.0.0.1
export PGUSER=test
export PGPASSWORD=testpass
integresql

Run within your CI/CD

You'll also want to use integresql within your CI/CD pipeline. We recommend using the Docker image. Simply run it next to the postgres service.

GitHub Actions

For a working sample see allaboutapps/go-starter.

jobs:
  build-test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:<TAG>
        env:
          POSTGRES_DB: "development"
          POSTGRES_USER: "dbuser"
          POSTGRES_PASSWORD: "dbpass"
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
      integresql:
        image: ghcr.io/allaboutapps/integresql:<TAG>
        env:
          PGHOST: "postgres"
          PGUSER: "dbuser"
          PGPASSWORD: "dbpass"

Integrate

You will typically want to integrate by a client lib (see below), but you can also integrate by RESTful JSON calls directly. The flow is illustrated in the follow up section.

Integrate by client lib

It's simple to integrate especially if there is already an client library available for your specific language. We currently have those:

• Go: integresql-client-go by Nick Müller - @MorpheusXAUT
• Python: integresql-client-python by Marcin Sztolcman - @msztolcman
• .NET: IntegreSQL.EF by Artur Drobinskiy - @Shaddix
• JavaScript/TypeScript: @devoxa/integresql-client by Devoxa - @devoxa
• ... Add your link here and make a PR

Integrate by RESTful JSON calls

A really good starting point to write your own integresql-client for a specific language can be found here (go code) and here (godoc). It's just RESTful JSON after all.

First start IntegreSQL and leave it running in the background (your PostgreSQL template and test database pool will then always be warm). When you trigger your test command (e.g. make test), 1..n test runners/processes can start in parallel and get ready and isoloated test database from the pool (after the template database(s) was/were initialized).

Once per test runner/process

Each test runner starts and need to communicate with IntegreSQL to setup 1..n template database pools. The following sections describe the flows/scenarios you need to implement.

Testrunner creates a new template database

sequenceDiagram
    You->>Testrunner: make test

    Note right of Testrunner: Compute a hash over all related 

 files that affect your database

 (migrations, fixtures, imports, etc.)

    Note over Testrunner,IntegreSQL: Create a new PostgreSQL template database

 identified a the same unique hash 

payload: {"hash": "string"} 

    Testrunner->>IntegreSQL: InitializeTemplate: POST /api/v1/templates

    IntegreSQL->>PostgreSQL: CREATE DATABASE 

template_<hash>
    PostgreSQL-->>IntegreSQL: 

    IntegreSQL-->>Testrunner: StatusOK: 200

    Note over Testrunner,PostgreSQL: Parse the received database connection payload and connect to the template database.

    Testrunner->>PostgreSQL: Apply all migrations, seed all fixtures, ..., disconnect.
    PostgreSQL-->>Testrunner: 

    Note over Testrunner,IntegreSQL: Finalize the template so it can be used!

    Testrunner->>IntegreSQL: FinalizeTemplate: PUT /api/v1/templates/:hash
    IntegreSQL-->>Testrunner: StatusOK: 200

    Note over Testrunner,PostgreSQL: You can now get isolated test databases for this hash from the pool!

    loop Each test
      Testrunner->>IntegreSQL: GetTestDatabase: GET /api/v1/templates/:hash/tests
      Testrunner->>PostgreSQL: 
    end

Testrunner reuses an existing template database

sequenceDiagram

    You->>Testrunner: make test

    Note over Testrunner,IntegreSQL: Subsequent testrunners or multiple processes 

 simply call with the same template hash again.

    Testrunner->>IntegreSQL: InitializeTemplate: POST /api/v1/templates
    IntegreSQL-->>Testrunner: StatusLocked: 423

    Note over Testrunner,IntegreSQL: Some other testrunner / process has already recreated 

 this PostgreSQL template database identified by this hash

 (or is currently doing it), you can just consider

 the template ready at this point.

    Note over Testrunner,PostgreSQL: You can now get isolated test databases for this hash from the pool!

    loop Each test
      Testrunner->>IntegreSQL: GetTestDatabase: GET /api/v1/templates/:hash/tests
      Testrunner->>PostgreSQL: 
    end

Failure modes while template database setup: 503

```mermaid sequenceDiagram

You->>Testrunner: make test

Testrunner->>IntegreSQL: InitializeTemplate: POST /api/v1/templates
IntegreSQL-->>Testrunner: StatusServiceUnavailable: 503

Note over Testrunner,PostgreSQL: Typically happens if IntegreSQL cannot communic