Why is this Knex migration not forcing a column to be unique?

Question

I'm creating a SQLite database with this Knex migration. When I review the DB in SQLiteStudio, it doesn't indicate that the email column is unique. Is there a mistake I'm missing?

exports.up = function (knex) {
    return knex.schema
        .createTable('users', users => {
            users.increments();
            users.string('email', 128).unique().notNullable();
            users.string('password', 256).notNullable();
        })

Generated DDL code:

CREATE TABLE users (
    id       INTEGER       NOT NULL
                           PRIMARY KEY AUTOINCREMENT,
    email    VARCHAR (128) NOT NULL,
    password VARCHAR (256) NOT NULL
);

Alternatives I've tried that didn't work:

-Switching order of unique() and notNullable()

users.string('email', 128).notNullable().unique()

-Creating a separate line to add the Unique constraint

        .createTable('users', users => {
            users.increments();
            users.string('email', 128).notNullable();
            users.string('password', 256).notNullable();
            users.unique('email');
        })

Rich Churcher · Accepted Answer · 2019-11-10T19:04:08.007

It's unique, you're just not going to see it in the CREATE TABLE statement. SQLite sets a UNIQUE constraint by creating an index with the UNIQUE qualifier. Take the following Knex migration, for example:

exports.up = knex =>
  knex.schema.debug().createTable("users", t => {
    t.increments("id");
    t.string("name").unique();
  });

Note debug(), very handy if you want to see what SQL is being generated. Here's the debug output:

[
  {
    sql: 'create table `users` (`id` integer not null ' +
      'primary key autoincrement, `name` ' +
      'varchar(255))',
    bindings: []
  },
  {
    sql: 'create unique index `users_name_unique` on `users` (`name`)',
    bindings: []
  }
]

As you can see, a second statement is issued to create the UNIQUE constraint. If we now go and look at the database, we'll see something like:

07:48 $ sqlite3 dev.sqlite3
sqlite> .dump users
BEGIN TRANSACTION;
CREATE TABLE `users` (`id` integer not null primary key autoincrement,
  `name` varchar(255));
CREATE UNIQUE INDEX `users_name_unique` on `users` (`name`);
COMMIT;

As an aside, you may wish to do more research about the possible length of user emails. See this answer as a starting point.

Why is this Knex migration not forcing a column to be unique?

1 Answers1