Error migrating "type does not exist"

im adding more stuff to my db, so i created this type and once i wanted to migrate i got his

[⢿] applying migrations...PostgresError: type "filetype" does not exist
    at ErrorResponse (/home/alejandro/.dev/toyota-ro/node_modules/drizzle-kit/bin.cjs:79677:27)
    at handle (/home/alejandro/.dev/toyota-ro/node_modules/drizzle-kit/bin.cjs:79454:7)
    at Socket.data (/home/alejandro/.dev/toyota-ro/node_modules/drizzle-kit/bin.cjs:79277:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42704',
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

[⢿] applying migrations...PostgresError: type "filetype" does not exist
    at ErrorResponse (/home/alejandro/.dev/toyota-ro/node_modules/drizzle-kit/bin.cjs:79677:27)
    at handle (/home/alejandro/.dev/toyota-ro/node_modules/drizzle-kit/bin.cjs:79454:7)
    at Socket.data (/home/alejandro/.dev/toyota-ro/node_modules/drizzle-kit/bin.cjs:79277:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42704',
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

the error is pointing out the type i created as lowercase, but its camel cased

export const fileTypeEnum = pgEnum('fileType', ['image/jpeg', 'application/pdf'])

export const files = pgTable('files', {
  id: text('id').primaryKey(),
  roId: text('roId').notNull(),
  name: text('name').notNull(),
  key: text('key').notNull(),
  url: text('url').notNull(),
  type: fileTypeEnum('type').notNull(),
  createdAt: timestamp('createdAt').defaultNow().notNull()
})

export const fileTypeEnum = pgEnum('fileType', ['image/jpeg', 'application/pdf'])

export const files = pgTable('files', {
  id: text('id').primaryKey(),
  roId: text('roId').notNull(),
  name: text('name').notNull(),
  key: text('key').notNull(),
  url: text('url').notNull(),
  type: fileTypeEnum('type').notNull(),
  createdAt: timestamp('createdAt').defaultNow().notNull()
})

the type exist in supabase, so i cant apply my other change because this one keeps on failing

DO $$ BEGIN
 CREATE TYPE "public"."fileType" AS ENUM('image/jpeg', 'application/pdf');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
ALTER TABLE "files" ALTER COLUMN "type" SET DATA TYPE fileType;

DO $$ BEGIN
 CREATE TYPE "public"."fileType" AS ENUM('image/jpeg', 'application/pdf');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
ALTER TABLE "files" ALTER COLUMN "type" SET DATA TYPE fileType;

10 Replies

francis•2mo ago

adding capital letters to postgres types is asking for a bad time if you could port it to file_type I would. You'll thank yourself later same with all your column names, honestly.

Pupok•2mo ago

so i should be moving to snake case for everything ? ill have to update alot of things, should i drop my supabase and rename everything and push new db schema ? so even roId should be ro_id?

francis•2mo ago

yes the reason is that postgres is case sensitive and queries are automatically converted to lower case unless you explicitly specify. It isn't a problem for drizzle, but it will be really annoying if you ever run queries by hand

francis•2mo ago

https://github.com/orgs/supabase/discussions/7095#discussioncomment-2860395

GitHub

Why can't I use a capitalized table name in my posgres trigger func...

Hi! I'm trying to set up my own public Profile table that holds custom data for my users. Every time a user signs up with Supabase Auth and is INSERTed into auth.users table, I want to run a tr...

francis•2mo ago

also, if you use supabase, I personally recommend writing your migrations / creating your schema with supabase which will let you drop down to raw sql to create row level security policies etc, then run a drizzle kit introspect to generate the drizzle schema from that. We use drizzle + supabase and that's what we do if you introspect with the camelcase option set, it will generate columnsNames in your schema correctly mapped to column_names in the db

Pupok•2mo ago

thanks !!

francis•2mo ago

fyi, if you use row level security with supabase, there is a good github discussion on how to implement it so you can have a drizzle client with only access to the current user's visible items, for security

francis•2mo ago

https://github.com/drizzle-team/drizzle-orm/issues/594

GitHub

[FEATURE]: Support PostgreSQL's Row Level Security (RLS) · Issue #5...

Describe want to want Supabase is really nicely using Row Level Secruity for granular authorization rules. 🔗 Here's the link to their docs: https://supabase.com/docs/guides/auth/row-level-secur...

Pupok•2mo ago

im going to check it out for sure, the app is on an early stage so we may implement this later, thanks alot

Mario564•2mo ago

@Pupok Hi there. Although what francis said is true about the fact that using snake case is better, Drizzle aims to support any casing. It'd be nice if you could create an issue on Github so the official team or any advocates can fix this issue later (hopefully soon) 🙂

Gaming

Programming

Error migrating "type does not exist"