knex migration code example

Example 1: move records from table to another using knex migration

// modified by Emmanuel Mahuni to use async/await more readable code
exports.up = async function(knex) {
 	await knex.schema.createTable('table_b', t => {
      t.string('col_a')
      t.string('col_b')
    })
  
    await knex.schema.createTable('table_c', t => {
      t.string('col_c')
      t.string('col_d')
    })
  
    let rows = await knex('table_a').select('col_a', 'col_b')
    await knex('table_b').insert(rows)
    
  	rows = await knex('table_a').select('col_c', 'col_d')
    await knex('table_c').insert(rows)
  
    await knex.schema.dropTableIfExists('table_a'))
};

exports.down = async function(knex) {
  await knex.schema.createTable('table_a', t => {
      t.string('col_a')
      t.string('col_b')
      t.string('col_c')
      t.string('col_d')
    })
  
  let rows = await knex('table_b').select('col_a', 'col_b')
  await knex('table_a').insert(rows)
  
  rows = await knex('table_c').select('col_c', 'col_d')
  await knex('table_a').insert(rows)
  
  await knex.schema.dropTableIfExists('table_b')
  await knex.schema.dropTableIfExists('table_c')
};

Example 2: knex migration

// best tutorial knex migration
https://www.youtube.com/watch?v=ipAH7lMfq7k
https://www.youtube.com/watch?v=U7GjS3FuSkA

Example 3: knex migrations tinyint

table.specificType('ColumName', 'smallint');

Example 4: knex datatypes

table.increments('id')
  table.string('account_name')
  table.integer('age')
  table.float('age')
  table.decimal('balance', 8, 2)
  table.boolean('is_admin')
  table.date('birthday')
  table.time('created_at')
  table.timestamp('created_at').defaultTo(knex.fn.now())
  table.json('profile')
  table.jsonb('profile')
  table.uuid('id').primary()

Example 5: knex js join schema

exports.up = (knex) => {
  knex.schema.createTable('bookings', (table) => {
    table.increment('booking_id').primary()
    table.foreign('user_id').references('user_id').inTable('users').notNullable().onDelete('CASCADE').index()
    table.foreign('room_id').references('room_id').inTable('rooms').notNullable().onDelete('CASCADE').index()
    table.integer('total_person').notNullable().defaultTo(0)
    table.datetime('booking_time').notNullable().defaultTo(knex.fn.now())
    table.string('noted').notNullable()
    table.datetime('check_in_time').defaultTo(knex.fn.now())
    table.datetime('check_out_time').defaultTo(knex.fn.now())
    table.timestamp('created_at').defaultTo(knex.fn.now())
    table.timestamp('update_at').defaultTo(knex.fn.now())
    table.timestamp('delete_at').defaultTo(knex.fn.now())
  })
}

exports.down = (knex) => knex.schema.dropTable('bookings')

Example 6: knexjs execute all migrations

$ knex migrate:make create_users_table
$ knex migrate:make create_tasks_table