DT
Drizzle Teamā€¢13mo ago
thdxr

Dropping primary keys every time

so latest drizzle-kit fixed a bunch of issues with dropping indexes and preserving pk order but it's still dropping and recreating PKs every time 
ALTER TABLE `account` DROP PRIMARY KEY;
ALTER TABLE `app` DROP PRIMARY KEY;
ALTER TABLE `aws_account` DROP PRIMARY KEY;
ALTER TABLE `issue` DROP PRIMARY KEY;
ALTER TABLE `issue_alert` DROP PRIMARY KEY;
ALTER TABLE `issue_alert_limit` DROP PRIMARY KEY;
ALTER TABLE `issue_count` DROP PRIMARY KEY;
ALTER TABLE `issue_subscriber` DROP PRIMARY KEY;
ALTER TABLE `lambda_payload` DROP PRIMARY KEY;
ALTER TABLE `log_poller` DROP PRIMARY KEY;
ALTER TABLE `log_search` DROP PRIMARY KEY;
ALTER TABLE `replicache_client` DROP PRIMARY KEY;
ALTER TABLE `replicache_cvr` DROP PRIMARY KEY;
ALTER TABLE `resource` DROP PRIMARY KEY;
ALTER TABLE `slack_team` DROP PRIMARY KEY;
ALTER TABLE `stage` DROP PRIMARY KEY;
ALTER TABLE `stripe` DROP PRIMARY KEY;
ALTER TABLE `usage` DROP PRIMARY KEY;
ALTER TABLE `user` DROP PRIMARY KEY;
ALTER TABLE `warning` DROP PRIMARY KEY;
ALTER TABLE `workspace` DROP PRIMARY KEY;
ALTER TABLE `account` ADD PRIMARY KEY(`id`);
ALTER TABLE `app` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `aws_account` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue_alert` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue_alert_limit` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue_count` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue_subscriber` ADD PRIMARY KEY(`workspace_id`,`stage_id`,`id`);
ALTER TABLE `lambda_payload` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `log_poller` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `log_search` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `replicache_client` ADD PRIMARY KEY(`id`);
ALTER TABLE `replicache_cvr` ADD PRIMARY KEY(`client_group_id`,`id`);
ALTER TABLE `resource` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `slack_team` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `stage` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `stripe` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `usage` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `user` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `warning` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `workspace` ADD PRIMARY KEY(`id`);
ALTER TABLE `account` DROP PRIMARY KEY;
ALTER TABLE `app` DROP PRIMARY KEY;
ALTER TABLE `aws_account` DROP PRIMARY KEY;
ALTER TABLE `issue` DROP PRIMARY KEY;
ALTER TABLE `issue_alert` DROP PRIMARY KEY;
ALTER TABLE `issue_alert_limit` DROP PRIMARY KEY;
ALTER TABLE `issue_count` DROP PRIMARY KEY;
ALTER TABLE `issue_subscriber` DROP PRIMARY KEY;
ALTER TABLE `lambda_payload` DROP PRIMARY KEY;
ALTER TABLE `log_poller` DROP PRIMARY KEY;
ALTER TABLE `log_search` DROP PRIMARY KEY;
ALTER TABLE `replicache_client` DROP PRIMARY KEY;
ALTER TABLE `replicache_cvr` DROP PRIMARY KEY;
ALTER TABLE `resource` DROP PRIMARY KEY;
ALTER TABLE `slack_team` DROP PRIMARY KEY;
ALTER TABLE `stage` DROP PRIMARY KEY;
ALTER TABLE `stripe` DROP PRIMARY KEY;
ALTER TABLE `usage` DROP PRIMARY KEY;
ALTER TABLE `user` DROP PRIMARY KEY;
ALTER TABLE `warning` DROP PRIMARY KEY;
ALTER TABLE `workspace` DROP PRIMARY KEY;
ALTER TABLE `account` ADD PRIMARY KEY(`id`);
ALTER TABLE `app` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `aws_account` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue_alert` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue_alert_limit` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue_count` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `issue_subscriber` ADD PRIMARY KEY(`workspace_id`,`stage_id`,`id`);
ALTER TABLE `lambda_payload` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `log_poller` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `log_search` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `replicache_client` ADD PRIMARY KEY(`id`);
ALTER TABLE `replicache_cvr` ADD PRIMARY KEY(`client_group_id`,`id`);
ALTER TABLE `resource` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `slack_team` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `stage` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `stripe` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `usage` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `user` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `warning` ADD PRIMARY KEY(`workspace_id`,`id`);
ALTER TABLE `workspace` ADD PRIMARY KEY(`id`);
35 Replies
Angelelz
Angelelzā€¢13mo ago
Are you pushing or migrating?
thdxr
thdxrOPā€¢13mo ago
this is mysql push
natedunn
natedunnā€¢13mo ago
yeah this is still a bummer, especially since "official" recommendation for planetscale has been to use push. oof, this just straight breaks local development unless I drop all tables. this feels pretty critical so if I got time I will file an issue. Update: already one filed:
natedunn
natedunnā€¢13mo ago
https://github.com/drizzle-team/drizzle-kit-mirror/issues/234
GitHub
BUG: v0.20.2 push:mysql error: there can be only one auto column an...
When pushing schema changes to a Planetscale database, drizzle-kit attempts to drop the primary key, resulting in an unexpected error that crashes the program and does not push schema changes. Wind...
kelbs
kelbsā€¢13mo ago
@thdxr curious if you're using drizzle in prod? If so, do you have workarounds for these drizzle kit issues? I've run into similar issues now and previously that have made me hesitant to switch
thdxr
thdxrOPā€¢13mo ago
i use planetscale so this feature not being perfect isn't a huge deal it does unnecessary work on my dev branch but the merge request on planetscale is still correct
Andrii Sherman
Andrii Shermanā€¢12mo ago
long response from my side, sorry for this. Taking this one and related introspect mysql issue will be released next and asap
chronark
chronarkā€¢12mo ago
running into this too
Andrii Sherman
Andrii Shermanā€¢12mo ago
it's fixed in drizzle-kit@beta seems like worked for other devs will release it now should be fixed in [email protected]
chronark
chronarkā€¢12mo ago
pushing after upgrading to latest gives me a new error šŸ˜„ 
Error [ERR_PACKAGE_PATH_NOT_EXPORTED]: Package subpath './relations' is not defined by "exports" in /Users/andreasthomas/github/unkeyed/unkey/node_modules/.pnpm/node_modules/drizzle-orm/package.json
    at new NodeError (node:internal/errors:405:5)
    at exportsNotFound (node:internal/modules/esm/resolve:359:10)
    at packageExportsResolve (node:internal/modules/esm/resolve:695:9)
    at resolveExports (node:internal/modules/cjs/loader:567:36)
    at Module._findPath (node:internal/modules/cjs/loader:636:31)
    at Module._resolveFilename (node:internal/modules/cjs/loader:1063:27)
    at Module._load (node:internal/modules/cjs/loader:922:27)
    at Module.require (node:internal/modules/cjs/loader:1143:19)
    at require (node:internal/modules/cjs/helpers:121:18)
    at src/introspect-pg.ts (/Users/andreasthomas/github/unkeyed/unkey/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/bin.cjs:18910:24) {
  code: 'ERR_PACKAGE_PATH_NOT_EXPORTED'
}
Error [ERR_PACKAGE_PATH_NOT_EXPORTED]: Package subpath './relations' is not defined by "exports" in /Users/andreasthomas/github/unkeyed/unkey/node_modules/.pnpm/node_modules/drizzle-orm/package.json
    at new NodeError (node:internal/errors:405:5)
    at exportsNotFound (node:internal/modules/esm/resolve:359:10)
    at packageExportsResolve (node:internal/modules/esm/resolve:695:9)
    at resolveExports (node:internal/modules/cjs/loader:567:36)
    at Module._findPath (node:internal/modules/cjs/loader:636:31)
    at Module._resolveFilename (node:internal/modules/cjs/loader:1063:27)
    at Module._load (node:internal/modules/cjs/loader:922:27)
    at Module.require (node:internal/modules/cjs/loader:1143:19)
    at require (node:internal/modules/cjs/helpers:121:18)
    at src/introspect-pg.ts (/Users/andreasthomas/github/unkeyed/unkey/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/bin.cjs:18910:24) {
  code: 'ERR_PACKAGE_PATH_NOT_EXPORTED'
}
0.19.3 (which I had before) worked (after manually deleting the table)
Andrii Sherman
Andrii Shermanā€¢12mo ago
šŸ‘€
chronark
chronarkā€¢12mo ago
that's what I looked like too cause the package definitely exports relations
Andrii Sherman
Andrii Shermanā€¢12mo ago
did you upgrade drizzle-orm as well? also did drizzle-kit ask you to upgrade orm?
chronark
chronarkā€¢12mo ago
first i upgrade both, that failed then I only upgrade kit and it failed too no it didn't ask might be a monorepo thing?
Andrii Sherman
Andrii Shermanā€¢12mo ago
It may be a monorepo thing, but I'm not sure yet. On the 0.19 -> 0.20 upgrade, drizzle-kit requires a version of drizzle-orm that is 0.29.0 or higher, and you should definitely see a request to upgrade. This is done by fetching the internal version of drizzle-orm directly from drizzle-orm import So, if you have more than one drizzle-orm package in your repository, I guess you need to upgrade all of them to 0.29.0 or higher when using drizzle-kit 0.20.0+
first i upgrade both, that failed
What was an error there?
chronark
chronarkā€¢12mo ago
give me a few min, I'll do a fresh try ah yeah we have a few same error
Andrii Sherman
Andrii Shermanā€¢12mo ago
I guess this was an issue it seems like, but let's check
chronark
chronarkā€¢12mo ago
Invalid input Either "uri" or "host", "database" are required for database connection
ok hold on, this is a completely new error šŸ˜„
Andrii Sherman
Andrii Shermanā€¢12mo ago
yes you need to change drizzle.config connection key to be uri it was changed to match a driver behavior
chronark
chronarkā€¢12mo ago
aah stupid zed has cached the old types
Andrii Sherman
Andrii Shermanā€¢12mo ago
I'll think about how to handle this case of multiple drizzle-orm packages and how drizzle-kit can check all of them because you was not the first
chronark
chronarkā€¢12mo ago
still fails on the 2nd push
Andrii Sherman
Andrii Shermanā€¢12mo ago
same primary key drop?
chronark
chronarkā€¢12mo ago
pnpm drizzle-kit push:mysql
drizzle-kit: v0.20.7
drizzle-orm: v0.29.1

No config path provided, using default path
Reading config file '/Users/andreasthomas/github/unkeyed/unkey/internal/db/drizzle.config.ts'
Reading schema files:
/Users/andreasthomas/github/unkeyed/unkey/internal/db/src/schema/index.ts

[āœ“] Changes applied


___


āžœ  db git:(drizzle-upgrade) āœ— pnpm drizzle-kit p
ush:mysql
drizzle-kit: v0.20.7
drizzle-orm: v0.29.1

No config path provided, using default path
Reading config file '/Users/andreasthomas/github/unkeyed/unkey/internal/db/drizzle.config.ts'
Reading schema files:
/Users/andreasthomas/github/unkeyed/unkey/internal/db/src/schema/index.ts

Error: target: unkey.-.primary: vttablet: rpc error: code = Unknown desc = Cannot drop index 'audit_logs_workspace_id_workspaces_id_fk': needed in a foreign key constraint (errno 1553) (sqlstate HY000) (CallerID: nkmzozmcad8ld2mez77t): Sql: "alter table audit_logs drop key audit_logs_workspace_id_workspaces_id_fk", BindVars: {REDACTED}
    at PromiseConnection.query (/Users/andreasthomas/github/unkeyed/unkey/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/bin.cjs:44278:26)
    at Command.<anonymous> (/Users/andreasthomas/github/unkeyed/unkey/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/bin.cjs:63113:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_DROP_INDEX_FK',
  errno: 1553,
  sql: 'DROP INDEX `audit_logs_workspace_id_workspaces_id_fk` ON `audit_logs`;',
  sqlState: 'HY000',
  sqlMessage: `target: unkey.-.primary: vttablet: rpc error: code = Unknown desc = Cannot drop index 'audit_logs_workspace_id_workspaces_id_fk': needed in a foreign key constraint (errno 1553) (sqlstate HY000) (CallerID: nkmzozmcad8ld2mez77t): Sql: "alter table audit_logs drop key audit_logs_workspace_id_workspaces_id_fk", BindVars: {REDACTED}`
}
pnpm drizzle-kit push:mysql
drizzle-kit: v0.20.7
drizzle-orm: v0.29.1

No config path provided, using default path
Reading config file '/Users/andreasthomas/github/unkeyed/unkey/internal/db/drizzle.config.ts'
Reading schema files:
/Users/andreasthomas/github/unkeyed/unkey/internal/db/src/schema/index.ts

[āœ“] Changes applied


___


āžœ  db git:(drizzle-upgrade) āœ— pnpm drizzle-kit p
ush:mysql
drizzle-kit: v0.20.7
drizzle-orm: v0.29.1

No config path provided, using default path
Reading config file '/Users/andreasthomas/github/unkeyed/unkey/internal/db/drizzle.config.ts'
Reading schema files:
/Users/andreasthomas/github/unkeyed/unkey/internal/db/src/schema/index.ts

Error: target: unkey.-.primary: vttablet: rpc error: code = Unknown desc = Cannot drop index 'audit_logs_workspace_id_workspaces_id_fk': needed in a foreign key constraint (errno 1553) (sqlstate HY000) (CallerID: nkmzozmcad8ld2mez77t): Sql: "alter table audit_logs drop key audit_logs_workspace_id_workspaces_id_fk", BindVars: {REDACTED}
    at PromiseConnection.query (/Users/andreasthomas/github/unkeyed/unkey/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/bin.cjs:44278:26)
    at Command.<anonymous> (/Users/andreasthomas/github/unkeyed/unkey/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/bin.cjs:63113:33)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_DROP_INDEX_FK',
  errno: 1553,
  sql: 'DROP INDEX `audit_logs_workspace_id_workspaces_id_fk` ON `audit_logs`;',
  sqlState: 'HY000',
  sqlMessage: `target: unkey.-.primary: vttablet: rpc error: code = Unknown desc = Cannot drop index 'audit_logs_workspace_id_workspaces_id_fk': needed in a foreign key constraint (errno 1553) (sqlstate HY000) (CallerID: nkmzozmcad8ld2mez77t): Sql: "alter table audit_logs drop key audit_logs_workspace_id_workspaces_id_fk", BindVars: {REDACTED}`
}
Andrii Sherman
Andrii Shermanā€¢12mo ago
ok, this one is a new error checking I guess I can fork unkey and check?
chronark
chronarkā€¢12mo ago
yeah, let me write down the instructions
Andrii Sherman
Andrii Shermanā€¢12mo ago
oh you are using new FK feature in planetscale?
chronark
chronarkā€¢12mo ago
1. update drizzle-kit and drizzle-orm
2. pnpm install
3. cd internal/db
4. DRIZZLE_DATABASE_URL=XXX pnpm drizzle-kit push:mysql
1. update drizzle-kit and drizzle-orm
2. pnpm install
3. cd internal/db
4. DRIZZLE_DATABASE_URL=XXX pnpm drizzle-kit push:mysql
that should be it yeah
Andrii Sherman
Andrii Shermanā€¢12mo ago
I guess we just need to check the behavior of it in PlanetScale maybe some indexes are created automatically and not seen in drizzle schema I'll do all checks today and ping you back
chronark
chronarkā€¢12mo ago
thanks šŸ™‚ it's not super important, deleting the table is somewhat fine for us planetscale migrations are handling it correctly afterwards, it's just annoying when we edit our schema in a development branch
Andrii Sherman
Andrii Shermanā€¢12mo ago
It's definitely not the right behavior, and we are trying to make schema diffs close to the PlanetScale experience. Thanks for helping with that! I'm 90% sure we just need to check a new FK behavior in PlanetScale
chronark
chronarkā€¢12mo ago
I did now get the warning btw
This version of drizzle-kit is outdated Please update drizzle-kit package to the latest version šŸ‘
sweet lmk how we can help šŸ™‚
Andrii Sherman
Andrii Shermanā€¢12mo ago
@chronark fixed really strange issue, will make more tests and deploy to beta tag for testing for you the case was, that for some reason lower case for introspection query was not working with PlanetScale ans is working with MySQL just making a query uppercase solved this issue previously foreign keys were not fetched from PlanetScale and kit assumed you want to delete indexes related to FK and create those FK 1 more time @chronark Whenever you are free, please check drizzle-kit@beta It will be released to the latest version either today or tomorrow morning
chronark
chronarkā€¢12mo ago
sweet, will do later tonight thank you so much! I can confirm, I can push over it now šŸ™‚ thank you so much! ā¤ļø
Andrii Sherman
Andrii Shermanā€¢12mo ago
Perfect
Want results from more Discord servers?
Add your server