DT
Drizzle Team•3mo ago
jsingleton37

Adding a new column

It has been awhile since I have touched my supabase DB 😅 😭 . I have a table 
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(),
})
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(),
})
I need to add a new column for sport and then backfill all the current records. In the past I remember adding the column in supabase's UI and that causing issues and don't want to go about that again 😅
2 Replies
JustWayne
JustWayne•3mo ago
You can set a default value in your column definition if you need sport to be notNull and and if necessary, after you generate your SQL migration you can edit the SQL file to add a custom command to back-fill them based on other column values (since you can't do that from a DEFAULT statement - https://stackoverflow.com/questions/16737738/postgresql-set-a-default-cell-value-according-to-another-cell-value ) So sport: integer('sport').default(0).notNull() and then in your SQL file UPDATE voter_ballot SET sport = (...) WHERE sport = 0; and if sport will have a foreign key constraint then you'll have to just pick an id to treat as the "dummy id" to set as the default instead of 0.
dfrn
dfrn•3mo ago
what @JustWayne is correct. Just adding that, If you're using drizzle-kit and are using migrations. You can create an empty migration file and do the backfill there, using SQL. https://orm.drizzle.team/docs/kit-custom-migrations
Drizzle ORM - Custom migrations
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Did you find this page helpful?