Can't connect to Postgres DB inside another docker container

nacho · July 25, 2020, 10:59am

I’m trying to install the community edition using docker but my DB is running inside a different docker container and I’m using a custom port. I can access it without any problems via psql but I get an error when I run this command:

sudo docker run \
>   -v `pwd`:/usr/src/app --init \
>   -e DATABASE_SCHEMA="public" \
>   -e DATABASE_SSL="false" \
>   -e DATABASE_URL="postgres://user:password@host.docker.internal:5555/my_database" \
>   -e APP_NAME="REDACTED" \
>   -e FOREST_EMAIL="REDACTED" \
>   -e FOREST_TOKEN="REDACTED" \
>   -e APPLICATION_HOST="localhost" \
>   -e APPLICATION_PORT="3310" \
>   -ti forestadmin/lumber
✖ Connecting to your database
> Cannot connect to the database due to the following error:
> SequelizeHostNotFoundError: getaddrinfo ENOTFOUND host.docker.internal host.docker.internal:5555

If I change host.docker.internal to localhost I get this error:

✖ Connecting to your database
> Cannot connect to the database due to the following error:
> SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5555

I’m using Docker 18.09 and Postgres 12

anon37102731 · July 27, 2020, 8:32am

Hi @nacho,

Welcome to the ForestAdmin community and thank you for reaching out with your issue.

In order to help you, could you please tell me on which OS you’re trying to run this command?

nacho · July 27, 2020, 10:38am

Thanks, Valentin.

My VPS is using Ubuntu 18.04, but the docker image with the Postgres DB has Debian 10.

anon37102731 · July 27, 2020, 1:17pm

@nacho, the docker run you shared looks like one generated either on a mac or windows OS.

I’m assuming that you made your onboarding on a windows or mac OS and then copy paste it to a Linux OS ?

nacho · July 27, 2020, 1:28pm

That’s correct. I didn’t log on to the website from my Linux VPS. I did it from my Mac. What should I change?

anon37102731 · July 27, 2020, 1:53pm

If you’re able to, yes, it is the simplest way.

But if you cannot, I’ll highlight what to change from the generated CLI command to make it work.

Just let me know

nacho · July 27, 2020, 2:04pm

Yes, please. I wouldn’t know how to connect to the website from the command line

anon37102731 · July 27, 2020, 2:14pm

Sure,

Having the CLI command given by the UI on macOS, you need to:

Add a --network host just after the --init
replace all occurrences of host.docker.internal by localhost

Let me know if it works correctly for you.

nacho · July 27, 2020, 2:34pm

Thanks. It’s a step forward, but now I get this:

✓ Connecting to your database
✖ Analyzing the database
> Cannot generate your project.
> An unexpected error occurred. Please reach out for help in our Slack community or create a Github issue with following error: SequelizeDatabaseError: more than one row returned by a subquery used as an expression
(node:15) UnhandledPromiseRejectionWarning: SequelizeDatabaseError: more than one row returned by a subquery used as an expression
    at Query.formatError (/usr/local/lib/node_modules/lumber-cli/node_modules/sequelize/lib/dialects/postgres/query.js:366:16)
    at query.catch.err (/usr/local/lib/node_modules/lumber-cli/node_modules/sequelize/lib/dialects/postgres/query.js:72:18)
    at tryCatcher (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/promise.js:725:18)
    at _drainQueueStep (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues [as _onImmediate] (/usr/local/lib/node_modules/lumber-cli/node_modules/bluebird/js/release/async.js:15:14)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
(node:15) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:15) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

anon37102731 · July 27, 2020, 3:02pm

@nacho could you please share with me the lumber version you’re using to generate your project?

lumber --version should do the job.

nacho · July 27, 2020, 4:01pm

Hi, Valentin. I don’t have lumber installed. All I did was execute the docker run command. Should I install it?

vince · July 29, 2020, 6:59am

Hi @nacho,

Could you try to generate your project with lumber instead of docker. If it still do not works add DEBUG=* before the lumber generate command.

nacho · July 29, 2020, 8:02am

Thanks, Vince. I was able to get the problematic SQL command using the log. It appears that the problem is that the Comment subquery returns multiple (duplicated) rows. Is there anything I can do to fix it

Here is the full query:

sequelize:sql:pg Executing (default): SELECT pk.constraint_type as "Constraint",c.column_name as "Field", c.column_default as "Default",c.is_nullable as "Null", (CASE WHEN c.udt_name = 'hstore' THEN c.udt_name ELSE c.data_type END) || (CASE WHEN c.character_maximum_length IS NOT NULL THEN '(' || c.character_maximum_length || ')' ELSE '' END) as "Type", (SELECT array_agg(e.enumlabel) FROM pg_catalog.pg_type t JOIN pg_catalog.pg_enum e ON t.oid=e.enumtypid WHERE t.typname=c.udt_name) AS "special", (SELECT pgd.description FROM pg_catalog.pg_statio_all_tables AS st INNER JOIN pg_catalog.pg_description pgd on (pgd.objoid=st.relid) WHERE c.ordinal_position=pgd.objsubid AND c.table_name=st.relname) AS "Comment" FROM information_schema.columns c LEFT JOIN (SELECT tc.table_schema, tc.table_name, cu.column_name, tc.constraint_type FROM information_schema.TABLE_CONSTRAINTS tc JOIN information_schema.KEY_COLUMN_USAGE  cu ON tc.table_schema=cu.table_schema and tc.table_name=cu.table_name and tc.constraint_name=cu.constraint_name and tc.constraint_type='PRIMARY KEY') pk ON pk.table_schema=c.table_schema AND pk.table_name=c.table_name AND pk.column_name=c.column_name WHERE c.table_name = 'posts' AND c.table_schema = 'public' +2ms
  sequelize:pool connection released +17ms
✖ Analyzing the database
> Cannot generate your project.
> An unexpected error occurred. Please reach out for help in our Slack community or create a Github issue with following error: SequelizeDatabaseError: more than one row returned by a subquery used as an expression
  superagent POST https://forestadmin-server.herokuapp.com/api/lumber/error +0ms
(node:20158) UnhandledPromiseRejectionWarning: SequelizeDatabaseError: more than one row returned by a subquery used as an expression

vince · July 29, 2020, 12:59pm

This query is something automatically generated by Sequelize. But I can’t find any issues related to that. Could you please share a full record result with all columns of that subquery comment so I can investigate what is going on ? I don’t have the issue on my side.

Out of curiosity, do you have multiple schemas with the same tables ?

nacho · July 29, 2020, 1:38pm

That seems to see the problem. I have a backup schema and if I simply add AND st.schemaname = 'public' I don’t get the error. Here it is in context (line 29, I’ve added a comment):

SELECT
  pk.constraint_type AS "Constraint",
  c.column_name AS "Field",
  c.column_default AS "Default",
  c.is_nullable AS "Null",
  (
    CASE WHEN c.udt_name = 'hstore' THEN c.udt_name ELSE c.data_type END
  ) || (
    CASE WHEN c.character_maximum_length IS NOT NULL THEN '(' || c.character_maximum_length || ')' ELSE '' END
  ) AS "Type",
  (
    SELECT
      array_agg(e.enumlabel)
    FROM
      pg_catalog.pg_type t
      JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
    WHERE
      t.typname = c.udt_name
  ) AS "special",
  (
    SELECT
      pgd.description
    FROM
      pg_catalog.pg_statio_all_tables AS st
      INNER JOIN pg_catalog.pg_description pgd ON (pgd.objoid = st.relid)
    WHERE
      c.ordinal_position = pgd.objsubid
      AND c.table_name = st.relname
      AND st.schemaname = 'public' -- <== I ADDED THIS
  ) AS "Comment"
FROM
  information_schema.columns c
  LEFT JOIN (
    SELECT
      tc.table_schema,
      tc.table_name,
      cu.column_name,
      tc.constraint_type
    FROM
      information_schema.TABLE_CONSTRAINTS tc
      JOIN information_schema.KEY_COLUMN_USAGE cu ON tc.table_schema = cu.table_schema
      AND tc.table_name = cu.table_name
      AND tc.constraint_name = cu.constraint_name
      AND tc.constraint_type = 'PRIMARY KEY'
  ) pk ON pk.table_schema = c.table_schema
  AND pk.table_name = c.table_name
  AND pk.column_name = c.column_name
WHERE
  c.table_name = 'posts'
  AND c.table_schema = 'public'

I’m specifying the schema name (public) when I answer the lumber questions but it seems this is not being taken into account for the subquery.

How can I add this piece of code so I can generate the app with the lumber command?

vince · July 29, 2020, 3:09pm

It’s in Sequelize in this file. I’ll need to do a Pull Request or if you want to do one feel free .

nacho · July 29, 2020, 4:20pm

Done.

Is there a workaround I can use in the meantime?

Sliman_Medini · July 31, 2020, 1:37pm

Hi @nacho,

Since the bug is at the generation process, a workaround may be to move you backup schema, in order to let Sequelize with only one schema.

nacho · August 3, 2020, 2:19pm

Thanks, Sliman. How should I go about this? Do you mean moving it to a different database and then moving it back? I’m a bit hesitant this might break things

jeffladiray · August 3, 2020, 4:02pm

Hi @nacho,

Not sure if that would work, but you could maybe try to yarn link your sequelize PR inside your globally installed lumber until it is fixed and merged, then in the lumber generated project ?
This is far from being a good solution.

Maybe a database dump imported inside a local docker and a removal of the backup schema would do the trick ? At least until your PR is merged ?

Topic		Replies	Views
Cannot Connect to the database Help me! setup	1	364	March 30, 2023
Docker - Cannot connect to my database Help me!	2	620	May 11, 2020
Connecting to Postgres DB with "forestadmin/toolbelt projects:create" command failed ECONNREFUSED Help me! setup	6	1608	June 14, 2022
Your server encountered an error (getaddrinfo ENOTFOUND postgres postgres:5432) Help me! setup	4	8946	February 8, 2021
New postgres db, can't reach Forest Admin panel Help me! setup	5	1613	November 27, 2020

Can't connect to Postgres DB inside another docker container

Related topics