Hey guys, I am really bad with numbers so after I saw your video regarding decimals on YouTube I decided to check back, I am storing lat and lon values inside my database and I wonder how I should define them now, the values are like

52,081318

,

4,324105

etc, currently in my prisma schema I have

lat Float @db.DoublePrecision

is this fine or should I change it?

Is there a paradigm for running Prisma CLI commands locally but against a remote (prod or preview) database? I'm using Vercel for my app but Heroku for my DB. Deploys go to preview first, so I can't tell Vercel to run the

migrate deploy

command for example, since it'll migrate the prod DB while deploying to preview.

I’m trying to understand the migration flow for production. Do I just run

prisma migrate deploy

instead of

migrate dev

and it generally will work out? Or do I have to mark each migration first with

resolve

?

Hi everyone. Does anyone get an error when migrating? sql is generated normally. This is my issue reported in this regard. https://github.com/prisma/prisma/issues/5235 I am trying to use Prisma in my company. Please help me…

hmm just realized that migrations are

schema

specific (postgres), so attempting to auto-generate new schemas to run automated tests on doesnt work out so well

Hi, i am new with prisma so i have a (typescript) project running v2.5.1, so i need to add 2 DateTime columns and want to know if its possible post migration script ?

Hi! i added two DateTime fields as optional and its was created as text type, i am using v2.5.1 is expected result ?

Hi! I tried to create a new migration using a Cloud database and a shadow database, but the soft reset is failing to drop the custom types. It seems that it tries to drop types before dropping the tables using it. Is it a known bug?

Error: Database error: Error querying the database: db error: ERROR: cannot drop type "AdminRoleType" because other objects depend on it
DETAIL: column type of table "AdminRole" depends on type "AdminRoleType"
HINT: Use DROP ... CASCADE to drop the dependent objects too.
   0: sql_migration_connector::sql_database_step_applier::apply_migration
             at migration-engine/connectors/sql-migration-connector/src/sql_database_step_applier.rs:14
   1: sql_migration_connector::best_effort_reset
           with connection=Connection(Postgres(PostgresUrl { url: Url { scheme: "postgresql", host: Some(Domain("<http://bj175749-001.dbaas.ovh.net|bj175749-001.dbaas.ovh.net>")), port: Some(35982), path: "/jda-develop-shadow", query: None, fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: <HIDDEN>, ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: "public", ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(5s), statement_cache_size: 500 } }))
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:104
   2: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:270
   3: sql_migration_connector::sql_database_migration_inferrer::validate_migrations
             at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:70
   4: migration_core::api::DevDiagnostic
             at migration-engine/core/src/api.rs:79

Hi, I'm trying to spin up a local postgres DB with docker-compose, and none of the examples I read work. I realise it might be outside the prisma scope, but hoping someone can help anyway. This is one of the examples I've tried: https://github.com/2color/real-world-grading-app/blob/master/docker-compose.yml In my .env file I have the following:

DATABASE_URL="<postgres://adminer>:testpw@localhost:5432/365manager-core-api"

My docker compose is like this:

version: '3.8'
services:
  postgres:
    image: postgres:10.3
    restart: always
    environment:
      - POSTGRES_USER=adminer
      - POSTGRES_PASSWORD=testpw
    volumes:
      - postgres:/var/lib/postgresql/data
    ports:
      - '5432:5432'
volumes:
  postgres:

I then did

docker-compose up

and the docker DB gets created and seems to be running. I'm don't really know the name of the DB, after some experimenting it seems to default to the name of the root project folder. Any way to specify it, or verify what it is? Finally I tried

prisma migrate dev --preview-feature

which gives the following error:

Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "365manager-core-api", schema "public" at "localhost:5432"

Error: P1001: Can't reach database server at `localhost`:`5432`

Hi team. I am getting the following error following making a small change to the schema and running the migrate.

Error: Schema parsing
error: Error validating: The relation field `defaultAddrs` uses the scalar fields defaultAddr. All those fields are optional. Hence the relation field must 
be optional as well.
  -->  schema.prisma:48
   | 
47 |   player       Player[] // 11
48 |   defaultAddrs Address     @relation(fields: [defaultAddr], references: [id]) // 23
49 | 
   | 

Validation Error Count: 1

The only change in the schema was to set the 'defaultAddr' scalar field to nullable (i.e. I added a '?'). Is this a bug with migrate or am I misunderstanding something.

Hi - wondering how people are handling data migrations as mentioned here

👋 Hello there - is there a good way to debug issues with prisma migrate? I've added a new type into my schema (minimally reproduced below) and then added a relation to an item

model UserItem {
  providerItemId Int
  userId         Int
  listItems.     ListItem[]
  @@id([userId, providerItemId])
}

modal ListItem {
  ...
  userId            Int
  providerItemId    Int
  userItem          UserItem @relation(fields: [userId, providerItemId], references: [userId, providerItemId])
}

When trying to apply a migrate for this, I run into an error of

Error: Database error: Foreign key constraint failed: ListItem_userId_fkey1
   0: migration_core::api::ApplyMigrations
             at migration-engine/core/src/api.rs:75

Any tips on trying to track this one down?

Hello everyone, I have a prisma project in version 2.12 with the old migrate workflow (migrate save, then migrate up) with a few migrations applied I wanted to update prisma to the latest version (2.19) with the new migrate workflow but the migrations folder is not letting me doing so : Error: The migrations folder contains migration files from an older version of Prisma Migrate which is not compatible. What are my options ? Make a backup, delete the migrations folder, update prisma, create a new migration with the new version of prisma migrate (it will reset the data), add the data from the backup ? I only use basic features of the schema.prisma, there is no breaking change Thanks

Hello! I'm trying to use a unique index eg

@@unique([accountId, productId])

in a model. When I run

prisma migrate dev

in the generated migration it creates an index named

Table.accountId_productId_unique

(which makes sense, to avoid naming conflicts). However if I then run

prisma introspect

it updates my schema to

@@unique([accountId, productId], name: "Table.accountId_productId_unique")

, and if I run prisma generate it creates a broken client. Is this intended behavior? Should I not use introspect with migrate?

Hey, we are in the middle of migrating a MySQL database to Postgres, I ran

pgloader

to import the MySQL database in Postgres and ran

npx prisma introspect

against the Postgres database, now I want to move to the Prisma migrate API but when introspecting, I got:

The following models were commented out as they do not have a valid unique identifier or id. This is currently not supported by the Prisma Client.

for all many-to-many middleware tables, is it safe to ignore it? If not, what are the right steps to fix them? Example:

/// The underlying table does not contain a valid unique identifier and can therefore currently not be handled by the Prisma Client.
model campaign_shops {
  campaign_id BigInt?
  shop_id     BigInt?
  campaigns   campaigns? @relation(fields: [campaign_id], references: [id])
  shops       shops?     @relation(fields: [shop_id], references: [id])

  @@index([campaign_id], name: "xxx")
  @@index([shop_id], name: "xxx1")
  @@ignore
}

I'm not sure if I need to change anything when I move to the migrate api

Is it not a common thing to move from introspection to the migrate API? I seem to be stuck in a loop: 1.

npx prisma introspect

model A {
  id         Int      @id @default(autoincrement()) @db.UnsignedInt
  from       DateTime @default(dbgenerated("00:00:00")) @db.Time
}

2.

npx prisma migrate dev

We need to reset the MySQL database "a" at "localhost:3306".
Do you want to continue? All data will be lost. … yes

? Name of migration › init 

Error: Database error
Error querying the database: Server error: `ERROR 42000 (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':00:00,
    `from` TIME NOT NULL DEFAULT 00:00:00,

I tried to do

npx prisma migrate dev --create-only

to edit 000000 to "000000" but when I then run

npx prisma migrate dev

it asks for a new migration name and the new one generates the wrong time as well so it asks for a new name after fixing the quote issue again

Hello! Can anyone tell me if it is possible to run

prisma migrate

without a

.env

file please e.g. by reading from env vars or providing custom config to a

PrismaClient

like you can in the app?

When you have

from  DateTime  @default(dbgenerated("\"00:00:00\"")) @db.Time(0)

using mysql everytime you run

migrate dev

it asks for a new migration name and it keeps generating the same

-- AlterTable
ALTER TABLE `test` MODIFY `from` TIME(0) NOT NULL DEFAULT "00:00:00",
    MODIFY `until` TIME(0) NOT NULL DEFAULT "00:00:00";

Is this a bug or am I missing something? 😅 I created a issue just in case https://github.com/prisma/prisma/issues/6382

Hi, guys. I have a problem during run

npx prisma migrate dev

, the error:

Error: P3006

Migration `20210404180101_init` failed to apply cleanly to a temporary database. 
Error:
Database error
Error querying the database: db error: ERROR: function uuid_generate_v4() does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:278
   1: sql_migration_connector::sql_database_migration_inferrer::validate_migrations
             at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:88
   2: migration_core::api::DevDiagnostic
             at migration-engine/core/src/api.rs:95

The problem is

uuid-ossp

extension is installed, i can see it in my IDE and when run

\x

in psql in terminal. Also, when i run

db push

, everything works fine. It looks like the problem is when prisma tries to create a shadow database, but did not install required extension. I also tried to add additional environment for shadow database as per manual on the prisma website for cloud database instances, but still get the same error. My schema mostly contains such @ids:

model User {
  id               String                @id @default(dbgenerated("uuid_generate_v4()")) @db.Uuid
  // ... other fields
}

// ... other models with similar declaration of primary key

Also, i tried to edit

init

migration file, and write on the top of it

CREATE EXTENSION "uuid-ossp";

, when i run such migration it says that

uuid-ossp

already installed 😕 Did i missed something, or is this a bug?

Hello! I'm looking for advice for executing Prisma migrations through a CI/CD (GitLab) for a node project hosted on Azure. I reckon I need to put the connection string as a CI variable and add a migration step (optimally using a Prisma CLI Docker image). - Are there any Prisma (Migrate) CLI docker images with the latest? - Is it fine executing the migration on every deploy, even if there is no new migrations? - Any other best practices for doing this?

@Nikolai Nergård Regarding migrations, it's always fine to run the migration commands. Prisma keeps a record of what migrations ran and what didn't, so you don't have to worry about it.

Hello! I was wondering if the prisma migrate CLI has an option to allow me to choose the

.env

file to use when specifying my

DATABASE_URL

. For example, my workflow for local development uses a

.env.local

file which has been `.gitignore`'d to ensure no leakage of secrets. So when I make a migration, I'd like to be able to specify

.env.local

instead.

Hi everyone! We have a schema where tables have

uuid

PKs like so:

uuid     String          @id @default(dbgenerated("gen_random_uuid()::TEXT"))

However, no matter how many migrations we run, Prisma Migrate still generates SQL like:

-- AlterTable
ALTER TABLE "User" ALTER COLUMN "uuid" SET DEFAULT gen_random_uuid()::TEXT;

Has anyone else experienced this issue, and how to resolve it?

Hey there, I’m looking for some guidance on using Prisma Migrate in CD. Our service is an ExpressJS server, shipped as a Docker container, using GitHub Actions for CD. Our db url/creds are fetched from a config server before starting the ExpressJS app. So, instead of storing the production URL in 

.env

, we instantiate the PrismaClient after parsing the config-server response, and programmatically override the datasource url as shown in these docs. The issue is that we don’t have the database credentials until the app starts. So we don’t have a datasource url when running 

prisma migrate deploy

 in CD. What is the suggested approach to running migrations in CD when the datasource url is set programmatically in production?

Hi, I'd like to delete/reset only the migrations table in a DB and then baseline it without incurring data loss. Is there a Prisma command to reset the migrations table in a DB without touching the other tables? Or do I need to manually DROP the migrations table and then baseline it?

Can someone help me? I just have a question regarding the proper workflow when doing migrations so that I we can continue to develop and not accidentally impact the production database, and then how to properly deploy changes to the production when development is done. And how do I manage this with multiple devs that might be working on different branches or features that are creating migrations too? We are still new to this and using the migration files. Right now we have the prodcution database and a development database. We recently ran into a situation when we are about to do the migrations from the dev to the production , that the db needs to be reset. And we dont' want that. So I think we're not approaching the workflow properly. We're pretty new at this. Any help would be really appreciated. Thanks

When and how to run prisma migrate deploy? I can't run it from my CI/CD since it doesn't have access to the production Postgres (not part of the VPC and shouldn't be), so when should it be done? And how...?

I am using a migration file to add some indexes. When I apply the migration (with prisma migrate dev), it creates another migration file where it drops the Indexes again… how can I add custom indexes?

Hello everyone. So I have the following schema:

model Account {
  id                  String  @id @default(dbgenerated("my_schema.uuid_generate_v4()")) @db.Uuid
  ...

  @@map(name: "accounts")
}

model Product {
  id          String          @id @default(dbgenerated("my_schema.uuid_generate_v4()")) @db.Uuid
  ...

  @@map(name: "products")
}

Every time I create or apply a migration Prisma generates the alter for the id columns despite the fact that those columns did not change:

-- AlterTable
ALTER TABLE "accounts" ALTER COLUMN "id" SET DEFAULT my_schema.uuid_generate_v4();

-- AlterTable
ALTER TABLE "products" ALTER COLUMN "id" SET DEFAULT my_schema.uuid_generate_v4();

Is this a bug? Am I doing something wrong here? Environment: prisma ^2.24.1 PostgreSQL 13.2 Thanks in advance for your help 🙂

After installing an extension through prisma-migrate, I can find it via

SELECT * FROM pg_extension

but it doesn’t work for my schema/tables.

-- migration.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

It does work if I manually install it through a terminal with admin user What is the right way to install an extension? Should I run migrate with admin privileges? PS: I am using a schema… does that matter?