[BUG]: Creating index with using does not yield any result

[lvisb]

What version of drizzle-orm are you using?

0.27.0

What version of drizzle-kit are you using?

0.19.1

Describe the Bug

1. Create a schema with the following code:

export const user = pgTable(
  'users',
  {
    userId: serial('user_id').primaryKey(),
    meta: jsonb('meta').default({
      invite: {
        invited_at: null,
        accepted_at: null,
      },
    }),
  },
  (users) => ({
    metaIndex: index('meta_idx').on(users.meta).using(sql`GIN`),
  }),
)

2. Generate the code for migration:

drizzle-kit generate:pg

3. Inspect the generated migration and note that the result of the index creation is only this:

CREATE INDEX IF NOT EXISTS "meta_idx" ON "users" ("meta");

I hope this helps! Let me know if you need any further assistance.

Expected behavior

The expected result should be the creation of an index as shown below:

CREATE INDEX IF NOT EXISTS  "meta_idx" ON "users" USING GIN(meta jsonb_ops);

Also, it would be necessary to somehow optionally specify the jsonb_ops or jsonb_path_ops in the GIN index.

Environment & setup

No response

[StarpTech]

I run into this limit as well. Would be great if we can add any kind of index.

[AndriiSherman]

drizzle-kit currently can't read any options for indexes from drizzle-orm. But it's planned to be added in next drizzle-kit releases

[andersoncardoso]

@AndriiSherman I have a suggestion for cases like this, I guess that if we could somehow provide a scape hatch on the schema definition to append a sql string directly, that plays well with drizzle-kit migrations, this could solve many issues, including this one. Another example that would be solved is for having always generated as. For example:

export const user = pgTable(
  'users',
  {
    userId: serial('user_id').primaryKey(),
    meta: jsonb('meta').default({ ... }),
    otherField: text("other_field").sql(`GENERATED ALWAYS AS (...some expression...) STORED`)
  },
  (users) => ({
    metaIndex: index('meta_idx').on(users.meta).sql(`USING GIN(meta jsonb_ops)`),
  }),
)

I think that this fit well with drizzle's idea to embrace SQL ("if you know sql you know drizzle") and provides flexibility to the user. Nothing more natural than allowing them to pass sql to a schema for generating the migrations. Makes sense?

[warflash]

Just stumbled across this after a bit of research. By the looks of it advanced index creation is not working yet right? Any workarounds you guys might be able to share? Would be highly appreciated!