CODEWITHSUNDEEP
postgresqlmigrationdatabase-migrationknex.js
JSK
JSK

Reputation: 1276

knex migration creat type for enum thows type already exists

I'm trying to alter a column in a table to modify knex enum to native types to benefit of the type system of Postgres, when I perform the migration I get this error type "request_type" already exists, any idea what's happening here ?

export async function up(knex: Knex): Promise<any> {
  return knex.schema.alterTable('appointments', table => {
    table.enu('type', ['video', 'physical'], { useNative: true, enumName: 'request_type' }).alter();
  });
}

export async function down(knex: Knex): Promise<any> {
  return knex.schema
    .alterTable('appointments', table => {
      table.dropColumn('type');
    })
    .then(() => knex.raw('CREATE TYPE request_type AS ENUM("video", "physical")'))
    .then(() => knex.raw('drop type request_type'));
}

Upvotes: 1

Views: 2343

Answers (2)

Udi Mazor
Udi Mazor

Reputation: 1826

There is a way to do it. You can create the type on the fly.

Let's say the I want to create a product column and to use "product" enum as its allowed values. We can use this query:

table.enu('product', ['level1', 'level2'], { useNative: true, enumName: 'product' })

This will create the type on the fly and will create a column with this enum type

Upvotes: -1

Mikael Lepist&#246;
Mikael Lepist&#246;

Reputation: 19718

Looks like there is a bug in knex which causes create type query to be added twice when altering columns like that.

https://runkit.com/embed/xqtl8p2knhi8

const Knex = require('knex');

const knex = Knex({
  client: 'pg',
});

knex.schema.alterTable('appointments', table => {
    table.enu('type', ['video', 'physical'], { useNative: true, enumName: 'request_type' }).alter();
}).toSQL()

/*
  Creates SQL:

0: Object {bindings: [], sql: "create type \"request_type\" as enum ('video', 'physical')"}
1: Object {bindings: [], sql: "create type \"request_type\" as enum ('video', 'physical')"}
2: Object {bindings: [], sql: "alter table \"appointments\" alter column \"type\" drop default"}
3: Object {bindings: [], sql: "alter table \"appointments\" alter column \"type\" drop not null"}
4: Object {bindings: [], …}
*/

Upvotes: 5

Related Questions