I’ve been working on the backend for a side project with Express, Objection.js, and Knex. As with most projects, users needed to have the ability to register with an email. I wanted my database to enforce the constraints and have valid indexes on the email{:sql} column.

[email protected]{:sql} and [email protected]{:sql} are the same per RFC822. A users should not be blocked from logging in due to the difference in casing on their email. We could call lower(email){:sql} ourselves but that skips the index we already have. Thankfully, we have access to CITEXT{:sql}.

CITEXT

CITEXT is a PostgreSQL data type for case insensitive text. When dealing with emails, I have found it better to use CITEXT. Running lower(column){:sql} manually is not ideal on every SQL query. This is compounded when you have a unique index on a column as the index will reference the case sensitive value.

Limitations

• It doesn’t handle unicode case insensitivity well.
• Not as efficient as text{:sql}, due to making copies of the data to make it lower case.
• Doesn’t work well if you want case sensitivity sometimes.

Even with these limitations, email is still a good fit for citext{:sql}.

Knex Migration

I decided to use Objection.js for my ORM which in turn uses Knex for queries and migrations. Let’s create a new migration via:

knex migrate:make ChangeEmailToCITextOnUsers

Let’s update our existing table to use citext{:sql} on email{:sql}. To do that, we need to enable the extension. Knex doesn’t have a way to do that directly but they do allow you to pass raw SQL.

exports.up = (knex) => {
  return knex.schema
    .raw('CREATE EXTENSION IF NOT EXISTS CITEXT') // Enables CITEXT on this database without throwing errors
    .alterTable('User', (t) => {
      t.specificType('email', 'CITEXT').notNullable().alter()
    })
}

exports.down = (knex) =>
  knex.schema.alterTable('User', (t) => {
    t.string('email').notNullable().alter()
  })

To use a type not available via Knex’s DSL, we can use specificType(column, type){:sql}. Running the migration using knex migrate:latest{:sh} will update our table to use CITEXT{:sql}: