Declaring indexes with index() and uniqueIndex()
Use index('name_idx').on(table.column) to declare a regular index. Use uniqueIndex('email_idx').on(table.column) to declare a unique index. Both are declared in the table definition callback.
cockroach/indexes
9 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
Index parameters: .on(), .onOnly(), .using(), and .where()
Indexes support the following parameters: .on() to specify columns with optional .asc() or .desc() ordering, .onOnly() for index-only constraints, .using() to specify the index type (e.g., 'btree') followed by columns and expressions, and .where() to add a SQL WHERE condition. Example: index('name').on(table.column1.asc()).where(sql``) or index('name').using('btree', table.column1.asc(), sql`lower(${table.column2})`)
PostgreSQL indexes on expressions require explicit names
When creating a PostgreSQL index on at least one expression, you must manually specify a name for the index. Indexes on simple columns will have names auto-generated, but indexes using expressions like index().on(sql`lower(${table.email})`) will produce an error unless a name is explicitly provided.
PostgreSQL push workflow cannot modify certain index properties
When using push with PostgreSQL, changes to the following index properties will not generate statements: expressions inside .on() and .using(), .where() statements, and operator classes .op() on columns. To modify these fields with push, you must comment out the index, push, uncomment and modify it, then push again.
PostgreSQL index on multiple columns without expressions
An index created with index().on(table.id, table.email) will work correctly and the index name will be auto-generated.
PostgreSQL index with explicit name on multiple columns
An index created with index('my_name').on(table.id, table.email) works correctly with a manually specified name.
PostgreSQL index on expression with name is valid
An index created with index('my_name').on(sql`lower(${table.email})`) is valid and will work correctly.
PostgreSQL index on expression without name causes error
An index created with index().on(sql`lower(${table.email})`) without an explicit name will produce an error.
Create indices for cursor-based pagination columns
To optimize cursor-based pagination queries, create indices on the columns used for cursor comparison. Use the index() function in the table definition with .on() to specify columns and .asc() for order direction. Example: index('first_name_index').on(t.firstName).asc() and index('first_name_and_id_index').on(t.firstName, t.id).asc(). These indices ensure efficient cursor comparison queries.