How can I use Drizzle Studio with Cloudflare D1 in local environment (connect to D1 local instance)

[JH365]

I am using Drizzle and Cloudflare D1 in my project and I like Drizzle a lot. And while I was trying Drizzle Studio, I found it connected to live database instead of local dev environment. I'd like to know if there is a way to connect to D1 local instance?

[rusintez]

Gm @JH365,

please check this excerpt of drizzle-kit code for hints :P

// src/orm-extenstions/d1-driver/wrangler-client.ts
var wrangler_client_exports = {};
__export(wrangler_client_exports, {
  execute: () => execute
});
async function execute(query, configPath, dbName) {
  try {
    const response = await $`NO_D1_WARNING=true wrangler d1 execute ${dbName} --config=${configPath} --command=${query} --json`;
    return { results: JSON.parse(response.stdout)[0].results };
  } catch (e) {
    try {
      await $`NO_D1_WARNING=true wrangler d1 execute ${dbName} --config=${configPath} --command=${query}`;
    } catch (e1) {
      throw new Error(e1.stdout);
    }
    throw e;
  }
}

[jwhits]

I am having the same issue, and I still don't get how I can pass the --local flag

[ProximaB]

I have the same problem.
We should probably address this by enabling the passing of additional options to the Drizzle-Kit Studio cli.
drizzle-kit studio --local
This adjustment could have a broader impact and would create some assumptions on how to develop similar features. But to postpone that, without going outside d1-driver context we could just extend d1 config definition. Till you decide if/how you want to handle this kind of cases.

{
    driver: "d1";
    dbCredentials: {
        wranglerConfigPath: string;
        dbName: string;
        local:bool
    };
}

[flexchar]

Looking at this code snippet, one could simply append dbName with --local

[OJFord]

Code injection as a feature, nice 😅

[flexchar]

Is there a clear consensus on how this should be addressed now? :)

[ProximaB]

Unfortunately drizzle-kit is not open sourced. I wasn't aware of that.
There is already posted similar issue with workaround from November last year.

const localConfig = {
  schema: "./schema/db/index.ts",
  driver: "better-sqlite",
  dbCredentials: {
    url: process.env.DB_URL!,
  },
} satisfies Config;

export default process.env.NODE_ENV === "production" ? cloudflareConfig : localConfig;

https://github.com/drizzle-team/drizzle-kit-mirror/issues/242

[flexchar]

Have you experienced Mapping SQL input into an array of statements error bubbling from Wrangler when trying to use Drizzle Studio?

[ajzbc]

Came up with a similar temp solution

{
  "scripts": {
    "db:studio": "cross-env LOCAL_DB_PATH=$(find .wrangler/state/v3/d1/miniflare-D1DatabaseObject -type f -name '*.sqlite' -print -quit) drizzle-kit studio"
  }
}

Instead of specifying the sqlite file path, just grabbing the path of the *.sqlite file in .wrangler/state/v3/d1/miniflare-D1DatabaseObject

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

export default process.env.LOCAL_DB_PATH
	? ({
			schema: './src/db/schema.ts',
			driver: 'better-sqlite',
			dbCredentials: {
				url: process.env.LOCAL_DB_PATH!
			}
		} satisfies Config)
	: ({
			schema: './src/db/schema.ts',
			out: './drizzle',
			driver: 'd1',
			dbCredentials: {
				wranglerConfigPath: 'wrangler.toml',
				dbName: 'NAME'
			}
		} satisfies Config);

Haven't been able to get it working with the remote D1 db though https://github.com/drizzle-team/drizzle-kit-mirror/issues/289

[predaytor]

it doesn't seem to pick up the latest version of the database? this worked for me (select file by last modified date):

cross-env LOCAL_DB_PATH=$(find .wrangler/state/v3/d1/miniflare-D1DatabaseObject -type f -name '*.sqlite' -print0 | xargs -0 ls -t | head -1) drizzle-kit studio

[clibequilibrium]

Joining the thread to get it working on Windows.

Create start-studio.ps1 with this content

$env:DB_URL=(Get-ChildItem .wrangler\state\v3\d1\miniflare-D1DatabaseObject\*.sqlite).FullName
drizzle-kit studio

drizzle.config.ts

export default {
	schema: './src/lib/server/schema.ts',
	out: './migrations',
	driver: 'better-sqlite',
	dbCredentials: {
		url: process.env.DB_URL!
	}
} satisfies Config;

package.json

  "scripts": {
    "db:studio": "powershell -ExecutionPolicy Bypass -File start-studio.ps1",
  },

Repeat the same steps for unix environment or use cross-env

[carloslato]

Thanks, work for me in windows but with a little modification to prevent duplication of string db route

$env:LOCAL_DB_PATH = ((Get-ChildItem .wrangler\state\v3\d1\miniflare-D1DatabaseObject*.sqlite | Select-Object -First 1).FullName)

[third774]

Thanks for sharing the tips, y'all! Here's the solution I came up with piecing everything together. I'm overall pretty happy with it since I can use Drizzle Studio with my local db, the preview db (for pages previews), and the prod db.

https://kevinkipp.com/blog/going-full-stack-on-astro-with-cloudflare-d1-and-drizzle/

[stuartin]

Just went through getting this working and settled on the below.

Anytime the NODE_ENV is production it'll use the D1 instance on Cloudflare, otherwise it'll use the local one setup by wrangler. This essentially applies to any command that uses drizzle.config.ts such as migrations as well when doing deployments.

As per the drizzle docs, you'll need to create the relevant token and db in cloudflare

# .env

CLOUDFLARE_D1_ACCOUNT_ID=xxx
CLOUDFLARE_D1_API_TOKEN=xxx

// drizzle.config.ts

import { defineConfig } from 'drizzle-kit';
import fs from 'fs';
import path from 'path';

function getLocalD1DB() {
	try {
		const basePath = path.resolve('.wrangler');
		const dbFile = fs
			.readdirSync(basePath, { encoding: 'utf-8', recursive: true })
			.find((f) => f.endsWith('.sqlite'));

		if (!dbFile) {
			throw new Error(`.sqlite file not found in ${basePath}`);
		}

		const url = path.resolve(basePath, dbFile);
		return url;
	} catch (err) {
		console.log(`Error  ${err.message}`);
	}
}

export default defineConfig({
	dialect: 'sqlite',
	schema: './schema.ts',
	out: './drizzle',
	...(process.env.NODE_ENV === 'production'
		? {
				driver: 'd1-http',
				dbCredentials: {
					accountId: process.env.CLOUDFLARE_D1_ACCOUNT_ID,
					databaseId: 'xxx',
					token: process.env.CLOUDFLARE_D1_API_TOKEN
				}
			}
		: {
				dbCredentials: {
					url: getLocalD1DB()
				}
			})
});

// package.json

{
	"scripts": {
		"studio:dev": "drizzle-kit studio",
		"studio:prd": "cross-env NODE_ENV=production drizzle-kit studio"
	}
}

[third774]

I've updated my blog post with the latest for local, preview, and prod environments!
https://kevinkipp.com/blog/going-full-stack-on-astro-with-cloudflare-d1-and-drizzle/

[eli-front]

This works great!

[zavierrodriguez]

I've updated my blog post with the latest for local, preview, and prod environments! https://kevinkipp.com/blog/going-full-stack-on-astro-with-cloudflare-d1-and-drizzle/

@third774 Thanks so much! Great write up!

[mohsentaleb]

This worked for me. Thanks!

[AlexBlokh]

This is now available in both Drizzle Studio for local development and with our Chrome Extension 🎉

[YannicEl]

this works great. thank you

[AlexBlokh]

we will further investigate on how to make it properly work locally

[bosconian-dynamics]

@AlexBlokh thank you! :)

The prior d1 Kit config that was in v0.21 seemed to work fine for Kit, just not for Studio.

Some unsolicited feedback:

The parity of using d1 configurations for both local Kit and ORM was really intuitive, albeit we provided them with disparate parameters - ORM gets a binding and Kit got the wrangler config path and database name. If Drizzle team has not already looked into it, getPlatformProxy() could provide Kit/Studio a means to grab a binding and expose a nice interface for Drizzle users (though note the bug in workers-sdk#5768 which is making getPlatformProxy() fail in CF Pages projects, for the moment):

export default defineConfig({
  driver: 'd1',
  // ...
  dbCredentials: {
    wranglerConfigPath: 'wrangler.toml', // Optional - default undefined
    wranglerEnvironment: 'production',   // Optional - default undefined
    bindingName: 'DB', // Used by Kit driver to obtain the actual binding from env
  }
});

Or the Kit-config interface could accept a binding to expose the same interface as the d1 ORM config, and the responsibility of obtaining the binding by using getPlatformProxy() could be left up to users instead.

Both options hand off all responsibility for properly parsing the Wrangler config and sorting out the correct binding back to Wrangler itself, and allows users to target configured bindings in the same manner in which they're used elsewhere.

In the interests of simplifying the user experience and substantially lowering the barrier to entry, I'd also really love to see a d1-http ORM config wrapping sqlite-proxy such that we'd end up with a nice straightforward

Target	ORM	Kit/Studio (drizzle.config.ts)
Local	d1	d1
Remote	d1-http	d1-http

Thank you for your work!

[clibequilibrium]

Is it me or Chrome Extension not doing anything on Cloudflare Dashboard ? I tried double clicking and nothing happens

[ceifa]

@AlexBlokh there is an issue or something that we can track it?

[cosbgn]

For the ORM:

The simplest and cleanest way would be an orm connector which uses binding when available (in production) and fallsback to rest API when not available. This way we could still connect to a remote db when developing and still get the best performance while in production.

For KIT:
d1-http is fine, you just miss studio in local but for me that's not the end of the world. Not being able to connect to a remote db from local is (ORM).

[bosconian-dynamics]

You can presently hook up ORM to a remote D1 instance via the HTTP Proxy client drizzle/sqlite-proxy.

But yeah, I'm all for anything which might simplify the Drizzle D1 dev experience. 4 different configurations leveraging 4 different drivers to connect all Drizzle packages to both local and remote D1 contexts is a pretty substantial barrier to entry.

[brettimus]

in case anyone else had troubles wiring things up from scratch (bc they didn't have a local D1 database present in .wrangler), i had to execute a simple command first to make the database show up locally

npx wrangler d1 execute DB_NAME --local --command='SELECT 1'

this created the necessary files in .wrangler for the local setups listed in this discussion thread to work

[ivo22dev]

So which would be the official way to setup thedrizzle.config.ts with local and production env?

[sassanh]

I started using drizzle a month ago and none of these solutions worked for me, I hope it hasn't changed since then and it works for you, I had to set it up like this:

import { defineConfig } from "drizzle-kit";

export default process.env.CLOUDFLARE_DATABASE_ID
  ? defineConfig({
      dialect: "sqlite",
      driver: "d1-http",
      out: "drizzle",
      schema: "src/db/schema",
      dbCredentials: {
        accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
        databaseId: process.env.CLOUDFLARE_DATABASE_ID,
        token: process.env.CLOUDFLARE_D1_TOKEN!,
      },
    })
  : defineConfig({
      dialect: "sqlite",
      out: "drizzle",
      schema: "src/db/schema",
      dbCredentials: {
        url: ".wrangler/state/v3/d1/miniflare-D1DatabaseObject/<sqlite_filename>.sqlite",
      },
    });

Btw, it's by no means official, achieved by try and error.

[ivo22dev]

It works like a charm, thanks bud 🙂‍↕️🙂‍↕️ btw, where do you write the .env variables? I was writing them in .dev.vars but idk if that is the correct way becase as far as i understand, they're only for development.

Thanks!

[sassanh]

I set them in the command line, with a space in the beginning of the line so that it doesn't save in the history, but you should be able to read them from .env if you initiate dotenv or similar packages on top.

[noelukwa]

Here's what works for me

//drizzle.config.ts
import type { Config } from 'drizzle-kit';


import fs from "fs";
import path from "path";


const getLocalD1 = () => {
    try {
        const basePath = path.resolve('.wrangler');
        const dbFile = fs
            .readdirSync(basePath, { encoding: 'utf-8', recursive: true })
            .find((f) => f.endsWith('.sqlite'));

        if (!dbFile) {
            throw new Error(`.sqlite file not found in ${basePath}`);
        }

        const url = path.resolve(basePath, dbFile);
        return url;
    } catch (err) {
        console.log(`Error  ${err}`);
    }
}

const isProd = () => process.env.NODE_ENV === 'production'

const getCredentials = () => {
    const prod = {
        driver: 'd1-http',
        dbCredentials: {
            accountId: process.env.CLOUDFLARE_D1_ACCOUNT_ID,
            databaseId: 'xxx',
            token: process.env.CLOUDFLARE_D1_API_TOKEN
        }

    }

    const dev = {
        dbCredentials: {
            url: getLocalD1()
        }
    }
    return isProd() ? prod : dev

}

export default {
    schema: './src/schemas/*.ts',
    out: './drizzle',
    dialect: "sqlite",
    ...getCredentials()
} satisfies Config;

[bdmackie]

Hi, this still seems to be an issue for me.

The above script works for a single database (thank you) but if you have a worker with, say, 3 databases, it seems a case of trial and error to find the right sqlite file. Anyone know of a better way (e.g. to get file name from database id / name)?

[taivo]

I've had to deal with this recently and decided to publish 2 packages that can save myself and others time in the future. One is the d1-http driver similar to code in drizzle-kit, but uses cloudflare SDK https://www.npmjs.com/package/@nerdfolio/drizzle-d1-proxy

The other is https://www.npmjs.com/package/@nerdfolio/drizzle-d1-helpers , which combines wrangler config reading with getPlatformProxy and the d1-http driver above to cover all the usage situations I can think of. It also generates the sqlite file hash based on Cloudflare's instruction instead of searching for it on disk (as there are situations where you may have multiple .sqlite files in the same directory). fyi @bdmackie

Hope it's helpful to you all!

[bdmackie]

Nice!

I had got it done with a cruder approach by 'marking' each local database with a table of a certain name, then using better-sqlite3 to look for that table in a drizzle.config.local.ts file. It works well and has the comfort factor of sighting the marker table in studio.

But... what you've got there is very cool. The knowledge for replicating the hash would provide a reliable and cleaner alternative to my 'marking' approach.

[brettimus]

This is awesome to see! A colleague and I spent some time replicating the hash at one point and thought it should be published as a utility but then never published it 😅

[taivo]

While researching for a more robust solution I luckily stumbled on these 2 links that explain the hash. cloudflare/workers-sdk#4548 and https://github.com/cloudflare/miniflare/releases/tag/v3.20230918.0

Ideally Cloudflare should just export the hash function for us to use but they haven't yet so I thought making it a part of this mini lib would be much better than copy pasta every time. Same regarding the drizzle-kit d1-http driver.

[MrChrisRodriguez]

Thanks so much @taivo. For folks newly running into this without all the context (why we need a D1 proxy, etc.) a complete example would help a lot. I'm just starting to wrap my head around this or I'd PR an example myself. If I can figure this out, I'll try to write something up.

[2-fly-4-ai]

Why can't I run my local next start command using the node env as production? Is always just seems to default to the development mode. Only when I run wrangler does it allow me to set the environmental variables to production. I have tried overriding them by setting NODE_ENV in the local .env and all.

[MrChrisRodriguez]

For posterity: if you're arriving here to figure out how to use your local Miniflare D1 in development and the remote D1 database in prod, here's what you need to do:

Install drizzle-d1-helpers

pnpm add -D @nerdfolio/drizzle-d1-helpers

Update drizzle.config.ts

import dotenv from "dotenv";
import { defineConfig } from "drizzle-kit";
import { D1Helper } from "@nerdfolio/drizzle-d1-helpers";

// I prefer to load from here than to duplicate the vars in .env
dotenv.config({ path: "./.dev.vars" });

// Replace with your D1 database binding name
const crawledDbHelper = D1Helper.get("DB");
const isProd = () => process.env.NODE_ENV === 'production'

const getCredentials = () => {
    const prod = {
        driver: 'd1-http',
        dbCredentials: {
            ...crawledDbHelper.withCfCredentials(
                process.env.CLOUDFLARE_ACCOUNT_ID,
                process.env.CLOUDFLARE_D1_TOKEN,
            ).proxyCredentials,
        }
    }

    const dev = {
        dbCredentials: {
            url: crawledDbHelper.sqliteLocalFileCredentials.url
        }
    }
    return isProd() ? prod : dev
}

export default defineConfig({
	schema: "./src/db/schema.ts",
	out: "./drizzle",
	dialect: "sqlite",
	...getCredentials()
});

[2-fly-4-ai]

Thanks!

[olafghanizadeh]

@MrChrisRodriguez The only issue with using .dev.vars is that the wrangler typegen would include those variables, which is only needed for local development.

[MrChrisRodriguez]

@olafghanizadeh I'm very new to this so apologies if I'm misunderstanding something. Just want to make sure I've got it right.

typegen

Yes, when I run wrangler types it does add them to the interface:

wrangler types

 ⛅️ wrangler 4.19.1
───────────────────
Generating project types...

declare namespace Cloudflare {
	interface Env {
		CLOUDFLARE_ACCOUNT_ID: string;
		CLOUDFLARE_DATABASE_ID: string;
		CLOUDFLARE_D1_TOKEN: string;
		CRAWLED_DB: D1Database;
		OTHER_SECRET: string;
	}
}

dev vs prod

Here's the part I'm not clear on and could use your clarification on: what do you mean that those vars are "only needed for local development?"

My (anecdotally derived, not well-researched) understanding is that:

• Cloudflare injects those env vars into production when (a) it sees the binding in wrangler.jsonc, or (b) when I've pushed specific values (like OTHER_SECRET) into the secrets store.
• In prod, Drizzle is using the d1-http driver (instead of a direct connection) and will be reading those credentials for the driver from env vars
• So having typegen add them to the interface isn't actually problematic.

There's a very good chance I'm wrong / missing something. Would love the clarification if you have any. Getting through the docs on this has been a bit challenging since Cloudflare doesn't natively support this so it's a layer cake of workarounds.

[kumardeo]

Inspired from @nerdfolio/drizzle-d1-helpers, here is my configuation:

drizzle.config.ts:

import crypto from 'node:crypto';
import { existsSync } from 'node:fs';
import path from 'node:path';
import type { Config } from 'drizzle-kit';
// using './node_modules/wrangler' instead of 'wrangler' because it may resolve to 'wrangler.json'
import { unstable_readConfig } from './node_modules/wrangler';

function durableObjectNamespaceIdFromName(uniqueKey: string, name: string) {
  /**
   * In v3.2, miniflare uses durable object to implement D1 and hashes the local sqlite filename.
   *
   * See the following for more context:
   * https://github.com/cloudflare/workers-sdk/issues/4548 (understand the hash of the local D1 filename)
   * https://github.com/cloudflare/miniflare/releases/tag/v3.20230918.0
   *
   * This function is copied from these links
   */
  const key = crypto.createHash('sha256').update(uniqueKey).digest();
  const nameHmac = crypto.createHmac('sha256', key).update(name).digest().subarray(0, 16);
  const hmac = crypto.createHmac('sha256', key).update(nameHmac).digest().subarray(0, 16);
  return Buffer.concat([nameHmac, hmac]).toString('hex');
}

function getD1BindingInfo(binding?: string, { persistTo }: { persistTo?: string } = {}) {
  const { d1_databases, configPath } = unstable_readConfig({});
  if (d1_databases.length === 0) {
    throw new Error('No D1 binding exists in config');
  }
  if (d1_databases.length > 1 && !binding) {
    throw new Error("Argument 'binding' is required when more than 1 D1 bindings exist in config");
  }
  let bindingConfig: (typeof d1_databases)[0] | undefined;
  if (binding) {
    bindingConfig = d1_databases.find((d1) => d1.binding === binding);
    if (!bindingConfig) {
      throw new Error(`Could not find D1 binding '${binding}' in config`);
    }
  } else {
    bindingConfig = d1_databases[0];
  }

  const localDatabaseId = bindingConfig.preview_database_id ?? bindingConfig.database_id;

  if (!localDatabaseId) {
    throw new Error(`Neither 'preview_database_id' nor 'database_id' is set for D1 binding '${bindingConfig.binding}'`);
  }

  const wranglerConfigDir = configPath ? path.dirname(configPath) : undefined;
  const wranglerStateDir = persistTo ?? path.relative('.', path.join(wranglerConfigDir ?? '', '.wrangler/state/v3'));

  const uniqueKey = 'miniflare-D1DatabaseObject';
  const miniflarePath = `${wranglerStateDir}/d1/${uniqueKey}`;
  const hash = durableObjectNamespaceIdFromName(uniqueKey, localDatabaseId);
  const filename = path.join(miniflarePath, `${hash}.sqlite`);

  if (!existsSync(filename)) {
    throw new Error(`Could not find sqlite file [${filename}] for databaseId [${localDatabaseId}]`);
  }

  return {
    binding: bindingConfig.binding,
    databaseId: localDatabaseId,
    localSqliteFile: filename,
  };
}

const ENV_CF_D1_BINDING = 'CLOUDFLARE_D1_DATABASE_BINDING' as const;
const ENV_CF_D1_ACCOUNT_ID = 'CLOUDFLARE_D1_ACCOUNT_ID' as const;
const ENV_CF_D1_API_TOKEN = 'CLOUDFLARE_D1_API_TOKEN' as const;

const useRemoteDatabase = process.env.NODE_ENV === 'production';
const databaseBinding = process.env[ENV_CF_D1_BINDING];
const binding = getD1BindingInfo(databaseBinding);

console.log(`D1 Binding: ${binding.binding}`);
console.log(`Database Id: ${binding.databaseId}`);
if (useRemoteDatabase) {
  const missingEnvVars = [ENV_CF_D1_ACCOUNT_ID, ENV_CF_D1_API_TOKEN].filter((v) => !process.env[v]);

  console.log('Mode: remote (using remote Cloudflare D1 database)');

  if (missingEnvVars.length > 0) {
    throw new Error(`Missing required environment variable${missingEnvVars.length > 1 ? 's' : ''}: ${missingEnvVars.join(', ')}.`);
  }
} else {
  console.log(`Mode: local (using local database found at ${binding.localSqliteFile})`);
}

// https://orm.drizzle.team/docs/drizzle-config-file
export default {
  out: './src/server/db/migrations',
  schema: './src/server/db/schema',
  dialect: 'sqlite',
  ...(useRemoteDatabase
    ? {
        driver: 'd1-http',
        dbCredentials: {
          databaseId: binding.databaseId,
          accountId: process.env[ENV_CF_D1_ACCOUNT_ID],
          token: process.env[ENV_CF_D1_API_TOKEN],
        },
      }
    : {
        dbCredentials: {
          url: `file:${binding.localSqliteFile}`,
        },
      }),
} satisfies Config;

.env:

CLOUDFLARE_D1_ACCOUNT_ID=xxxxxxxxx
CLOUDFLARE_D1_API_TOKEN=xxxxxxxxx
# CLOUDFLARE_D1_DATABASE_BINDING=DB

package.json:

{
  // ...
  "scripts": {
    // ...
    "db:studio": "drizzle-kit studio",
    "db:studio:prod": "NODE_ENV=production drizzle-kit studio"
    // ...
  }
  // ...
}

[piotrrinv]

Works like a charm, thank you!