Resolved: Need help with my multi column primary and foreign key table

Setup:

Turso / libsql, dialect is set to turso
Versions:
    "@libsql/client": "^0.14.0"
    "drizzle-orm": "^0.34.1"
    "drizzle-kit": "^0.25.0"

Turso / libsql, dialect is set to turso
Versions:
    "@libsql/client": "^0.14.0"
    "drizzle-orm": "^0.34.1"
    "drizzle-kit": "^0.25.0"

Hey I have created this table:

export const userAsyncTasks = createTable(
  "userAsyncTask",
  {
    userId: text("userId").notNull(),
    identifier: text("identifier").notNull(),
    type: text("type", { enum: userAsyncTaskTypeEnum }).notNull(),
    data: text("data", { mode: "json" }),
    startedAt: int("startedAt", { mode: "timestamp" }).notNull(),
    timeoutAt: int("timeoutAt", { mode: "timestamp" }),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.userId, table.type, table.identifier] }),
    fk: foreignKey({
      columns: [table.userId],
      foreignColumns: [users.id],
    })
      .onDelete("cascade")
      .onUpdate("cascade"),
  }),
);

export const userAsyncTasks = createTable(
  "userAsyncTask",
  {
    userId: text("userId").notNull(),
    identifier: text("identifier").notNull(),
    type: text("type", { enum: userAsyncTaskTypeEnum }).notNull(),
    data: text("data", { mode: "json" }),
    startedAt: int("startedAt", { mode: "timestamp" }).notNull(),
    timeoutAt: int("timeoutAt", { mode: "timestamp" }),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.userId, table.type, table.identifier] }),
    fk: foreignKey({
      columns: [table.userId],
      foreignColumns: [users.id],
    })
      .onDelete("cascade")
      .onUpdate("cascade"),
  }),
);

4 Replies

DariOP•2mo ago

The first db push works fine. On a second call without any changes it still tries to "alter" the table. First db:push:

CREATE TABLE `userAsyncTask` (
        `userId` text NOT NULL,
        `identifier` text NOT NULL,
        `type` text NOT NULL,
        `data` text,
        `startedAt` integer NOT NULL,
        `timeoutAt` integer,
        PRIMARY KEY(`userId`, `type`, `identifier`),
        FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE cascade ON DELETE cascade
);

CREATE TABLE `userAsyncTask` (
        `userId` text NOT NULL,
        `identifier` text NOT NULL,
        `type` text NOT NULL,
        `data` text,
        `startedAt` integer NOT NULL,
        `timeoutAt` integer,
        PRIMARY KEY(`userId`, `type`, `identifier`),
        FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE cascade ON DELETE cascade
);

Second db:push:

CREATE TABLE `__new_userAsyncTask` (
        `userId` text NOT NULL,
        `identifier` text NOT NULL,
        `type` text NOT NULL,
        `data` text,
        `startedAt` integer NOT NULL,
        `timeoutAt` integer,
        PRIMARY KEY(`userId`, `type`, `identifier`),
        FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE cascade ON DELETE cascade
);

INSERT INTO `__new_userAsyncTask`("userId", "identifier", "type", "data", "startedAt", "timeoutAt") SELECT "userId", "identifier", "type", "data", "startedAt", "timeoutAt" FROM `userAsyncTask`;  
DROP TABLE `userAsyncTask`;
ALTER TABLE `__new_userAsyncTask` RENAME TO `userAsyncTask`;

CREATE TABLE `__new_userAsyncTask` (
        `userId` text NOT NULL,
        `identifier` text NOT NULL,
        `type` text NOT NULL,
        `data` text,
        `startedAt` integer NOT NULL,
        `timeoutAt` integer,
        PRIMARY KEY(`userId`, `type`, `identifier`),
        FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE cascade ON DELETE cascade
);

INSERT INTO `__new_userAsyncTask`("userId", "identifier", "type", "data", "startedAt", "timeoutAt") SELECT "userId", "identifier", "type", "data", "startedAt", "timeoutAt" FROM `userAsyncTask`;  
DROP TABLE `userAsyncTask`;
ALTER TABLE `__new_userAsyncTask` RENAME TO `userAsyncTask`;

Any suggestions? I've tried defining the foreign key with the foreignKey function and directly in the schema field with .references(). Same result Found the issue.

pk: primaryKey({ columns: [table.userId, table.type, table.identifier] }),

pk: primaryKey({ columns: [table.userId, table.type, table.identifier] }),

The columns need to be in the correct order. So it worked as soon as I changed it to:

pk: primaryKey({ columns: [table.userId, table.identifier, table.type] }),

pk: primaryKey({ columns: [table.userId, table.identifier, table.type] }),

@Andrew Sherman is this intended behaviour or a bug?

Andrii Sherman•2mo ago

it's definitely a bug it's great you found a workaround with columns order but ideally kit should not detect a change

DariOP•2mo ago

shall i open an issue?

Andrii Sherman•2mo ago

I don't think sqlite have the same thing as mysql where order matters I would need to check that as well would be perfect

Gaming

Programming

Resolved: Need help with my multi column primary and foreign key table