Supabase•2w ago

Can you add a unique to an existing table? supabase + drizzle

I am using supabase and drizzle in my Next.js application and unfortunately noticed a year later I forgot to add a unique().on() for a table that holds voter ballots. 🫠 I thought everything was fine and dandy and no duplicates were being inserted into the DB until this week when I noticed some. 😬 The schema for this table looks like

export const voterBallots = pgTable(
  'voter_ballot',
  {
    id: serial('id').primaryKey(),
    userId: varchar('userId', { length: 256 }).notNull(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year')
      .default(sql`EXTRACT(year FROM CURRENT_DATE)`)
      .notNull(),
    createdAt: timestamp('created_at')
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    teamId: varchar('team_id', { length: 256 }).notNull(),
    rank: integer('rank').notNull(),
    points: integer('points').notNull(),
    sportId: varchar('sport_id', { length: 256 }).references(() => sportsTable.id),
  }
)

export const voterBallots = pgTable(
  'voter_ballot',
  {
    id: serial('id').primaryKey(),
    userId: varchar('userId', { length: 256 }).notNull(),
    division: varchar('division', { length: 10 }).notNull(),
    week: integer('week').notNull(),
    year: integer('year')
      .default(sql`EXTRACT(year FROM CURRENT_DATE)`)
      .notNull(),
    createdAt: timestamp('created_at')
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    teamId: varchar('team_id', { length: 256 }).notNull(),
    rank: integer('rank').notNull(),
    points: integer('points').notNull(),
    sportId: varchar('sport_id', { length: 256 }).references(() => sportsTable.id),
  }
)

And I am thinking of adding (table) => [unique().on(table.userId, table.division, table.week, table.year, table.sportId)], to it and then generating and then migrating. I guess my question is, will this break anything? I believe I removed all the duplicates and want to make sure I get this in there before more votes get added.

5 Replies

silentworks•2w ago

You should have a staging environment where you test such changes. This is how folks developing production applications work. I don't know drizzle inner workings but it should generate a migration file before applying it, so you can look at the generated migration file and see what actions it will perform and see if any of them are destructive.

jsingleton37OP•2w ago

Gotcha, yeah I need to figure out a way to seed my local Supabase with what is in production so I have a bunch of data to work with 😅 I was not good about testing locally in the beginning since the project was small 😬🥲 but now it has grown to a point where I need to be careful.

silentworks•2w ago

I'd suggest getting your schema setup in a local instance and then seed the database with some data and try performing the drizzle schema change to see if there are any issues. This isn't something I'd rely on someone on the internet to tell me as they don't have my environment setup or know my exact setup.

jsingleton37OP•2w ago

Any docs on how to get the data from my prod DB to my local/seeding my local db?

silentworks•2w ago

You should never pull production data into your local instance, especially if it's sensitive data. You should create fake dummy data for your local instance.

Gaming

Programming

Can you add a unique to an existing table? supabase + drizzle

Did you find this page helpful?