I've got a dumb question: in a

prisma migrate

data migration in MySQL, how do I generate a "Prisma" ID - you know, one that looks like

cl30ffuzq04558gfz8s0gi65y

? Is there a utility in MySQL, or maybe a stored-procedure I can create that generates compatible values to those Prisma generates?

Hello, I am having an issue with the following schema:

model User {
  id                 String               @id(map: "PK_cace4a159ff9f2512dd42373760") @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  username           String               @unique(map: "UQ_78a916df40e02a9deb1c4b75edb") @db.VarChar
  email              String?              @unique(map: "UQ_e12875dfb3b1d92d7d7c5377e22") @db.VarChar
  password           String               @db.VarChar

  sheet Sheet[]

  @@index([username], map: "username-idx")
  @@map("user")
}

model Sheet {
  id          String     @id() @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  author      User       @relation(fields: [authorId], references: [id])
  authorId    String     @db.Uuid
  name        String     @unique() @db.VarChar(512)
  slug        String     @unique() @db.VarChar(512)
}

When running

npx prisma migrate dev --name sheets

I get:

Migration `20220523212222_sheets` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: foreign key constraint "Sheet_authorId_fkey" cannot be implemented
DETAIL: Key columns "authorId" and "id" are of incompatible types: text and uuid.

But I thought this line in the Sheet model was correct?

authorId    String     @db.Uuid

Hi, i wonder if i can have a seed that updated a field if it is empty and fill it with a concatenation of 2 other fields in the same row. Maybe someone can give ma an advice?

any recommendations on how to use prisma to backfill data? a typical scenario we have is that we add a column to a table. All the existing records need to be updated to add a value based on some logic. How can I use prisma to insert those values to the new column on existing records?

Hey i have litle problem with foreignkeys in migration. Every time i run comand "npx prisma migrate dev --name" i get 4 keys removed and add those are allmost the same keys and i use them in same way in other tables but only those 2 causing problem

ColumnName      Table1 @relation(fields: [ColumnNameId], references: [Id], name: "FirstParameterExactNameSpecifyInTable1")
  ColumnName2     Table1 @relation(fields: [ColumnName2Id], references: [Id], name: "SecondParameterExactNameSpecifyInTable1")

Table1 Receiver lets say

FirstParameterExactNameSpecifyInTable1 Organization[]                             @relation("FirstParameterExactNameSpecifyInTable1")
SecondParameterExactNameSpecifyInTable1 Organization[]                             @relation("SecondParameterExactNameSpecifyInTable1")

Meybe some of you could point me direction where could i find solution?

ok i found the solution problem was in hand written migration which was nesesery to push data(infinity reference) Problem was that prisma didn't know what it shoud be onDelete and onUpdate and i had to specify + in 1 place had written that different option than in all others

trying to follow steps in down migration. after applying the down migration sql I try to mark the migration as rolled back but I get a

cannot be rolled back because it is not in a failed state.

error. What are the right steps to mark it as rolled back in _prisma_migration table._

just got a quick question about the migration engine: how are out-of-order migrations handled? e.g. I create and apply a migration to my dev environment, then before committing it, a colleague creates and pushes a different migration which is then ran through our CI/CD pipeline. Now the production database will be missing my migration if I try to push it! Will this cause

prisma migrate deploy

to fail?

Hello all, I’m close to implement the workaround to speed up migrations here: https://github.com/prisma/prisma/issues/7933 before I do so, maybe there’s something I’m missing and there’s already a way to have them run fast.. Let me know I’m highly interested into that matter..

Hi, is there any feature to use migrations with data? I searched and didn't find anything focusing on this problem

Hi. Is there a way to run the migrations via the client? I'm currently refactoring my express Server with Sequelize ORM to Prisma. With Sequelize I was running the migrations on the Server startup, as the process got integrated in the code. But as far as I have seen with Prisma the only option to run migrations is by the CLI.

I am facing an issue in prisma https://stackoverflow.com/questions/72828692/please-make-sure-your-database-server-is-running-at-localhost5432-prisma please help me to fix this issue

Hi everyone! I have a backend repo with Prisma in it, with a bunch of schema migrations inside a

migrations/

folder. I also have the backend running in production and it seems like somewhere along the line, the production Prisma DB migrations diverged from the one in the github repository. The production migrations seem to contain a migration that is not in the version controlled

migrations/

folder. It looks like the actual DB schemas are sync, but the migrations arent. Is there a way to sync them? Thanks

I have a project I'm working on and Supabase is the DB and I'm using Prisma Client with it. Everything in dev is functioning as it should. I'm getting my data back from my supabase DB. Now, when trying to deploy it on both Vercel and Netlify, everything is building fine with no errors but I am getting a 500-Failed to load data error when loading the pages consuming the data. I read the supabase docs on adding a shadow DB connection, so I did this and my migration and build was successful and still everything is functioning properly. I updated my ENV vars with the shadow DB and deployed. All deployed without errors except I'm still getting the 500-Failed to load data error. Any insight would be appreciated! Currently I have figured out that it works fine on the first migration, then it seems to change something in supabase because after further migrations, the data becomes unavailable. I saw a couple posts that talk about this. So everything works as expected in development, connected to Supabase...once I deploy it to lets say Vercel or Netlify, Data becomes unavailable. I've even added a shadow DB that Supabase and Vercel recommend. Has anyone had this issue before? Would appreciate any help I could get. Thanks!!!

Is there a cleaner/more straightforward way to undo a bad migration in a dev/test environment? I had a migration that was committed to a dev branch deployed to a test system. The migration failed, so I simply deleted the

migration.sql

file, and deleted the record in

_prisma_migrations

and tried to re-deploy but it didn’t work. My flow now is to restore the db to the point before I deployed the broken migration, but it would be nice if there was a easier way to simply remove a migration and not do it before it goes to a higher level environment

Hey, is there a way to run migrations without installing Node?

We use Prisma purely for the migration tools, but our stack is Golang. Currently we can't easily switch to using a scratch image (empty OS without any of the tools) because we need to install Node.js to execute

prisma migrate deploy

before the app starts up - is there a way to just grab the Rust binary directly and execute that?

is there an easy way I can just run the migrations for a (simple) postgres database on a local sqlite instance for integration testing?

I know there's the

datasource

bit in the schema, but we can't have multiple of these currently and can't adjust the provider to be

sqlite

just for when tests run

any way to force

prisma migrate

to use

UNIQUE NULLS NOT DISTINCT

instead of

UNIQUE

with postgres? i need to support an existing pattern in use in our company for soft deletes by way of a "date deleted" column. the following data would be considered valid with a unique constraint on both "email" and "deleted" ... a "valid" or "undeleted" object would NOT have a value set for the

deleted

column thereby allowing the email address to be reused

{
  id: 2,
  email: "<mailto:joe.blow@nowhere.com|joe.blow@nowhere.com>"
  created: "2022-08-11T21:12:36+00:00",
},
{
  id: 2,
  email: "<mailto:joe.blow@nowhere.com|joe.blow@nowhere.com>"
  created: "2011-1-11T11:11:11+00:00",  
  deleted: "2022-2-22T22:22:22+00:00",
}

link to a similar question on SO: https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns

Hey is there a way I can run my migrations to a specific point? so like

prisma migrate reset

but I want it to stop one migration before the most recent just so I can test my custom migration SQL with real data

without checking out an older commit etc

okay, scratch that, new question:

The migration

20220204125655_rename_fields_add_new

was modified after it was applied.

This migration is from aaaages ago (February) so this is surprising, it comes up every time I try to migrate, I'd rather not clear my database just because of this, how can I resolve this?

Hi guys, I've got a hopefully not unique situation here (multi-tenant architecture), I'd appreciate any inputs/suggestions 🙂 I have developed an API running on nodejs. that is split into different packages, where each package has its own

schema.prisma

e.g.

- project_folder
-- migrate.js
-- src
---- index.ts
---- packages
------- package_1
---------- index.ts
---------- prisma
------------- client
------------- migrations
------------- schema.prisma
------- package_2
---------- index.ts
---------- prisma
------------- client
------------- migrations
------------- schema.prisma

package_1 > prisma > schema.prisma

looks like:

generator client {
    provider        = "prisma-client-js"
    previewFeatures = ["interactiveTransactions"]
    output          = "./client"
}

datasource db {
    provider = "mysql"
    url      = env("ORGANISATION_CONTEXT_DATABASE_URL")
}

ORGANISATION_CONTEXT_DATABASE_URL

is always the same for a given env (dev, prod, etc...)

package_2 > prisma > schema.prisma

looks like:

generator client {
    provider        = "prisma-client-js"
    previewFeatures = ["interactiveTransactions"]
    output          = "./client"
}

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

DATABASE_URL

changes depending the user making a request. Everything locally works just fine. The issue I have is at the moment of applying migrations when deploying through github actions. At the moment I'm testing the following. Whenever everything has been deployed run the command:

ci.yml

- name: "Run migrations"
     run: yarn migrate

package.json

{
  "scripts": {
    "migrate": "node migrate.js"
  }
}

migrate.js

process.env.ORGANISATION_CONTEXT_DATABASE_URL = 'postgressql://....'

const organisationContextSchema='./src/packages/package_1/prisma/prisma.schema'
const cmd1 = spawnSync('npx', ['prisma', 'generate', `--schema=${organisationContextSchema}`]);

const cmd2 = spawnSync('npx', ['prisma', 'migrate', 'deploy', `--schema=${organisationContextSchema}`]);

// HERE IS WHERE PROBLEMS HAPPEN!!!! :(
const orgContDbClient = new PrismaClient({
    datasources: { db: { url: process.env.ORGANISATION_CONTEXT_DATABASE_URL } },
});

console.log('connected! Fetching orgs...');
const dbOrgs = await orgContDbClient.organisation.findMany();

console.log('dbOrgs: ', dbOrgs);

Error:

Error: @prisma/client did not initialize yet. Please run "prisma generate" and try to import it again.

😞

Yo, is there a way to ignore an existing table entirely? Use-case: we've merged two tables into one, the old table is still there for data science reasons (before we delete it entirely, there's some data in there we want to play with) but I want to remove this table from our

schema.prisma

file without it triggering a migration to drop the table. Is this possible? I tried this:

model companies {
  // deleted every column here and set to ignore
  @@ignore
}

but when I run

prisma migrate dev

it wants to drop these columns still, I'd rather just hide this table from prisma entirely so it doesn't want to control it any more.

what is the checksum column in the __prisma_migrations_ table ? it seems to be a deterministic id based on the contents of the migration but want to confirm that

I was wondering if anyone knew how to create a unique constraint on a key in a jsonb column in the prisma.schema file, I know the raw sql to do it but I want to do it via the schema file

I’m comparing the output of a schema created with prisma using introspection to the original schema in PostgreSQL, and something with unique constraints stuck out to me. When you create a table using

UNIQUE

in postgres, it creates a unique constraint. With Prisma and

@@unique

, it just creates a unique index. For example

model AnotherUser {
  id        Int     @id @default(autoincrement())
  firstName String?
  lastName  String?

  @@unique([firstName, lastName])
}

Generates the following migration

-- CreateTable
CREATE TABLE "AnotherUser" (
    "id" SERIAL NOT NULL,
    "firstName" TEXT,
    "lastName" TEXT,

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

-- CreateIndex
CREATE UNIQUE INDEX "AnotherUser_firstName_lastName_key" ON "AnotherUser"("firstName", "lastName");

And the table description lists it as simply UNIQUE

"AnotherUser_firstName_lastName_key" UNIQUE, btree ("firstName", "lastName")

And I don’t see a constraint in

pg_constraint

. BUT if I create the same table with

UNIQUE

CREATE TABLE "AnotherUser" (
    "id" SERIAL NOT NULL,
    "firstName" TEXT,
    "lastName" TEXT,

    CONSTRAINT "AnotherUser_pkey" PRIMARY KEY ("id"),
    UNIQUE("firstName", "lastName")
);

The table description lists the unique constraint

"AnotherUser_firstName_lastName_key" UNIQUE CONSTRAINT, btree ("firstName", "lastName")

And I can see the constraint in

pg_constraint

Does anyone know why Prisma Migrate is only creating unique indexes and not unique constraints (which are backed by a unique index)?

👋 Hello everyone! To streamline support for our Community, all questions about the Prisma ORM (Client, Studio, Migrate) will now be answered in #orm-help channel, and this channel will be archived next week is now archived. We hope that this will allow us to help you more quickly and share the knowledge among all our community members. 😊 If you have any pending questions / issues, feel free to resolve them in the current threads or move the conversation into #orm-help! 🧑‍💻 Happy coding!

This message was deleted.