How to load init.sql file to postgres service in gitlab ci?

Question

I want to init my service in the test stage using a sql file.

test:
  services:
    - postgres:latest
  variables:
    POSTGRES_DB: test
  stage: test
  image: gliderlabs/herokuish:latest
  script:
    - setup_test_db
    - cp -R . /tmp/app
    - /bin/herokuish buildpack test
  only:
    - branches
  except:
    variables:
      - $TEST_DISABLED

This is the test job as in auto devops.
I want to use the postgres image docker-entrypoint.
This is how I use it in docker-compose on my pc:

volumes:
  - './src/sql:/docker-entrypoint-initdb.d'

This way, when the postres image starts up, I have my schema ready to use.
How can I manage to do it in gitlab-ci.yml?

Mercurio

Answer 1

At the moment volumes is not supported in Gitlab CI.

A workaround solution for this during your test you use docker-compose which has a service for postgres to spin up your project and you can mount it volume as normal

Mesothorax

Answer 2

You can added below script into your gitlab ci to make it work.

before_script:
  - apt update && apt install -y postgresql
  - export PGPASSWORD=$POSTGRES_PASSWORD
  - psql -h "postgres" -U "$POSTGRES_USER" -d "$POSTGRES_DB" -f ./src/sql

Epigraphy

Answer 3

If you only have a simple SQL fixture (such as adding a USER) You could override command in your service definition to inject your SQL into the service container's /docker-entrypoint-initdb.d

test:
  services:
    - postgres:latest
      command:
        - /bin/sh
        - -c
        - >-
          echo "$SOME_SQL" > /docker-entrypoint-initdb.d/init.sql &&
          docker-entrypoint.sh # or whatever is called in that image
  script:
    - ...

And in case you are wondering, no... there is no way to populate the $SOME_SQL variable with a file in your repository. So this is only a viable workaround if you can get away with an inline variable in this YAML, CI variable from the Gitlab UI, etc.

Narcissus

You can use a "here string" to make populating the file in the command: hash easier. This is what works for me (I'm posting in JSON because code blocks are not supported in comments): {"command":["/bin/sh","-c","cat >>/docker-entrypoint-initdb.d/init.sql <<EOF && /usr/local/bin/docker-enforce-initdb.sh && su postgres -c postgres\nCREATE ROLE bol_gen_ro WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;\nCREATE ROLE bol_gen_rw WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;\nEOF\n"]}. – Uninspired

@StefanvandenAkker that's an option, but $SOME_SQL can be cleanly defined in the same YAML using a literal string (with SOME_SQL: | ..., or > ). I'd argue that this is more readable than relying on the shell redirection. What about SQL containing quotes? – Narcissus

Right, that's definitely a better idea. I somehow interpreted $SOME_SQL to be a constant (as in SOME_SQL) and not an indirection of a Gitlab CI variable. – Uninspired