Feature request: an alias for creating postgre indexes CONCURRENTLY in a migration

[capaj]

currently if I want to create an index in postgre CONCURRENTLY it requires some config and using the db.raw, like this:

import Knex from 'knex'

const table = 'scheduled_campaign_actions'

exports.up = async (db: Knex) => {
  await db.schema.raw(`
    CREATE INDEX CONCURRENTLY IF NOT EXISTS ${table}_notification_id_index
	  ON ${table} (notification_id);
  `)
}

exports.down = async (db: Knex) => {
  await db.schema.alterTable(table, (table) => {
    table.dropIndex('notification_id')
  })
}

exports.config = {
  transaction: false
}

it sure would be nice, if this could be achievable with some config alias, for example like this:

import Knex from 'knex'

const tableName = 'scheduled_campaign_actions'

exports.up = async (db: Knex) => {
  await db.schema.alterTable(tableName, (table) => {
    table.index('notification_id')
  })
}

exports.down = async (db: Knex) => {
  await db.schema.alterTable(tableName, (table) => {
    table.dropIndex('notification_id')
  })
}

exports.config = {
  indexConcurrently: false
}

[elhigu]

Having table.index(..., opts) would be knexy way to implement support for this.

[capaj]

That would be. Problem is you can't have it in transaction so you'd have to always explicitly specify this like so:

import Knex from 'knex'

const tableName = 'scheduled_campaign_actions'

exports.up = async (db: Knex) => {
  await db.schema.alterTable(tableName, (table) => {
    table.index('notification_id', {concurrently: true})
  })
}

exports.down = async (db: Knex) => {
  await db.schema.alterTable(tableName, (table) => {
    table.dropIndex('notification_id', {concurrently: true})
  })
}

exports.config = {
  transaction: false
}

which for me would be okay too, just a little more verbose.

[raijinsetsu]

@capaj I think it would work out fine. Knex does not execute those schema operations until the function returns. It then orders those operations: table creation, constraint creation, index creation - they have to occur in this order even when inside a transaction because you cannot create an index on a column that does not exist. When creating these types of indexes, the first transaction would create the table, etc., but not those "concurrent" indexes. It would then not use a transaction for those.

Side note:
I just want to point out that you should never use string templates or constructed strings in the queries. Re-write your raw queries to use bindings:

await db.schema.raw('
    CREATE INDEX CONCURRENTLY IF NOT EXISTS :index:
	  ON :table: (notification_id);
  ', {index: `${table}_notification_id_index`, table})

This makes it impossible for SQL injection attacks to occur as the bindings are properly escaped. Reference: http://knexjs.org/#Raw-Bindings

Sorry if that seems preachy and you already know... but I've seen too many security vulnerabilities in software to overlook it.

[capaj]

@raijinsetsu migrations don't deal with user input at all. There's no possibility of SQL injection there.

Right, knex schema builder could skip the transacting() call. That could theoretically work.

[elhigu]

@raijinsetsu actually identifier bindings are not correctly escaped by knex, but knex just adds siple quoting. Knex also may try to escape it to be safe, but doesn’t guarantee anything. I wouldn’t trust it being safe.

So one must always do manual validation for identifiers read from user input.