Drizzle Team•7d ago

I messed up my supabase and drizzle setup

To start off, databases and ORMs I am not super familiar with and didn't realize I was just directly running migrations against the production db 🙃 so after chatting with some folks I decided to install docker and run pnpm supabase start locally and that ended up failing due to

ERROR: column "class_year" cannot be cast automatically to type integer (SQLSTATE 42804)
At statement 1: --> statement-breakpoint                                                
ALTER TABLE "transfer_portal_entries" ALTER COLUMN "class_year" SET DATA TYPE integer

ERROR: column "class_year" cannot be cast automatically to type integer (SQLSTATE 42804)
At statement 1: --> statement-breakpoint                                                
ALTER TABLE "transfer_portal_entries" ALTER COLUMN "class_year" SET DATA TYPE integer

I used to have a class_year column in that table, however, I no longer do as it was renamed and even shows up under https://github.com/JamesSingleton/redshirt-sports/tree/feature/transfer-portal/supabase as being renamed... So not entirely sure what to do from here.

GitHub

redshirt-sports/supabase at feature/transfer-portal · JamesSingleto...

Website for Redshirt Sports. Contribute to JamesSingleton/redshirt-sports development by creating an account on GitHub.

13 Replies

Mario564•7d ago

@jsingleton37 In what migration file is the error ocurring in the repo you linked? More specifically, where is the following statement located at? ALTER TABLE "transfer_portal_entries" ALTER COLUMN "class_year" SET DATA TYPE integer

jsingleton37OP•7d ago

So here is where the column got added https://github.com/JamesSingleton/redshirt-sports/blob/feature/transfer-portal/supabase/migrations/0012_burly_manta.sql

GitHub

redshirt-sports/supabase/migrations/0012_burly_manta.sql at feature...

Website for Redshirt Sports. Contribute to JamesSingleton/redshirt-sports development by creating an account on GitHub.

jsingleton37OP•7d ago

And then I guess added here https://github.com/JamesSingleton/redshirt-sports/blob/feature/transfer-portal/supabase/migrations/0015_fat_trauma.sql

GitHub

redshirt-sports/supabase/migrations/0015_fat_trauma.sql at feature/...

Website for Redshirt Sports. Contribute to JamesSingleton/redshirt-sports development by creating an account on GitHub.

jsingleton37OP•7d ago

I also can’t run the generate command at the moment but I was told that due to something I did that I don’t fully recall doing 😅 None of the new tables added in that branch are being used in production by I honestly don’t know the best way to clean everything up

Mario564•7d ago

If you're not using any of those tables then perhaps it would be best for the migrations to start from scratch

jsingleton37OP•7d ago

How exactly do I do that without messing up the ones that I do use?

Mario564•7d ago

Before doing that, I'll try to solve the initial issue, I think that should solve everything I found this statement in migration 0013 line 7. To solve the error you're getting, change that line to this:

ALTER TABLE "transfer_portal_entries" ALTER COLUMN "class_year" SET DATA TYPE integer USING "class_year"::integer;--> statement-breakpoi

ALTER TABLE "transfer_portal_entries" ALTER COLUMN "class_year" SET DATA TYPE integer USING "class_year"::integer;--> statement-breakpoi

jsingleton37OP•7d ago

What if I just want to start from scratch

Mario564•7d ago

If you want to do that, you'll have to delete your migrations folder and reset the database you're using I run this to achieve the latter:

async function resetDatabase(db: typeof db) {
  if (env.NODE_ENV === 'production') return;
  await db.execute('drop schema if exists "public" cascade');
  await db.execute('create schema "public"');
  await db.execute('drop schema if exists "drizzle" cascade');
  await db.execute('create schema "drizzle"');
}

async function resetDatabase(db: typeof db) {
  if (env.NODE_ENV === 'production') return;
  await db.execute('drop schema if exists "public" cascade');
  await db.execute('create schema "public"');
  await db.execute('drop schema if exists "drizzle" cascade');
  await db.execute('create schema "drizzle"');
}

jsingleton37OP•7d ago

🤔 that would also delete those in use though?

Mario564•7d ago

Yeah, but that's what I mean by "starting from scratch"

jsingleton37OP•7d ago

Ah ok, yea I have some of my tables that I need to keep because it's being used in production Was just curious if there was a way I could delete some migrations for the latest tables along with the .sql

Mario564•7d ago

You can try using the drop command in Drizzle Kit

Gaming

Programming

I messed up my supabase and drizzle setup