alchemy: D1 Adoption Error with Migrations

Discord thread for context

When adopting a D1 db Alchemy tries to apply migrations to it as if it is a fresh DB. Commenting out the migrationDir prop “fixes” the issue and the D1 resource is adopted just fine.

 await D1Database(dbName, {
    name: dbName,
    adopt: true,
    //migrationsDir: migrationsDir,
    primaryLocationHint: "enam",
    readReplication: {
      mode: "auto",
    },
  })

Here is the error I receive from Alchemy

[updating]  solstatus-prod Updating Resource...
Updating D1 database: solstatus-prod
ERROR Failed to apply D1 migrations: CloudflareApiError: Error 400 executing migration SQL D1 database 'redacted-id': table `uptimeChecks` already exists at offset 13: SQLITE_ERROR
    at handleApiError (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/api-error.ts:101:9)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at executeD1SQL (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/d1-migrations.ts:105:5)
    at applyMigrations (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/d1-migrations.ts:131:5)
    at Function.<anonymous> (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/d1-database.ts:338:9)
    at AsyncFunction.run (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/lib/alchemy.js:236:16)
    at _apply (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/apply.ts:175:20)
    at D1Database (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/d1-database.ts:156:14)
    at createDB (/Users/jbeckman/projects/personal/solstatus-personal/packages/common/infra/index.ts:25:10)
    at SolStatus (/Users/jbeckman/projects/personal/solstatus-personal/packages/infra/src/solstatus.ts:17:14) {
  status: 400,
  statusText: 'Bad Request',
  errorData: [
    {
      code: 7500,
      message: 'table `uptimeChecks` already exists at offset 13: SQLITE_ERROR'
    }
  ]
}
ERROR Scope failed solstatus/prod/solstatus-prod
WARN Scope is in error, skipping finalize
ERROR Scope failed solstatus/prod
node:internal/modules/run_main:129
    triggerUncaughtException(
    ^

CloudflareApiError: Error 400 executing migration SQL D1 database 'redacted-id': table `uptimeChecks` already exists at offset 13: SQLITE_ERROR
    at handleApiError (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/api-error.ts:101:9)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at executeD1SQL (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/d1-migrations.ts:105:5)
    at applyMigrations (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/d1-migrations.ts:131:5)
    at Function.<anonymous> (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/d1-database.ts:338:9)
    at AsyncFunction.run (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/lib/alchemy.js:236:16)
    at _apply (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/apply.ts:175:20)
    at D1Database (/Users/jbeckman/projects/personal/solstatus-personal/node_modules/.pnpm/alchemy@0.36.0_@ai-sdk+openai-compatible@0.2.14_zod@3.25.67__@ai-sdk+openai@1.3.22_zod@_aa1f8ebe8ae99950bda05e9f17decb9d/node_modules/alchemy/src/cloudflare/d1-database.ts:156:14)
    at createDB (/Users/jbeckman/projects/personal/solstatus-personal/packages/common/infra/index.ts:25:10)
    at SolStatus (/Users/jbeckman/projects/personal/solstatus-personal/packages/infra/src/solstatus.ts:17:14) {
  status: 400,
  statusText: 'Bad Request',
  errorData: [
    {
      code: 7500,
      message: 'table `uptimeChecks` already exists at offset 13: SQLITE_ERROR'
    }
  ]
}

About this issue

  • Original URL
  • State: closed
  • Created a year ago
  • Comments: 19 (13 by maintainers)

Most upvoted comments

Perfect, thanks for working through this with me Sam!

Image

@unibeck please try the latest version of https://github.com/sam-goodwin/alchemy/pull/473, I’d expect the id and name to be consistent now

In the photo migrations 0-12 were applied before the migration and have an incrementing id. Though the 13th migration, which was applied after the Alchemy->Wrangler schema migration, has some other type. Seems like a unix time with random string?

@unibeck i’ve checked this out, added a test for migration and it all passes. LMK if it works for you now.

Got it. I’ll look at this carefully tonight. Been a bit busy today and not much time at the laptop. Sorry for the false starts

Will test and get this working tonight

PR https://github.com/sam-goodwin/alchemy/pull/473 won’t work because the schema Alchemy uses is (id, applied_at), yet the schema used by wrangler is (id, name, applied_at).