API of INSERT Query Results for CAP - Node.js

[SebastianEsch]

Hi CAP Community,

I'm unable to find any description of the returned object for database queries in the CAP documentation. For SELECT queries it's straight forward, I get an array of objects matching my predicate, for SELECT.one I get either the an object or null.

For DELETE and UPDATE statements the returned value is the update count.

But for insert statements an InsertResult is returned, e.g.:

{
    "req": {
        // ... lots of other properties
        "data": [
            {
                "name": "Foo",
                "description": "Foo Bar",
                "type_code": "development",
                "ID": "01064279-17cf-4cc6-8ff4-37dfab54693d",
                "createdAt": "2022-01-26T12:53:38.869Z",
                "createdBy": "anonymous",
                "modifiedAt": "2022-01-26T12:53:38.869Z",
                "modifiedBy": "anonymous"
            },
            {
                "name": "Bar",
                "description": "Foo Bar",
                "type_code": "development",
                "ID": "25de8d1e-9126-4408-a7fe-5d17e4ae67fc",
                "createdAt": "2022-01-26T12:53:38.869Z",
                "createdBy": "anonymous",
                "modifiedAt": "2022-01-26T12:53:38.869Z",
                "modifiedBy": "anonymous"
            }
        ],
        // ... lots of other properties
    },
    "results": [
        {
            "lastID": 1,
            "affectedRows": 1,
            "values": [
                "Foo",
                "Foo Bar",
                "development",
                "01064279-17cf-4cc6-8ff4-37dfab54693d",
                "2022-01-26T12:53:38.869Z",
                "anonymous",
                "2022-01-26T12:53:38.869Z",
                "anonymous"
            ]
        },
        {
            "lastID": 2,
            "affectedRows": 1,
            "values": [
                "Bar",
                "Foo Bar",
                "development",
                "25de8d1e-9126-4408-a7fe-5d17e4ae67fc",
                "2022-01-26T12:53:38.869Z",
                "anonymous",
                "2022-01-26T12:53:38.869Z",
                "anonymous"
            ]
        }
    ]
}

I think result.results would be the "public" API and results.req would be considered "private" from a CAP perspective. Unfortunately result.results contains only arrays with the values inserted into the database - which makes it very hard to determine e.g. the inserted ID.

result.req.data contains objects with the inserted values (does not contain properties, for which no values are provided in the data to be inserted).

What is the "right" way to get either the ID of the record inserted into the database or even better the inserted object?

The behaviour described above is valid for SQLite and HANA, the cds-pg adapter for PostgreSQL returns the actual objects in result.results and has objects with SQL functions in result.req.data:

{
    "req": {
        // ... lots of other properties
        "data": [
	    {
	        "name": "Foo",
	        "description": "Foo Bar",
	        "type_code": "development",
	        "ID": "f78f73cf-46bd-488c-8387-5fc66a109c51",
	        "createdAt": "NOW ()",
	        "createdBy": "anonymous",
	        "modifiedAt": "NOW ()",
	        "modifiedBy": "anonymous",
	    },
	    {
	        "name": "Bar",
	        "description": "Foo Bar",
	        "type_code": "development",
	        "ID": "20e1725b-c65f-454c-9161-04ca4491bc34",
	        "createdAt": "NOW ()",
	        "createdBy": "anonymous",
	        "modifiedAt": "NOW ()",
	        "modifiedBy": "anonymous",
	    },
	]
        // ... lots of other properties
    },
    "results": [
        {
	    "name": "Foo",
	    "description": "Foo Bar",
	    "type_code": "development",
	    "ID": "f78f73cf-46bd-488c-8387-5fc66a109c51",
	    "createdAt": Date(timestamp of NOW()),<br>	    "createdBy": "anonymous",
	    "modifiedAt": Date(timestamp of NOW()),<br>	    "modifiedBy": "anonymous",
	},
	{
	    "name": "Bar",
	    "description": "Foo Bar",
	    "type_code": "development",
	    "ID": "20e1725b-c65f-454c-9161-04ca4491bc34",
	    "createdAt": Date(timestamp of NOW()),
	    "createdBy": "anonymous",
	    "modifiedAt": Date(timestamp of NOW()),<br>	    "modifiedBy": "anonymous",
	},
    ]
}<br>

The cds-pg approach for results makes it easier to access the inserted data, but is missing the meta data (insert rows). The HANA and SQLite results providing arrays of inserted values without the respective property / column information is of little value from my point of view.

Kind regards,

Sebastian

[AndreM]

I won't call that inconsistent and incomplete list of words behind that link a documentation ...

can you pls explain how that iterator mentioned there can be accessed? For me INSERT returns an object of type InsertResult containing only 2 properties:

1. req (the Request-object)
2. results ( a list objects that contain the values I inserted in a form that cannot be used for anything at all)

[SebastianEsch]

The iterator for the InsertResult can be used like this:

const result = await INSERT.into(Customers).entries({name: 'Foo', industry: 'Bar', country: 'DE'})
const entries = [...result]

entries will then be an array with the inserted IDs:

[
  {
    ID: "1ce2a57c-d157-4205-94ed-9089c803fffc",
  },
]

With this information you can at least read the inserted records, if you need all attributes.

Kind regards,

Sebastian