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

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));

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);

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?

2 Replies

pax•3mo ago

Same issue here

Angelelz•3mo ago

I don't think desc is implemented in drizzle kit You'll need to create that migration manually; and apply it

Gaming

Programming

Create index in DESC order