POST - Create new record in a table having IDENTITY primary key column

[TheGongoDev82]

Hello! I just started few days ago using DAB. I tried to insert a new record in a table having IDENTITY primary key column. Is there a method to enable IDENTITY INSERT by using REST POST method?

[yorek]

You can not at the moment. The options you have to make it work are 2:

• Create a stored procedure that runs SET IDENTITY_INSERT ON and then insert the data or,
• Replace the identity column with a sequence + default constraint so that it doesn't require any special SET option to be overridden

[TheGongoDev82]

Thanks @yorek for your reply. I have created a SP and it works!!!

By using REST POST method, is it possible to skip the PK insertion and let it increase automatically?

[yorek]

Yep, you can

[TheGongoDev82]

May you suggest me please how to perform it by using MS-SQL Server?

Let's say I have this table

CREATE TABLE dbo.persons (
	id_person TINYINT PRIMARY KEY IDENTITY(1, 1)
	, first_name VARCHAR(255) NOT NULL
	, last_name VARCHAR(255) NOT NULL
	, date_of_birth DATE
)

This is my dab-config.json > runtime > rest configuration:

...
    "rest": {
      "enabled": true,
      "path": "/api",
      "request-body-strict": false
    },
...

This is my dab-config.json > entities configuration:

...
  "entities": {
    "Person": {
      "source": {
        "object": "dbo.persons",
        "type": "table"
      },
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "Person",
          "plural": "People"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*"
            }
          ]
        }
      ]
    }
  }
...

By using OpenAPI Swagger, if I try REST POST method with this value

{
  "first_name": "Tizio",
  "last_name": "Caio",
  "date_of_birth": "1900-01-01"
}

I have this response body

{
  "error": {
    "code": "BadRequest",
    "message": "Invalid request body. Missing field in body: id_person.",
    "status": 400
  }
}

[yorek]

You need to use a SEQUENCE instead of an IDENTITY (which is a good practice independently from DAB usage)

CREATE SEQUENCE dbo.GlobalSequence
AS INT START WITH 1;
GO

CREATE TABLE dbo.persons (
	id_person INT PRIMARY KEY DEFAULT(NEXT VALUE FOR dbo.GlobalSequence)
	, first_name VARCHAR(255) NOT NULL
	, last_name VARCHAR(255) NOT NULL
	, date_of_birth DATE
)
GO