Drizzle Team•17mo ago

Error when pushing to the db

I am trying to do a one-to-many relationship, but I get an error that is column "twitchSubscription" cannot be automatically cast to type integer. According to what I read in the documentation I'm doing everything right but the error says something else, maybe I misses something. - Schema:

export const GuildSubscriptionTable = pgTable(
    'Guilds',
    {
        id: varchar('id', { length: 20 }).primaryKey(), // Guild Id
        twitchSubscription: integer('twitchSubscription')
            .notNull()
            .references(() => TwitchSubscriptionTable.id, {
                onDelete: 'cascade'
            }),
        channelId: varchar('channel_id', { length: 20 }).notNull(),
        message: text('message').notNull()
    },
    (g) => ({
        unq: unique().on(g.twitchSubscription, g.id)
    })
);

export const GuildSubscriptionRelations = relations(
    GuildSubscriptionTable,
    ({ one }) => ({
        twitchSubscription: one(TwitchSubscriptionTable, {
            fields: [GuildSubscriptionTable.twitchSubscription],
            references: [TwitchSubscriptionTable.id]
        })
    })
);

export const TwitchSubscriptionTable = pgTable(
    'TwitchSubscriptions',
    {
        id: serial('id').primaryKey(),
        subscriptionId: varchar('subscription_id', { length: 36 }).notNull(),
        streamerId: varchar('streamer_id', { length: 10 }).notNull(),
        subscriptionType: text('subscription_type').notNull()
    },
    (t) => ({
        unq: unique().on(t.streamerId, t.subscriptionType)
    })
);

export const TwitchSubscriptionRelations = relations(
    TwitchSubscriptionTable,
    ({ many }) => ({
        guildSubscriptions: many(GuildSubscriptionTable)
    })
)

export const GuildSubscriptionTable = pgTable(
    'Guilds',
    {
        id: varchar('id', { length: 20 }).primaryKey(), // Guild Id
        twitchSubscription: integer('twitchSubscription')
            .notNull()
            .references(() => TwitchSubscriptionTable.id, {
                onDelete: 'cascade'
            }),
        channelId: varchar('channel_id', { length: 20 }).notNull(),
        message: text('message').notNull()
    },
    (g) => ({
        unq: unique().on(g.twitchSubscription, g.id)
    })
);

export const GuildSubscriptionRelations = relations(
    GuildSubscriptionTable,
    ({ one }) => ({
        twitchSubscription: one(TwitchSubscriptionTable, {
            fields: [GuildSubscriptionTable.twitchSubscription],
            references: [TwitchSubscriptionTable.id]
        })
    })
);

export const TwitchSubscriptionTable = pgTable(
    'TwitchSubscriptions',
    {
        id: serial('id').primaryKey(),
        subscriptionId: varchar('subscription_id', { length: 36 }).notNull(),
        streamerId: varchar('streamer_id', { length: 10 }).notNull(),
        subscriptionType: text('subscription_type').notNull()
    },
    (t) => ({
        unq: unique().on(t.streamerId, t.subscriptionType)
    })
);

export const TwitchSubscriptionRelations = relations(
    TwitchSubscriptionTable,
    ({ many }) => ({
        guildSubscriptions: many(GuildSubscriptionTable)
    })
)

- Console Output:

 Warning  You are about to execute current statements:

ALTER TABLE "Guilds" ALTER COLUMN "twitchSubscription" SET DATA TYPE integer;
DO $$ BEGIN
 ALTER TABLE "Guilds" ADD CONSTRAINT "Guilds_twitchSubscription_TwitchSubscriptions_id_fk" FOREIGN KEY ("twitchSubscription") REFERENCES "TwitchSubscriptions"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

ALTER TABLE "Guilds" ADD CONSTRAINT "Guilds_twitchSubscription_id_unique" UNIQUE("twitchSubscription","id");

error: column "twitchSubscription" cannot be cast automatically to type integer
    at Parser.parseErrorMessage (C:\Users\Chiko\Documents\Github\rakkunvt\tanuki\node_modules\drizzle-kit\index.cjs:40659:98)
    at Parser.handlePacket (C:\Users\Chiko\Documents\Github\rakkunvt\tanuki\node_modules\drizzle-kit\index.cjs:40500:25)
    at Parser.parse (C:\Users\Chiko\Documents\Github\rakkunvt\tanuki\node_modules\drizzle-kit\index.cjs:40424:34)
    at Socket.<anonymous> (C:\Users\Chiko\Documents\Github\rakkunvt\tanuki\node_modules\drizzle-kit\index.cjs:40700:44)
    at Socket.emit (node:events:511:28)
    at addChunk (node:internal/streams/readable:332:12)
    at readableAddChunk (node:internal/streams/readable:305:9)
    at Readable.push (node:internal/streams/readable:242:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 209,
  severity: 'ERROR',
  code: '42804',
  detail: undefined,
  hint: 'You might need to specify "USING "twitchSubscription"::integer".',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '12302',
  routine: 'ATPrepAlterColumnType'
}

 Warning  You are about to execute current statements:

ALTER TABLE "Guilds" ALTER COLUMN "twitchSubscription" SET DATA TYPE integer;
DO $$ BEGIN
 ALTER TABLE "Guilds" ADD CONSTRAINT "Guilds_twitchSubscription_TwitchSubscriptions_id_fk" FOREIGN KEY ("twitchSubscription") REFERENCES "TwitchSubscriptions"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

ALTER TABLE "Guilds" ADD CONSTRAINT "Guilds_twitchSubscription_id_unique" UNIQUE("twitchSubscription","id");

error: column "twitchSubscription" cannot be cast automatically to type integer
    at Parser.parseErrorMessage (C:\Users\Chiko\Documents\Github\rakkunvt\tanuki\node_modules\drizzle-kit\index.cjs:40659:98)
    at Parser.handlePacket (C:\Users\Chiko\Documents\Github\rakkunvt\tanuki\node_modules\drizzle-kit\index.cjs:40500:25)
    at Parser.parse (C:\Users\Chiko\Documents\Github\rakkunvt\tanuki\node_modules\drizzle-kit\index.cjs:40424:34)
    at Socket.<anonymous> (C:\Users\Chiko\Documents\Github\rakkunvt\tanuki\node_modules\drizzle-kit\index.cjs:40700:44)
    at Socket.emit (node:events:511:28)
    at addChunk (node:internal/streams/readable:332:12)
    at readableAddChunk (node:internal/streams/readable:305:9)
    at Readable.push (node:internal/streams/readable:242:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 209,
  severity: 'ERROR',
  code: '42804',
  detail: undefined,
  hint: 'You might need to specify "USING "twitchSubscription"::integer".',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'tablecmds.c',
  line: '12302',
  routine: 'ATPrepAlterColumnType'
}

5 Replies

ChikoOP•17mo ago

Migration file

8qAeEuJ.sql

xamarot•17mo ago

in your guilds table you reference twitch table's primary key as a serial, but it should probably be an integer. or change both to serial, preferrably.

ChikoOP•17mo ago

I thought the same but after trying that I got another error

xamarot•17mo ago

Which was?

ChikoOP•17mo ago

I don't remember, sorry but I'll do it again after eating and show you the error Nvm, I think I kinda fixed my issue with just deleting the reference thing

Gaming

Programming

Error when pushing to the db