[BUG]: drizzle-kit migrate fail "applying migrations...error: column "authorid" does not exist"

[Weixuanf]

What version of drizzle-orm are you using?

0.31.0

What version of drizzle-kit are you using?

0.22.1

Describe the Bug

npx drizzle-kit migrate throws error and fail to create any table in my postgres db , while npx drizzle-kit push throws error but can create tables in my postgres db

Before I upgrade to 0.22.1, this issue doesn't occur, I was at:
drizzle-kit: v0.21.2
drizzle-orm: v0.30.10

After upgrading to drizzle-kit: v0.22.1, this issue happened.
drizzle-kit: v0.22.1
drizzle-orm: v0.31.0

$ npx drizzle-kit migrate
drizzle-kit: v0.22.1
drizzle-orm: v0.31.0

No config path provided, using default path
Reading config file '/Users/weixuan/git/peach-icon/drizzle.config.ts'
Using 'pg' driver for database querying
[⣟] applying migrations...error: column "authorid" does not exist
    at /Users/weixuan/git/peach-icon/node_modules/drizzle-kit/bin.cjs:77692:15
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at <anonymous> (/Users/weixuan/git/peach-icon/node_modules/src/pg-core/dialect.ts:89:7)
    at NodePgSession.transaction (/Users/weixuan/git/peach-icon/node_modules/src/node-postgres/session.ts:155:19)
    at PgDialect.migrate (/Users/weixuan/git/peach-icon/node_modules/src/pg-core/dialect.ts:82:3)
    at migrate (/Users/weixuan/git/peach-icon/node_modules/src/node-postgres/migrator.ts:10:2) {
  length: 98,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'indexcmds.c',
  line: '1884',
  routine: 'ComputeIndexAttrs'
}

Expected behavior

No response

Environment & setup

No response

[CamTheGoblin]

I just ran into a similar issue that happens with drizzle-kit push as well. appears to be an issue with the setting up indexes. As a simple example:

export const user = pgTable(
  "user",
  {
    id:serial("id").primaryKey(),
    testId: string("testId"),
  },
  (table) => ({
    testIdx: index("testidx").on(table.testId),
  }),
);

This generates the error: "error: column "testid" does not exist
If I change the testId column name to all lower case to match the error message, then things work testId: string("testid")

Additionally index does not seem to like dashes in the column name, so testId: string("test-id") generates this error : "error: syntax error at or near "-" "

[Weixuanf]

I just ran into a similar issue that happens with drizzle-kit push as well. appears to be an issue with the setting up indexes. As a simple example:

export const user = pgTable(
  "user",
  {
    id:serial("id").primaryKey(),
    testId: string("testId"),
  },
  (table) => ({
    testIdx: index("testidx").on(table.testId),
  }),
);

This generates the error: "error: column "testid" does not exist If I change the testId column name to all lower case to match the error message, then things work testId: string("testid")

Additionally index does not seem to like dashes in the column name, so testId: string("test-id") generates this error : "error: syntax error at or near "-" "

oh interesting..but I don't want to use all lower case column name.. i hope drizzle team can fix this soon. I really need to use the new features in drizzle 0.31.0 to create compound index with desc() inside the index().on()...but this bug is preventing me to upgrate to 0.31.0

thanks very much for your response! this is helpful information for me!

[mandarzope]

This happens with PostgreSQL. By default, PostgreSQL converts all tokens to lowercase unless they are wrapped in double quotes. The solution is to keep your keys in the required case; however, database columns need to be lowercase for PostgreSQL.

[Weixuanf]

This happens with PostgreSQL. By default, PostgreSQL converts all tokens to lowercase unless they are wrapped in double quotes. The solution is to keep your keys in the required case; however, database columns need to be lowercase for PostgreSQL.

Oh! Thanks for the explanation. It seems it is best practice to use snake_case for column names in postgres (https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names). I didn't know this before hehe. This makes sense then. However, the previous version of drizzle (kit 21.0 , orm 30.10) worked fine on this.

Anyway I'll change to use snake case to follow the best practice.
Thanks for the info!

[ThomasAunvik]

Related Issue: #2413

[AndriiSherman]

Should be fixed in drizzle-kit@0.22.5

[Weixuanf]

Thank you for fixing this!!

…

________________________________ From: Andrii Sherman ***@***.***> Sent: Friday, June 7, 2024 11:15:32 PM To: drizzle-team/drizzle-orm ***@***.***> Cc: Weixuan Fu ***@***.***>; Author ***@***.***> Subject: Re: [drizzle-team/drizzle-orm] [BUG]: drizzle-kit migrate fail "applying migrations...error: column "authorid" does not exist" (Issue #2423) Should be fixed in ***@***.*** — Reply to this email directly, view it on GitHub<#2423 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AEMEEOK233YHQF5ZIVYF6Y3ZGHFBJAVCNFSM6AAAAABIVE2KTKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNJVGA2DMNJZGA>. You are receiving this because you authored the thread.Message ID: ***@***.***>