Error staging a schema with database functions as default value

[MaLub]

Checklist

• I'm using the latest version of Directus
• There's no other issue that already describes the problem.
• I've completed all Troubleshooting Steps.

Describe the Bug

I want to transfer the schema of a Directus instance to an empty Directus instance.
For this I use the steps as described here: https://docs.directus.io/reference/system/schema.html

We use a Postgres 15.1 and Directus 9.23.1

In Postgres it is possible to specify a function as the default for generating a UUID. ( gen_random_uuid())
With /schema/snapshot this function is included.

  "table": "test2",
                    "data_type": "uuid",
                    "default_value": "gen_random_uuid()",
                    "max_length": null,
                    "numeric_precision": null,

During the subsequent import (/schema/diff, /schema/apply) an invalid SQL is generated.

[09:59:53.698] ERROR: Failed to create collection "test2"
[09:59:53.700] ERROR: create table "test2" ("id" uuid not null default '''gen_random_uuid()''', constraint "test2_pkey" primary key ("id")) - invalid input syntax for type uuid: "'gen_random_uuid()'"
    err: {
      "type": "DatabaseError",
      "message": "create table \"test2\" (\"id\" uuid not null default '''gen_random_uuid()''', constraint \"test2_pkey\" primary key (\"id\")) - invalid input syntax for type uuid: \"'gen_random_uuid()'\"",
      "stack":
          error: create table "test2" ("id" uuid not null default '''gen_random_uuid()''', constraint "test2_pkey" primary key ("id")) - invalid input syntax for type uuid: "'gen_random_uuid()'"
              at Parser.parseErrorMessage (xxx/node_modules/pg-protocol/dist/parser.js:287:98)
              at Parser.handlePacket (xxx/node_modules/pg-protocol/dist/parser.js:126:29)
              at Parser.parse (xxx/node_modules/pg-protocol/dist/parser.js:39:38)
              at Socket.<anonymous> (xxx/node_modules/pg-protocol/dist/index.js:11:42)
              at Socket.emit (node:events:513:28)
              at addChunk (node:internal/streams/readable:324:12)
              at readableAddChunk (node:internal/streams/readable:297:9)
              at Readable.push (node:internal/streams/readable:234:10)
              at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      "length": 118,
      "name": "error",
      "severity": "ERROR",
      "code": "22P02",
      "position": "50",
      "file": "uuid.c",
      "line": "133",
      "routine": "string_to_uuid"

---

AddOn
When executing the above SQL statement within the DB, I get a similar error.

create table "test2" (
    "id" uuid not null default 'gen_random_uuid()',
    constraint "test2_pkey" primary key ("id")
)

ERROR:  invalid input syntax for type uuid: "gen_random_uuid()"
LINE 2:     "id" uuid not null default 'gen_random_uuid()',
                                       ^
SQL state: 22P02
Character: 55

-->> Removing the quotes solved the problem

create table "test2" (
    "id" uuid not null default gen_random_uuid(),
    constraint "test2_pkey" primary key ("id")
);

Query returned successfully in 50 msec.

To Reproduce

• Start an local postgres-instance (for instance with docker)
  docker run -p 5432:5432 --name my-database -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_USER=postgres -d postgres:15.1

• Start an empty local directus instance (Port 8555) - connected to the postgres.

• Import the attached snapshot response as described here https://docs.directus.io/reference/system/schema.html
  (First /schema/diff and afterwards the result /schema/apply)
  schema-snapshot-response.json.zip

Hosting Strategy

Self-Hosted (Docker Image)

[azrikahar]

I believe this shares the same fundamental issue with #12042 around "database functions being passed as string with quotes" in default value, thus causing this error to happen as @MaLub described.

Although the previous issue was technically resolved for CURRENT_TIMESTAMP (because this isn't as vendor-specific as gen_random_uuid), there may need to be a slightly more robust way of handling default values that are db functions.

[azrikahar]

/linear

[github-actions]

🤖 Linear issue created! Maintainers can access it here: ENG-785

[u12206050]

We have the same issue with gen_random_uuid. Is there any fix for this, we can't use apply schema.