How to have both production and test databases in Drizzle ORM?

[ridhwaans]

here is my package.json for the db package, which depends on the webapp's .env file

scripts": {
    "db:check": "dotenv -e ../../apps/web/.env.local drizzle-kit check",
    "db:generate": "dotenv -e ../../apps/web/.env.local drizzle-kit generate",
    "db:migrate": "dotenv -e ../../apps/web/.env.local drizzle-kit migrate",
    "db:migrate:drop": "dotenv -e ../../apps/web/.env.local drizzle-kit drop",
    "db:pull": "dotenv -e ../../apps/web/.env.local drizzle-kit introspect",
    "db:push": "dotenv -e ../../apps/web/.env.local drizzle-kit push",
    "db:studio": "dotenv -e ../../apps/web/.env.local drizzle-kit studio",
    "db:up": "dotenv -e ../../apps/web/.env.local drizzle-kit up",
    "lint": "eslint .  --max-warnings 0",
    "typecheck": "tsc"
  },
"dependencies": {
    "drizzle-orm": "^0.30.10",
    ...

config

import type { Config } from 'drizzle-kit';

import { env } from '@turborepo/env/web';

export default {
  dialect: 'sqlite',
  schema: './src/schema/index.ts',
  out: './drizzle',
  dbCredentials: {
    url: env.DATABASE_URL,
  },
} satisfies Config;

Would i have to have two sets of env files and two sets of db commands in the package.json section, or is there a better solution?

thanks in advance

[Strernd]

It depends on what you want to achieve. There are many ways.

1. Use the drizzle-kit commands and provide a drizzle config. Create one drizzle config for dev using DB_URL_DEV, one for prod using DB_URL_PROD
2. Change your DATABASE_URL before running
3. Create a Makefile and pass an environment flag that sets the appropriate DATABASE_URL before command execution.

We're running a version of 3, because we have multi client migrations.

[ridhwaans]

I use turbopack, not makefile, for option 3
is yours a remote database provider or self-hosted?