Drizzle Team•2mo ago

Am i correctly created a unique email input for my form?

export const inquiries = createTable(
  "inquiry",
  {
    id: serial("id").primaryKey(),
    name: varchar("name", { length: 256 }),
    email: varchar("email", { length: 256 }).notNull().unique(),
    phone: varchar("phone", { length: 256 }).notNull(),
    status: varchar("status", { length: 256 }),
    location: varchar("location", { length: 256 }),
    date: timestamp("dateTime"),
    duration: integer("duration"),
    type: varchar("type", { length: 256 }),
    addOn: varchar("add_on", { length: 256 }),
    packages: varchar("packages", { length: 256 }),
    notes: varchar("notes", { length: 1024 }),
    discount: varchar("discount", { length: 256 }),
    people: integer("people").notNull(),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updatedAt"),
  },
  (inquiry) => ({
       emailUniqueIndex: uniqueIndex('emailUniqueIndex').on(lower(inquiry.email)),
     }),
  // (example) => ({
  //   invoiceIdIndex: index("invoice_id_idx").on(example.id),
  // })
);

export const inquiries = createTable(
  "inquiry",
  {
    id: serial("id").primaryKey(),
    name: varchar("name", { length: 256 }),
    email: varchar("email", { length: 256 }).notNull().unique(),
    phone: varchar("phone", { length: 256 }).notNull(),
    status: varchar("status", { length: 256 }),
    location: varchar("location", { length: 256 }),
    date: timestamp("dateTime"),
    duration: integer("duration"),
    type: varchar("type", { length: 256 }),
    addOn: varchar("add_on", { length: 256 }),
    packages: varchar("packages", { length: 256 }),
    notes: varchar("notes", { length: 1024 }),
    discount: varchar("discount", { length: 256 }),
    people: integer("people").notNull(),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updatedAt"),
  },
  (inquiry) => ({
       emailUniqueIndex: uniqueIndex('emailUniqueIndex').on(lower(inquiry.email)),
     }),
  // (example) => ({
  //   invoiceIdIndex: index("invoice_id_idx").on(example.id),
  // })
);

7 Replies

CBaker•2mo ago

im getting read error lines unnder lower. Should that be importedd form somehwere, I dont see anything about it in the docs These are the current imports

import { desc, sql, SQL} from "drizzle-orm";
import {
  index,
  pgTableCreator,
  serial,
  time,
  timestamp,
  varchar,
  integer,
  boolean,
  numeric, //percision how man digits and scale how many decimal places
  uniqueIndex,
  pgTable,
} from "drizzle-orm/pg-core";
import { stat } from "fs";

import { desc, sql, SQL} from "drizzle-orm";
import {
  index,
  pgTableCreator,
  serial,
  time,
  timestamp,
  varchar,
  integer,
  boolean,
  numeric, //percision how man digits and scale how many decimal places
  uniqueIndex,
  pgTable,
} from "drizzle-orm/pg-core";
import { stat } from "fs";

rphlmr ⚡•2mo ago

I’m not sure you can use lower here. It expects a column name, not a value.

rphlmr ⚡•2mo ago

If you want, you can create a zod or valibot schema and override the email to transform it into a lowercase email: https://orm.drizzle.team/docs/zod Then, you use it to parse/validate what you will insert

Drizzle ORM - drizzle-zod

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

CBaker•2mo ago

all I want to do is make sure the email entered is unique. Like .unqiue() added to the end like in sequelize DO you know how to make an intem have a constraint that makes sure it is unique is the real quesitons. Is that not possible with Drizzle?

rphlmr ⚡•2mo ago

You already add .unique() for email in your schema, so this should be good. The uniqueIndex you define is also doing this check + creates an index on it. But it is a strict comparison, so email@mail.com and Email@mail.com are not the same email To lower it and be sure all emails are unique, you have to lower it before inserting Or write custom sql check (not yet implemented) This why I suggested drizzle-zod if you do some validation somewhere in your code before inserting values on your tables Ok sorry

rphlmr ⚡•2mo ago

I have fixed your example: https://drizzle.run/kwwpxipbfaijyhccbnuryj9t

Drizzle Run

Unique with lower comparison - Drizzle Run

rphlmr ⚡•2mo ago

You was right, we can do that with unique index, with a little fix

export const users = pgTable(
  "users",
  {
    id: integer("id").primaryKey().generatedByDefaultAsIdentity(),
    email: text("email").notNull(),
  },
  (t) => ({
    emailIdx: uniqueIndex("email_idx").on(sql`lower(${t.email})`),
  }),
);

export const users = pgTable(
  "users",
  {
    id: integer("id").primaryKey().generatedByDefaultAsIdentity(),
    email: text("email").notNull(),
  },
  (t) => ({
    emailIdx: uniqueIndex("email_idx").on(sql`lower(${t.email})`),
  }),
);

Gaming

Programming

Am i correctly created a unique email input for my form?