Index using hash

[MaxHXie]

I want to create an index on a table in my Drizzle schema with one caveat, I want the index to be using the HASH index type.

However, when I am looking into the Drizzle documentation I see that there may or may not be support for this already, if the documentation is accurate, I would of course prefer to use its methods instead of writing custom SQL. Link to Drizzle documentation

The documentation states that only the name and on() params are currently supported. However, the example immediately below the statement says otherwise. Additionally, the typescript types mentioned in the example are defined in the codebase. And lastly, why does the using method take a SQL statement as the input and not a string (e.g. "b-tree", "hash" etc.)? Anyone from the Drizzle ORM community who could give me some guidance on this? Thanks.

My own attempt using custom SQL so far.

export const template = pgTable(
  "post",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    authorId: text("authorId")
      .notNull()
      .references(() => users.id, {onDelete: "cascade"}),
  },
  (table) => {
    return {
      authorIdIdx: sql`CREATE INDEX "authorIdIdx" ON "post" USING HASH ("authorIdId");`,
    };
  }
);

[pripishchik]

did you find a solution?

[zy1p]

In case you are using pg, this should work.

export const template = pgTable(
  "post",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => crypto.randomUUID()),
    authorId: text("authorId")
      .notNull()
      .references(() => users.id, {onDelete: "cascade"}),
  },
  (table) => {
    return {
      // authorIdIdx: sql`CREATE INDEX "authorIdIdx" ON "post" USING HASH ("authorIdId");`,
      authorIdIdx: index().using(
        "hash",
        table.authorIdId,
      )
    };
  }
);

[cjkihl]

Update. You're currently using the old syntax for defining indexes in pgTable
The new syntax uses an array-based structure for defining indexes:

(tbl) => [
  index("author_id_idx").using("hash").on(tbl.authorId),
]