migrate resolve: The migration could not be found

[bandinopla]

Bug description

Manually creating the folder "migrations" and a sub folder "whatever" and putting a "migration.sql" in it wont allow prisma migrate to resolve it.

How to reproduce

1. have a database with whatever data in it already on your enviroment and call npx prisma db pull to create a schema.
2. call npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > migration.sql
3. manually create a folder inside prisma's folder called "migrations" and another child folder called "initial"
4. drag the migration.sql file into "initial"
5. run npx prisma migrate status to see, prisma knows that folder exists "1 migration found"
6. run npx prisma migrate resolve --applied "initial"
7. get error "The migration initial could not be found"

Expected behavior

especting the "_prisma_migrations" table to be created on the db and populated with the first migration marked as applied.

Prisma information

schema was generated calling npx prisma db pull from the existing database

Environment & setup

• OS: Windows-->
• Database: Apache/2.4.38 (Debian) | 8.0.26 - MySQL Community Server - GPL
• Node.js version: v14.18.3

Prisma Version

Environment variables loaded from .env
prisma                  : 4.2.0
@prisma/client          : 4.2.0
Current platform        : windows
Query Engine (Node-API) : libquery-engine 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Migration Engine        : migration-engine-cli 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine    : introspection-core 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary           : prisma-fmt 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Default Engines Hash    : 2920a97877e12e055c1333079b8d19cee7f33826
Studio                  : 0.469.0

[frankkhiem]

I am having the same problem doing the squashing migration from the guide: How to create a clean history in a production environment.

I have done steps 1 to 3 successfully. However the guide fails at step 4 with an error:

Error: P3017

The migration 000000000000_squashed_migrations could not be found. Please make sure that the migration exists, and that you included the whole name of the directory. (example: "20201207184859_initial_migration")

error Command failed with exit code 1.

I made sure I did everything as in the official guide. I hope to find a solution to this problem here!

[pimeys]

I cannot reproduce this. I started with two clean databases: database_a and database_b. Created a new Prisma project explicitly with version 4.2.0 used in this issue.

root@51a758d136a2:~/test/test-project# npx prisma --version
Environment variables loaded from .env
prisma                  : 4.2.0
@prisma/client          : Not found
Current platform        : debian-openssl-3.0.x
Query Engine (Node-API) : libquery-engine 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Migration Engine        : migration-engine-cli 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules/@prisma/engines/migration-engine-debian-openssl-3.0.x)
Introspection Engine    : introspection-core 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-3.0.x)
Format Binary           : prisma-fmt 2920a97877e12e055c1333079b8d19cee7f33826 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-3.0.x)
Default Engines Hash    : 2920a97877e12e055c1333079b8d19cee7f33826
Studio                  : 0.469.0

Created manually a table in database_a:

create table test (id serial primary key, val int);

Pointed my new test project to database_a and ran npx prisma db pull, which created the following PSL:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model test {
  id  Int  @id @default(autoincrement())
  val Int?
}

Next, I point my test project to database_b that is still empty, creating a new migration file:

root@51a758d136a2:~/test/test-project# npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > migration.sql

root@51a758d136a2:~/test/test-project# cat migration.sql 
-- CreateTable
CREATE TABLE "test" (
    "id" SERIAL NOT NULL,
    "val" INTEGER,

    CONSTRAINT "test_pkey" PRIMARY KEY ("id")
);

root@51a758d136a2:~/test/test-project# mkdir -p prisma/migrations/initial

root@51a758d136a2:~/test/test-project# mv migration.sql prisma/migrations/initial/

Following the reproduction:

root@51a758d136a2:~/test/test-project# npx prisma migrate status
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "new_db", schema "public" at "10.89.4.2:5432"

1 migration found in prisma/migrations

Following migration have not yet been applied:
initial

To apply migrations in development run prisma migrate dev.
To apply migrations in production run prisma migrate deploy.
The current database is not managed by Prisma Migrate.

If you want to keep the current database structure and data and create new migrations, baseline this database with the migration "initial":
prisma migrate resolve --applied "initial"

Read more about how to baseline an existing production database:
https://pris.ly/d/migrate-baseline

We have one migration, we can see that. Trying to resolve it:

root@51a758d136a2:~/test/test-project# npx prisma migrate resolve --applied "initial"
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "new_db", schema "public" at "10.89.4.2:5432"
Migration initial marked as applied.

The database has _prisma_migrations table and the migration called initial is marked as applied. What did I do differently here? Tests were run in a clean docker image, based on Ubuntu 22.04. Database is Postgres 15.

[leonambeez]

I am having the same issue. Followed the instructions in the docs for migration of a mysql database, but it returns "The migration init could not be found. Please make sure that the migration exists, and that you included the whole name of the directory.".

I followed the instructions on this page: https://www.prisma.io/docs/getting-started/setup-prisma/add-to-existing-project/relational-databases/baseline-your-database-node-mysql

[janpio]

That is buggy and is being fixed right now @leonambeez . Use 0_init instead as the name for the baseline migration and it will work. Sorry for the confusion caused.

[leonambeez]

I tried again and it still returns the same error. To be sure, I created a new project and follow the tutorial step by step, creating a new database with the SQL schema provided in the tutorial.

[janpio]

Even when using 0_init instead of init as the name of the folder and migration? What error exactly did you get, when running which command?

[LCD344]

Even when using 0_init instead of init as the name of the folder and migration? What error exactly did you get, when running which command?

It is happening with 0_init for me

Command

npx prisma migrate resolve --applied 0_init

Error:

The migration 0_init could not be found. Please make sure that the migration exists, and that you included the whole name of the directory. (example: "20201207184859_initial_migration")

as you can see in the screenshot - it is there

I am following this guide: https://www.prisma.io/docs/getting-started/setup-prisma/add-to-existing-project/relational-databases/baseline-your-database-typescript-postgres - with mysql and js thought

[LyghtCode]

I have the same issue following guide exactly. My team is inheriting a database from a client and building a new front-end with prisma. Everything has worked well until this point. i tried changing name from 0_init to other names and it returns the same error of it not being able to find the migration directory specified.

[LyghtCode]

postgres database btw

[bandinopla]

if anyone find it useful, i did a pull request a few months ago "fixing" this issue: https://github.com/prisma/prisma/pull/14819

[janpio]

Hey everyone here, we have a related issue that could be relevant for some of you: Are you on Windows and using PowerShell (e.g. via VS Code or IntelliJ) to create the migration file via migrate diff, and then it fails on migrate resolve --applied? Then this could be another case of this problem here: https://github.com/prisma/prisma/issues/17558#issuecomment-1410856229

[Benji377]

I just had the exact same problem as the original author of the issue and I also tried all the suggestions above but nothing worked. I can however confirm that the last suggestion from @janpio worked for me. Likewise, I just opened the migration.sql file in Notepad++ and changed the Encoding to UTF-8. After that, running npx prisma migrate resolve --applied initial worked just fine.

[ruanswanepoel]

If you're using VsCode and your 0_init/migration.sql is in the wrong encoding you can:

• Open the file 0_init/migration.sql
• Press Ctrl+Shift+p
• Type "Change File Encoding"
• Select "Save With Encoding"
• Select "UTF-8"

[armendane]

@ruanswanepoel This resolve to me! I really appreciate! Thanks!!!!!

[baizhi958216]

If you're using VsCode and your 0_init/migration.sql is in the wrong encoding you can:

• Open the file 0_init/migration.sql
• Press Ctrl+Shift+p
• Type "Change File Encoding"
• Select "Save With Encoding"
• Select "UTF-8"

It works !

[ArslanSaleem]

I tried had the same error tried the rebase the migration but still getting this error although the schema complete synced with the database:

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.

[MrSharpp]

ruanswanepoel

This solution fixed my issue, but i wonder why did it happened at first place? why .sql file was created in utf-8 format?

[janpio]

On Windows, using Powershell (which is the default in VS Code) using > foo.sql to pipe the output of a command (like for example migrate diff) creates a UTF-16 LE file instead of the expected UTF-8. In the latest release of Prisma CLI we added an option --output that can take a file name so you migrate diff ... --output foo.sql instead of using migrate diff ... > foo.sql. That should avoid the problem.

[AlbeRCLife]

Just adding to this... I saw nowhere in the documentation that the migrations folder must be within the path of the schema file (I saw it in prisma's code here)

So if as in my case you are trying for example to keep an structure of multiple schemas in one "schemas" folder and then have the "migrations" folder at the same level of "schemas"... that's not currently possible as even with UTF-8 or using WSL to create the diff you'll always get the "The migration 0_init could not be found. Please make sure that the migration exists, and that you included the whole name of the directory." error.

So going from this:

• schemas
  • whatever.prisma
• migrations
  • 0_init/migration.sql

To this:

• schemas
  • whatever.prisma
  • migrations
  • 0_init/migration.sql

Worked for me.

[akaigao]

If you're using VsCode and your 0_init/migration.sql is in the wrong encoding you can:

• Open the file 0_init/migration.sql
• Press Ctrl+Shift+p
• Type "Change File Encoding"
• Select "Save With Encoding"
• Select "UTF-8"

it's worked for me. thanks!

[electrovir]

I ran into this issue on a failed migration.

1. prisma migrate resolve --applied <name>: fail
2. prisma migrate resolve --rolled-back <name>: success
3. prisma migrate resolve --applied <name>: still fail

Maybe I'm confused as to when migrate resolve --applied is supposed to be used, but this is really strange imo that --rolled-back worked but --applied doesn't.

Edit

I figured it out. The machine I was running commands on simply needed a git pull 😁