Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Using Prisma with a PlanetScale database #7292

Closed
janpio opened this issue May 27, 2021 · 68 comments
Closed

Using Prisma with a PlanetScale database #7292

janpio opened this issue May 27, 2021 · 68 comments

Comments

@janpio
Copy link
Member

janpio commented May 27, 2021

UPDATE

As of 27.10.2022, the details in this issue are outdated.

Refer to https://www.prisma.io/docs/guides/database/using-prisma-with-planetscale for the latest instructions on using Prisma with PlanetScale.

Outdated Issue content

PlanetScale is a new and interesting cloud database based on MySQL. It's exciting!

PlanetScale is a bit different than other MySQL database providers though:

  1. No support for foreign keys. This is challenging for Prisma users, as Prisma by default uses foreign keys to express relations.
  2. No creation of additional databases using CREATE DATABASE but need to use their tooling (web UI or CLI). This can be challenging, as some development commands of Prisma like prisma migrate dev create temporary databases in the background.
  3. No schema changes on your production database (but make them on branches, then merge the schema changes back). This is challenging for Prisma users, as usually they run Migrations against their database directly via prisma migrate deploy or prisma db push.

Here is how you can use Prisma and PlanetScale together anyway:

  1. No foreign keys / No Prisma Migrate (migrate dev and db push)

    • Problem: PlanetScale does not allow foreign keys in the database schema (ERROR 70100 (1317): foreign key constraints are not allowed, see https://code.openark.org/blog/mysql/the-problem-with-mysql-foreign-key-constraints-in-online-schema-changes), which Prisma relies on for relations.
    • Starting with 2.27.0 Prisma catches the error and outputs a helpful error message (that currently redirects here)
    • Workaround starting with 3.1.1: Use the preview feature referentialIntegrity and the datasource propertly referentialIntegrity = "prisma" to enable a mode that automatically leaves out foreign keys in migrations:
      generator client {
        provider        = "prisma-client-js"
        previewFeatures = ["referentialIntegrity"]
      }
      
      datasource db {
        provider             = "mysql"
        url                  = env("DATABASE_URL")
        shadowDatabaseUrl    = env("SHADOW_DATABASE_URL")
        referentialIntegrity = "prisma"
      }
      
      ...
      
      You can now properly use prisma migrate dev and prisma db push
      (Between 2.24.0 and 3.1.1 the setting was called planetScaleMode = true and hidden behind the preview feature flag planetScaleMode)
  2. If you are using migrate dev to migrate: No CREATE DATABASE / No automatic shadow database creation

    • Problem: PlanetScale does not allow creating new databases with CREATE DATABASE, which Prisma Migrate prefers to use for the shadow database of Prisma Migrate.
    • Solution: Create a branch shadow or similar and open put its connection string as shadowDatabase of your datasource in schema.prisma
      datasource db {
        provider             = "mysql"
        url                  = env("DATABASE_URL")
        shadowDatabaseUrl    = env("SHADOW_DATABASE_URL")
        referentialIntegrity = "prisma"
      }
      
    • Potential improvement: Catch error thrown if you try anyway and output better error message with link to documentation.
  3. If you are using migrate dev to migrate: Prisma migration table is not copied to main by default

    • PlanetScale now has a setting "Automatic schema migrations" that enables this behavior:
      image
      As you can see Prisma is one of the default options, and enabling this option and choosing Prisma makes sure that the content of the migration table _prisma_migrations is copied from the branch to main with the deploy request. 🥳
  4. No schema changes on production branches

    • Problem: PlanetScale does not allow schema changes on the production branch of the database (ERROR HY000 (1105): direct DDL is disabled), which Prisma Migrate tries to do if you tell it to.
    • Starting with 2.27.0 Prisma catches the error and outputs a helpful error message (that currently redirects here)
    • Solution: Only execute Prisma Migrate on non production branches, then use a PlanetScale "deploy request" to merge the schema changes to your main

We are very excited for you to try PlanetScale with Prisma. If you hit any bumps, let us know either here or in new issues or discussions in our repository. Thanks!

@janpio janpio added kind/feature A request for a new feature. topic: database labels May 27, 2021
@janpio janpio changed the title Support for PlanetScale Improve support for PlanetScale May 27, 2021
@heymartinadams
Copy link

Holy sh*t @janpio — that’s awesome of you to put this together! Thank you! 💚

We’ll stick with AWS for now until PlanetScale is a bit more out of beta and has enabled serverless deployment connections.

@janpio
Copy link
Member Author

janpio commented May 29, 2021

I updated the original post:

  • More concrete instructions on how do work get the shadow database working (which works great!)
  • Added a "Potential improvement" point to all problems, to show and communicate what we are thinking about right now

@janpio janpio self-assigned this May 31, 2021
@janpio
Copy link
Member Author

janpio commented May 31, 2021

I updated the original post:

  • Improved formatting and added error message for google-ability
  • Added links to the two "Potential improvements" we have planned on our side
  • Mentioned our brand new workaround planetScaleMode = true for the "No foreign keys" problem in Prisma Migrate (added in 2.24.0)

@bigint

This comment has been minimized.

@janpio
Copy link
Member Author

janpio commented Jul 10, 2021

I updated the original post:

  • Link to Provide a connection string out of the box planetscale/discussion#15 for the "No connection URL" and "No serverless connection" secttions as PlanetScale has communicated they are working on connection string support which will solve both these problems ⛰️
  • Mention for the both limitations errors (direct DDL is disabled, foreign key constraints are not alloweed) that Prisma will provide a helpful error message about these starting in 2.27.0.
  • Mention for "No support for Prisma migration table" that PlanetScale and Prisma are finishing implementation of support for this.

@janpio
Copy link
Member Author

janpio commented Jul 27, 2021

I just updated the post:

  • with the brand new Automatic schema migrations feature PlanetScale launched today, that includes support for Prisma's _prisma_migrations table that holds the migration meta information. With that it is now much easier to use Prisma Migrate on branches, and then use PlanetScale's deploy requests to merge the schema changes back to the main branch. Nice - thanks @planetscale 💚

@janpio
Copy link
Member Author

janpio commented Jul 29, 2021

I just updated the post:

  • Mention the use of --execute-protocol 'mysql' in both 1) and 4) to make the pscale CLI to generate an env var with the mysql:// protocol vs. the default mysql2:// one that Prisma does not support.
    • Pssst, you can also add e.g. --execute-env-url 'PLANETSCALE_DATABASE_URL' if you want it to put the value into another env var than DATABASE_URL.

@heymartinadams

This comment has been minimized.

@janpio

This comment has been minimized.

@bigint
Copy link

bigint commented Aug 17, 2021

This is finally available 🎉

https://twitter.com/planetscaledata/status/1427371097352069120

image

@janpio
Copy link
Member Author

janpio commented Aug 17, 2021

Testing the details of this right now and will update the message above soon. Looking good!

@heymartinadams

This comment has been minimized.

@bigint

This comment has been minimized.

@heymartinadams

This comment has been minimized.

@thebiglabasky

This comment has been minimized.

@bigint
Copy link

bigint commented Aug 18, 2021

Are foreign keys bad in general for scalable apps? 🤔

@pantharshit00
Copy link
Contributor

Read this classic response from GitHub's Ghost team on this: github/gh-ost#331 (comment)

Basically foreign keys makes sharding hard and sharding is a core part of planetscale which is based on the vitess sharding manager.

@jde
Copy link

jde commented Dec 21, 2021

Hey all. I've been using prisma with planetscale for a week now and am hitting what I believe to be either a bug or an incompatibility with standard MySQL in the planetscale db. It may also be a bug in the way prisma writes a specific relation query.

When updating, prisma creates many queries based on the relationships in the schema. Here's the relevant parts of the schema I'm using:

model Trip {
    id          Int                 @id @default(autoincrement())

    organizer   User                @relation(fields: [organizerId], references: [id])
    organizerId String
    services    ServicesOnTrips[]

    @@map("rd_trip")
}

model Service {
    id          Int         @id @default(autoincrement())
...
}
model ServicesOnTrips {
    trip      Trip    @relation(fields: [tripId], references: [id])
    tripId    Int
    service   Service @relation(fields: [serviceId], references: [id])
    serviceId Int

   @@map("rd_services_on_trips")
}

model User {
  id            String    @id @default(cuid())
  contact       String    @default("SMS and Email")

  trips         Trip[]

  @@map("auth_user")
}

As you can see, Trips have an organizer who's a user. They also are related to services through the many to many mapping model ServicesOnTrips. Pretty basic relationships.

When I try to update a user, hoewever, I'm getting an error:

    await prisma.user.update({
        where: {
            id: s.user.id,
        },
        data: {
            contact: contact
        }
    })

outputs:
PrismaClientUnknownRequestError: 
Invalid `prisma.user.update()` invocation:

  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1105, message: "symbol rolldeep.rd_trip.id not found", state: "HY000" })) })
    at cb (/Users/jde/dev/github/jde/rolldeep/node_modules/@prisma/client/runtime/index.js:38687:17)

Note that the error is symbol rolldeep.rd_trip.id not found.

I can confirm that in the database rolldeep, table rd_trip, there indeed is an id field.

After outputting the query log from prisma, I can see that this symbol is being used in two different queries:

Query: SELECT `rolldeep`.`rd_trip`.`id`, `rolldeep`.`rd_services_on_trips`.`serviceId` FROM `rolldeep`.`rd_services_on_trips` WHERE 1=0

and

SELECT `rolldeep`.`rd_trip`.`id`, `rolldeep`.`rd_trip`.`organizerId` FROM `rolldeep`.`rd_trip` WHERE (1=1 AND `rolldeep`.`rd_trip`.`organizerId` IN (?))

These relations aren't relevant to my update statement.

Is this an issue with prisma writing malformed sql? Or is this a planetscale specific issue?

At least one other user is experiencing this: #10755

$ npx prisma -v
Environment variables loaded from .env
prisma                  : 3.6.0
@prisma/client          : 3.6.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt dc520b92b1ebb2d28dc3161f9f82e875bd35d727 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : dc520b92b1ebb2d28dc3161f9f82e875bd35d727
Studio                  : 0.440.0
Preview Features        : referentialIntegrity

@janpio
Copy link
Member Author

janpio commented Dec 22, 2021

I think that sounds similar to a few of the other issues we recently got about referentialIntegrity and PlanetScale, check some out at https://github.com/prisma/prisma/issues?q=is%3Aopen+label%3A%22topic%3A+database-provider%2Fplanetscale%22+sort%3Aupdated-desc. In any case, please move your comment into its own issue @jde so we can investigate properly. Thanks.

Update: I think your comment is another instance of what is being tracked in #10758 @jde

@janpio
Copy link
Member Author

janpio commented Dec 23, 2021

Lesson: prisma will not recognize differences in migrations caused by these kinds of config changes. You'll have to delete the effected migrations and recreate them.

@jde I created an issue about this so we can try to improve the situation: #10843 Not highest priority of course, but sooner or later.

@janpio
Copy link
Member Author

janpio commented Dec 23, 2021

I updated the original post:

  • PlanetScale made a change that newly created databases' main branch initially is not marked as the production branch, which means you can make schema changes to it by default. This removes the necessity to first create a branch, then use db push to make the schema changes and then merging that back via a deploy request. I moved the mention of deploy requests etc to the bottom of the list of things to know.

The one thing I did not fully incorporate in this post (and will not) is that we generally recommend using db push only for Migrations on Planetscale (instead of usually migrate dev and migrate deploy). That is because PlanetScale has its own representations of schema changes with the deploy request -and also frees you from the need to have a shadow database to detect drift and so on.

@jde
Copy link

jde commented Dec 26, 2021

Thanks for upgrading this to an issue @janpio!

@focux
Copy link

focux commented Dec 27, 2021

@janpio I'm a bit curious about why you didn't incorporate the recommendation about preferring the use of db push over migrate dev/deploy on the original post? Is it because you aren't still sure about it?

@janpio
Copy link
Member Author

janpio commented Dec 27, 2021

Lack of time and me not being a documentation writer.
db push is the right thing to use with PlanetScale.

@jonahallibone

This comment has been minimized.

@2color
Copy link
Contributor

2color commented Jan 11, 2022

Hey folks,
We just published a video on Prisma & PlanetScale best practices. It covers a lot of the knowledge encapsulated in this issue:

Prisma Planetscale Best Practices Guide

tanberry added a commit to prisma/docs that referenced this issue Jan 11, 2022
Added a Note to tell users of PlanetScale that we recommend `db push` instead of `migrate`. Added a link to the Getting Started docs... but after the PlanetScale-specific docs are published, we can change that link to be more specific. And we can add more details to the upcoming Guide as well, about WHY we recommend `db push`. See [Issue #7282](prisma/prisma#7292 (comment)).
tanberry added a commit to prisma/docs that referenced this issue Jan 12, 2022
* Update 150-db-push.mdx

Added a Note to tell users of PlanetScale that we recommend `db push` instead of `migrate`. Added a link to the Getting Started docs... but after the PlanetScale-specific docs are published, we can change that link to be more specific. And we can add more details to the upcoming Guide as well, about WHY we recommend `db push`. See [Issue #7282](prisma/prisma#7292 (comment)).

* Update 150-db-push.mdx

fixed capitalization

* Update 150-db-push.mdx

added exact links to the PS version of Start from scratch and Add to existing project.
@janpio
Copy link
Member Author

janpio commented Jan 27, 2022

Our mighty documentation team just published a much better version of all the content of this issue in our documentation:

image

https://www.prisma.io/docs/guides/database/using-prisma-with-planetscale

Please take a look and follow the instructions in there from now on.
If anything does not work on PlanetScale with Prisma, please continue opening issues so we can investigate and fix. You can find existing issues with this link: https://github.com/prisma/prisma/issues?q=is%3Aopen+label%3A%22topic%3A+database-provider%2Fplanetscale%22+sort%3Aupdated-desc

Thanks 👋

@huv1k
Copy link

huv1k commented Jul 21, 2022

I just wonder is referentialIntegrity going to be moved out of preview features any time soon?

matthewmueller pushed a commit to prisma/docs that referenced this issue Jul 22, 2022
* Update 150-db-push.mdx

Added a Note to tell users of PlanetScale that we recommend `db push` instead of `migrate`. Added a link to the Getting Started docs... but after the PlanetScale-specific docs are published, we can change that link to be more specific. And we can add more details to the upcoming Guide as well, about WHY we recommend `db push`. See [Issue #7282](prisma/prisma#7292 (comment)).

* Update 150-db-push.mdx

fixed capitalization

* Update 150-db-push.mdx

added exact links to the PS version of Start from scratch and Add to existing project.
@janpio
Copy link
Member Author

janpio commented Jul 30, 2022

Yes, working on that right now. There are a few bugs left in the emulation unfortunately, so this will not happen in the next release(s), but we definitely want to make this a normal part of Prisma @huv1k.

@janpio
Copy link
Member Author

janpio commented Nov 30, 2022

@huv1k It happened, with 4.7.0 there is no more referentialIntegrity preview feature any more but this is generally available. Here is our documentation: https://www.prisma.io/docs/concepts/components/prisma-schema/relations/relation-mode

Note the recent addition of validation warnings for indexes: https://www.prisma.io/docs/concepts/components/prisma-schema/relations/relation-mode#indexes This is quite important to make sure your database and queries are fast, and your PlanetScale bill does not explode.

@end0cr1ne
Copy link

@janpio is it common to get a really high number of P1017: Server has closed the connection errors when using Prisma with planetscale?
I've tried adding: connect_timeout=300 to the connection string and ensuring that I have no other connections to the DB, but nothing seems to work.
Any suggestions??

@janpio
Copy link
Member Author

janpio commented Jan 17, 2023

No, definitely not common. Best open a new discussion or issue and provide more information.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests