Unable to connect to local postgres through Docker container

Build Environment
, ,
1

Hi everyone,

I have tried for hours to connect to a locally configured DB in Postgres without any success.
I tried whitelisting the docker IP (got it connecting through SSH to the docker container) in the Postgres config files (plus listening to all connections and also reloaded the pg config).

I have tried everything I could think of basically for hours.

This is my .yml file:

version: 2
jobs:
  build:
    docker:
      - image: circleci/node:12
        environment:
          DATABASE_URL: postgres://username@127.0.0.1:5432/dbname
          PGHOST: 127.0.0.1
          PGUSER: "username"
      - image: circleci/postgres:alpine
        environment:
          POSTGRES_DB: dbname
          POSTGRES_USER: "username"
          POSTGRES_PASSWORD: ""

[...]

Basically I am trying to get some more understanding on how all these bits glue together. Eventually, I would like to have a db migration/seeding strategy that the VM will use and won’t have (hopefully) any issue while testing data.

At the moment, it can’t find the relation I am trying to test, therefore it gives me the hint that it is not really connecting to the DB at all.

I can’t think of anything else I am forgetting, but please feel free to ask!

Thank you very much in advance everyone!

2

If it cannot find the relation then this means that it is able to connect. Can you provide the full error message?

In addition, can you describe how you are creating your schema as a part of the test run?

3

Hi levlaz,

Thank you for your reply! At the moment, I have a build that is passing as I am now not connecting to my local DB but creating a DB directly in the docker and migrating/seeding it.

 docker:
      - image: circleci/node:12
        environment:
          NODE_ENV: test
      - image: circleci/postgres:alpine
        environment:
          POSTGRES_DB: circleci
          POSTGRES_USER: "circleci"
          POSTGRES_PASSWORD: ""

                         [...]

      - run: npm --prefix ./server run migrateDb # migration
      - run: npm --prefix ./server run seedDb # seeding
      - run: npm --prefix ./server run coverage # run tests

As you can see I have removed pretty much everything from the Node image environment setup (I think that’s the section where the connection to the DB must be set up if I am not mistaken).

Then, to answer your first question, below I paste the full error message from one of the past failing builds:

> server@1.0.0 coverage /home/circleci/repo/server
> jest --collectCoverageFrom=./**/*.js --coverage --detectOpenHandles --no-cache

  console.log node_modules/sequelize/lib/sequelize.js:1187
    Executing (default): SELECT "id", "name", "password" FROM "Users" AS "User";

  console.log node_modules/sequelize/lib/sequelize.js:1187
    Executing (default): CREATE TABLE IF NOT EXISTS "Users" ("id"   SERIAL , "name" VARCHAR(255), "password" VARCHAR(255), PRIMARY KEY ("id"));

  console.warn node_modules/bluebird/js/release/debuggability.js:888
    Unhandled rejection SequelizeDatabaseError: relation "Users" does not exist
        at Query.formatError (/home/circleci/repo/server/node_modules/sequelize/lib/dialects/postgres/query.js:366:16)
        at /home/circleci/repo/server/node_modules/sequelize/lib/dialects/postgres/query.js:72:18
        at tryCatcher (/home/circleci/repo/server/node_modules/bluebird/js/release/util.js:16:23)
        at Promise._settlePromiseFromHandler (/home/circleci/repo/server/node_modules/bluebird/js/release/promise.js:517:31)
        at Promise._settlePromise (/home/circleci/repo/server/node_modules/bluebird/js/release/promise.js:574:18)
        at Promise._settlePromise0 (/home/circleci/repo/server/node_modules/bluebird/js/release/promise.js:619:10)
        at Promise._settlePromises (/home/circleci/repo/server/node_modules/bluebird/js/release/promise.js:695:18)
        at _drainQueueStep (/home/circleci/repo/server/node_modules/bluebird/js/release/async.js:138:12)
        at _drainQueue (/home/circleci/repo/server/node_modules/bluebird/js/release/async.js:131:9)
        at Async.Object.<anonymous>.Async._drainQueues (/home/circleci/repo/server/node_modules/bluebird/js/release/async.js:147:5)
        at Immediate.Async.drainQueues [as _onImmediate] (/home/circleci/repo/server/node_modules/bluebird/js/release/async.js:17:14)
        at processImmediate (internal/timers.js:439:21)

This error ends up in socket hang up after showing the failing tests below the error details.

My first thought was the same you had: that it was indeed connecting to the database that I had created in my local machine, but then, why then wouldn’t it query it right?

I decided to move on and do the other (I believe right) approach to do the integration tests. This way, I think I gained a deeper understanding. I believe what was happening originally is that the Docker postgres image was creating the database because of the environment set up I had under the image! But then, of course it could not find the relation ‘Users’ as it was never defined within the Docker.

My conclusion is I think I am not doing something well when trying to connect to my local database.

The issue is solved with the correct approach, but still, I would really like to understand what is really going on here and not based everything on assumptions.

To your second question, and related to my previous comments:
Do we really need to create a schema as part of the test if we were pointing to a local database with a set schema and the connection was right? If this is needed for something that I do not know, then this issue is solved now with the latest approach I took.

Thanks a lot!

4

Hey there,

Thanks for the detailed response. Can you help me understand exactly what you mean by local database in this context?