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

Deprecation of provider array notation #3834

Closed
thebiglabasky opened this issue Oct 1, 2020 · 24 comments
Closed

Deprecation of provider array notation #3834

thebiglabasky opened this issue Oct 1, 2020 · 24 comments

Comments

@thebiglabasky
Copy link
Member

@thebiglabasky thebiglabasky commented Oct 1, 2020

TL;DR We recently realized that supporting an array of providers in the Prisma schema was premature, adding complexity in the process of improving the product (migrate, native types...), and causing some confusion about our intent.

We are deprecating its use and will consider another solution, better addressing how we want Prisma to support working with multiple databases in the future.

Context

On July 7th, we released Prisma 2.2.0, which came with the following feature: #1487

It allowed to define a provider using an array notation like:

datasource db {
  provider = ["sqlite", "postgres"]
  url = env("DATABASE_URL")
}

We want to deprecate this use and only allow a single provider per datasource.

Why are we doing this?

Our goal at Prisma is to make it approachable to work any database while ensuring people can make the most of them too.

Each database provider has their own strengths and weaknesses, making them fit certain use cases better than others. As an example, SQLite is great to easily get started anywhere but doesn't scale in high-concurrency scenarios.

By allowing a Prisma data source to fit multiple providers, and leaving it up to the environment to decide which provider was active, we made it look like a single Prisma data source could work interchangeably with any of our supported database vendors, despite of their differences. This, in turn, restricts the set of functionalities one can use with their database.

This is not fully aligned with the goal mentioned above.

What we want is:

  • to make it easy to start developing using your database of choice (often SQLite as being the simplest for a lot of people) and make it possible to, when the time comes, switch to another database vendor while understanding the implication such a change will have on the schema, your data, and your code;
  • to enable developers making the most out of functionalities of their specific database provider, and not feel limited by using Prisma;
  • in the future, to make it simpler to have a single application using multiple data sources, each data source being possibly from a different provider.

I was using this! What should I do?

What will happen

In an upcoming release, Prisma will reject schemas that include multiple providers in a data source declaration.

We will communicate this date and update the issue once it's defined. This issue is a heads up so we can let you know of the intent and you can prepare for it.

At that point, this means that the following will no longer be possible:

datasource db {
  provider = ["sqlite", "postgres"]
  url = env("DATABASE_URL")
}

Instead, Prisma will expect to have only a single provider listed:

datasource db {
  provider = "sqlite" // or "postgres"
  url = env("DATABASE_URL")
}

Implications

A situation where we've seen people using multiple providers for a single data source was when SQLite was used in their development environment while Postgres or MySQL was used in the staging/production environment.

This won’t be possible anymore after this change. Note that, while some projects have been famous for doing that, it is also generally considered more robust to align the providers across environments, as the tests will be consistent.

In situations where you started off an SQLite local database, we recommend installing a local instance of Postgres or MySQL on your local machine.

@rhlsthrm
Copy link

@rhlsthrm rhlsthrm commented Nov 25, 2020

We use the same schema in two versions of our project one of which uses SQLite and one which uses Postgres. This change will definitely affect us. We were previously using the constructor to specify DB type at runtime which works great, but this schema issue will be a problem for migrations.

Loading

@mazzaker
Copy link

@mazzaker mazzaker commented Nov 26, 2020

If you remove this, can we please use environment variable for provider instead? As @rhlsthrm said migrations would not work for us anymore.

Loading

@mbush92
Copy link

@mbush92 mbush92 commented Nov 27, 2020

As has been previously commented the ability to use an environment variable for the provider would solve the issue I have of using sqlite in local environment development but running another database such as postgres in the production environment and allows us to keep local development light weight until we need to have postgres locally

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Nov 30, 2020

This won't be an option for the time being unfortunately.
The reason is that migrations are now SQL-based and generated depending on the provider, making dynamically switching provider not a possibility.
This will require to install a Postgres instance locally.

Loading

@rhlsthrm
Copy link

@rhlsthrm rhlsthrm commented Nov 30, 2020

@thebiglabasky we are in a different situation where we support both SQLite and PostgreSQL using the same schema.prisma. How can we resolve this? Would I need a separate schema file for SQLite?

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Nov 30, 2020

You would indeed need multiple schema files for the time being.
We would like to explore possibilities to import files from a schema, to simplify the process, but that's not possible at the moment. You can follow this issue for that capability: #2377

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Dec 8, 2020

Note: the new migrate is coming out in preview in this 2.13 release and will not be working with schemas using multiple providers

Loading

@rhlsthrm
Copy link

@rhlsthrm rhlsthrm commented Dec 8, 2020

@thebiglabasky is it an acceptable workaround to "find and replace" the provider name in our schema with the other db type?

Edit: After reading replies it seems like it's not possible since migrations are generated in raw SQL which might not be compatible between providers?

Is there any suggestion for working around this?

Loading

@mbush92
Copy link

@mbush92 mbush92 commented Dec 8, 2020

I do not see any SQL in the migration files in my repo, am I missing something or is something changing between the version today and the next version in regards to this? I am using the netlify plugin for switching the provider at build time and its working today with a single provide defined in my schema

datasource DS {
  // optionally set multiple providers
  // example: provider = ["sqlite", "postgresql"]
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

At build time before Prisma is executed the plugin swaps the provider in the schema and all of the migration schemas as well as steps.json for each of the migrations where the provider is mentioned.

In the new release will this continue to work or are there other changes which are being made that will make this process not work any longer?

Loading

@rhlsthrm
Copy link

@rhlsthrm rhlsthrm commented Dec 8, 2020

@mbush92 if I understand correctly, this will be rolled out in the next version, which will break the multiple providers. I would think the SQL replaces steps.json.

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Dec 8, 2020

Starting in 2.13 (released in a few hours minutes) migrate will generate SQL based on the provider specified, hence not accept schemas that are referencing multiple providers in the array notation.

The suggestion is to stick to a single provider that reflects your production environment by installing the database on your system (e.g. Postgres has plenty of easy options to install it locally, MySQL too, Docker is also an option...), or working with a hosted instance used for development purposes if you really can't install anything on your workstation (e.g. a free Heroku Postgres...)
This is generally considered a good practice to develop against the same database provider as the one running in production as it prevents from facing discrepancies that these systems have.

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Dec 8, 2020

If you need two DBMS' for your project, I'd love to learn more about it and understand better your setup @rhlsthrm
I assume that this would require to keep two versions of the schema with separate providers or do a search / replace at runtime, but that's hard to tell without understanding the specifics.

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Dec 8, 2020

@mbush92 I assume this could still work, but that's not something we've necessarily actively tested as we assume stable providers across environments. You should be able to try that out with the new version once it's out and tell us how it goes!

Loading

@rhlsthrm
Copy link

@rhlsthrm rhlsthrm commented Dec 8, 2020

If you need two DBMS' for your project, I'd love to learn more about it and understand better your setup @rhlsthrm
I assume that this would require to keep two versions of the schema with separate providers or do a search / replace at runtime, but that's hard to tell without understanding the specifics.

@thebiglabasky we are building a distributed system that we provide docker images of. The images allow databases to be plugged in by our user and we need to support both Postgres and SQLite. If we do a search/replace at runtime (assuming for just the provider string in the schema?), will the migrations be properly generated?

Loading

@mbush92
Copy link

@mbush92 mbush92 commented Dec 8, 2020

@rhlsthrm this is the plugin that we are using on netlify, it is currently working to replace the database provider at build time when we deploy to production, maybe you could do something like this in your build scripts

netlify-plugin-prisma-provider

Loading

@rhlsthrm
Copy link

@rhlsthrm rhlsthrm commented Dec 8, 2020

@mbush92, cool this is interesting! It seems to me like this will stop working once the migrations start generating SQL though.

Loading

@mbush92
Copy link

@mbush92 mbush92 commented Dec 8, 2020

Yeah, I am afraid for that as well. It's not that I can't spin up a local postgres database but it sure was easy to get started with RedwoodJS when this just worked the way that it did as out of the box I didn't have to do anything locally to be able to develop outside of spinup the app

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Dec 9, 2020

@rhlsthrm If you're up for it I'd love to hop on a call to dig more into your setup, and how the upgrade system works currently. That would be important for us to enable products that aim at supporting multiple database systems under the hood, and it looks like your use case could help figuring some aspects out.
Here's a Calendly link if you can find a spot. If timezones differences make that harder, I'm happy to accommodate: https://calendly.com/labas-prisma/multi-provider

Loading

@rhlsthrm
Copy link

@rhlsthrm rhlsthrm commented Dec 9, 2020

Thank you very much @thebiglabasky! I've scheduled time and looking forward to chatting.

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Dec 9, 2020

@mbush92 That's indeed a drawback of the new migrate, but the benefits of being able to customize migrations and have predictable execution on production environments won over the convenience of not having to install a local instance of the database used in production.
If this turns out to be a major requirement by most of our users, we'll evaluate solutions to better support these scenarios (e.g. a DSL, or else).

Loading

@thebiglabasky
Copy link
Member Author

@thebiglabasky thebiglabasky commented Apr 20, 2021

Hi,
We are planning to proceed with removing support of the multi-provider notation entirely in the upcoming release in two weeks.
You can refer to this section of our documentation if you wonder about how to deal with this change.

Loading

@DarkBitz
Copy link

@DarkBitz DarkBitz commented Apr 30, 2021

Is there an example repository for NestJS and Prisma somewhere that uses SQLite for unit testing but PostgreSQL for production?

Loading

@3dmind
Copy link

@3dmind 3dmind commented May 4, 2021

@DarkBitz A unit test should never depend on an external system like a database, in my opinion. To decouple unit tests from a database query I recommend the repository pattern. You define an abstract class with abstract methods for particular queries, like getUserById. Then you implement the repository twice:

The first one uses an in-memory data structure and is used in your unit tests.
The second one uses the actual Prisma client.

You inject the in-memory repository into your unit tests and the Prisma repository into your production code.

Example:
The abstract repository:

// user.repository.ts
abstract class UserRepository {
  abstract save(user: User): Promise<void>{}
  abstract getUserById(id: string): Promise<User>{}
}

The in-memory implementation:

// in-memory-user.repository.ts
@Injectable()
class InMemoryUserRepository extends UserRepository {
  private readonly users = new Map<string, User>()

  async save(user: User): Promise<void> {
    this.user.set(user.id, user);
  }

  async getUserById(id: string): Promise<User> {
    return this.users.get(id)
  }
}

Inject the in-memory repository in the unit test:

//...
const module: TestingModule = await Test.createTestingModule({
      providers: [
        {
          provide: UserRepository,
          useClass: InMemoryUserRepository,
        },
        //...
      ],
    }).compile();
//...

The Prisma repository:

// prism-user.repository.ts
@Injectable()
export class PrismaUserRepository extends UserRepository {
  constructor(private readonly prismaService: PrismaService) {
    super();
  }

  async save(user: User): Promise<void> {
    // Use actual Prisma query here.
  }

  async getUserById(id: string): Promise<User> {
    // Use actual Prisma query here.
  }
}

The provider for the Prisma repository

// user-repository.provider.ts
export const UserRepositoryProvider: Provider = {
  provide: UserRepository,
  useClass: PrismaUserRepository,
};

Load this provider when you would like to inject and use the actual Prisma repository. For example in a controller or service.

Loading

@tomhoule
Copy link
Member

@tomhoule tomhoule commented May 5, 2021

Provider arrays were removed as of 2.22.0.

Thanks for all the feedback in this issue. Please open new ones for discussions about related problems in the future.

Loading

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

Successfully merging a pull request may close this issue.

None yet
9 participants