Gitlab CI - Create database in service container

How to Use GitLab
1

Hi !

I’m a bit stuck right now. All I want to do is to use a Postgresql service and to import a sql file into it.

I have postgres:alpine as one of my service. During my test stage I want to use a sql file in my repository and execute the following command : psql -U $POSTGRES_USER --password $POSTGRES_PASSWORD $POSTGRES_DB < ./db/db_create.sql

My variables are also in place, but I can’t find a way to do this (quite simple) manipulation easily. Any clue ?

2

Are you trying to create a new database in Gitlab’s postgresql?
If so, I was doing something like following to execute a sql statement.
sudo -u gitlab-psql /opt/gitlab/embedded/bin/psql -h /var/opt/gitlab/postgresql/ gitlabhq_production -c “select statement here”

3

Hi,

Yes, I’m using Gitlab postgresql “service”

My yml (with the non working command). Of course this command can’t work as I did not copy “db_create.sql” into the postgresql container.

image: docker:latest
services:
- docker:dind

stages:
- build
- test
- release
- deploy

variables:
  CONTAINER_TEST_IMAGE: registry.gitlab.com/myrepo:$CI_COMMIT_SHA
  CONTAINER_RELEASE_IMAGE: registry.gitlab.com/myrepo:latest
  # Postgres variables for testing
  POSTGRES_DB: database
  POSTGRES_USER: user
  POSTGRES_PASSWORD: password

before_script:
  - docker login -u gitlab-ci-token -p $CI_BUILD_TOKEN registry.gitlab.com

build:
  stage: build
  script:
    - docker build --build-arg SSH_PRIVATE_KEY="$SSH_PRIVATE_KEY" -t $CONTAINER_TEST_IMAGE .
    - docker push $CONTAINER_TEST_IMAGE

unittest:
  stage: test
  services:
    - name: postgres:alpine
      command: ["sh", "-c", "/bin/su", "-", "postgres", "postgres", "&&", "psql", "-U","$POSTGRES_USER", "--password", "$POSTGRES_PASSWORD", "$POSTGRES_DB", "<", "./db/db_create.sql" ]
  script:
    - export PGPASSWORD=$POSTGRES_PASSWORD
    - docker run -e SSH_PRIVATE_KEY="$SSH_PRIVATE_KEY" $CONTAINER_TEST_IMAGE npm run test-ci

release-image:
  stage: release
  script:
    - docker pull $CONTAINER_TEST_IMAGE
    - docker tag $CONTAINER_TEST_IMAGE $CONTAINER_RELEASE_IMAGE
    - docker push $CONTAINER_RELEASE_IMAGE
  only:
    - master

deploy:
  stage: deploy
  script:
    - chmod +x ./ci/deploy.sh
    - ./ci/deploy.sh
  only:
    - master
4

Hello, did you found a solution to create a database and tables using sql files into online gitlab-ci?
I’m interested too

5 
connect:
  image: postgres
  script:
  # official way to provide password to psql: http://www.postgresql.org/docs/9.3/static/libpq-envars.html
  - export PGPASSWORD=$POSTGRES_PASSWORD
  - psql -h "postgres" -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT 'OK' AS status;"
1 Like