Issues with PostgreSQL Migrations: Unexplained Column Existence Errors, Revert Challenges, and Supabase Dashboard

[wojtekKrol]

Describe the bug

When working with migrations in PostgreSQL using Drizzle Kit, I encounter a series of challenges. Most notably, I get console errors claiming that a column already exists, even after attempts to revert the changes. Additionally, none of these changes appear to be reflected in the Supabase dashboard.

To Reproduce

Here are the steps to reproduce:

1. Create an initial table definition using Drizzle Kit:

   export const todos = pgTable('todos', {
     id: serial('id').primaryKey(),
     title: varchar('title', { length: 256 }),
     description: varchar('description', { length: 256 }),
     isDone: boolean('isDone').default(false),
   });

2. Run yarn dev to fire the migration on server startup.

3. Add a new column to the table definition:

   export const todos = pgTable('todos', {
     // ... (previous columns),
     isNotDone: boolean('isNotDone').default(false),
   });

4. Run the migration again.
5. Console error suggests that the column "isNotDone" already exists.
6. Revert the table definition to its previous state and generate a new migration file.
7. Run the migration script again.

Expected behavior

I expect the migration to work smoothly without throwing errors about pre-existing columns when a new column is actually being added. Additionally, reverting changes should be straightforward and reflected in the Supabase dashboard.

Error Messages / Logs

Here are some of the console errors received:

PostgresError: column "isNotDone" of relation "todos" already exists

Moreover, each migration run logs the following notices:

{
  "severity_local": "NOTICE",
  "code": "42P06",
  "message": "schema \"drizzle\" already exists, skipping",
  ...
}

Questions

1. Is there a simpler way to revert changes to a table?
2. Can drizzle kit drop be used to remove the last migration files and run the app again?
3. Are the 'NOTICE' logs just informational or is there something I should address?
4. Why are the changes not being reflected in the Supabase dashboard?

Additional Context

My migration script is as follows:

import { migrate } from 'drizzle-orm/postgres-js/migrator';
import db from './db';

export const migrateDB = async () => {
  console.log('migrating db');
  await migrate(db, { migrationsFolder: 'drizzle/migrations' });
  console.log('db migrated');
};

migrateDB();

[JayFranco]

I'm having the same issue with this exact setup.

[sam-frampton]

I'm having this issue as well trying to connect to supabase with same setup

[kravetsone]

+1