Search code examples
javascriptsqlnode.jspostgresqlprisma

Prisma migration error: "type "serial" does not exist" despite using PostgreSQL 15

I'm trying to run a migration in my Prisma project, but I'm encountering the error "type "serial" does not exist", even though I'm using PostgreSQL 15.8, which should support this data type.

Steps I've taken:

I've ensured that I'm using the latest version of Prisma Client (@prisma/client: "5.11.0"). I've checked my PostgreSQL version (psql -V), which is PostgreSQL 15.8. I've cleared the Prisma cache by deleting the node_modules and prisma directories and then reinstalling dependencies (npm install). I've tried manually executing the SQL migration in the PostgreSQL console, but I still encounter the error. I don't have any additional PostgreSQL extensions installed (\dx only shows plpgsql). My migration file:

-- AlterTable
ALTER TABLE "ClosedPositions" DROP CONSTRAINT "ClosedPositions_pkey",
ALTER COLUMN "id" SET DATA TYPE SERIAL,
ADD CONSTRAINT "ClosedPositions_pkey" PRIMARY KEY ("id");

-- AlterTable
ALTER TABLE "OpenPositions" DROP CONSTRAINT "OpenPositions_pkey",
ALTER COLUMN "id" SET DATA TYPE SERIAL,
ADD CONSTRAINT "OpenPositions_pkey" PRIMARY KEY ("id");

Error: P3006

Migration `20240920182327_change_id_to_int_in_positions` failed to apply cleanly to the shadow database. 
Error:
ERROR: type "serial" does not exist
   0: sql_schema_connector::validate_migrations
           with namespaces=None
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:325
   1: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:276

What could be causing this error, and how can I resolve it? Is there a conflict or configuration issue that's preventing Prisma from correctly recognizing the serial data type?

I've also tried

 npx prisma migrate reset

and I get error:

Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20240920182327_change_id_to_int_in_positions

Database error code: 42704

Database error:
ERROR: type "serial" does not exist

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42704), message: "type \"serial\" does not exist", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_type.c"), line: Some(274), routine: Some("typenameType") }

many thanks for any help

Solution

  • The issue is serial is not a type it is essentially a macro as described in the link:

    The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

    CREATE TABLE tablename ( colname SERIAL );

    is equivalent to specifying:

    CREATE SEQUENCE tablename_colname_seq AS integer; CREATE TABLE tablename ( colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

    The error is coming from Postgres itself:

    create table id_test(id integer);

alter table id_test alter column id type serial;
ERROR:  type "serial" does not exist

    If you want to add an autoincremented default to the column then from here Alter Table:

    ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

    Which using my previous example would be:

    alter table id_test alter column id set not null, alter column id add generated always as identity;

    The set not null is required for an identity column.