Accessing PostgreSQL 9.6 from Spring Boot in CircleCi

Question

I have a Spring Boot application that currently builds and runs tests in Heroku's CI and I'm trying to get it to work in Circle CI as well. My config file looks like this:

version: 2
jobs:
  build:
    docker:
      - image: circleci/jdk8:0.1.1
      - image: postgres:9.6
    working_directory: ~/repo

    environment:
      # Customize the JVM maximum heap limit
      JVM_OPTS: -Xmx3200m
      TERM: dumb

    steps:
      - checkout
      - run: chmod +x gradlew

      # Download and cache dependencies
      - restore_cache:
          keys:
          - v1-dependencies-{{ checksum "build.gradle" }}
          # fallback to using the latest cache if no exact match is found
          - v1-dependencies-

      - run: ./gradlew dependencies

      - save_cache:
          paths:
            - ~/.m2
          key: v1-dependencies-{{ checksum "build.gradle" }}

      # run tests!
      - run: ./gradlew test

I tried various ways of defining DATABASE_URL to no effect:

jobs:
  build:
    docker:
      - image: circleci/jdk8:0.1.1
        environment:
        - DATABASE_URL=postgresql://dashman_test@localhost:5433/dashman_test
      - image: postgres:9.6
        environment:
        - POSTGRES_USER=dashman_test
        - POSTGRES_DB=dashman_test

jobs:
  build:
    docker:
      - image: circleci/jdk8:0.1.1
        environment:
        - DATABASE_URL=postgresql://dashman_test@localhost:5434/dashman_test
      - image: postgres:9.6
        environment:
        - POSTGRES_USER=dashman_test
        - POSTGRES_DB=dashman_test

jobs:
  build:
    docker:
      - image: circleci/jdk8:0.1.1
        environment:
          DATABASE_URL: postgresql://dashman_test@localhost:5434/dashman_test
      - image: postgres:9.6
        environment:
          POSTGRES_USER: dashman_test
          POSTGRES_DB: dashman_test


TEST_DATABASE_URL: postgresql://ubuntu@localhost/circle_test?sslmode=disable        
DATABASE_URL: postgresql://ubuntu@localhost/circle_test?sslmode=disable

DATABASE_URL: postgres://ubuntu:@127.0.0.1:5433/circle_test

DATABASE_URL: postgres://localhost:5433/dashman_test

DATABASE_URL: postgresql://ubuntu@localhost:5434/circle_test?sslmode=disable

DATABASE_URL: postgres://dashman_test:KnDnHtzneyTzps0WuYr35r9@localhost:5433/dashman_test

Nothing seems to work, I always end up with this error:

tech.dashman.dashmanserver.models.AccountTest > create FAILED
    java.lang.IllegalStateException
        Caused by: org.springframework.beans.factory.BeanCreationException
            Caused by: org.springframework.beans.BeanInstantiationException
                Caused by: org.springframework.beans.factory.BeanCreationException
                    Caused by: org.springframework.beans.BeanInstantiationException
                        Caused by: org.springframework.boot.autoconfigure.jdbc.DataSourceProperties$DataSourceBeanCreationException

tech.dashman.dashmanserver.models.UserTest > create FAILED
    java.lang.IllegalStateException
        Caused by: org.springframework.beans.factory.BeanCreationException
            Caused by: org.springframework.beans.BeanInstantiationException
                Caused by: org.springframework.beans.factory.BeanCreationException
                    Caused by: org.springframework.beans.BeanInstantiationException
                        Caused by: org.springframework.boot.autoconfigure.jdbc.DataSourceProperties$DataSourceBeanCreationException

tech.dashman.dashmanserver.DashmanserverApplicationTests > contextLoads FAILED
    java.lang.IllegalStateException
        Caused by: org.springframework.beans.factory.BeanCreationException
            Caused by: org.springframework.beans.BeanInstantiationException
                Caused by: org.springframework.beans.factory.BeanCreationException
                    Caused by: org.springframework.beans.BeanInstantiationException
                        Caused by: org.springframework.boot.autoconfigure.jdbc.DataSourceProperties$DataSourceBeanCreationException

What's the proper way of configuring the database? I'm a bit lost.

Answer 1

Here are a few points that should help you to address the problem.

---

(1) The documentation you mentioned in comments (this one) is either outdated or plain misleading. This:

The default user, port, test database for PostgreSQL 9.6 are as follows: postgres://ubuntu:@127.0.0.1:5432/circle_test

... is not true.

The actual defaults for postgres:9.6 are:

• username: postgres
• password: <empty>
• port: 5432
• database: postgres

You can reach postgres instance from your application on 127.0.0.1.

You can find more info about defaults here but there is a catch about setting them (more on this in (3)).

---

(2) As far as I know there is no way to pass usrename\password in jdbc url for postgres connector, so you probably have to tell your application not only DATABASE_URL but also something like DATABASE_USER and DATABASE_PASSWORD.

This part is dependent on specifics of your application, but for typical spring boot application with default database settings you want to end up with the following settings:

spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=

---

(3) Alternatively, if your connection settings are hardcoded you probably want to configure credentials for postgres instance.

Unfortunately, even though setting POSTGRES_* environment variables when running container with docker run works fine, setting them in .circleci/config.yml does not work. There are few open bug reports (1, 2) describing this or similar issue and my money is on this being a bug.

Fortunately, you still can work around this by installing psql and creating desired user credentials during your build (default credentials still work). Adding something like:

  - run: apt-get update -qq && apt-get install -y postgresql
  - run:
      command: |
        psql -h 127.0.0.1 -U postgres -c "CREATE DATABASE databasename;"
        psql -h 127.0.0.1 -U postgres -c "CREATE USER username WITH PASSWORD 'password'; GRANT ALL PRIVILEGES ON DATABASE databasename TO username;"

... to your steps should do the trick (see full example here).

Using machine executor to run postgres manually (see last example on this page) might be an option too, but I have not tried this one myself.

---

(4) I actually tried configuring this for myself and you can check out repo with working version here. Build output example here.

I used this spring boot sample and made it use postgres, left only relevant tests, added circle ci along with other minor tweaks. It demonstrates both configuring application via env. variables and configuring postgres instance.

The most interesting parts are .circleci/config.yml (where credentials env. variables are defined and user\db gets created in postgres instance):

version: 2
jobs:
  build:
    docker:
      - image: maven:3.5.0-jdk-8
        environment:
          DATABASE_URL: jdbc:postgresql://127.0.0.1:5432/databasename
          DATABASE_USER: username
          DATABASE_PASSWORD: password
      - image: postgres:9.6

    working_directory: ~/repo

    steps:
      - checkout
      - run: apt-get update -qq && apt-get install -y postgresql
      - run:
          command: |
            psql -h 127.0.0.1 -U postgres -c "CREATE DATABASE databasename;"
            psql -h 127.0.0.1 -U postgres -c "CREATE USER username WITH PASSWORD 'password'; GRANT ALL PRIVILEGES ON DATABASE databasename TO username;"
- run: mvn test

... and application.properties (where credentials env. variables are used):

spring.h2.console.enabled=false

logging.level.org.hibernate.SQL=error

spring.datasource.url=${DATABASE_URL}
spring.datasource.username=${DATABASE_USER}
spring.datasource.password=${DATABASE_PASSWORD}
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.database=POSTGRESQL
spring.datasource.platform=postgres
spring.jpa.show-sql=true
spring.database.driverClassName=org.postgresql.Driver