Allow .env in the project root with the Prisma Schema somewhere else

[matthewmueller]

Problem

Right now we support an optional .env file that must live next to your schema.prisma file in the same directory. This coupling is a bit surprising and is often incompatible with how people want to organize their project.

Additionally, when you run prisma init, we write our env file to prisma/.env alongside prisma/schema.prisma. This layout works for us but is unusual and incompatible with projects that assume .env is in the project root. If you try moving prisma/.env into the project root, you'll also need to move prisma/schema.prisma to the project root. This approach is documented, but subtle.

For context, there are two components that rely on .env:

• The CLI when introspecting and migrating.
• The Client when connecting to the database.

Suggested solution

At the very least, we should allow .env in the project root and the schema.prisma in a prisma/ directory. This will allow people to re-use their .env that's already supported in Next.js, Redwood, Blitzjs, Create React App, etc.

Alternatives

Decouple .env and schema.prisma

It's not clear to me why we have this coupling between .env and schema.prisma in the first place. From my perspective,

• Client depends on DATABASE_URL being present by the time we connect.
• Introspect checks if the DATABASE_URL is in the environment at the time of introspection.
• Migrate checks if the DATABASE_URL is in the environment at the time of migration.

The CLI calls require('dotenv').config() in the beginning of the run. The Client doesn't do anything and expects the application to load the environment.

This would additionally allow us to properly support frameworks that use .env.test, .env.production. See #1717 for an example of this.

This solution would be my preference because it additionally solves #1717. (See Sept 24 Update)

Do nothing, suggest wrapping

Redwood & BlitzJS work around this problem by wrapping the Prisma CLI in their own CLI. This allows them to have the project structure they want without our limitations

I'm not advocating for this one, but it would solve the problem.

Related

• Change the prisma init default: Adjust prisma init to write to .env instead of prisma/.env #3721

Updates

Sept 24

Decoupling wouldn't completely add support for .env.test and .env.production in every case. For example, if you want to run the CLI with the test configuration (to migrate your test database), just calling require('dotenv').config() would load .env in all cases.

I don't think there's much harm in using something like require("dotenv-flow").config() as @flybayer suggested in the CLI to switch based on NODE_ENV.

[rntjr]

When calling the Prisma Client builder, it is possible to pass a URL parameter to replace what is already in the .env. Why not use this functionality for introspection and migration as well?

In my case for example, I have a monorepo in React and ApolloGraphQL. To be able to access the workspace .env I had to create an environment.ts file, get the DATABASE_URL and export it. Accessed my server.ts, imported the environment and insert it as a URL parameter in the Prisma Client.

If migrate and introspection were able to access this parameter from PrismaClient, when making the call on the CLI, they would access this variable and follow the flow.

So you can keep the prisma.schema in the folder ./prisma.

[flybayer]

Yes!! 💯💯💯 We also need this for nice integration with Blitz.

Also, with Blitz/Next, we use dotenv-flow which automatically picks up .env.local, .env.production, etc based on NODE_ENV. Prisma should definitely support this as well.

[wambugucoder]

Hey @matthewmueller check out this it might be of help

[thebiglabasky]

Planning outcome: @timsuchanek proposed to sit down and figure out some more detailed implications of doing that before actually tackling it.

[beeman]

It would be amazing if we could have support for using the .env in the project root. 🙏

I'm working on a course that includes Prisma and it's currently a bit clunky to have two env files, or a symlink.

[matthewmueller]

Thanks for your feedback so far! @timsuchanek and I sat down today to discuss the details on how we could solve the following problems.

.env file that must live next to your schema.prisma

Problem

This coupling is incompatible with how people want to organize their project. This incompatibility often leads to projects having multiple environment files per environment.

Proposed Solution

The CLI will look for .env in the project root in addition to the prisma/ folder. During prisma generate, we'll generate a relative path link from the generated client to .env.

Current users may have both a .env and a prisma/.env. We'll continue to allow both files for now. If there's a required environment variable that's present in both, we'll throw an error when you generate. For example, if DATABASE_URL is defined in both .env and prisma/.env, we'll throw an error when you run prisma generate.

Tradeoffs

If you move one of these files after you generate, you risk breaking your generated client because the relative link between Prisma Client and .env has changed. To fix this, you'd need to run prisma generate again.

We also thought about embedding the resolved .env file inside the Prisma Client during generate, but this would mean if you change an environment variable you'd need to run prisma generate again.

We figured moving files around is less common than changing variables, but we're curious to hear your thoughts.

Switching Environments

Problem

Our current .env feature doesn't provide a way to switch environments. A good illustration of this problem is if you want to migrate your test database, you need to run prisma migrate with your test environment.

The current workaround is to provide the environment variables explicitly. For example, DATABASE_URL="..." prisma migrate. This workaround quickly gets cumbersome with more environment variables.

You can also use environment pre-loaders, but we haven't tested or documented this yet.

Proposed Solution

For the Prisma CLI, we'll test and document how to preload the environment before calling the CLI. That will look something like this:

dotenv -e env.test -- prisma migrate

You'd typically stick this in NPM scripts or a Makefile.

For the Prisma Client, we'll continue to use the environment provided to it. If you want to run tests with your Prisma Client, you'd write something like:

require('dotenv').config({ env: ".env.test" })
const { PrismaClient } = require('prisma')

Tradeoffs

We originally talked about integrating .env.* support inside Prisma and switching files based on NODE_ENV, but this quickly became a rabbit hole when you start talking about adding this support to the generated Prisma Client. To remain consistent with the CLI, you'd need to resolve different .env.* files at runtime in the Prisma Client depending on the NODE_ENV. This would mean more relative links.

We may consider this option in the future. I've created a separate issue to track demand for it.

---

The proposed solutions are the direction we're planning on heading, but we haven't started coding it yet. Now is a great time to share your feedback before we commit. Thanks!

[flybayer]

Thanks for working on this @matthewmueller!

Unfortunately this proposal doesn't help the Blitz integration at all. And that's because by default we have .env as a file checked into git. The .env.*.local files are the env files not checked into git. Therefore, .env is never used to set DATABASE_URL.

This is also the recommended approach by Create React App, Next.js, and dotenv-flow.

[matthewmueller]

Thanks for your feedback @flybayer! I've opened a separate issue to discuss this feature further.

[beeman]

I'm happy with the proposal, as long as the Prisma client will traverse up to the project root various levels. In my apps, the Prisma file lives in some/path/to/prisma/schema.prisma.

I also prefer not embedding the files into the prisma client that gets generated.

Switching to different ENV's would be awesome to, but I'd love so see the project-level .env to be supported! 👍

[matthewmueller]

I'm happy with the proposal, as long as the Prisma client will traverse up to the project root various levels. In my apps, the Prisma file lives in some/path/to/prisma/schema.prisma.

Right now we're thinking of resolving .env in two places:

1. Read .env that's next to schema.prisma
2. Read .env that's in your project root (for node.js, where package.json is located)

This would mean that for prisma introspect --schema=some/path/to/prisma/schema.prisma we would look for .env in:

1. some/path/to/prisma/.env
2. .env

Does that work for you @beeman?

[beeman]

@matthewmueller yes, it does! Currently I create a symlink from the root to where I have my schema.prisma :)