Unable to Load .env Database URL in Drizzle-Kit's drizzle.config.ts with Next.js 14

[riculum]

I am currently leveraging Drizzle-Kit to manage my database tables, specifically using drizzle-kit push:pg. However, in order to execute this command, it necessitates a drizzle.config.ts file containing essential configurations like schema, output, and credentials.

Traditionally, I rely on .env files for storing sensitive credentials. But when integrating with Next.js 14, drizzle-kit push:pg seems unable to access the database URL stored within the .env file. It appears that the environment variable isn't being picked up.

Considering security best practices and the risk associated with storing credentials directly in the drizzle.config.ts file, especially when dealing with version control (Git), I'm reluctant to include these sensitive details there. This approach also requires managing credentials in two separate places, which is far from ideal.

Is there a recommended method or workaround to enable drizzle.config.ts to access the .env file for the database URL while utilizing Drizzle Kit in conjunction with Next.js 14? I'm aiming to maintain a secure approach to handling credentials without compromising on functionality. Any insights or alternative strategies would be greatly appreciated.

[Angelelz]

Next.js makes the environment variables in .env file available for you when you it. However, you want to use a drizzle-kit command running outside next.js (it's a CLI tool), for that, you need to provide the environment variables yourself.

The most popular approach is to use a tool like dotenv, although lately, node in it's latest version seems to want to support this out of the box. Oh, bun supports it out of the box since v1.

[deadcoder0904]

@Angelelz how to use node.js environment variable in combination with drizzle-kit?

for example, with tsx i use it like this:

"db:seed": "node --import tsx --env-file .env.development ./src/app/seed/insert.ts",
"db:delete": "node --import tsx --env-file .env.development ./seed/delete.ts",

but i can't seem to figure out why my seed script isn't working?

import * as dotenv from "dotenv"
import type { Config } from "drizzle-kit"

// bug in drizzle: see https://github.com/drizzle-team/drizzle-orm/issues/1228
// import { DATABASE_PATH } from '@/app/lib/env/server'

const NODE_ENV = process.env.NODE_ENV
console.log({ x: process.env.NODE_ENV })
const path = NODE_ENV ? ".env.development" : ".env.production"

dotenv.config({ path })

const DATABASE_PATH = process.env.DATABASE_PATH

console.log({ NODE_ENV: process.env.NODE_ENV, DATABASE_PATH })

if (!DATABASE_PATH) {
	console.log("bla")
	throw new Error("DATABASE_PATH is not set!")
}

export default {
	schema: "./src/app/db/schema.ts",
	out: "./src/app/db/migrations",
	driver: "better-sqlite",
	dbCredentials: {
		url: DATABASE_PATH,
	},
	verbose: true,
} satisfies Config

Also, I get this in console:

{ x: undefined }

even though I have set NODE_ENV in both .env.development & .env.production files.

[deadcoder0904]

now i know the problem is dotenv.config loads environment variable after path is specified so NODE_ENV is not set by then where it is undefined.

to make it work, i just removed NODE_ENV check & directly used dotenv.config({ path: ".env.development" }) so it became:

import * as dotenv from "dotenv"
import type { Config } from "drizzle-kit"
// bug in drizzle: see https://github.com/drizzle-team/drizzle-orm/issues/1228
// import { DATABASE_PATH } from '@/app/lib/env/server'

dotenv.config({ path: ".env.development" })

const DATABASE_PATH = process.env.DATABASE_PATH

export default {
	schema: "./src/app/db/schema.ts",
	out: "./src/app/db/migrations",
	driver: "better-sqlite",
	dbCredentials: {
		url: DATABASE_PATH,
	},
	verbose: true,
} satisfies Config

This is working for seeding & deleting.

        "db:push": "drizzle-kit push:sqlite --config drizzle.config.ts",
		"db:generate": "drizzle-kit generate:sqlite --config drizzle.config.ts",
		"db:studio": "drizzle-kit studio --host localhost --port 3002 --verbose --config drizzle.config.ts",
		"db:seed": "node --import tsx --env-file .env.development ./src/app/seed/insert.ts",
		"db:delete": "node --import tsx --env-file .env.development ./src/app/seed/delete.ts",

Although I wished it was easier in Next.js as it is in Remix. But yeah Next.js makes environment variables extremely hard with 5 different methods. Anyways, I'll probably use Bun when its released on Windows to drop tsx as it supports Environment Variables out of the box.

If I want something to work for a production script, I'll probably duplicate the scripts or use cross-env with NODE_ENV. I think that should work Idk.

[reisblucas]

Found this discussion when I was finding some approach to multiple envs with multiples drizzle-{env}.config.ts as docs suggests, but I can't find the pros of that, so I leave in one config with dotenv and it's enough.

stack: Nestjs + Drizzle + PostgreSQL(pg)

// nestjs has a builtin dotenv inside `@nestjs/config`, yet it can export to be used outside the nest context.
// So, you'll need to install `dotenv` or do some fs script.

package.json:

{
  "scripts": {
    "start:dev": "NODE_ENV=local nest start --watch",
    "start:debug": "nest start --debug --watch",
    "start:prod": "NODE_ENV=prod node dist/main",
    "generate:local": "NODE_ENV=local exec drizzle-kit generate",
    "generate:dev": "NODE_ENV=dev exec drizzle-kit generate",
    "generate:prod": "NODE_ENV=prod exec drizzle-kit generate",
    "migrate:local": "NODE_ENV=local exec drizzle-kit migrate",
    "migrate:dev": "NODE_ENV=dev exec drizzle-kit migrate"
}

Here's my approach to that:

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';
import * as dotenv from 'dotenv';

const NODE_ENV = process.env.NODE_ENV;
console.log('NODE_ENV', NODE_ENV);
const isLocalEnv = NODE_ENV === 'local';
dotenv.config({ // because of that correct read of env
  path: isLocalEnv ? '.env.local' : '.env.production',
});

const DATABASE_URL = process.env.DATABASE_URL;
if (isLocalEnv) {
  console.log('DATABASE_URL', DATABASE_URL);
}

if (!DATABASE_URL) {
  throw new Error(
    'Cannot read environment, probably DATABASE_URL was not setted',
  );
}

export default defineConfig({
  out: `./drizzle/${NODE_ENV}`,
  schema: './src/db/schema/**.schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
    host: process.env.DATABASE_HOST!,
    user: process.env.DATABASE_USER!,
    password: process.env.DATABASE_PASSWORD!,
    database: process.env.DATABASE_DB!,
    ssl: isLocalEnv ? false : 'require', // and the correct use of ssl, this hole condition should be done
  },
  schemaFilter: ['public'],
  breakpoints: true,
});