Prisma Models: can autoincrement() start at 0?

Question

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

In relation to : 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 ?

A primary key can never be null, so NOT NULL is required. If you leave it out, the database will (re-)create this constraint anyway. — Frank Heikens, Mar 07 '23 at 16:27

score 3 · Answer 1 · answered Mar 09 '23 at 01:02

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:

create a sequence,
create column of data type integer,
set the sequence as default for column.

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.

Thanks for the great answer. Seeing your answer, I decided to create another question for which your answer is a more direct answer : https://stackoverflow.com/questions/75683301/postgresql-can-a-serial-starts-at-0 Would you like to paste it over there so I green check it ? I have already put a link to here there — cassepipe, Mar 09 '23 at 10:29
No that just creates a duplicate/answer question. A link is sufficient. — Belayer, Mar 09 '23 at 18:24
This question has received an answer on Prisma's github "discussions". While I am waiting for the authorization to post that answer here, here is the link : https://github.com/prisma/prisma/discussions/18246 — cassepipe, Mar 10 '23 at 10:44

cassepipe · Accepted Answer · 2023-03-11T13:46:14.727

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

Prisma Models: can autoincrement() start at 0?

2 Answers2

Linked

Related