Dropping Primary Key

still having some issues where push is trying to drop my primary keys

ALTER TABLE `account` DROP PRIMARY KEY
ALTER TABLE `replicache_cvr` DROP PRIMARY KEY
ALTER TABLE `workspace` DROP PRIMARY KEY
ALTER TABLE `account` ADD PRIMARY KEY(`id`);
ALTER TABLE `replicache_cvr` ADD PRIMARY KEY(`id`);
ALTER TABLE `workspace` ADD PRIMARY KEY(`id`);

ALTER TABLE `account` DROP PRIMARY KEY
ALTER TABLE `replicache_cvr` DROP PRIMARY KEY
ALTER TABLE `workspace` DROP PRIMARY KEY
ALTER TABLE `account` ADD PRIMARY KEY(`id`);
ALTER TABLE `replicache_cvr` ADD PRIMARY KEY(`id`);
ALTER TABLE `workspace` ADD PRIMARY KEY(`id`);

19 Replies

thdxr•11mo ago

we're close though! all other problems seem gone

thdxr•11mo ago

here's an example schema: https://github.com/serverless-stack/console/blob/dev/packages/core/src/account/account.sql.ts

GitHub

console/packages/core/src/account/account.sql.ts at dev · serverles...

Contribute to serverless-stack/console development by creating an account on GitHub.

thdxr•11mo ago

here's the current schema

sst/thdxr> describe account;
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type         | Null | Key | Default           | Extra                                         |
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id           | char(24)     | NO   | PRI | NULL              |                                               |
| email        | varchar(255) | NO   | UNI | NULL              |                                               |
| time_created | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| time_updated | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| time_deleted | timestamp    | YES  |     | NULL              |                                               |
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+

sst/thdxr> describe account;
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type         | Null | Key | Default           | Extra                                         |
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id           | char(24)     | NO   | PRI | NULL              |                                               |
| email        | varchar(255) | NO   | UNI | NULL              |                                               |
| time_created | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| time_updated | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| time_deleted | timestamp    | YES  |     | NULL              |                                               |
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+

hey help me! @Andrew Sherman

Andrii Sherman•11mo ago

sorry, missed this message oh I know the issue here @thdxr There is one known issue with primary keys that will be fixed after I add the definition for the names of pk/fk constraints. I wrote about it here: https://orm.drizzle.team/kit-docs/faq#pushpg-command-is-attempting-to-apply-changes-even-when-there-are-no-changes-in-the-code If you can try to move this primaryKey (https://github.com/serverless-stack/console/blob/dev/packages/core/src/account/account.sql.ts#L17) to a column builder (cuid().primaryKey()), it should work. Just checked with your example, it worked well But while looking into your example I've got an idea how to fix it with current setup without a need to move primaryKey to a column and adding names for a constraint. I'll try to do it now and ping you here Yes, works will prepare release today

thdxr•11mo ago

ur the best

Andrii Sherman•11mo ago

thdxr•11mo ago

@Andrew Sherman was my issue reusing columns between tables? I think this solved my problems

export const workspaceID = {
  get id() {
    return cuid("id").notNull();
  },
  get workspaceID() {
    return cuid("workspace_id").notNull();
  },
};

export const workspaceID = {
  get id() {
    return cuid("id").notNull();
  },
  get workspaceID() {
    return cuid("workspace_id").notNull();
  },
};

and then doing ...workspaceID will create a new instance every time

Andrii Sherman•11mo ago

No, the issue was on the drizzle-kit end. Regardless of whether the instance was created every time or not, drizzle-kit just retrieves the properties inside. If those properties are the same, there are no changes for the kit. The problem was with the wrong primaryKeys being saved into the object, which was going to be checked as a difference(between generate and introspect). It was handled correctly on the SQLite side, but I made a mistake with MySQL. During the push, we are executing the generate function by reading the ts file and saving it into a JSON object. I mistakenly saved the column primaryKey into the columns object and the 3rd parameter, primaryKey, into compositePKs. The same mistake was made in the introspect part. If there was only one column in the primary key, I saved it into the column object, and if there were more than one, I saved it into compositePKs. The issue arises when you specify primaryKey() in the 3rd parameter with only one column, as it gets saved into compositePKs, but during introspection, it gets saved into the column definition. Consequently, we have drop pk + add pk statements, one for the difference between column definition and one for the compositePk. For now, I will always store all of them into the compositePk object, and there will be a proper diff, no matter where you define this primary key @thdxr I have a tag for patch release for today. Just deployed this fix there Could you please check drizzle-kit@fixes-pg?

thdxr•11mo ago

lol well i changed it to that getter and now the problem is gone....lol let me switch it back and try

Andrii Sherman•11mo ago

that's super strange

thdxr•11mo ago

oh i changed to using .primaryKey() also let me undo that also the getter fixed some other issue i saw

Andrii Sherman•11mo ago

oh, this is why

thdxr•11mo ago

ok recreated the issue let me try the patch

Andrii Sherman•11mo ago

thdxr•11mo ago

yep that fixes

Andrii Sherman•11mo ago

thdxr•11mo ago

lol

Andrii Sherman•11mo ago

I'll push to the latest a bit later today Just published it in drizzle-kit@0.19.11 btw, would suggest to use strict: true in drizzle.config. In this case you can approve all statements before execution https://orm.drizzle.team/kit-docs/config-reference#strict may be useful for current drizzle-kit state

thdxr•11mo ago

ah cool

Gaming

Programming

Dropping Primary Key