prisma db pull doesn't read .env file and errors with Environment variable not found: DATABASE_URL

[smoothdvd]

Bug description

yarn prisma db pull report Environment variable not found: DATABASE_URL.

Prisma schema loaded from prisma/schema.prisma
Error: Get config: Schema Parsing P1012

error: Environment variable not found: DATABASE_URL.
  -->  schema.prisma:10
   | 
 9 |   provider = "postgresql"
10 |   url      = env("DATABASE_URL")
   | 

Validation Error Count: 1

.env:
DATABASE_URL="postgresql://user:pass@localhost:49154/db?schema=public"

How to reproduce

Expected behavior

read DATABASE_URL from .env correctly

Prisma information

Environment variables loaded from .env
prisma : 3.9.0
@prisma/client : 3.9.0
Current platform : darwin-arm64
Query Engine (Node-API) : libquery-engine bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine : migration-engine-cli bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine : introspection-core bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary : prisma-fmt bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Default Engines Hash : bcc2ff906db47790ee902e7bbc76d7ffb1893009
Studio : 0.457.0

Environment & setup

• OS: macOS 12.2
• Database: PostgreSQL 13
• Node.js version: v14.18.1

Prisma Version

[Jolg42]

Hi @smoothdvd

Thanks for the report!

I can confirm this is a regression, we are working on a fix and are planning a patch release later today 🚢

[millsp]

Hey, this issue has been fixed in 3.9.1 which was just published. Thanks for reporting this!

[cweekly]

Phew! Thought I was losing my mind. Confirmed I saw this in 3.9.0, and thankfully not in 3.9.2.

[stivencardonam]

I have this problem with mysql and my @prisma/client version is 4.2.1

[janpio]

Cna you describe your problem fully @stivencardonam? If there was another full regression, I think we would have already seen more reports of it. So please spend some time to describe the full situation and we can try to figure out what is going on.

[stivencardonam]

Sure,

Bug Description

yarn prisma db pull report Environment variable must start with the protocol mysql://

Error: Get Config: Schema parsing - Error while interacting with query-engine-node-api library
Error code: P1012
error: Error validating datasource `db`: the URL must start with the protocol `mysql://`.
  -->  schema.prisma:7
  |
6 |   provider = "mysql"
7 |   url      = env("DATABASE_URL")
  | 

Validation Error Count: 1

Prisma CLI Version : 4.2.1
error Command failed with exit code 1.

.env:

DATABASE_URL="mysql://docker:docker@localhost:3306/test"

Environment & setup
OS: macOS 12.3.1
Database: MySQL 8.0.27
Node.js version: v14.17.3

DESCRIPTION

I start using prisma with nestjs, I have a folder name prisma with schema.prisma file, when I execute yarn prisma db pull I have the error but when I replace the 7 line with the value of the env variable works fine:

prisma/schema.primsma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL") // line that I replace with the env value directly and work fine
}

[stivencardonam]

Sorry dude, I see my error now, I was overwriting the DATABASE_URL variable hahahahahaha 🙃

[janpio]

That explains it 😆 Great you figured it out.

[ZiadJ]

Sorry dude, I see my error now, I was overwriting the DATABASE_URL variable hahahahahaha 🙃

I'm getting the same error and I'm not sure what you mean by overwriting the variable.

EDIT:

Nevermind, so there was another .env file at the root folder that had priority and, since I assumed that the file closer to the schema file had priority instead, I did not bother looking any further. It would probably be more intuitive if it was the other way round though.

[Jolg42]

@fwjayy This looks unrelated to this issue and a duplicate of #13647
Could you try adding a database name to your connection url like

mongodb+srv://user:[email protected]/DATABASENAME?retryWrites=true&w=majority

If that does not work, please create a new issue, so we can help you with that.

[jedstroke]

@smoothdvd
@Jolg42
Remove the quote from your .env file

[franva]

@jedstroke @janpio @Jolg42 Hi this issue comes back again in 5.9.1.

With or without the quote from my .env file. And I have double checked that there is only one .env file in my project.
I also removed the .env.local file, so there is no any other versions of the .env file. So no overwrite.

Here is the link for the details: #23000

[abdoo9]

the problem is that $npx prisma init is automatically adding a line in the .env file you need to delete that line and it will work.

[Tobby-pro]

the problem is that $npx prisma init is automatically adding a line in the .env file you need to delete that line and it will work.

so you cant be specfic about how you did this. why talk in the first place ..when you can't explain to us how you fixed a problem.mtchew!

[LavGlides]

the problem is that $npx prisma init is automatically adding a line in the .env file you need to delete that line and it will work.

can you show the addition, my has no addition of line. copied from vercel