Loopback-next: LOOPBACK 4 - POSTGREQL ERROR relation "public.XXX" does not exist

[jotamora]

Hello,

When you made a simple program in LB4 with postgresql, using the command line interface, you have this error when you try to use the API

500 error: relation "public.XXX" does not exist
In my case, with this model:

@model({ settings: {} })
export class ImageModel extends Entity {
  @property({
    type: 'string',
    id: true,
  })
  idImage?: string;

  @property({
    type: 'string',
  })
  name?: string;


  constructor(data?: Partial<ImageModel>) {
    super(data);
  }
}

I have this problem:
500 error: relation "public.imagemodel" does not exist

If for example I change to a Mongo or to MySQL everything start to works well. Anybody knows what happens with postgre??

Thanks

[dougal83]

Hi @jotamora

Have you run the database migration script? npm run migrate

If it is not that can you please make a public repo to on GitHub to enable someone to take a deeper look at the issue?

[Tpuljak]

I have the same issue, except I used lb4 discover to discover the models from the datasource and then any request towards the database yields the 500 error: relation "public.XXX" does not exist result.

Also, I tried running npm run migrate after the discover but that too yields errors.
syntax error at or near "null" at character 286 where char 286 points to TIMESTAMP (null) WITH TIME ZONE NOT NULL. Im not sure why (null) is present after TIMESTAMP in the query.... and the other errors are pointing to VARCHAR(undefined) which, I guess, are created from @properties of type postgresql: {columnName: 'XXX', dataType: 'character varying', dataLength: null,....}

[Tpuljak]

I think the issue is with plurality. Most of my tables are in plural form of the model, but queries try to execute on the singular name. Thats why I get an error 500 error: relation "public.user" does not exist, since the table it should be looking into is "public.users".

When I run lb4 discover the models are clearly generated from the "plural" tables (e.g. devices, users,...) but then I run npm run migrate the script attempts to create "singular" tables...

So I guess 2 issues are present:

1. "Plurality" issue stated in this comment.
2. The Null/undefined issue stated above.

[jotamora]

Hello @Tpuljak ,

I have discover that lb4 don´t create the table in DB in postgre, if it doesn´t exist, like in Mongo. You need to create the table of the DB first. After that in your model:

@model({
  settings: {
    postgresql: {
      table: 'imageModel', <-- the name of the table.
    },
  },

})

Careful: For each element in you model, you need to create a column in the table of the DB. If you don´t make that it is going to fail.

I hope loopback team could improve it and could work with postgre like in mongo.

Best,

[achrinza]

Hi @jotamora, have you tried using the database auto migration feature in LoopBack 4? That's what's supposed to be used to generate the database.

AFAIK, it requires explicit execution to prevent any accidental data loss.

[achrinza]

Closing as solved.