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:

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?


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.


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.