[DOCS]: Index creation docs unclear

[baronyoung]

Enhancement hasn't been filed before.

• I have verified this enhancement I'm about to request hasn't been suggested before.

Describe the enhancement you want to request

The docs here are unclear. If I use the syntax first described, I get a 'deprecated' warning. The second description below, which it states it the new syntax, does not include what should surround it (obviously these lines should not be on the top level).

// with on()
index('name')
  .on(table.column1.asc(), table.column2.nullsFirst(), ...) or .onOnly(table.column1.desc().nullsLast(), table.column2, ...)
  .concurrently()
  .where(sql``)
  .with({ fillfactor: '70' })
// with using()
index('name')
  .using('btree', table.column1.asc(), sql`lower(${table.column2})`, table.column1.op('text_ops'))
  .where(sql``) // sql expression
  .with({ fillfactor: '70' })

I'm going with the deprecated version for now as I don't know how to use the new format based on the current docs. If anyone knows how this should be done I'd greatly appreciate the help.

[AdamAkiva]

I agree it is a confusing and the syntax I use and seems to work fine on the latest version of drizzle (0.38.3 & drizzle-kit 0.30.1) is:

const userModel = pgTable(
  'user',
  {
    id: uuid('id').primaryKey().defaultRandom().notNull(),
    firstName: varchar('first_name').notNull(),
    lastName: varchar('last_name').notNull(),
    email: varchar('email').unique().notNull(),
    hash: varchar('hash').notNull(),
    roleId: uuid('role_id')
      .references(
        () => {
          return roleModel.id;
        },
        { onDelete: 'no action', onUpdate: 'cascade' },
      )
      .notNull(),
    ...timestamps,
  },
  (table) => {
    // The relevant part
    return [
      uniqueIndex('user_email_unique_index').using('btree', table.email.asc()),
      index('user_role_id_index').using('btree', table.roleId.asc()),
      uniqueIndex('user_cursor_unique_index').using(
        'btree',
        table.id.asc(),
        table.createdAt.asc(),
      ),
    ];
  },
);

Basically you need to return an array with the relevant indexes.
For reference these are the relevant generated SQL statements: (asc is omitted due to it being the default order of the indexed field(s))

CREATE UNIQUE INDEX "user_email_unique_index" ON "user" USING btree ("email");--> statement-breakpoint
CREATE INDEX "user_role_id_index" ON "user" USING btree ("role_id");--> statement-breakpoint
CREATE UNIQUE INDEX "user_cursor_unique_index" ON "user" USING btree ("id","created_at");

As a side note, I agree it may be beneficial to add an example with the new syntax

[baronyoung]

Thank you. I had figured it out for the standard index, but I had it wrong for uniqueIndex for the table below and didn't even realize it (there was no error). Your example helped me fix it. Note: it seems 'return' is no longer necessary. Here's what I ended up with:

export const ruleset = pgTable('ruleset', {
	id: integer('id').primaryKey().generatedAlwaysAsIdentity(),
	name: varchar('name', {length: 15}).notNull(),
	version: doublePrecision().notNull(),
	modified: timestamp('modified', {withTimezone: true, mode: 'date'}).notNull().defaultNow(),
	status: rulesetStatus('status').notNull().default('draft'),
	favorite: boolean('favorite').notNull().default(false),
	chartFavoriteDates: varchar('chart_favorite_dates', {length: 10}).array().default([]).notNull(),
	holdPositionsTimeLimit: holdPositionsTimeLimitEnum('hold_positions_time_limit').default('day').notNull(),
	positionsLimit: positionsLimitEnum('positions_limit').default('one per account').notNull(),
	indicators: json('indicators').notNull().default([]).$type<Indicator[]>(),
	tradeStartTime: varchar('trade_start_time', {length: 5}).default('07:30').notNull(),
	tradeEndTime: varchar('trade_end_time', {length: 5}).notNull().default('15:00'),
	enterEndTime: varchar('enter_end_time', {length: 5}).notNull().default('14:00'),
	vwapStdDev: doublePrecision('vwap_std_dev').notNull().default(1.5)
}, (t) => [
	uniqueIndex('name_version_index').on(t.name, t.version)
])

[AdamAkiva]

Glad to here you resolved it, with or without my help.
Regarding the return statement, I know it can be omitted for a single statement but the reason I explicitly use a return is just a stylistic choice I've made in my own projects