Cloud-builders: Document how to connect to a database

[kevinburke]

I am supposed to use the Go cloud builder to e.g. run Go programs or run tests, however, I have tests that are supposed to connect to a Postgres database and it's not clear to me how I am supposed to create or run a database with Google Cloud Build.

I have tried to read through/search the documentation on the website, as well as the "go" builder here, and the open/closed issues here.

[Philmod]

Hey @kevinburke ,

Are you planning to use an existing database on Google Cloud? In which case, your build should have easily access to it.

If you want to run a fresh/empty database for each build, you could use docker-compose to set it up. Here is an example: https://github.com/Philmod/gcb-docker-compose

[kevinburke]

I suppose I could use an existing database, but there would be concurrency problems, if there were two concurrent builders accessing the same tables.

This is a pretty common use case in a CI environment and I can find examples and easy configuration for any of your competitors:

https://docs.travis-ci.com/user/database-setup/#postgresql
https://circleci.com/docs/2.0/postgres-config/#example-go-app-with-postgresql
https://devcenter.heroku.com/articles/heroku-ci-in-dyno-databases
https://semaphoreci.com/docs/databases/postgresql.html

(finding those 4 links took me 1 minute tops)

[NinoFloris]

Good to see there's an open issue for this, very important to have some docs around this use case!

[yetanotherchris]

I don't think Docker Compose is the solution, at least not in the .NET Core world - you don't necessarily want to have to build your integration tests into a container, in order to run them. The commonest use case with Travis, Gitlab, Appveyor is to run the tests on the build agent host, and they connect to the database.

In the case of the dotnet cloud-builder you are running the dotnet CLI inside a container, and (presumably) volume mapping to the host build agent.

The problem comes with communicating with another container outside of that container as they're not on the same network.

Here's an example (fails on the final step):

steps:
  - id: 'docker-run'
    name: gcr.io/cloud-builders/docker
    args: ['run', '-d', '-p', '5432:5432', '--name', 'roadkill-postgres', 'postgres']

  - id: 'docker-ps'
    name: gcr.io/cloud-builders/docker
    args: ['ps', '-a']

  - id: 'sleep'
    name: gcr.io/cloud-builders/docker
    args: ['exec', 'roadkill-postgres', 'sleep', '5']

  - id: 'docker-logs'
    name: gcr.io/cloud-builders/docker
    args: ['logs', 'roadkill-postgres']

  - id: 'psql-create-db'
    name: gcr.io/cloud-builders/docker
    args: ['exec', 'roadkill-postgres', 'psql', '-c', 'CREATE DATABASE roadkill;', '-U', 'postgres']
    waitFor: ['sleep']

  - id: 'psql-create-user'
    name: gcr.io/cloud-builders/docker
    args: ['exec', 'roadkill-postgres', 'psql', '-c', 'CREATE USER roadkill WITH PASSWORD ''roadkill'';', '-U', 'postgres']
    waitFor: ['psql-create-db']

  - id: 'psql-assign-role'
    name: gcr.io/cloud-builders/docker
    args: ['exec', 'roadkill-postgres', 'psql', '-c', 'ALTER USER roadkill WITH SUPERUSER;', '-U', 'postgres']
    waitFor: ['psql-create-user']

  - id: 'dotnet-build'
    name: gcr.io/cloud-builders/dotnet
    args: ['build', '-c', 'Release']

  - id: 'dotnet-test-unit'
    name: gcr.io/cloud-builders/dotnet
    args: ['test']
    dir:  'src/Roadkill.Tests.Unit'
    waitFor: ['dotnet-build']

  - id: 'dotnet-test-integration'
    name: gcr.io/cloud-builders/dotnet
    args: ['test']
    dir:  'src/Roadkill.Tests.Integration'
    waitFor: ['dotnet-build']

Maybe there's a way of connecting to another running container that I'm missing?

For reference, here is a Travis CI equivalent:

# https://docs.travis-ci.com/user/environment-variables/
language: csharp
dotnet: 2.1.300

branches:
  only:
  - master

services:
  - docker
  - postgresql

addons:
  postgresql: "9.5"

before_script:
  - psql -c "create database roadkill;" -U postgres
  - psql -c "CREATE USER roadkill WITH PASSWORD 'roadkill';" -U postgres
  - psql -c "ALTER USER roadkill WITH SUPERUSER;" -U postgres

script:
   - dotnet restore
   - dotnet build -c Release  
   - pushd src/Roadkill.Tests.Unit
   - dotnet test
   - popd
   - pushd src/Roadkill.Tests.Integration
   - dotnet test

[markmssd]

@yetanotherchris I'm having the same issue to connect to Neo4J and run tests. Did you figure something out?

[vigandhi]

Using @yetanotherchris 's setup worked up to the same point - we're using Jest (24) and postgres. Everything seems fine until we try to run the tests, at which point, when we try to connect to the DB, everything just halts. The step (in my case it's step 7) completes successfully, but it seems like the process dies at the point where we try to connect to the DB - no error logs, no console logs (if we add them in) etc.

We're running the following npm script "test:once": "node ./node_modules/jest/bin/jest.js -i --bail"

With the below cloud build config

- name: gcr.io/cloud-builders/npm
  args: ['run', 'test:once']

[apstndb]

Cloud Build uses cloudbuild Docker network so we can minimize use of raw docker run without Docker Compose.

steps:
- name: gcr.io/cloud-builders/docker
  args: ['run', '-d', '--name=postgres', '--network=cloudbuild', 'postgres']
- name: jwilder/dockerize:0.6.1
  args: ['dockerize', '-timeout=60s', '-wait=tcp://postgres:5432']
# use postgres here
- name: postgres
  args: ['psql', '--host=postgres', '--user=postgres', '--command', 'SELECT * FROM INFORMATION_SCHEMA.Tables']
# cloud-build-local don't clean `docker run -d`-ed container
- name: gcr.io/cloud-builders/docker
  args: ['rm', '--force', 'postgres']

https://cloud.google.com/cloud-build/docs/overview?hl=en

Each build step is run with its container attached to a local Docker network named cloudbuild. This allows build steps to communicate with each other and share data.

[Datamance]

Is the above solution the recommended way to go?

[yetanotherchris]

With the help of @apstndb 's reply, this Cloud Build cloudbuild.yaml now works with .NET Core 3.1:

https://github.com/roadkillwiki/roadkill_new/blob/1984178d422fabf7a53e15c046d5dba7bf0de99c/cloudbuild.yaml

Just make sure you use --network=cloudbuild for any docker run steps - that should be included in the docs I think.

[sanastos]

I believe this is documented already:
https://cloud.google.com/cloud-build/docs/configuring-builds/build-test-deploy-artifacts#running_unit_tests_and_integration_tests

[apstndb]

The description in the document seems not to be sufficient.
There are no description about below.

• How to use cloudbuild network(docker-compose.yml is not contained in the document)
• What is gcb-docker-compose (There are no links)
• How to communicate between containers by docker run(without Docker Compose) with cloudbuild network(discussed in this issue)

I don't think that every Cloud Build users who want to communicate between containers use Docker Compose in Cloud Build. Some users may even have never used Docker Compose.
docker run level example will be also useful to understand why they can communicate(and can't).

[sanastos]

The fact that the cloudbuild network exists and how it can be used with docker run is described in the build steps doc:
https://cloud.google.com/cloud-build/docs/build-config#network

I agree that the documentation does kind of imply that you must use docker compose, when docker run works just as well for simple dependencies (coincidentally I just addressed this issue).

I will file a documentation bug to improve this section.

[marco-lavagnino]

Hey @kevinburke ,

Are you planning to use an existing database on Google Cloud? In which case, your build should have easily access to it.

If you want to run a fresh/empty database for each build, you could use docker-compose to set it up. Here is an example: https://github.com/Philmod/gcb-docker-compose

@Philmod Could you elaborate on "your build should have easily access to it"?

I don't see how to access my DB from a unix socket without running sql-proxy myself, while other services (like cloud run) can setup sql proxy from the UI. Is there something I'm missing?

[Datamance]

@yetanotherchris strangely, your solution doesn't work for me - dockerize itself seems to encounter problems:

Step #1 - "wait-for-postgres": 2020/03/03 16:04:38 Problem with dial: dial tcp: lookup lookit-postgres on 127.0.0.11:53: no such host. Sleeping 1s

Is it maybe better to run dockerize on the cloudbuild network as well?

[Datamance]

I believe this is documented already:
https://cloud.google.com/cloud-build/docs/configuring-builds/build-test-deploy-artifacts#running_unit_tests_and_integration_tests

That link is broken now - the fragment doesn't correspond to any sections.

[Datamance]

@apstndb Your solution doesn't work -

the following configuration:

steps:

- id: boot-postgres
  name: gcr.io/cloud-builders/docker
  args: ['run', '-d', '--publish=5432:5432', '--network=cloudbuild', '--name=postgres', 'postgres']


- id: wait-for-postgres
  name: jwilder/dockerize:0.6.1
  args: ['dockerize', '-timeout=60s', '-wait=tcp://postgres:5432']

dies because it waits until dockerize times out looking for the wrong host:
Step #1 - "wait-for-postgres": 2020/03/03 16:52:33 Problem with dial: dial tcp: lookup postgres on 127.0.0.11:53: no such host. Sleeping 1s

Has anyone else encountered this?

[apstndb]

@Datamance
It should be because postgres:latest forbid default no password behavior without POSTGRES_HOST_AUTH_METHOD=trust.
https://hub.docker.com/_/postgres

It seems to work.

steps:
- name: gcr.io/cloud-builders/docker
  args: ['run', '-d', '--network=cloudbuild', '--env=POSTGRES_HOST_AUTH_METHOD=trust', '--name=postgres', 'postgres:9.6']
- name: jwilder/dockerize:0.6.1
  args: ['dockerize', '-timeout=60s', '-wait=tcp://postgres:5432']
- name: postgres:9.6
  args: ['psql', '--host=postgres', '--user=postgres', '--command', 'SELECT * FROM INFORMATION_SCHEMA.Tables']
- name: gcr.io/cloud-builders/docker
  args: ['rm', '--force', 'postgres']

[govindrai]

The docs say to use --network on docker build and not docker run. Is that a mistake?

steps:

• name: gcr.io/cloud-builders/docker
  args: ["build","--network=cloudbuild", "."]

[confiq]

@apstndb that is exactly the reason why we need documentation about this.... thanks!

@govindrai no, that is not a mistake. It should work. Documentation is usually more correct than GitHub issue...

[bendory]

To report an issue with the hosted Google Cloud Build service, please report it to your Google Cloud Support team or use the public issue tracker at
https://issuetracker.google.com/issues/new?component=190802&template=1162743.

This issue tracker is intended for issues specifically related to build steps.