Prisma•4d ago

Drift detected after generation the schema and init migration script on mssql

hello community does someone have a idea? I setup a existing mssql database with prisma db pull the schema which was generated looks good. Also i created the init migration sql file in 0_init and applied that to the database after I run prisma migrate dev I got the following error Drift detected: Your database schema is not in sync with your migration history. The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database. It should be understood as the set of changes to get from the expected schema to the actual schema. [*] Changed the MARKET_COMMENT table [+] Added primary key on columns (ID) )

7 Replies

Prisma AI Help•4d ago

You've gone full bot mode! Let me fetch that AI answer for you ASAP. This is a common issue when working with existing databases in Prisma. The "drift detected" error occurs when your database schema doesn't match what Prisma expects based on your migration history. In your case, it seems that the primary key on the MARKET_COMMENT table is defined in your actual database but wasn't properly captured in your migration file. To fix this issue without resetting your database, you can use the baselining approach: 1. First, make sure your schema.prisma file correctly reflects your database structure by running:

npx prisma db pull

npx prisma db pull

2. Then create a new migration that represents your current database state:

mkdir -p prisma/migrations/init
npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/init/migration.sql

mkdir -p prisma/migrations/init
npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/init/migration.sql

3. Mark this migration as already applied:

npx prisma migrate resolve --applied init

npx prisma migrate resolve --applied init

4. Run migrate dev to verify everything is in sync:

npx prisma migrate dev

npx prisma migrate dev

This approach tells Prisma that your current database state is the baseline, and any future migrations should build on top of it rather than trying to recreate it from scratch. The issue likely occurred because when you created your initial migration, it didn't properly capture all the database constraints like the primary key on the MARKET_COMMENT table. For more details on baselining, check out this GitHub discussion and this GitHub issue. 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

MerlinOP•4d ago


model FfaMarketComment {
  id           Int      @id(map: "PK_MARKET_COMMENT") @default(autoincrement()) @map("ID")
  comment      String?  @map("COMMENT") @db.Text
  published    Int      @default(0, map: "DF__MARKET_CO__PUBLI__4AB81AF0") @map("PUBLISHED") @db.TinyInt
  timestamp    DateTime @default(dbgenerated("getutcdate()"), map: "DF__MARKET_CO__TIMES__4BAC3F29") @map("TIMESTAMP") @db.DateTime
  byUser       Int      @map("BY_USER")
  sysStartTime DateTime @default(dbgenerated("sysutcdatetime()"), map: "SYS_START_TIME_FFA_MARKET_COMMENT") @map("SYS_START_TIME")
  sysEndTime   DateTime @default(dbgenerated("CONVERT([datetime2](0),'9999-12-31 23:59:59',(0))"), map: "SYS_END_TIME_FFA_MARKET_COMMENT") @map("SYS_END_TIME")

  @@map("MARKET_COMMENT")
  @@schema("FFA")
}

model BunkerMarketComment {
  id                 Int       @id(map: "PK__MARKET_C__3214EC27C0865E1A") @default(autoincrement()) @map("ID")
  comment            String?   @map("COMMENT") @db.VarChar(Max)
  published          Int       @default(0, map: "DF__MARKET_CO__PUBLI__731B1205") @map("PUBLISHED") @db.TinyInt
  savedTimestamp     DateTime  @default(dbgenerated("getutcdate()"), map: "DF__MARKET_CO__SAVED__7226EDCC") @map("SAVED_TIMESTAMP") @db.DateTime
  publishedTimestamp DateTime? @map("PUBLISHED_TIMESTAMP") @db.DateTime
  byUser             Int       @map("BY_USER")
  oldComment         String?   @map("OLD_COMMENT") @db.NVarChar(Max)
  sysStartTime       DateTime  @default(dbgenerated("sysutcdatetime()"), map: "SYS_START_TIME_BUNKER_MARKET_COMMENT") @map("SYS_START_TIME")
  sysEndTime         DateTime  @default(dbgenerated("CONVERT([datetime2](0),'9999-12-31 23:59:59')"), map: "SYS_END_TIME_BUNKER_MARKET_COMMENT") @map("SYS_END_TIME")

  @@map("MARKET_COMMENT")
  @@schema("BUNKER")
}



CREATE TABLE [FFA].[MARKET_COMMENT] (
    [ID] INT NOT NULL IDENTITY(1,1),
    [COMMENT] TEXT,
    [PUBLISHED] TINYINT NOT NULL CONSTRAINT [DF__MARKET_CO__PUBLI__4AB81AF0] DEFAULT 0,
    [TIMESTAMP] DATETIME NOT NULL CONSTRAINT [DF__MARKET_CO__TIMES__4BAC3F29] DEFAULT getutcdate(),
    [BY_USER] INT NOT NULL,
    [SYS_START_TIME] DATETIME2 NOT NULL CONSTRAINT [SYS_START_TIME_FFA_MARKET_COMMENT] DEFAULT sysutcdatetime(),
    [SYS_END_TIME] DATETIME2 NOT NULL CONSTRAINT [SYS_END_TIME_FFA_MARKET_COMMENT] DEFAULT CONVERT([datetime2](0),'9999-12-31 23:59:59',(0)),
    CONSTRAINT [PK_MARKET_COMMENT] PRIMARY KEY CLUSTERED ([ID])
);

-- CreateTable
CREATE TABLE [BUNKER].[MARKET_COMMENT] (
    [ID] INT NOT NULL IDENTITY(1,1),
    [COMMENT] VARCHAR(max),
    [PUBLISHED] TINYINT NOT NULL CONSTRAINT [DF__MARKET_CO__PUBLI__731B1205] DEFAULT 0,
    [SAVED_TIMESTAMP] DATETIME NOT NULL CONSTRAINT [DF__MARKET_CO__SAVED__7226EDCC] DEFAULT getutcdate(),
    [PUBLISHED_TIMESTAMP] DATETIME,
    [BY_USER] INT NOT NULL,
    [OLD_COMMENT] NVARCHAR(max),
    [SYS_START_TIME] DATETIME2 NOT NULL CONSTRAINT [SYS_START_TIME_BUNKER_MARKET_COMMENT] DEFAULT sysutcdatetime(),
    [SYS_END_TIME] DATETIME2 NOT NULL CONSTRAINT [SYS_END_TIME_BUNKER_MARKET_COMMENT] DEFAULT CONVERT([datetime2](0),'9999-12-31 23:59:59'),
    CONSTRAINT [PK__MARKET_C__3214EC27C0865E1A] PRIMARY KEY CLUSTERED ([ID])
);

this is the generated code for both

Nurul•4d ago

Hi! Can you share the commands that you ran? Did you run these commands to generate the init migration and apply it?

1. mkdir -p prisma/migrations/init

2. npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/init/migration.sql

3. npx prisma migrate resolve --applied init

1. mkdir -p prisma/migrations/init

2. npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/init/migration.sql

3. npx prisma migrate resolve --applied init

Also, are you on the latest prisma version?

MerlinOP•4d ago

Hey, yes i did it 3 times Is there currently any possibility to ignore tables? Because we are using mssql with the versioning for the data.

Nurul•3d ago

Is there currently any possibility to ignore tables?

Not natively, there is a workaround for now, where you can change the ownership of tables to a specific database user, so that the tables are visible only to a specific user. For example: You want to hide table "Accounts", you can change it to be visible to only database user - "admin", and then in the connection string that you pass to prisma you can pass a different database user "nonadmin", which doesn't see this table and hence will not be to interact with it. So, if you do npx prisma db pull or any migrate commands, it won't see the "Accounts" table because it doesn't have access.

MerlinOP•3d ago

okay thank you

Nurul•5h ago

No worries! 🙂

Gaming

Programming

Drift detected after generation the schema and init migration script on mssql

Did you find this page helpful?