Question 89491 in PostgreSQL Question posted in
The official documentation can be found here.

Prisma Models: can autoincrement() start at 0? – Postgresql

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 ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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 Output you should see 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


  2. 0

    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.

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search