Nodejs: Identity returned by Insert is a nested object. It doens't return id anymore

[JonL]

Using PostgreSQL

[patrick]

I have fixed the identity for the other databases, but postgres does not return the identity after an insert.

Checked the knex docs and with postgres you have to pass the field that it should return as an extra parameter.

[JonL]

Interesting. I would have suspected that from mysql as there is no return statement. Unless it’s being retrieved via last id or something similar.

So now you have the problem of what field we selected as identity, right?

[patrick]

With the metadata from the database manager in wappler we could probably know what the primary key is and use that to return the correct identity for postgres. We have to investigate what is possible.

[JonL]

Yeah. i thought of that. But still you would have the problem of databases not created in wappler or created initially but modified later elsewhere.

I found this in case it’s useful.

https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns

Maybe overkill to query before every insert/update in the app.

And another option is to allow the user to define the field to be returned. It may be even practical in other contexts.

[patrick]

Will be improved/fixed in today’s update.

[JonL]

Hi guys,

Now it returns a single value which is much better than a nested object but can’t do much with this

Mentioning you guys as I don’t know if you will get the notification in an unlisted post. @patrick @George

[patrick]

Do you have a primary key in that table, I tested with the ones generated by Wappler database manager and it worked fine.

[JonL]

Yup. I have one, but I must admit that I deviate from Knex implementation of increments for PG.
Knex uses serial but I use identity.

Feature Request: Support for identity columns in lieu of serial in postgres

https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial

PostgreSQL 10 identity columns explained - 2ndQuadrant | PostgreSQL

So my migration file actually declares the id column as:

table.specificType("id", "integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY");

Don’t know if that has something to do.

[patrick]

Does the database manager detect the column correctly with primary key? I haven’t tested this specific field type, only the serial. Will do some testing on Monday.

[JonL]

I believe so.

@patrick

Will do some testing on Monday.

Thanks!

[patrick]

Did some testing with an integer as primary key and it worked fine. What is the json generated by the insert action?

It should look like:

      {
        "name": "insert1",
        "module": "dbupdater",
        "action": "insert",
        "options": {
          "connection": "db",
          "sql": {
            "type": "insert",
            "values": [
              {
                "table": "test",
                "column": "firstname",
                "type": "text",
                "value": "John"
              },
              {
                "table": "test",
                "column": "lastname",
                "type": "text",
                "value": "Doe"
              }
            ],
            "table": "test",
            "query": "INSERT INTO test\n(firstname, lastname) VALUES ('John', 'Doe')",
            "params": [],
            "returning": "id"
          }
        },
        "meta": [
          {
            "name": "identity",
            "type": "text"
          },
          {
            "name": "affected",
            "type": "number"
          }
        ],
        "output": true
      }

Important is the added "returning": "id".

[JonL]

OK. That was the issue. I did not recreate the step so the json file was outdated.

I confirm it’s working to with Identity type instead of serial.

Thanks Patrick! This topic can be closed.