Drizzle Team•2y ago

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 ⚡•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)

hotshoeOP•2y ago

Thanks Raphaël! That did the trick. Much appreciated 🙂

Gaming

Programming

planetscale: how to index col using drizzle?