Populate a database with existing data

Preloading test data before integration tests

In an another example we have seen how you can run integration tests with a database such as PostgreSQL. Sometimes however, the integration tests require the database to already have some test data beforehand. With Codefresh you can use the setup block in service containers to preload data to a db.

Preloading test data to a DB

Preloading test data to a DB

In this pipeline the database is populated with data from an SQL file.

The example PostgreSQL project

You can see the example project at https://github.com/codefresh-contrib/preload-db-integration-tests. The repository contains a simple integration test and an SQL file that inserts test data.

The SQL file creates a single table in the db:

preload.sql

CREATE TABLE link (
   ID serial PRIMARY KEY,
   url VARCHAR (255) NOT NULL,
   name VARCHAR (255) NOT NULL,
   description VARCHAR (255),
   rel VARCHAR (50)
);

INSERT INTO link (url, name)
VALUES
 ('http://www.google.com','Google'),
 ('http://www.azure.microsoft.com','Azure'),
 ('http://www.codefresh.io','Codefresh');

To work with the project locally, you need to have docker, golang and postgres-client installed on your workstation first.

$ docker run -p 5432:5432 postgres:11.5

Then open another terminal and load the test data:

$ psql -h localhost -U postgres < testdata/preload.sql

A Postgres instance is now running at localhost:5432 and you can run the tests with:

$ go test -v

Create a pipeline the preloads test data to PostgreSQL

Here is the whole pipeline:

codefresh.yml

version: "1.0"
stages:
- prepare
- package
- test
steps:
  main_clone:
    type: "git-clone"
    description: "Cloning main repository..."
    repo: "codefresh-contrib/preload-db-integration-tests"
    revision: "master"
    title: "Checking out source code"
    git: github
    stage: prepare
  package_my_app:
    stage: package
    image: 'golang:1.13'
    title: "Compile code"
    commands:
      - 'go build'
  run_my_db_tests:
    stage: test
    image: 'golang:1.13'
    title: "Running integration tests"
    commands:
      - 'go test -v'
    environment:
    - POSTGRES_HOST=my_postgresql_db
    services:
      composition:
        my_postgresql_db:
          image: postgres:11.5
          ports:
            - 5432 
      readiness:
        timeoutSeconds: 30
        initialDelaySeconds: 10
        periodSeconds: 15
        image: 'postgres:11.5'
        commands:
          - "pg_isready -h my_postgresql_db -U postgres"
      setup:
        image: 'postgres:11.5'
        commands:
          - "psql -h my_postgresql_db -U postgres < /codefresh/volume/preload-db-integration-tests/testdata/preload.sql"

This pipeline does the following:

  1. Clones the source code with a Git clone step.
  2. Compiles the code with a freestyle step that runs go build
  3. Runs the tests while launching a service container for an active PostgreSQL instance. Before tests are run we launch another container with the psql executable to load db data

In this simple example we use psql to preload the database. In a production application you might also use dedicated db tools such as liquibase or flyway or other command line tools that communicate with your database.

Notice that we also use the readiness property in the testing phase so that we can verify PostgreSQL is ready and listening, before running the tests. The exact order of events is:

  1. Codefresh launches postgres:11.5 at port 5432.
  2. It then launches another container in the same network with pg_isready in order to wait for the DB to be up.
  3. Then it launches a third container with psql to preload data.
  4. Finally it launches a container with golang:1.13 to run the actual tests.

All containers are discarded after the pipeline has finished.