https://www.prisma.io/ logo
Join Slack
Powered by
Hello. I was doing the migration path from 2.x to ...
# prisma-migrate
m
Hello. I was doing the migration path from 2.x to 3.x. With the Named Constraits upgrade I took the second option (I want to use Prisma's default constraint and index names). So I did 
npx prisma migrate dev
on my development branch and db. When doing the 
npx prisma migrate deploy
against staging environemtn it errored out on this part
Copy code
Database error:
ERROR: relation "User.email_unique" does not exist
So exactly on this sql statement:
Copy code
-- RenameIndex
ALTER INDEX "User.email_unique" RENAME TO "User_email_key";
From schema.prisma:
Copy code
model User {
  //deleted unnecessary stuff
  email            String       @unique
}
So only then I did notice the text at the bottom (Dealing with cases where more than one database environment is used for the same application). 🤕 So I have pulled schemas from other database environments and I have a problem, because they do differ indeed. In example on production the index does not have the name 
User.email_unique
, but:
Copy code
model User {

  email            String       @unique(map: "booking-production$prod.User.email._UNIQUE")

}
It has also the postgres schema name prepended (!). So what should I do now? I would also very much like to use the default Prisma 3 naming convention for all environments (
User_email_key
)?
r
@Maciek K 👋 This is a complicated one! I would suggest holding off the migration for this at the moment and connect with @User for a better way to do this 🙂
m
Thank you, will do 🙏
🙌 1
Sorry, it seems I can't connect with Tom, I don't understand Slack sometimes. I assume I should just wait for Tom to chime in?
t
Hey, sorry!
So the migrations team is not working tomorrow (team learning day), and after that it's the weekend, but I'll try to help as much as I can in the meantime. It's tough to resolve this with the public API we have for migrate right now, but we have the tools that could help you.
What kind of machine are you running on?
m
Ubuntu 20.04 (dev laptop and remote production on Digital Ocean)
t
Great! We don't have the tools in the current migrate to fix that automatically. Would you be open to trying an experimental command? The expected outcome is that you have a 
.sql
script that would sync constraint names between your prisma schema and your staging db. If you are open to that, I can either send you a compiled binary or you can compile from source (if you have a rust toolchain installed).
m
Hit me with the compiled one 🙂
t
I'm on ubuntu 20.4 too so it should Just Work™. I'm now noticing we're not exposing the option we would need, fixing that right now and coming back to you.
(I can make a smaller binary if that's too large)
Ok here it is
The steps to follow would be: • Download the 
test-cli
binary I just sent • Point your 
prisma.schema
to your staging database (either string literal or env var in your 
datasource
block) • Make sure the 
prisma.schema
is the version you actually want. • Run 
./test-cli migrate-diff prisma/schema.prisma --output-type=ddl
— this command does not write anything to the database, it just reads, so it's safe. You can also remove the 
--output-type=ddl
to have a more readable diff summary. The output should be the SQL script you need. This is only a very very rough version of the command we want, but the actual machinery is there and well tested. The full command would let you diff between two schemas from any source (a database, a prisma schema, a migrations directory) and produce the migration migrate would use to sync them. It should be super useful for debugging, recovering from failed migrations, etc.
m
Ok I have donwloaded, will procede in couple of minutes, I need to take a deep breath 🙂 . Btw that index is the only one with the problem. Also should this error be taken care of beforehand (because the migrattion didn't apply, there's a lock?)?:
Copy code
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.
Migration name: 20210914183711_prisma_3_upgrade
Copy code
Database error code: 42P01
Copy code
Database error:
ERROR: relation "User.email_unique" does not exist```
Or should I just proceed to the cli straight away
t
Ah yes 😕 I think migrate currently has a very poor story for recovering from failed migrations. 
migrate diff
is going to be part of the solution. In your case, once you have generated the script, reviewed that it makes sense, and applied it, you can run 
prisma migrate resolve --applied '20210914183711_prisma_3_upgrade'
on your staging database, and it should be back on track.
m
FYI env var in datasource doesnt work:
Copy code
17:59 $ ./test-cli migrate-diff prisma/schema.prisma --output-type=ddl
reading the prisma schema from prisma/schema.prisma
Error: error: Environment variable not found: DATABASE_URL.
  -->  schema.prisma:7
   | 
 6 |   provider = "postgresql"
 7 |   url      = env("DATABASE_URL")
   |
Adding whole db string to datasource works
Wow it works, the output looks great, the same as migration but with fixed index name:
Copy code
-- RenameIndex
ALTER INDEX "booking-production$prod.User.email._UNIQUE" RENAME TO "User_email_key";
Thank you very much, I guess I'll proceed with this to production also
t
Glad it worked! Yes the env var thing is probably a bug, this is a very very rough prototype. Please ask for as much help as you need in this thread, I am really curious to know how it goes for you, because I'd like to make this a proper migrate feature.
m
After doing 
migrate resolve --applied
this is how it looks in db. Is this correct with the 
applied_steps_count
at 0? Running 
prisma migrate deploy
after this gives no errors. 
No pending migrations to apply.
t
Yep, it looks like it worked 🎉
🙏 1
🦜 1
m
Ok I have thought that 
applied_steps_count
should not be 0. Anyway I'm reporting that I have succesfully applied all migrations with this tool all up to production with no issues. Smooth as butter. Thank you again, classy as always 🙏 🔥 prisma rainbow
🦜 1
🙏 1
t
Ok I have thought that 
applied_steps_count
should not be 0.
Yes, that's a bit misleading, but it's totally fine. Migrate writes to 
applied_steps_count
, but in practice we are not using it.
f
hey @Maciek K - we have a proposal out for a new diffing tool so we dont need to send you binaries anymore :) Please take a look and let us know what you think: https://github.com/prisma/prisma/issues/10561
m
Yes, I'm aware 🙂 You guys rock 🙏prisma rainbow
Open in Slack
PreviousNext
Powered by