Drizzle-ORM creating hash index on table

Question

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. In the bottom you will find my current attempt.

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

Drizzle index documentation screenshot

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.

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");`,
    };
  }
);

Why would you do this from an app? Business-grade apps don't allow DML in production. — The Impaler
– The Impaler, Commented May 29, 2024 at 13:57
Usually your application database role won’t have enough permissions to create database objects like indexes. That’s not safe — Frank Heikens
– Frank Heikens, Commented May 30, 2024 at 2:09
The code is only manually run from the CLI not the application to trigger schema changes that manifest in the production database. It is part of the Drizzle ORM tool. Or am I missing something here? — Max Xie
– Max Xie, Commented May 30, 2024 at 15:18

Yhozen · Accepted Answer · 2024-08-07 12:11:30Z

0

Since 0.31 it is possible to have more complex indexes. See here

PD: It is completely safe to create indexes in this way, I think they were not familiar with drizzle.

answered Aug 7, 2024 at 12:11

Yhozen

1,3371 gold badge9 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Drizzle-ORM creating hash index on table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related