Loopback-next: Using postgres function to generate uuid's fails

[spicemc]

Steps to reproduce / Current Behavior

I'd like to use a different postgres function to generate uuids.
But having this model property

  @property({
    type: 'string',
    id: true,
    generated: true,
    useDefaultIdType: false,
    postgresql: {
      dataType: 'uuid',
      extension: 'pgcrypto',
      defaultFn: 'gen_random_uuid',
    },
  })
  id?: string;

creates an "int4" data type with a default "nextval('user_id_seq'::regclass)".

Expected Behavior

It should create the data type "uuid" with "gen_random_uuid()" as default.

Additional information

node -e 'console.log(process.platform, process.arch, process.versions.node)'
linux x64 12.14.1

npm ls --prod --depth 0 | grep loopback
+-- @loopback/[email protected]
+-- @loopback/[email protected]
+-- @loopback/[email protected]
+-- @loopback/[email protected]
+-- @loopback/[email protected]
+-- @loopback/[email protected]
+-- @loopback/[email protected]
+-- @loopback/[email protected]
`-- [email protected]

The extension pgcrypto is enabled in postgres and works as expected with this query:

SELECT gen_random_uuid();

[dhmlau]

@spicemc, I believe you need to have the brackets after gen_random_uuid.

I was able to run npm run migrate and create the table with the UUID column.
Here is the property setting I used, which is pretty much as yours:

@property({
    type: 'string',
    id: true,
    generated: true,
    useDefaultIdType: false,
    postgresql: {
      dataType: 'uuid',
      extension: 'pgcrypto',
      defaultFn: 'gen_random_uuid()', //<---- only this line is different
    },
  })
  id?: string;

And I assume you've created the pgcrypto extension in the database: https://starkandwayne.com/blog/uuid-primary-keys-in-postgresql/

Hope it helps.

[spicemc]

@dhmlau, Thanks for your answer and for the link. I tried your suggestion but without success.
Yes, i`ve created the extension and also adding it via SQL works:

ALTER TABLE public."user" ADD testuuid uuid NULL DEFAULT gen_random_uuid();

After that i made further attempts. When i set a normal property then the datatype is taken:

  @property({
    type: 'string',
    postgresql: {
      dataType: 'uuid',
    },
  })
  test?: string;

Setting generated: true does not use the specified value of the datatype. Setting generated: false sets the datatype to uuid but does not use the specified function in defaultFn:

  @property({
    type: 'string',
    id: true,
    generated: false, //<---now the datatype uuid is taken but without defaultFn
    useDefaultIdType: false,
    postgresql: {
      dataType: 'uuid',
      extension: 'pgcrypto',
      defaultFn: 'gen_random_uuid', //<--- with brackets acts the same
    },
  })
  id?: string;

I think i will use the following aproach. Would you recommend that?

  @property({
    type: 'string',
    id: true,
    generated: false,
    useDefaultIdType: false,
    default: () => generateSomeUuid(),
    postgresql: {
      dataType: 'uuid',
    },
  })
  id?: string;

[dhmlau]

Interesting. I was able to use what you had in the original description.
Here is what I've done:

1. In postgres database, run
   CREATE EXTENSION pgcrypto;

2. In LB app, after creating all the models, datasource, repository and controller, run:

$ npm run build        <--- this transpiles the code to JavaScript
$ npm run migrate   <--- this create the table in the postgresql database

1. Check the definition of the table.
   Mine shows:

# \d review
                             Table "public.review"
  Column  |           Type           | Collation | Nullable |      Default      
----------+--------------------------+-----------+----------+-------------------
 reviewid | uuid                     |           | not null | gen_random_uuid()
 date     | timestamp with time zone |           | not null | 
 comment  | text                     |           | not null | 
Indexes:
    "review_pkey" PRIMARY KEY, btree (reviewid)

1. Test the endpoint using API Explorer. I can see the generated UUID in the corresponding property.

---

If you're going to use:

@property({
    type: 'string',
    id: true,
    generated: false,
    useDefaultIdType: false,
    default: () => generateSomeUuid(),
    postgresql: {
      dataType: 'uuid',
    },
  })
  id?: string;

I'd recommend you removing generated: false because you're actually setting the default value.

[dhmlau]

The reason I added the brackets for gen_random_uuid because it failed when I run the migrate script.

[spicemc]

Yes very strange. There are no errors in the logs and i have a different behaviour.
Even if i create another property the datatype is "int4" if i set generated: true :

npm run build
npm run migrate -- --rebuild

  @property({
    type: 'string',
    generated: true,
    postgresql: {
      dataType: 'uuid',
      extension: 'pgcrypto',
      defaultFn: 'gen_random_uuid()',
    },
  })
  test?: string;

It seems like a fallback.
My postgres version is 12.1 and pgcrypto is 1.3

[spicemc]

Oh there is an error in the postgres logs. Running
npm run migrate -- --rebuild
logs:
ERROR: schema "public" already exists

[spicemc]

Now it's getting interesting. If i delete the table and then running migrate twice:

npm run migrate //<--- creates the properties as int4
npm run migrate //<--- the second throws an error

Migrating schemas (alter existing schema)
Cannot migrate database schema error: cannot cast type integer to uuid
    at Connection.parseE (/app/node_modules/pg/lib/connection.js:604:13)
    at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:403:19)
    at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:123:22)
    at Socket.emit (events.js:223:5)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:290:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:181:23) {
  name: 'error',
  length: 103,
  severity: 'ERROR',
  code: '42846',
  detail: undefined,
  hint: undefined,
  position: '71',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_expr.c',
  line: '2826',
  routine: 'transformTypeCast'
}

It seems that the second run now knows about the datatype and converting fails. But why does the first run create a different datatype (int4)?

[agnes512]

@spicemc IIUC you want the test and id properties all be auto-generated uuid? The fields generated and useDefaultIdType are only applicable for the id property id. See https://github.com/strongloop/loopback.io/pull/918.

_I think_ the setting

  @property({
    type: 'string',
    postgresql: {
      dataType: 'uuid',
    },
  })
  test?: string;

would allow you to have auto-generated uuid for non-id property in Postgres. Please let me know if it works.

[spicemc]

@agnes512, thanks, i tried this setting. Unfortunately the uuid is not generated automatically but the datatype is uuid.

Actually i just wanted to use the auto-generated uuid for the id property. But it didn't work. I have added the Test property for testing purposes only.

[agnes512]

@spicemc We just made a release. Could you try -- [email protected]?
I think the setting:

  @property({
    type: 'string',
    id: true,
    generated: true,
    useDefaultIdType: false,
    postgresql: {
      dataType: 'uuid',
      extension: 'pgcrypto',
      defaultFn: 'gen_random_uuid()',
    },
  })
  id?: string

should work 😄

[spicemc]

@agnes512 Good to know that it should work. :smile:

I updated and when i run npm run migrate then these errors occur:

Migrating schemas (alter existing schema)
Cannot migrate database schema error: permission denied to create extension "uuid-ossp"
//...
or with --rebuild
//...
Migrating schemas (drop existing schema)
error: permission denied to create extension "uuid-ossp"
//...
Cannot migrate database schema error: permission denied to create extension "uuid-ossp"

Now it tries to use the extension "uuid-ossp" which is currently not installed/created and "pgcrypto" is ignored. Another problem is, that this is not working anymore (which went smoothly with [email protected]):

  @property({
    type: 'string',
    id: true,
    useDefaultIdType: false,
    default: () => uuid(), 
    postgresql: {
      dataType: 'uuid',
    },
  })
  id?: string;

The same errors as above are thrown, so it forces to use "uuid-ossp", too.

[agnes512]

Hmm it's weird. Because we use extension uuid-ossp and function uuid_generate_v4() by default if users don't provide both extension and function.

The setting

  @property({
    type: 'string',
    id: true,
    generated: true,  // I've modified the settings a bit
    useDefaultIdType: false,
    postgresql: {
      dataType: 'uuid',
    },
  })
  id?: string;

will use the default values because only postgresql.dataType is being set.

But the setting below shouldn't be using the default ones :(

  @property({
    type: 'string',
    id: true,
    // settings below are needed
    generated: true,
    useDefaultIdType: false,
    postgresql: {
      dataType: 'uuid',
      extension: 'pgcrypto',
      defaultFn: 'gen_random_uuid()',
    },
  })
  id?: string

I've tested it on my end, I am able to use pgcrypto and gen_random_uuid(). Do you mind checking the settings one more time?
If it still doesn't work, could you try to create the extension pgcrypto first in you database and do the migration again? I am thinking the issue might be related to when it creates extensions. Thank you!!

[spicemc]

No worries. Too bad it is not reproducible on your side. I double checked the settings and copied yours. But the error still exist. Migrate sends this to the postgres server:

ERROR:  permission denied to create extension "uuid-ossp"
HINT:  Must be superuser to create this extension.
STATEMENT:  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE SCHEMA "public"

Really strange.

I also checked the postgres server again, the extension is active and it is possible to add a table and a uuid column with the function gen_random_uuid() which perfectly generates uuid's on insert.

[agnes512]

Ah I think it might because I am always using root user so it works fine.

Could you try create the extension in your database with
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"?
Just want to check if it's causing by the db permissions.

Sorry we didn't consider that when designing it D:

[spicemc]

Now it works! So i need to have both extensions created or at least uuid-ossp. Now also works again setting a uuid as default like this default: () => uuid().

Thanks for your help. :relaxed:

[agnes512]

I am closing this issue. Feel free to reopen it if you have any questions!