Remove misleading mention of composing env variables that only works via `.env` files

[yannickgloster]

Update: See problem in https://github.com/prisma/docs/issues/2835#issuecomment-1026293087

Discussed in https://github.com/prisma/prisma/discussions/11453

^{Originally posted by yannickgloster January 27, 2022} Following prisma/prisma#1673, I converted the SSL Certificate that I needed from here Azure Database for MySQL into base64 to store it in as an environment variable that I can then load in into the tmp folder on the serverless function. I do this when I create my prisma client: /lib/prisma.js

import { PrismaClient } from "@prisma/client";

const fs = require("fs");
const path = "/tmp";

try {
  if (fs.existsSync(`${path}/BaltimoreCyberTrustRoot.crt.pem`)) {
    // do nothing
  } else {
    // creates crt.pem file from ENV

    fs.mkdirSync(path, { recursive: true });

    fs.writeFileSync(
      `${path}/BaltimoreCyberTrustRoot.crt.pem`,
      Buffer.from(process.env.CRT_PEM, "base64").toString("ascii")
    );
  }
} catch (err) {
  console.error(err);
}

const prisma = global.prisma || new PrismaClient();

if (process.env.NODE_ENV === "development") global.prisma = prisma;

export default prisma;

Locally this works as expected, and when I check on the API endpoint, the file exists correctly

import fs from "fs";

export default async function handler(req, res) {
  const files = fs.readFileSync(`/tmp/BaltimoreCyberTrustRoot.crt.pem`);

  res.status(200).json({ files: files.toString() });
}

My environment variables look like this:

SSL_CERT_PATH=/tmp/BaltimoreCyberTrustRoot.crt.pem
DATABASE_URL=mysql://user:pass@lserverip:3306/registration?sslcert=${SSL_CERT_PATH}&connect_timeout=300
CRT_PEM=longbase64string

I'm able to successfully connect to the DB when I use those variables locally, but when deployed I get the following error when I go to make a request to the DB.

2022-01-27T23:22:08.847Z	c63d91bb-ae49-40c3-95dd-c20a224d3f4d	ERROR	PrismaClientInitializationError: Can't reach database server at `IP OF SERVER`:`3306`
Please make sure your database server is running at `IP OF SERVER`:`3306`.
    at cb (/var/task/node_modules/@prisma/client/runtime/index.js:38697:17)
    at async handler (/var/task/.next/server/pages/api/tournaments.js:60:25)
    at async Object.apiResolver (/var/task/node_modules/next/dist/server/api-utils.js:102:9)
    at async NextNodeServer.handleApiRequest (/var/task/node_modules/next/dist/server/base-server.js:1076:9)
    at async Object.fn (/var/task/node_modules/next/dist/server/base-server.js:963:37)
    at async Router.execute (/var/task/node_modules/next/dist/server/router.js:222:32)
    at async NextNodeServer.run (/var/task/node_modules/next/dist/server/base-server.js:1103:29)
    at async NextNodeServer.handleRequest (/var/task/node_modules/next/dist/server/base-server.js:319:20)
    at async Server.<anonymous> (/var/task/___next_launcher.js:32:9) {
  clientVersion: '3.8.1',
  errorCode: undefined
}
2022-01-27T23:22:08.847Z	c63d91bb-ae49-40c3-95dd-c20a224d3f4d	ERROR	PrismaClientInitializationError: Can't reach database server at `IP OF SERVER`:`3306`
Please make sure your database server is running at `IP OF SERVER`:`3306`.
    at cb (/var/task/node_modules/@prisma/client/runtime/index.js:38697:17)
    at async handler (/var/task/.next/server/pages/api/tournaments.js:60:25)
    at async Object.apiResolver (/var/task/node_modules/next/dist/server/api-utils.js:102:9)
    at async NextNodeServer.handleApiRequest (/var/task/node_modules/next/dist/server/base-server.js:1076:9)
    at async Object.fn (/var/task/node_modules/next/dist/server/base-server.js:963:37)
    at async Router.execute (/var/task/node_modules/next/dist/server/router.js:222:32)
    at async NextNodeServer.run (/var/task/node_modules/next/dist/server/base-server.js:1103:29)
    at async NextNodeServer.handleRequest (/var/task/node_modules/next/dist/server/base-server.js:319:20)
    at async Server.<anonymous> (/var/task/___next_launcher.js:32:9) {
  clientVersion: '3.8.1',
  errorCode: undefined
}
RequestId: c63d91bb-ae49-40c3-95dd-c20a224d3f4d Error: Runtime exited with error: exit status 1
Runtime.ExitError

If I remove the SSL information from my DB connection string, I get an error stating that I need to provide SSL information to continue. Server error: `ERROR 28000 (9002): SSL connection is required. Please specify SSL options and retry.\u0000'

Is there something I'm doing wrong here? Thanks in advance

The reason I'm moving this to an issue is that due to the nature error, I believe this may be a bug.

[janpio]

Generally this looks fine, I see nothing obvious you are doing wrong. Are you getting the exact same error when you point to a wrong on non existent file in the connection string?

[yannickgloster]

I hadn't though of testing that @janpio, I just did and I got the same error:

[GET] /api/tournaments
17:56:15:24
2022-01-28T17:56:15.974Z	4b670608-cb13-48cf-aca0-26f63e32b1f7	ERROR	PrismaClientInitializationError: Can't reach database server at `IDONOTEXIST`:`3306`
Please make sure your database server is running at `IDONOTEXIST`:`3306`.
    at cb (/var/task/node_modules/@prisma/client/runtime/index.js:38697:17)
    at async handler (/var/task/.next/server/pages/api/tournaments.js:60:25)
    at async Object.apiResolver (/var/task/node_modules/next/dist/server/api-utils.js:102:9)
    at async NextNodeServer.handleApiRequest (/var/task/node_modules/next/dist/server/base-server.js:1076:9)
    at async Object.fn (/var/task/node_modules/next/dist/server/base-server.js:963:37)
    at async Router.execute (/var/task/node_modules/next/dist/server/router.js:222:32)
    at async NextNodeServer.run (/var/task/node_modules/next/dist/server/base-server.js:1103:29)
    at async NextNodeServer.handleRequest (/var/task/node_modules/next/dist/server/base-server.js:319:20)
    at async Server.<anonymous> (/var/task/___next_launcher.js:32:9) {
  clientVersion: '3.8.1',
  errorCode: undefined
}
2022-01-28T17:56:15.975Z	4b670608-cb13-48cf-aca0-26f63e32b1f7	ERROR	PrismaClientInitializationError: Can't reach database server at `IDONOTEXIST`:`3306`
Please make sure your database server is running at `IDONOTEXIST`:`3306`.
    at cb (/var/task/node_modules/@prisma/client/runtime/index.js:38697:17)
    at async handler (/var/task/.next/server/pages/api/tournaments.js:60:25)
    at async Object.apiResolver (/var/task/node_modules/next/dist/server/api-utils.js:102:9)
    at async NextNodeServer.handleApiRequest (/var/task/node_modules/next/dist/server/base-server.js:1076:9)
    at async Object.fn (/var/task/node_modules/next/dist/server/base-server.js:963:37)
    at async Router.execute (/var/task/node_modules/next/dist/server/router.js:222:32)
    at async NextNodeServer.run (/var/task/node_modules/next/dist/server/base-server.js:1103:29)
    at async NextNodeServer.handleRequest (/var/task/node_modules/next/dist/server/base-server.js:319:20)
    at async Server.<anonymous> (/var/task/___next_launcher.js:32:9) {
  clientVersion: '3.8.1',
  errorCode: undefined
}
RequestId: 4b670608-cb13-48cf-aca0-26f63e32b1f7 Error: Runtime exited with error: exit status 1
Runtime.ExitError

The server is reachable, as mentioned previously, when I don't include SSL parameters, it informs me that I need to use SSL to connect to the server.

[janpio]

Ok thanks. I think you did this with the IP address, what I meant was to set SSL_CERT_PATH to an invalid file so the sslcert has a definitely invalid value. (My theory right now is that it can not access that file at that location or the content is wrong) Can you try that?

[yannickgloster]

Ah yes, I did do it with the IP, I changed the cert path and I'm still getting the same error.

As far as I can tell, the content is correct, I created a test API endpoint to read the contents of the file and so I am able to successfully read the file on the server and it is the correct information as far as I can tell.

What's strange is using that same flow, it works on my dev PC and connects successfully.

[janpio]

That would point to either a permission or path problem - both are unfortunately super hard to debug as Prisma currently does not output great error messages. Can you try to simplify the path and put the file into the same folder as the schema file or similar?

[yannickgloster]

I was able to find the route cause of the issue.

Prisma was not reading the composed env when deployed through vercel. Once I fixed the path directly into the single environmental variable, it worked. Looks like there might be a bug with relation to how/when vercel sets the envs.

[janpio]

Interesting. How did you set the original environment variables with the place holders at Vercel? we should understand this and figure out if there is a good way to achieve that on Vercel (and then document it for users like you).

[yannickgloster]

I set the composed environment variables following what was written in the docs: https://www.prisma.io/docs/concepts/components/prisma-schema#accessing-environment-variables-from-the-schema

And they were uploaded to vercel using their UI that was documented here: https://vercel.com/docs/concepts/projects/environment-variables

[janpio]

So you had mysql://user:pass@lserverip:3306/registration?sslcert=${SSL_CERT_PATH}&connect_timeout=300 CRT_PEM=longbase64string in the field for DATABASE_URL? I don't think that is supposed to work at Vercel indeed.

The docs page you link to also has a super weird mention of "In version 2.7.0 and later ..." about dotenv-expand - which is super misleading as that I think only works when using our .env file. I will report this internally.

[yannickgloster]

So you had mysql://user:pass@lserverip:3306/registration?sslcert=${SSL_CERT_PATH}&connect_timeout=300 CRT_PEM=longbase64string in the field for DATABASE_URL? I don't think that is supposed to work at Vercel indeed.

Yep that would be correct. It would be great to either attempt to unify the experience if possible or have some sort of note.

[yannickgloster]

Thank you very much for all your help.

[janpio]

It would be great to either attempt to unify the experience if possible or have some sort of note.

Unifying is unfortunately not possible, as this is additional code we load and execute that can only work for the .env file. But we should remove the misleading mention of composing at that spot at least.

[sontiO]

I just found this thread and I am so confused. How am I su

I set the composed environment variables following what was written in the docs: https://www.prisma.io/docs/concepts/components/prisma-schema#accessing-environment-variables-from-the-schema

And they were uploaded to vercel using their UI that was documented here: https://vercel.com/docs/concepts/projects/environment-variables

so how am I supposed to set the env for vercel? I did just like you on those two documentations linked. I found this thread but still lost on what to do instead

[janpio]

Depends on what you want to to. If you want to set a static env var: Use the Vercel instructions at the second link. If you want to compose a new env var value out of other bits and pieces you have available: Use an .env file that just composes things together, use the first link.

[sontiO]

I tried setting it static in Vercel UI. But I get: ENOENT: no such file or directory, open '/var/task/schema.prisma' undefined on my data in deployment. And my data obv undefined too. Since it works locally, my first thought was something was wrong with my variant variable and this thread is the only thing i stumbled upon

[janpio]

That is completely unrelated to the env var problem and is probably related to the bundler you are using or something like that. Please open a new bug report issues in prisma/prisma about this if you can reproduce this. Thanks.

[Jolg42]

https://www.prisma.io/docs/guides/development-environment/environment-variables#expanding-variables Clearly state

Variables stored in .env files can be expanded using the format specified by dotenv-expand.

But https://www.prisma.io/docs/concepts/components/prisma-schema#accessing-environment-variables-from-the-schema

In version 2.7.0 and later, you can expand variables using the format specified by dotenv-expand. This is useful when dealing with PaaS or similar products. such as Heroku):

TODO: Could be improved, the fact that it only work with the .env file is implicit

[Jolg42]

I created a PR for the misleading text at https://github.com/prisma/docs/pull/4189

[Jolg42]

The misleading text was removed in https://github.com/prisma/docs/pull/4189 from https://www.prisma.io/docs/concepts/components/prisma-schema#accessing-environment-variables-from-the-schema