DT
Drizzle Team•2y ago
hotshoe

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 ⚡•2y 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
hotshoeOP•2y ago
Thanks Raphaël! That did the trick. Much appreciated 🙂

Did you find this page helpful?