Prisma Models: can autoincrement() start at 0?

Is it possible to have @id @default(autoincrement()) with auto-incrementation starting the ids from 0 instead of 1 ?

In relation to : https://stackoverflow.com/questions/66415593/start-ids-at-an-arbitrary-number-in-prisma-data-model
(which did not receive any answer either)

I am interested to see if it is possible with postgresql in particular.

Prisma generates the corresponding SQL :

CREATE TABLE "Message" (
    "id" SERIAL NOT NULL,
    "from" TEXT NOT NULL,
    "content" TEXT NOT NULL,
    "discussionId" INTEGER NOT NULL,

    CONSTRAINT "Message_pkey" PRIMARY KEY ("id")
);

Is the NOT NULL the issue ? Would it be ok to remove it ?

That SO answer seems to at least suggest that it would be possible : https://stackoverflow.com/a/32728273/10469162

And if it's possible, is there a reason for Prisma not to expose it ?

There are a couple options available. The first and preferred is generated always as identity. Available only in versions 10 and above.

create table message (
      id integer generated always as identity 
                 (minvalue 0  start with 0)
    , _from text not null
    , content text not null
    , discussionid integer not null
    , constraint message_pkey primary key (id)
);

The other (and required for versions prior to 10) is not define the id column as serial, but manually do what serial does. Although serial occupies the place of data type in the ddl it is not a data type; it is actually a short for:

1. create a sequence,
2. create column of data type integer,
3. set the sequence as default for column. <br/>

So:

create sequence message_id_seq
                minvalue 0
                start with 0;
            
create table message (
    id integer default nextval('message_id_seq')
    , _from text not null
    , content text not null
    , discussionid integer not null
    , constraint message_pkey primary key (id)
    );

See demo. Sorry, but I am unable to translate into your obscurification language (Prisma) as I am not familiar enough with it.

It's not possible from the schema directly but you can modify the migration file to achieve this. This example uses a Postgres database.

Let's assume that we have to start the Task id from 0. To do that, Add the model in schema.prisma file

model Task {
  id        Int   @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String?
}

Then run the below command

 npx prisma  migrate dev --create-only

It will add the migration file in the prisma/migrations folder. Then open the migration.sql file and add the following code

ALTER SEQUENCE "Task_id_seq" MINVALUE 0 START 0 RESTART 0;

Then run the following command to apply in the database

 npx prisma  migrate dev

Then you should see the output like below

From here, you can then proceed to create records. For example

const res = await prisma.task.create({
  data: {
    title: "testing",
    updatedAt: new Date()
  }
 })

Logging the result to the console, you should see the id starting at 0

{
  id: 0,
  createdAt: 2023-03-09T02:35:53.587Z,
  updatedAt: 2023-03-09T02:35:53.532Z,
  title: 'testing'
}

This response was crafted by ludralph over at github and reproduced here with him being notified about it for the purpose of sharing knowledge.
Here is the original link of the answer: https://github.com/prisma/prisma/discussions/18246