Can't connect to Postgres DB inside another docker container

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

1 Like

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?

Thanks, Valentin.

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

@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 ?

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?

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 :slight_smile:

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

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.

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.

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

lumber --version should do the job.

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

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.

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

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 ?

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?

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

Done.

Is there a workaround I can use in the meantime?

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.

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 :sweat_smile:

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 ?

2 Likes