Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[BUG]: drizzle-kit pull doesn't work with md5 index #3745

Open
1 task done
alexanderniebuhr opened this issue Dec 11, 2024 · 2 comments · May be fixed by #3888
Open
1 task done

[BUG]: drizzle-kit pull doesn't work with md5 index #3745

alexanderniebuhr opened this issue Dec 11, 2024 · 2 comments · May be fixed by #3888
Labels
bug Something isn't working drizzle/kit priority Will be worked on next

Comments

@alexanderniebuhr
Copy link
Contributor

Report hasn't been filed before.

  • I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.38.0

What version of drizzle-kit are you using?

0.30.0

Other packages

No response

Describe the Bug

I'm getting the following error using drizzle-kit pull. I'm kind of lost, please help!

_ZodError: [
  {
    "code": "invalid_type",
    "expected": "string",
    "received": "null",
    "path": [
      "columns",
      2,
      "expression"
    ],
    "message": "Expected string, received null"
  }
]
    at get error (/Users/alexanderniebuhr/Developer/Projects/frickegroup/workers/node_modules/.pnpm/drizzle-kit@0.30.0/node_modules/drizzle-kit/bin.cjs:2109:28)
    at _ZodObject.parse (/Users/alexanderniebuhr/Developer/Projects/frickegroup/workers/node_modules/.pnpm/drizzle-kit@0.30.0/node_modules/drizzle-kit/bin.cjs:2189:22)
    at Object.squashIdx (/Users/alexanderniebuhr/Developer/Projects/frickegroup/workers/node_modules/.pnpm/drizzle-kit@0.30.0/node_modules/drizzle-kit/bin.cjs:6212:16)
    at /Users/alexanderniebuhr/Developer/Projects/frickegroup/workers/node_modules/.pnpm/drizzle-kit@0.30.0/node_modules/drizzle-kit/bin.cjs:6396:86
    at /Users/alexanderniebuhr/Developer/Projects/frickegroup/workers/node_modules/.pnpm/drizzle-kit@0.30.0/node_modules/drizzle-kit/bin.cjs:5387:24
    at Array.reduce (<anonymous>)
    at mapValues (/Users/alexanderniebuhr/Developer/Projects/frickegroup/workers/node_modules/.pnpm/drizzle-kit@0.30.0/node_modules/drizzle-kit/bin.cjs:5386:39)
    at /Users/alexanderniebuhr/Developer/Projects/frickegroup/workers/node_modules/.pnpm/drizzle-kit@0.30.0/node_modules/drizzle-kit/bin.cjs:6395:35
    at Array.map (<anonymous>)
    at squashPgScheme (/Users/alexanderniebuhr/Developer/Projects/frickegroup/workers/node_modules/.pnpm/drizzle-kit@0.30.0/node_modules/drizzle-kit/bin.cjs:6394:37) {
  issues: [
    {
      code: 'invalid_type',
      expected: 'string',
      received: 'null',
      path: [Array],
      message: 'Expected string, received null'
    }
  ],
  addIssue: [Function (anonymous)],
  addIssues: [Function (anonymous)],
  errors: [
    {
      code: 'invalid_type',
      expected: 'string',
      received: 'null',
      path: [Array],
      message: 'Expected string, received null'
    }
  ]
}

This is the table in question.

-- Table Definition
CREATE TABLE "kv"."translations" (
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "source_language" int8 NOT NULL,
    "target_language" int8 NOT NULL,
    "source_text" varchar NOT NULL,
    "target_text" varchar NOT NULL,
    "updated_at" timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY ("id")
);


-- Indices
CREATE UNIQUE INDEX translations_pk ON kv.translations USING btree (id);
CREATE INDEX translations_source_text_md5_idx ON kv.translations USING btree (md5((source_text)::text));
CREATE UNIQUE INDEX translations_source_language_target_language_md5_idx ON kv.translations USING btree (source_language, target_language, md5((source_text)::text));

Okay after removing these two index it works. Am I missing something?

CREATE INDEX translations_source_text_md5_idx ON kv.translations USING btree (md5((source_text)::text));
CREATE UNIQUE INDEX translations_source_language_target_language_md5_idx ON kv.translations USING btree (source_language, target_language, md5((source_text)::text));

https://discord.com/channels/1043890932593987624/1070810929475883038/1316310831364964442
@alexanderniebuhr alexanderniebuhr added the bug Something isn't working label Dec 11, 2024
@L-Mario564 L-Mario564 added drizzle/kit priority Will be worked on next labels Dec 23, 2024
@jaredp
Copy link

jaredp commented Jan 2, 2025

This appears to happen when reading the schema from a database with a compound index where one of the index components is a non-column expression. I'm having a similar issue.

That is, in your example:

-- this one should be fine
CREATE INDEX translations_source_text_md5_idx ON kv.translations USING btree (md5((source_text)::text));

-- this one is likely causing the error
-- as it's a compound index where the second part is a-column expression `md5((source_text)::text))`
CREATE UNIQUE INDEX translations_source_language_target_language_md5_idx ON kv.translations USING btree (source_language, target_language, md5((source_text)::text));

It happens when running drizzle-kit push for me. Specifically, it fails to ingest the db's current schema.

This is extra confusing because drizzle-kit push succeeded when I first added the index. It's only in subsequent syncs that it fails reading back the index it created.

I've tracked the issue down to https://github.dev/drizzle-team/drizzle-orm/blob/04c91434c7ac10aeb2923efd1d19a7ebf10ea9d4/drizzle-kit/src/serializer/pgSerializer.ts#L1534

The dbIndexes it pulls from postgres has a column_name=NULL for the row with indexname='translations_source_language_target_language_md5_idx', index_order=2, where it ought to have column_name='md5((source_text)::text)'.

Downstream, this null causes the Expected string, received null zod error.

Working with Claude gave me this alternative SQL that seems to work when tested on my project

SELECT DISTINCT ON (t.relname, ic.relname, k.i) 
    t.relname as table_name,
    ic.relname AS indexname,
    k.i AS index_order,
    i.indisunique as is_unique,
    am.amname as method,
    ic.reloptions as with,
    CASE 
        WHEN i.indkey[k.i-1] != 0 THEN a.attname
        ELSE pg_get_indexdef(i.indexrelid, k.i, true)
    END AS column_name,
    CASE WHEN pg_get_expr(i.indexprs, i.indrelid) IS NOT NULL THEN 1 ELSE 0 END AS is_expression,
    i.indoption[k.i-1] & 1 = 1 AS descending,
    i.indoption[k.i-1] & 2 = 2 AS nulls_first,
    pg_get_expr(i.indpred, i.indrelid) as where,
    opc.opcname
FROM pg_class t
    LEFT JOIN pg_index i ON t.oid = i.indrelid
    LEFT JOIN pg_class ic ON ic.oid = i.indexrelid
    CROSS JOIN LATERAL generate_series(1, array_length(i.indkey, 1)) AS k(i)
    LEFT JOIN pg_attribute AS a ON i.indrelid = a.attrelid AND i.indkey[k.i-1] = a.attnum
    JOIN pg_namespace c on c.oid = t.relnamespace
    LEFT JOIN pg_am AS am ON ic.relam = am.oid
    JOIN pg_opclass opc ON opc.oid = ANY(i.indclass)

Pretty diff from the current SQL query: https://www.diffchecker.com/D6VK4atj/

I'm personally not great at Postgres SQL, so do very much take this with a grain of salt. I can't meaningfully read the original query, nor the one Claude wrote.

Hopefully someone with better SQL can read, give feedback, and create a PR

jaredp added a commit to jaredp/drizzle-orm that referenced this issue Jan 2, 2025
@jaredp
fix drizzle-kit push on schemas with compound indexes with expressi…
5c8fee5 
…on (non-column) members

fix drizzle-team#3745
@jaredp jaredp linked a pull request Jan 2, 2025 that will close this issue
Open
jaredp added a commit to jaredp/drizzle-orm that referenced this issue Jan 22, 2025
@jaredp
fix drizzle-kit push on schemas with compound indexes with expressi…
dabb00e 
…on (non-column) members

fix drizzle-team#3745
@jaredp
Copy link

jaredp commented Feb 15, 2025

Fixed by #3888 , waiting for a maintainer to review + merge!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working drizzle/kit priority Will be worked on next
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix drizzle-kit push crash on compound indexes with expression members jaredp/drizzle-orm
3 participants
@jaredp @alexanderniebuhr @L-Mario564