Managing Prisma ORM environment variables and settings

An environment variable is a key value pair of string data that is stored on your machine's local environment. Refer to our Environment variables reference documentation for specific details.

Typically the name of the variable is uppercase, this is then followed by an equals sign then the value of the variable:

MY_VALUE=prisma

The environment variable belongs to the environment where a process is running.Any program can read and create these environment variables. They are a cheap and effective way to store simple information.

tip

With Prisma ORM v6.4.0, we released the prisma.config.ts file into Early Access. This file allows you to manage your environment variables and settings in a more flexible way.

View our reference for more information and don't forget to share your feedback on our GitHub discussion!

How Prisma ORM can use environment variables

Prisma ORM always reads environment variables from the system's environment.

When you initialize Prisma ORM in your project with prisma init, it creates a convenience .env file for you to set your connection url as an environment variable. When you use Prisma CLI or Prisma Client, the .env file content and the variables defined in it are added to the process.env object, where Prisma ORM can read it and use it.

Using an .env file

warning

Do not commit your .env files into version control!

The Prisma CLI looks for .env files, in order, in the following locations:

1. In the root folder of your project (./.env)
2. From the same folder as the schema specified by the --schema argument
3. From the same folder as the schema taken from "prisma": {"schema": "/path/to/schema.prisma"} in package.json
4. From the ./prisma folder

If a .env file is located in step 1., but additional, clashing .env variables are located in steps 2. - 4., the CLI will throw an error. For example, if you specify a DATABASE_URL variable in two different .env files, you will get the following error:

Error: There is a conflict between env vars in .env and prisma/.env
Conflicting env vars:
  DATABASE_URL

We suggest to move the contents of prisma/.env to .env to consolidate your env vars.

The following table describes where the Prisma CLI looks for the .env file:

Command	schema location	.env file locations checked, in order
prisma [command]	./prisma/schema.prisma	./.env ./prisma/.env
prisma [command] --schema=./a/b/schema.prisma	./a/b/schema.prisma	./.env ./a/b/.env ./prisma/.env
prisma [command]	"prisma": {"schema": "/path/to/schema.prisma"}	.env ./path/to/schema/.env ./prisma/.env
prisma [command]	No schema (for example, when running prisma db pull in an empty directory)	./.env ./prisma/.env

Any environment variables defined in that .env file will automatically be loaded when running a Prisma CLI command.

info

Looking to use more than one .env file? See Using multiple .env files for information on how to setup and use multiple .env files in your application.

Refer to the dotenv documentation for information about what happens if an environment variable is defined in two places.

Expanding variables with .env files

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

.env

DATABASE_URL=postgresql://test:test@localhost:5432/test
DATABASE_URL_WITH_SCHEMA=${DATABASE_URL}?schema=public

Additionally, you can use environment variables in the expansion that are set outside of the .env file. For example a database URL that is set on a PaaS like Heroku or similar:

# environment variable already set in the environment of the system
export DATABASE_URL=postgresql://test:test@localhost:5432/test

.env

DATABASE_URL_WITH_SCHEMA=${DATABASE_URL}?schema=foo

This will make the environment variable DATABASE_URL_WITH_SCHEMA with value postgresql://test:test@localhost:5432/test?schema=foo available for Prisma ORM.

Using environment variables in your code

If you want environment variables to be evaluated at runtime, you need to load them manually in your application code (for example, by using dotenv):

import * as dotenv from 'dotenv'

dotenv.config() // Load the environment variables
console.log(`The connection URL is ${process.env.DATABASE_URL}`)

If you are using a custom file name for your environment variables, you can configure dotenv to use that filename:

import * as dotenv from 'dotenv'

var envFile = path.resolve(join(__dirname, "myenv.env"))
dotenv.config({path: envFile}) // Load the environment variables
console.log(`The connection URL is ${process.env.DATABASE_URL}`)

If you need variable expansion across environment files, you can additionally use dotenv-expand:

import * as dotenv from 'dotenv'
const dotenvExpand = require('dotenv-expand')

var envFile = path.resolve(join(__dirname, "myenv.env"))
var mySqlEnv = dotenv.config({path: envFile})
dotenvExpand.expand(mySqlEnv)

If you are using multiple .env files, you can refernce an environment file in your project's code depending on the environment you are running in.

import { config } from 'dotenv'

const envFile = process.env.NODE_ENV === 'development' ? '.env.development' : '.env.production'
config({ path: envFile })

Manually set environment variables

Because Prisma ORM reads from the system's environment when looking for environment variables, it's possible to skip using .env completely and create them manually on your local system.

info

The following examples will use setting the DATABASE_URL environment variable which is often used for the database connection URL.

Manually set an environment variable on a Mac/Linux system

From a terminal on a Unix machine (Mac/Linux), you export the variable as a key value pair.

export DATABASE_URL=postgresql://test:test@localhost:5432/test?schema=public

Then check that it has been successfully set using printenv:

printenv DATABASE_URL

Show CLI results

postgresql://test:test@localhost:5432/test?schema=public

Manually set an environment variable on a Windows system

The following examples illustrate how to set the environment variable (for the current user) using both Command Prompt (cmd.exe) and PowerShell, depending on your preference.

Command Prompt

set DATABASE_URL="postgresql://test:test@localhost:5432/test?schema=public"

Powershell

[Environment]::SetEnvironmentVariable("DATABASE_URL", "postgresql://test:test@localhost:5432/test?schema=public")

Then check that it has been successfully set:

Command Prompt

set DATABASE_URL

Powershell

Get-ChildItem Env:DATABASE_URL

Using multiple .env files

There is a risk that your production database could be deleted if you store different connection URLs to each of your environments within a single .env file.

One solution is to have multiple .env files which each represent different environments. In practice, this means you create a file for each of your environments:

• .env.development
• .env.sample

Then using a package like dotenv-cli, you can load the correct connection URL for the environment you are working in.

info

For our purposes, it is assumed you have a dedicated development database that you use while developing your application.

1. Rename your .env file to .env.development

.env.development

DATABASE_URL="postgresql://prisma:prisma@localhost:5433/dev"

2. Create a new .env.sample file and change the database name to sample (or your preferred name)

.env.sample

DATABASE_URL="postgresql://prisma:prisma@localhost:5433/sample"

3. Install dotenv-cli

In order for Prisma ORM and Jest to know which .env file to use, alter your package.json scripts to include and call the dotenv package and specify which file to use depending on what commands you are running and in which environment you want them to run.

info

Any top-level script that is running the tests and migrations needs the dotenv command before it. This makes sure that the env variables from .env.sample are passed to all commands, including Jest.

Running migrations on different environments

You can use the dotenv-cli package to specify which environment file Prisma ORM should use when running a migration.

The below script uses dotenv-cli to pass the .env.sample environment file (which holds a DATABASE_URL connection string) to the Prisma ORM migration script.

Migration script

package.json

  "scripts": {
    "migrate:postgres": "dotenv -e .env.sample -- npx prisma migrate deploy",
  },

Running tests on different environments

When running tests, we advise you to mock Prisma Client. In doing so, you need to tell Jest which environment it should use when running its tests.

By default, Prisma Client will use the environment specified in the default .env file located at the project's root.

If you have created a separate .env.sample file to specify your testing database, then this environment will need to be passed to Jest.

The below script uses dotenv-cli to pass the .env.sample environment file (which holds a DATABASE_URL connection string) to Jest.

package.json

  "scripts": {
    "test": "dotenv -e .env.sample -- jest -i"
  },