How To Create Table If Not Exists with Knex

I recently found myself in a situation where I needed to create a database table and check IF NOT EXISTS. Personally, I think it's probably almost always a good idea to check IF NOT EXISTS while creating a database table, but I was using knex and didn't think to add that condition.

If you are curious how to add IF NOT EXISTS there are two ways to do this in your database migrations.

1. Use Raw SQL:

One way is to use raw SQL, so your migration will look like this:

const tableName = 'table_one'

exports.up = async function (knex) {
  const exists = await knex.schema.raw(`tableName, 
  id integer primary key
  column_name text
  `)
}

exports.down = async function (knex) {
  await knex.schema.dropTableIfExists(tableName)
}

2. Use Knex hasTable:

The second option is to check if the table exists using the knex function hasTable.

Here is an example of how that would work with the same table as above.

const tableName = 'table_one'

exports.up = async function (knex) {
  const exists = await knex.schema.hasTable(tableName)
  if (!exists) {
    await knex.schema
      .createTable(tableName, (table) => {
        table.integer('id').primary()
        table.text('column_name)
      })
  }
}

exports.down = async function (knex) {
  await knex.schema.dropTableIfExists(tableName)
}

In this method, we check to see if the table currently exists using hasTable, and if it does not then we will create the table.

Conclusion

I actually tried createTableIfNotExists because I had seen that on StackOverflow. When I ran my migrations, I got a warning in my console telling me to use the hasTable method instead. Either of these ways will allow you to check if the table exists while creating a database table with knex, but they do not recommend using createTableIfNotExists anymore, and it is no longer in their documenation.