Loopback-next: Discover not working

[bagintz]

Hello, I am not able to get lb4 discover to work as described for MySQL. The 2 different results I get are as follows:

1) When using the default "root" user with no passoword and connecting to the DB I want to discover, I get back a list of "models to discover" that do not include the actual table from the DB, but a bunch of other data that looks like it might be from the performance schema or some other ancillary source (host_summary, host_summary_by_file_io....., metrics, processlist, etc)

2) When using the actual DB user/pass (tested on the command line to connect successfully), it gives me back a completely empty list of models, even though there are 18 tables. And to be sure that it wasn't some sort of "cranking" through all the data, I tried on a version of the DB with all the data gone, but with structure intact.

Any ideas on what I am doing wrong?

Thanks!

[kushball]

Hey Bagintz,

I was having the same issue this morning and realized I had to specify the schema to be the database name in order to find the tables I expected.

lb4 discover --schema [DB_NAME_HERE]

[bagintz]

@kushball That worked thanks! Now I am having the problem that it is not finding importing/discovering the foreign keys, does lb4 support this?

[dhmlau]

Looking at https://github.com/strongloop/loopback-next/blob/master/packages/cli/lib/model-discoverer.js and https://github.com/strongloop/loopback-next/blob/master/packages/cli/lib/model-discoverer.js, it doesn't seem like the lb4 discover command has an option to discover foreign keys.

I believe you can reference this LB3 docs https://loopback.io/doc/en/lb3/Discovering-models-from-relational-databases.html#additional-discovery-functions for discovering the foreign keys. I can give it a try tomorrow (it's a bit late for me now).

[dhmlau]

@marvinirwin, might need you to chime in here. When running the lb4 discover command, if we don't specify the database name with the --schema option, the tables cannot be found.

If --schema option is required, we might need to be more specific about it in the documentation.

[marvinirwin]

@dhmlau The schema which is used for discovery is either --schema, settings.database or '' AFAIK, I'm not sure it should be required because it can already be set beforehand.

These are the potential solutions I could think of to handle "Table not found" errors.

1. Make --schema required
2. Catch all errors when attempting to discover, display If your table was not found, do you need to set --schema ? to give the user a hint
3. Check if the table exists, perhaps even check other schemas for this table. I'm not sure if all connectors support this though, it seems too ambitious.

I'm indifferent to which I implement, what do you think?

[marvinirwin]

@dhmlau Strange, I can't reproduce your bug, when I specify my schema I get the tables I want. Try DEBUG=loopback:connector:postgres lb4 discover --schema testdb

You should see one of three statements look something like this.

loopback:connector:postgresql SQL: SELECT 'view' AS "type", table_name AS "name", table_schema AS "owner" FROM information_schema.views WHERE table_schema='testdb' +1ms

When I CREATE SCHEMA testdb; CREATE TABLE test (test int); I can see test in the results of the query.

However, I did find that my assumption that it would use the database parameter for the table_schema wrong.

The loopback-postgres-connector will use public if you don't provide the --schema option.
The loopback-mysql-connector will use your username if you don't provide the --schema option.

[dhmlau]

@marvinirwin, I just tried again and it worked without the --schema option! It's probably because it's taking a long time to get the table list and appeared as it hung (?).

Regarding your comment about table_schema value, for my database, table_schema is owner for me with or without the --schema option. I'm using the postgresql docker image.

FYI --

$ DEBUG=loopback:connector:* lb4 discover --schema testdb
  loopback:connector:postgresql Settings {"name":"ds","connector":"postgresql","url":"","host":"localhost","port":5432,"user":"xxx","password":"xxx","database":"testdb","debug":false} +0ms
? Select the connector to discover ds
  loopback:connector:postgresql SQL: SELECT 'table' AS "type", table_name AS "name", table_schema AS "owner" FROM information_schema.tables WHERE table_schema='testdb' +5s
  loopback:connector:postgresql SQL: SELECT 'view' AS "type", table_name AS "name", table_schema AS "owner" FROM information_schema.views WHERE table_schema='testdb' +0ms
  loopback:connector:postgresql:data {"command":"SELECT","rowCount":0,"oid":null,"rows":[],"fields":[{"name":"type","tableID":0,"columnID":0,"dataTypeID":25,"dataTypeSize":-1,"dataTypeModifier":-1,"format":"text"},{"name":"name","tableID":12948,"columnID":3,"dataTypeID":1043,"dataTypeSize":-1,"dataTypeModifier":-1,"format":"text"},{"name":"owner","tableID":12948,"columnID":2,"dataTypeID":1043,"dataTypeSize":-1,"dataTypeModifier":-1,"format":"text"}],"_parsers":[null,null,null],"_types":{"_types":{"arrayParser":{}},"text":{},"binary":{}},"RowCtor":null,"rowAsArray":false} +0ms
  loopback:connector:postgresql:data {"command":"SELECT","rowCount":0,"oid":null,"rows":[],"fields":[{"name":"type","tableID":0,"columnID":0,"dataTypeID":25,"dataTypeSize":-1,"dataTypeModifier":-1,"format":"text"},{"name":"name","tableID":12994,"columnID":3,"dataTypeID":1043,"dataTypeSize":-1,"dataTypeModifier":-1,"format":"text"},{"name":"owner","tableID":12994,"columnID":2,"dataTypeID":1043,"dataTypeSize":-1,"dataTypeModifier":-1,"format":"text"}],"_parsers":[null,null,null],"_types":{"_types":{"arrayParser":{}},"text":{},"binary":{}},"RowCtor":null,"rowAsArray":false} +3ms
? Select the models which to discover (Press <space> to select, <a> to toggle al
l, <i> to invert selection)

[marvinirwin]

@dhmlau

Regarding your comment about table_schema value, for my database, _table_schema is owner for me with or without the --schema option._ I'm using the postgresql docker image.

I don't understand what you mean by this. Do you mean that the table_schema of your tables you created in testdb is 'owner'?

Looking at your debug output it looks like it's querying correctly. I'm not sure why the tables are missing though.

SELECT 'table' AS "type", table_name AS "name", table_schema AS "owner" FROM information_schema.tables could be useful, as it would show the table_schema of the missing tables

[dhmlau]

@marvinirwin, sorry about the confusion.
I was referring to your below comment that I don't have the problem you're seeing.

The loopback-postgres-connector will use public if you don't provide the --schema option.

However, someone encountered this issue: https://github.com/strongloop/loopback-next/issues/3074.

Since it's working for me, if you agree, we'll keep this issue closed, and continue discussion on https://github.com/strongloop/loopback-next/issues/3074 for the table_schema issue. Thanks!