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 column.

bob@example.com and BOB@example.com 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) ourselves but that skips the index we already have. Thankfully, we have access to CITEXT.

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) 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, 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.

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:

1

knex migrate:make ChangeEmailToCITextOnUsers

Let’s update our existing table to use citext on email. 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.

1
2
3
4
5
6
7
8
9
10
11

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). Running the migration using knex migrate:latest will update our table to use CITEXT:

Describing User table