Drizzle Team•2y ago

Migration failure on fresh DB

I'm currently experiencing a migration failure on a fresh Postgres DB with both postgres.js and node-postgres. It's complaining that an enum I have defined in my schema (which should have created already as it's in the generated migration) does not exist.

⌛ Running Migrations
❌ Migration Failed
error: type "grandcompany" does not exist
    at Parser.parseErrorMessage (g:\code\htg-app\node_modules\.pnpm\[email protected]\node_modules\pg-protocol\dist\parser.js:287:98)
    at Parser.handlePacket (g:\code\htg-app\node_modules\.pnpm\[email protected]\node_modules\pg-protocol\dist\parser.js    at Socket.<anonymous> (g:\code\htg-app\node_modules\.pnpm\[email protected]\node_modules\pg-protocol\dist\index.js:11:42)
    at Socket.emit (node:events:512:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 101,
  severity: 'ERROR',
  code: '42704',
  detail: undefined,
  hint: undefined,
  position: '1817',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

⌛ Running Migrations
❌ Migration Failed
error: type "grandcompany" does not exist
    at Parser.parseErrorMessage (g:\code\htg-app\node_modules\.pnpm\[email protected]\node_modules\pg-protocol\dist\parser.js:287:98)
    at Parser.handlePacket (g:\code\htg-app\node_modules\.pnpm\[email protected]\node_modules\pg-protocol\dist\parser.js    at Socket.<anonymous> (g:\code\htg-app\node_modules\.pnpm\[email protected]\node_modules\pg-protocol\dist\index.js:11:42)
    at Socket.emit (node:events:512:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 101,
  severity: 'ERROR',
  code: '42704',
  detail: undefined,
  hint: undefined,
  position: '1817',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

6 Replies

volks•2y ago

Best to post your generated migration and the relevant schema file

Andrii Sherman•2y ago

Make sure you export all entities in schema file before running the migration Enums should also be exported

SilentSeanOP•2y ago

Yep, everything is exported.

import { pgEnum, pgTable, uuid, varchar } from 'drizzle-orm/pg-core';

export const grandCompany = pgEnum('grandCompany', ['Unknown', 'Maelstrom', 'Adder', 'Flames']);

export const character = pgTable('character', {
  id: uuid('id').defaultRandom().primaryKey(),
  xivId: varchar('xivId').notNull(),

  server: varchar('server').notNull(),
  dc: varchar('dc').notNull(),
  name: varchar('name').notNull(),

  grandCompany: grandCompany('grandCompany'),
});

import { pgEnum, pgTable, uuid, varchar } from 'drizzle-orm/pg-core';

export const grandCompany = pgEnum('grandCompany', ['Unknown', 'Maelstrom', 'Adder', 'Flames']);

export const character = pgTable('character', {
  id: uuid('id').defaultRandom().primaryKey(),
  xivId: varchar('xivId').notNull(),

  server: varchar('server').notNull(),
  dc: varchar('dc').notNull(),
  name: varchar('name').notNull(),

  grandCompany: grandCompany('grandCompany'),
});

DO $$ BEGIN
 CREATE TYPE "grandCompany" AS ENUM('Unknown', 'Maelstrom', 'Adder', 'Flames');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "character" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "xivId" varchar NOT NULL,
    "server" varchar NOT NULL,
    "dc" varchar NOT NULL,
    "name" varchar NOT NULL,
    "grandCompany" grandCompany
);

DO $$ BEGIN
 CREATE TYPE "grandCompany" AS ENUM('Unknown', 'Maelstrom', 'Adder', 'Flames');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "character" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "xivId" varchar NOT NULL,
    "server" varchar NOT NULL,
    "dc" varchar NOT NULL,
    "name" varchar NOT NULL,
    "grandCompany" grandCompany
);

Andrii Sherman•2y ago

Yeah, that’s a bug. Will fix it 2 workarounds for now 1. Change 1 param in pgEnum to “grand_company” 2. Add double quotes to grandCompany in sql file. Do it before applying to database First should be better if you fine with snake case naming on database side You using latest drizzle kit version?

SilentSeanOP•2y ago

Awesome, thanks! Yeah, I have drizzle kit 0.17.6 at the moment.

Andrii Sherman•2y ago

Thanks!

Gaming

Programming

Migration failure on fresh DB