IntegreSQL Documentation

The IntegreSQL strategy is designed to balance speed and isolation. By using a warm pool of replicas, the overhead of switching databases is minimized.

Key Performance Metrics:

• Cold Start: You only pay the full cost of template creation (truncate + migrate + seed) if your migration or fixture files have changed.
• Replica Selection: Once the pool is warm, selecting a new database for a test is extremely fast (e.g., ~11ms on average in small suites).
• Scaling: Even in larger suites (e.g., 280+ tests), the strategy overhead typically remains low (e.g., ~11% of total execution time), meaning tests spend most of their time executing rather than waiting for database setup.

--- -----------------<storageHelper strategy report>------------------ ---
    replicas switched:             280    avg=26ms min=11ms max=447ms
    replicas awaited:              1      prebuffer=8 avg=417ms max=417ms
    background replicas:           288    avg=423ms min=105ms max=2574ms
    - warm up template (cold):     40%    5151ms
        * truncate:                8%     980ms
        * migrate:                 26%    3360ms
        * seed:                     4%     809ms
    - switching:                   60%    7461ms
        * disconnect:               2%     322ms
        * switch replica:           6%     775ms
            - resolve next:         2%     358ms
            - await next:          3%     417ms
        * reinitialize:             50%    6364ms
    strategy related time:         ---    12612ms
    vs total executed time:        11%    111094ms
--- -------------------------------------------------------------------- ---

IntegreSQL uses a hierarchical structure to manage database lifecycle and isolation:

• Server: The top-level component that owns the Manager.
• Manager: Orchestrates the collection of Templates and HashPools.
• Template: Contains a TemplateDatabase, which is used as a base for creating test databases.
• HashPool: Manages a collection of TestDatabases.
• TestDatabase: An individual database instance with a unique ID and its own Database configuration.
• Database: The core data object containing a TemplateHash and DatabaseConfig.

This structure allows IntegreSQL to efficiently manage multiple test databases derived from various templates using a pooling mechanism.

erDiagram
    Server ||--o| Manager : owns
    Manager {
        Template[] templateCollection
        HashPool[] poolCollection
    }
    Manager ||--o{ HashPool : has
    Manager ||--o{ Template : has
    Template {
        TemplateDatabase database
    }
    HashPool {
        TestDatabase database
    }
    HashPool ||--o{ TestDatabase : "manages"
    Template ||--|| TemplateDatabase : "sets"
    TestDatabase {
        int ID
        Database database
    }
    TemplateDatabase {
        Database database
    }
    Database {
        string TemplateHash
        Config DatabaseConfig
    }
    TestDatabase o|--|| Database : "is"
    TemplateDatabase o|--|| Database : "is"

IntegreSQL implements a high-performance testing strategy called Approach 3c: Isolation by cached templates and pool.

Instead of using slow resets (truncate/migrate/seed) or risky transactions (which don't support nested transactions well), IntegreSQL uses a warm pool of PostgreSQL databases.

The Workflow:

1. Template Creation: IntegreSQL hashes your migration and fixture files. If they haven't changed, it reuses a cached PostgreSQL template database. If they have changed, it recreates the template by applying migrations and seeding fixtures.
2. Replica Pooling: It maintains a pool of 'replica' databases created from that template.
3. Test Execution: For each test, IntegreSQL selects an available database from the pool. This provides full isolation (each test gets its own database) with near-zero latency.
4. Background Provisioning: As tests consume databases from the pool, IntegreSQL provisions new replicas from the template in the background so they are ready when the next test starts.

A TestDatabase follows a specific lifecycle managed by IntegreSQL to ensure test isolation and availability:

1. init: The database is initialized and enters the ready state.
2. GetTestDatabase(): When a client requests a database, the state transitions from ready to dirty.
3. ReturnTestDatabase(): Once the client is finished, the database transitions from dirty back to ready.
4. RecreateTestDatabase(): If a database needs to be cleaned or refreshed (triggered by a CLEAN_DIRTY task), it moves from dirty to recreating.
5. generation++: After recreation is complete, the database returns to the ready state.

If a recreation is attempted while the database is still in use, it will retry the recreating state.

stateDiagram-v2

    HashPool --> TestDatabase: Task EXTEND

    state TestDatabase {
        [*] --> ready: init
        ready --> dirty: GetTestDatabase()
        dirty --> ready: ReturnTestDatabase()
        dirty --> recreating: RecreateTestDatabase()\nTask CLEAN_DIRTY
        recreating --> ready: generation++
        recreating --> recreating: retry (still in use)
    }

To run IntegreSQL locally without Docker, you need Go (1.14 or above). You can install the binary to your $GOBIN and then run it by exporting the necessary environment variables.

1. Install the server:

go install github.com/allaboutapps/integresql/cmd/server@latest
mv $GOBIN/server $GOBIN/integresql

2. Run the server:

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

# 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

You can include IntegreSQL in a docker-compose.yml file alongside your main service and a PostgreSQL instance.

Important Performance Note for PostgreSQL: When running PostgreSQL for local development/testing, you can significantly increase speed (~30%) by disabling certain durability guarantees.

WARNING: Never use these settings in production as they can lead to data corruption:

• fsync=off
• synchronous_commit=off
• full_page_writes=off

Example docker-compose.yml structure:

version: "3.4"
services:
  service:
    depends_on:
      - postgres
      - integresql
    environment:
      PGDATABASE: "development"
      PGUSER: "dbuser"
      PGPASSWORD: "9bed16f749d74a3c8bfbced18a7647f5"
      PGHOST: "postgres"
      PGPORT: "5432"
      PGSSLMODE: "disable"
      # Optional: env for integresql client testing
      # INTEGRESQL_CLIENT_BASE_URL: "http://integresql:5000/api"

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

  postgres:
    image: postgres:12.2-alpine
    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: "development"
      POSTGRES_USER: "dbuser"
      POSTGRES_PASSWORD: "9bed16f749d74a3c8bfbced18a7647f5"
    volumes:
      - pgvolume:/var/lib/postgresql/data

volumes:
  pgvolume:

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.
    # 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

If a client library is not available for your language, you can integrate IntegreSQL directly using its RESTful JSON API. The integration involves two main phases: Once per test runner/process (setting up template databases) and Per each test (obtaining isolated test databases).

Phase 1: Once per test runner/process

To ensure your database schema (migrations, fixtures, etc.) is ready, you must manage template databases using a unique hash representing your database state.

1. Initialize a Template: Send a POST /api/v1/templates request with a payload containing the hash: {"hash": "string"}.
   • If the template is being created by another process, IntegreSQL returns 423 Locked. In this case, you should wait and then proceed to get a test database.
   • If IntegreSQL cannot communicate with PostgreSQL, it returns 503 Service Unavailable.
2. Apply Migrations/Fixtures: Once the template is initialized, connect to the returned database connection payload to apply your migrations and seeds.
3. Finalize the Template: Send a PUT /api/v1/templates/:hash request to mark the template as ready for use.

Phase 2: Per each test

For every individual test, you need an isolated database instance.

1. Get a Test Database: Call GET /api/v1/templates/:hash/tests. This returns a connection payload for a fresh, isolated database derived from your template.
   • If the template hasn't been initialized, you will receive 404 Not Found.
   • If the template was discarded or failed setup, you will receive 410 Gone.
   • If there is a PostgreSQL connectivity issue, you will receive 503 Service Unavailable.
2. Run Test: Connect to the provided database and execute your test code.
3. Cleanup (Optional):
   • For Read-only tests: If you did not modify the database, call POST /api/v1/templates/:hash/tests/:id/unlock to return the database to the pool immediately without recreation.
   • Manual Recreation: To avoid waiting for the automatic FIFO (First-In-First-Out) recreation, call POST /api/v1/templates/:hash/tests/:id/recreate to force a recreation of the test database.