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.

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

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

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.

@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?

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

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

@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?

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?

@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.

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.

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.

@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!

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?

@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

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

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

@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

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

@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).

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.

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

@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.

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.

define an abstract class with abstract methods

Well one could do all that... or one could just start a tiny sqlite instance if you guys would let us. What do you think would be more efficient?

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

Have you got any news about working with multiple databases now?