Drizzle TeamDT
Drizzle Team•2y ago
Zeraph

Create index in DESC order

Consider this schema:

export const contactFormSubmission = pgTable(
  'contact_form_submission',
  {
    id: serial('id').primaryKey(),
    name: varchar('name').notNull(),
    email: varchar('email'),
    subject: varchar('subject'),
    message: varchar('message').notNull(),
    showInGuestbook: boolean('show_in_guestbook').default(false),
    submittedAt: timestamp('submitted_at').defaultNow(),
  },
  ({ showInGuestbook, submittedAt }) => ({
    guestbookIdx: index().on(showInGuestbook, submittedAt),
  }),
);


The index of the query is expected to serve this query only, so I wanna have it in DESC order.

db
    .select()
    .from(contactFormSubmission)
    .where(eq(contactFormSubmission.showInGuestbook, true))
    .orderBy(desc(contactFormSubmission.submittedAt));


I.e. the goal:

CREATE INDEX contact_form_submission_show_in_guestbook_submitted_at_index 
ON public.contact_form_submission 
USING btree 
(show_in_guestbook DESC, submitted_at DESC);


The issue is desc() doesn't seem to do any when declaring the index. How am I supposed to specify DESC in this case?
Was this page helpful?