drizzle-kit: Proxy is not implemented for D1

[harik-crayond]

When running Drizzle Studio on https://local.drizzle.studio, I encountered an error message indicating that the proxy is not implemented for D1

Drizzle Studio version: "^0.20.17"
Node.js version: "v20.10.0"

This issue started occurring after updating to Drizzle Studio version 0.20.16

[KyGuy2002]

Seeing this as well.

[DarkDev365]

The reason for this is that the D1 proxy implementation is truly not implemented within the latest drizzle-kit.

The code in question is this:

if (driver2 === "d1") {
    const proxy = async (params) => {
        throw new Error("Proxy is not implemented for D1");
    };
    return {
        dialect: "sqlite",
        proxy,
        customDefaults
    };
}

There are reports of this working fine with previous releases of drizzle-kit but I've not attempted to confirm since drizzle-kit forces the end user to use the latest drizzle-kit.

Since drizzle-kit is not yet open sourced it is problematic to attempt to patch it, but folks wanting to explore implementing this locally can use the associated patch-package as a starting point:
drizzle-kit+0.20.17.patch

The patch can be applied to drizzle-kit 0.20.17. It is admittedly not the best code, but I wanted to provide something that people can verify/validate/build on.

Something to note: due to Cloudflare D1 prescribing to higher security over higher limits they have the SQLITE_LIMIT_COMPOUND_SELECT currently set to 5. So if you have more than 5 tables in your DB you will not be able to return the summary of row counts for all tables.

Note: If anyone has further follow questions I'd be happy to help troubleshoot/provide guidance. Since drizzle-kit is currently closed source I've created this throwaway account so might be slow to respond (legal implications for tinkering with closed code).

[KyGuy2002]

Thank you for all these details and the patch!

I am curious why this works when using a local d1 database with miniflare, but not when its hosted by cloudflare. Wouldn't it be the same implementation for drizzle?

I followed some tutorials to enable local and remote database connections with studio, but I guess recently they added that d1 check and broke that. Unfortunate that its not open source.

[DarkDev365]

Local D1 with miniflare actually makes use of a local sqllite DB file within your local .wrangler folder at:
/.wrangler/state/v3/d1/miniflare-D1DatabaseObject/<RandomID>.sqlite

@KyGuy2002 - Is this the tutorial you followed?
https://kevinkipp.com/blog/going-full-stack-on-astro-with-cloudflare-d1-and-drizzle/

Since if it is, you will notice the fallback to leverage LOCAL_DB_PATH + better-sqlite using the path I highlighted above:

// Use better-sqlite driver for local development
export default LOCAL_DB_PATH
	? ({
			schema: "./src/schema.ts",
			driver: "better-sqlite",
			dbCredentials: {
				url: LOCAL_DB_PATH,
			},
		} satisfies Config)

The other piece of the puzzle being the package.json entry point addition:

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

[KyGuy2002]

That was the tutorial I used.

That makes sense now, so wrangler (miniflare) reads and writes to the sqlite files for its needs, and drizzle studio does the same thing. But neither have any knowledge of eachother, they just share a common standard data source.

And when using a cloudflare hosted d1 db, their infrastructure is between the sqlite files and drizzle, and drizzle needs an implementation to be able to communicate with cloudflares api to access those sqlite files. Drizzle just never wrote one.

Why did it work in the past then? Was the cloudflare api just close enough to some other one that they had already implemented that it worked in most cases? Wouldn't it have to use cloudflares own rest api, since its local and not hosted on cloudflares infrastrucure? Do they even have a rest api to access d1 outside of workers or functions?

Somewhere drizzle hinted at offering a hosted version of drizzle studio in the future. I guess it would have to be a worker or pages project that you deploy to your own cloudflare account and bind the database?

[DarkDev365]

@KyGuy2002 - the older version of drizzle-kit (0.20.14) had this code instead of the code block referenced in my initial reply:

if (drizzleConfig.driver === "d1") {
    const { drizzle: drizzle2 } = await Promise.resolve().then(() => (init_driver(), driver_exports));
    const { execute: execute2 } = await Promise.resolve().then(() => (init_wrangler_client(), wrangler_client_exports));
    return {
        db: drizzle2(
            execute2,
            drizzleConfig.dbCredentials.wranglerConfigPath,
            drizzleConfig.dbCredentials.dbName,
            { logger }
        ),
        type: "sqlite",
         schema: models.sqliteSchema
    };
}

That code allowed it to make use of the wrangler cli client (effectively having their own D1 driver) to communicate with the hosted D1 databases. Since drzzle-kit is closed source it is hard to track down all the changes within that change set.

Also due to drizzle-kit forcing users to the latest version: it is hard to verify the previously working state.

I'm not aware of the drizzle team's plans for a hosted version of studio, but I'd imagine that it'd be a subscription based offering and would leverage the cloudflare API + authentication to make that work.