Skip to main content

Auto-increment field with Prisma

· 2 min read

SELECT setval(pg_get_serial_sequence('tablename', 'id'), coalesce(max(id)+1, 1), false) FROM "tablename";

This post is a knowledgebase article on Prisma with PostgreSQL.

The typical model in a Prisma schema has an id field that auto-increments:

model User {
  id Int @id @default(autoincrement())
  name String
}

The following is working (most of the time) code that inserts a new entry:

prisma.user.create({
  data: { name: 'Some User' },
});

However, the following error may occur:

Unique constraint failed on the fields: (id)

This is baffling - if the field auto-increments, how can the unique constraint be violated?

Why?​

The answer (for PostgreSQL at least) is that this error typically happens if there is an entry created before that was not created using the auto-incremented ID. In other words, if there is an entry that was created where the ID was explicitly set, a conflict arises when Prisma tries to set its own auto-incremented ID.

Solution​

So, where possible, avoid setting the ID explicitly for tables which have the primary key field being an auto-incremented number field.

But what about...​

Alright, so we know what to avoid doing in the future. But is there a solution for tables that already have entries with manual IDs created?

As a matter of fact, there is!

SELECT setval(pg_get_serial_sequence('"User"', 'id'), coalesce(max(id)+1, 1), false) FROM "User";

Notice that the table name is encapsulated within a pair of single quotes and a pair of double quotes ('"User"')? This is because the name of the table begins with an uppercase character (U). This is a quirk of PostgreSQL. If the table name does not begin with an uppercase letter, the inner double quotes is not necessary (e.g. 'user').

See this discussion in Prisma's Github Discussions.