planetscale: how to index col using drizzle?

Hi, seems most of the samples are pg. I'm using planetscale, and no having any luck figuring out how to index a col. here's simple table I'm wanting to use to test/eval:
import {
varchar,
text,
mysqlTable,
timestamp,
} from "drizzle-orm/mysql-core";

export const users = mysqlTable(
"users",
{
id: varchar("id", { length: 16 }).primaryKey(),
email: varchar("email", { length: 320 }),
name: text("name").notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
},
(table) => ({
// how to index email?
})
);
import {
varchar,
text,
mysqlTable,
timestamp,
} from "drizzle-orm/mysql-core";

export const users = mysqlTable(
"users",
{
id: varchar("id", { length: 16 }).primaryKey(),
email: varchar("email", { length: 320 }),
name: text("name").notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
},
(table) => ({
// how to index email?
})
);
Any help much appreciated. Thanks!
2 Replies
rphlmr ⚡
rphlmr ⚡16mo ago
Hello, did you try:
import {
varchar,
text,
mysqlTable,
timestamp,
index
} from "drizzle-orm/mysql-core";

export const users = mysqlTable(
"users",
{
id: varchar("id", { length: 16 }).primaryKey(),
email: varchar("email", { length: 320 }),
name: text("name").notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
},
(table) => ({
// 👇 the name you want for your index, could be empty
emailIdx: index('email_idx').on(table.email)
})
);
import {
varchar,
text,
mysqlTable,
timestamp,
index
} from "drizzle-orm/mysql-core";

export const users = mysqlTable(
"users",
{
id: varchar("id", { length: 16 }).primaryKey(),
email: varchar("email", { length: 320 }),
name: text("name").notNull(),
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
},
(table) => ({
// 👇 the name you want for your index, could be empty
emailIdx: index('email_idx').on(table.email)
})
);
if it should be also unique, you can swap with uniqueIndex (ref: https://orm.drizzle.team/docs/mysql/indexes-foreign-primary-keys)
hotshoe
hotshoe16mo ago
Thanks Raphaël! That did the trick. Much appreciated 🙂