drizzle-orm: [BUG] Cloudflare D1: drizzle-kit push is not working (error 7500 SQLITE_AUTH)

Report hasn’t been filed before.

  • I have verified that the bug I’m about to report hasn’t been filed before.

What version of drizzle-orm are you using?

0.38.0

What version of drizzle-kit are you using?

0.30.0

Other packages

No response

Describe the Bug

I followed the docs (btw: driver ist missing in the example) but drizzle-kit push gives an error:

Error: 7500: not authorized: SQLITE_AUTH
    at remoteCallback (/Users/mick/git/.../node_modules/drizzle-kit/bin.cjs:78956:21)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async Object.query (/Users/mick/git/.../node_modules/drizzle-kit/bin.cjs:78980:27)
    at async logSuggestionsAndReturn (/Users/mick/git/.../node_modules/drizzle-kit/bin.cjs:45182:23)
    at async sqlitePush (/Users/mick/git/.../node_modules/drizzle-kit/bin.cjs:82108:13)
    at async Object.handler (/Users/mick/git/.../node_modules/drizzle-kit/bin.cjs:92040:9)
    at async run (/Users/mick/git/.../node_modules/drizzle-kit/bin.cjs:90337:7)

In contrast drizzle-kit studio is working fine, I can create tables and items as well as drizzle-kit generated and drizzle-kit migrate.

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Reactions: 51
  • Comments: 23 (2 by maintainers)

Most upvoted comments

If you have the same experience guys, please upvote the issue

Your answer is not even related to this issue. Is this AI generated?

How to set up Drizzle with Wrangler is already shown in the docs. generate and migrate, as you show there, both work fine. This issue is about push not working.

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

const config: Config = {
  out: "./drizzle",
  schema: "./database/schema.ts",
  dialect: "sqlite",
  driver: "d1-http",
  dbCredentials: {
    accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
    databaseId: process.env.CLOUDFLARE_DATABASE_ID!,
    token: process.env.CLOUDFLARE_API_TOKEN!,
  },
  tablesFilter: ["!_cf_KV"], <----this should solve the issue.
};

export default config satisfies Config;

should be fixed in drizzle-kit@0.30.6

Thought I was on an island, but I’m experiencing the same. Since Drizzle Studio seems to be able to properly read/write to my database, it seems something particular to the push process.

Your answer is not even related to this issue. Is this AI generated?

How to set up Drizzle with Wrangler is already shown in the docs. generate and migrate, as you show there, both work fine. This issue is about push not working.

I used AI to polish my original issue with the goal of making the logic clearer. Sorry to make you look uncomfortable. I did get the same 7500 error, I didn’t fix the problem with drizzle, I used wrangle cli instead of drizzle to do a push operation, bypassing the 7500 error. My English is not good, I used AI to polish my original issued, if you don’t think it should be here, I’ll delete it

Same issue here. I can confirm that using drizzle-kit push does not work as expected. Other commands like migrate or generate work fine. I can even create tables on my remote D1 database using drizzle studio without problems using the same credentials and permissions. The error arises when using drizzle-kit push only.

I have tried tablesFilter: ['/^(?!.*_cf_KV).*$/'] as @bjunix commented but even when it does not show the error in the CLI as if everything had worked fine, the changes are not reflected in the remote database.

same with me

have same issue when using drizzle-kit push with Cloudflare D1 too

❯ bunx drizzle-kit push
No config path provided, using default 'drizzle.config.ts'
Reading config file '/Users/petrukngantuk/oprek/labs/drizzle.config.ts'
[✓] Pulling schema from database...
Error: 7500: not authorized: SQLITE_AUTH
    at remoteCallback (/Users/petrukngantuk/oprek/labs/node_modules/drizzle-kit/bin.cjs:79120:21)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async Object.query (/Users/petrukngantuk/oprek/labs/node_modules/drizzle-kit/bin.cjs:79144:27)
    at async logSuggestionsAndReturn (/Users/petrukngantuk/oprek/labs/node_modules/drizzle-kit/bin.cjs:45204:23)
    at async sqlitePush (/Users/petrukngantuk/oprek/labs/node_modules/drizzle-kit/bin.cjs:82272:13)
    at async Object.handler (/Users/petrukngantuk/oprek/labs/node_modules/drizzle-kit/bin.cjs:92204:9)
    at async run (/Users/petrukngantuk/oprek/labs/node_modules/drizzle-kit/bin.cjs:90501:7)

can confirm that drizzle-kit generate and drizzle-kit migrate working fine

adding patch fix now

So what happens in this case?

I also added tablesFilter: ['/^(?!.*_cf_KV).*$/'] - the SQLITE_AUTH resolves, but the schema changes are not being reflected on the D1 database at all.

Can we get any updates on this one?

Ok I decided to take a look at the code and I think I see the issue?

https://github.com/drizzle-team/drizzle-orm/blob/e5c63db0df0eaff5cae8321d97a77e5b47c5800d/drizzle-kit/src/cli/commands/push.ts#L514

You need d1-http driver to do the push but the sqlite code only pushes changes if a driver doesn’t exist on the object? I got rid of the if statement but got an error due to the transcation.

Error: 7500: To execute a transaction, please use the state.storage.transaction() or state.storage.transactionSync() APIs instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects’ automatic atomic write coalescing.

However once I commented those out the tables were replicated fine, I also included the tablesFilter because I assume the _cf_KV table is some internal metadata table and the tool automatically tries to delete any table that isn’t in the schema so we get the auth error.

I encountered the same issue

Adding this line resolves the SQLITE_AUTH error: tablesFilter: ['/^(?!.*_cf_KV).*$/'] (as mentioned by @josaelprz and @bjunix and fixed in Pull Request #3550).

However, there’s a bigger issue: even after fixing this problem, running npx drizzle-kit push still fails to generate the tables.

As a workaround, I added this to drizzle.config.ts:

tablesFilter: ['/^(?!.*_cf_KV).*$/'],

Edit: As @josaelprz is pointing out below, this does not work. It just suppresses the error 😃

For remote d1, once #2941 and #3550 are merged, a push can be performed. The #2941 allows multiple queries to be sent at once without using a transaction. It also resolves the error that occurred when sending an empty array to d1 with no parameters. In #3550, d1’s internal table (_cf_KV) is now excluded when detecting differences to prevent difference mismatch errors.

In local d1, it is now possible to perform a push by specifying the path to a sqlite file without setting a value for driver.

remote

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: 'aaa.ts',
  dialect: 'sqlite',
  driver: 'd1-http',
  dbCredentials: {
    accountId: process.env.CLOUDFLARE_ACCOUNT_ID,
    token: process.env.CLOUDFLARE_API_TOKEN,
    databaseId: process.env.CLOUDFLARE_DATABASE_ID,
  },
});

local

import { defineConfig } from 'drizzle-kit';
import { globSync } from "fast-glob";

export default defineConfig({
  schema: 'aaa.ts',
  dialect: 'sqlite',
  dbCredentials: {
    url: '.wrangler/bbb/ccc/ddd/eee.sqlite' 
   // If only one d1 is used, it may be possible to search with glob
   // url: globSync(".wrangler/**/*.sqlite", { stats: true }).sort((a, b) => (b.stats?.ctimeMs ?? 0) - (a.stats?.ctimeMs ?? 0))[0].path
  },
});

Potentially this unmerged PR is related: https://github.com/drizzle-team/drizzle-orm/pull/2941

faced the same problem. At first, “push” worked as it should, and there were no problems. When it was necessary to change the table structure, this error appeared. When updating using generate/migrate, everything works