Drizzle Kit dotenv support

[AlexBlokh]

As of now drizzle-kit config will be automatically supplied with .env file via backed in dotenv package.

In the next version kit will consume .env* files based on NODE_ENV and will be compatible with Bun env consumption. Kit will not consume dotenv files when run in Bun, since Bun already does:

we will read the following files automatically (listed in order of increasing precedence)

• .env
• .env.production .env.development .env.test (depending on value of NODE_ENV)
• .env.local

we will support variables expansion:

DB_USER=postgres
DB_PASSWORD=secret
DB_HOST=localhost
DB_PORT=5432
DB_URL=postgres://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME

you will have an ability to provide custom env file or multiple files or tell Drizzle to not consume files automatically
drizzle-kit --env-file=.env.custom
drizzle-kit --env-file=.env.custom --env-file=.env.custom2
drizzle-kit --no-env -> load custom/yourself

UPD 05.11.24:
pending Bun fix, for now we'll use dotenvx for every runtime

[nguyendv]

Love it! Do we have a way to track when will this dotenv support land?

[kravetsone]

Why not use built-in Node.js features?

[jbergstroem]

I'd advise against using NODE_ENV values to branch. Its actually covered in the "best practices" around node.js/js development: https://www.platformatichq.com/node-principles#always-set-node_env=production - also in a podcast format here: https://changelog.com/jsparty/347

[Ashvith10]

@AlexBlokh has the support been added? Because it doesn't seem to be working for me locally, even after adding @next/env. After adding @next/env, only .env, .env.local, .env.production and .env.production.local is detected.

[do-adams]

Nice. It's working for me out of the box without a dotenv pkg dependency, so that's super cool!

It'd be nice to update the Drizzle tutorials to reflect this 🙌

[gianpaj]

what version did you run? It doesn't work for me

$ npx drizzle-kit --env-file=.env.local push
Unrecognized options for command 'push': --env-file

$ npx drizzle-kit --version
drizzle-kit: v0.30.5
drizzle-orm: v0.38.4

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

console.error(
  "process.env.TURSO_CONNECTION_URL",
  process.env.TURSO_CONNECTION_URL,
);

export default defineConfig({
  dialect: "turso",
  schema: "./src/app/lib/db/schema.ts",
  dbCredentials: {
    url: process.env.TURSO_CONNECTION_URL,
    authToken: process.env.TURSO_AUTH_TOKEN,
  },
});

$ npx drizzle-kit push
No config path provided, using default 'drizzle.config.ts'
Reading config file '/Users/gianpaj/github/escuela-dev/tracking/drizzle.config.ts'
process.env.TURSO_CONNECTION_URL undefined
 Error  Please provide required params:
    [x] url: undefined

[do-adams]

Here's my current version (running my local drizzle-kit instance not the latest one from npm with pnpx):

% pnpm exec drizzle-kit -v
drizzle-kit: v0.30.5
drizzle-orm: v0.40.0 

[P4R4DiSi4C]

In addition to this, using bun drizzle-kit push doesn't support bun's default dotenv expansion, for example:

DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=pg_dev
DB_HOST=localhost
DB_PORT=5432
DATABASE_URL=postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME

export default defineConfig({
	out: "./drizzle",
	schema: "./src/db/schema.ts",
	dialect: "postgresql",
	dbCredentials: {
		url: env.DATABASE_URL,
	},
})

It throws

at BoundPool.query (x/node_modules/pg-pool/index.js:403:10)
    at Object.query (xnode_modules/drizzle-kit/bin.cjs:78355:39)
    at fromDatabase2 (x/node_modules/drizzle-kit/bin.cjs:19095:34)
    at pgPushIntrospect (x/node_modules/drizzle-kit/bin.cjs:81201:9) {
  code: 'ERR_INVALID_URL',
  input: 'postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME',
  base: 'postgres://base'

Workaround is to do the following:

export default defineConfig({
	out: "./src/db/migrations",
	schema: "./src/db/schema.ts",
	dialect: "postgresql",
	dbCredentials: {
		host: env.DB_HOST,
		port: env.DB_PORT,
		user: env.DB_USER,
		password: env.DB_PASSWORD,
		database: env.DB_NAME,
		ssl: false,
	},
})

[TylerRick]

Looking forward to this. Seems a little biased towards bun's behavior... which is fine, we're not all going to agree on everything 😄, though personally I'm currently more biased towards Vite's behavior since our project is built on SvelteKit, which is built on Vite.

What concerns me the most is how all these tools we use (and drizzle-kit is just one of them) are each going to have their own subtle little differences in the way they do things, which ultimately means that different tools may end up with slightly different environment loaded — even though our .env files remain exactly the same in all cases!

So honestly, the option I'm most excited about is this one:

drizzle-kit --no-env  #-> load custom/yourself

---

I do like that most tools seem to be standardizing on having an --env-file option. But honestly, that seems to have limited usefulness (mostly for the case where you only use a single env file). It certainly doesn't help when you actually want to have it look for and load not just 1 but 4 files (whichever of those 4 candidates exist), like Vite does. To replicate Vite's env loading behavior using CLI options, you'd have to write out something really, really long like this (and you'd need the --env-file-if-exists option to even exist on all of your tools):

tool-name --env-file-if-exists .env --env-file-if-exists .env.local --env-file-if-exists .env.$mode --env-file-if-exists .env.$mode.local …

(to replicate Next.js's behavior, etc., you'd need a slightly different variation of this, but not much different)

Of course no one is going to take the time or effort to write out all that every time they run a command! 🤦 So by this point, you may as well just prefix/wrap the command with dotenvx (which I saw mentioned above as a workaround for the lack of this feature). Or — since dotenvx may not load your env in exactly the way you need out of the box (it doesn't provide Vite's load behavior that I'm wanting, for example), you could extract extract your own wrapper command that loads env before passing off to the main command... which is what I ended up doing.

I think an even better option than having to remember to prefix with dotenvx every time you run drizzle-kit or another tool, would be the ability to load your env files (using whatever common bootstrap script you want to use for your project) from that tool's config.js file. This way, it would get automatically loaded any time you used that tool, without having to remember.

We're doing this from our vite.config.ts and playwright.config.ts files, for example, without any troubles. So I had hoped that I would be able to do the same thing from our drizzle.config.ts — and simply call our project's loadEnv loader from there. What I discovered, however, was that by the time we're inside of drizzle.config.ts, it's too late to call loadEnv, because drizzle-kit has already loaded the base .env file and populated process.env from it. 😞

An example of when this subtle bug happens: We define DATABASE_URL both in the base .env file and in the more specific .env.development file, with the intention that the value in .env.development will take precedence and be used instead of the value from the less-specific .env. What happens instead is that vite's loadEnv (which internally uses parse from their slightly-patched dotenv) sees that DATABASE_URL has already been set in process.env (which is given the highest precedence), assumes it was manually set by the user (rather than from the lowest-precedence .env as it was intended to be seen) and ignores the value from .env.development when it should be using that value.

This kind of subtle behavior can be tricky to even notice or figure out.

And this is why I think the ability to opt out of any env loading by drizzle-kit with a --no-env option is the most important addition proposed here. ❤️

---

I agree with @jbergstroem that it would be better to not use NODE_ENV to decide which env files to load — or to do much branching at all based on that — as platformatichq.com wisely points out. Too bad Bun and Next.js don't agree.

One limitation of using NODE_ENV that Vite seems to handle better is that it limits you to one of the "standard" values for NODE_ENV ("development" or "production", maybe also "test" depending whom you ask). Vite is more flexible and lets you specify any custom mode name you want, for example, "staging", in which case it would load .env.staging. 🎉

As the Vite docs point out, NODE_ENV and modes are two different concepts. They even go so far as to suggest using your .env.${mode} file to specify which NODE_ENV value should be used for that mode. Rather than the other way around. 🤔

The only thing I don't like about Vite's loader is that there's no built-in way to set the mode via an environment variable. (They expect you to set the mode with vite --mode, but that only works if you're actually running the vite command itself.) But that's okay; their loadEnv is flexible enough and it's easy enough to just handle this the way want in our own loader: we just pick our own variable to control the mode, and pass that to loadEnv, like vite.loadEnv(process.env.MODE).

In summary, the main advantage to this approach is that we get the exact same env loading behavior (automatic loading of the same 4 file patterns) across our whole project — no subtle but surprising differences in behavior between the various different tools used in the project (such as drizzle-kit).