Knex.js migrations with unique constraint function

Question

I am currently trying to migrate a new table into my database. This table is the users table. Here is the code that I have:

exports.up = function(knex, Promise) {
  return knex.schema.createTableIfNotExists('users', function(table) {
    table.increments('id').primary();
    table.string('first_name').notNullable();
    table.string('last_name').notNullable();
    table.string('email').unique().notNullable();
    table.string('password').notNullable();
    table.timestamps(false, true);
  }).then(() => {
    console.log('Users Table is Created!');
  })
};

exports.down = function(knex, Promies) {
  return knex.schema.dropTable('users')
    .then(() => {
      console.log('Users Table has been Dropped!');
    })
};

This returns the two errors:

Knex:warning - migrations failed with error: alter tableusersadd uniqueusers_email_unique(email) - Key column 'email' doesn't exist in table

Error: Key column 'email' doesn't exist in table

It appears that what unique() is trying to do is preform an alter table on a table that I'm trying to create. Thus the error that the email column doesn't exist. Am I doing something wrong here or can you not perform the unique() function on a column when creating a table? I saw examples of using the unique() function in a createTable on the docs. So I'm at a loss as to why this would respond with said error.

My database is getting created and I can see it. It just appears that the unique() constraint doesn't get applied to the email column that does NOT get created either.

Solution 1

knex.schema.createTableIfNotExists('users', function(table) {
    table.increments('id').primary();
    table.string('first_name').notNullable();
    table.string('last_name').notNullable();
    table.string('email').unique().notNullable();
    table.string('password').notNullable();
    table.timestamps(false, true);
  }).toSQL().forEach(query => console.log(query.sql))

creates following SQL

create table if not exists "users" ("id" serial primary key, "first_name" varchar(255) not null, "last_name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "created_at" timestamptz not null default CURRENT_TIMESTAMP, "updated_at" timestamptz not null default CURRENT_TIMESTAMP);
alter table "users" add constraint "users_email_unique" unique ("email");

So if table already has the constraint by that name the constraint creating query it will fail. But your error does seem to refer some other strange situation.

Solution 2

So it turns out that I need to drop all tables and re-run my migration. That is why it wasn't working and why I think it was trying to perform an alter table.