Knex Cheat Sheet

[Migrations] Setting up

Create knexfile.js

./node_modules/.bin/knex init

Create a migration

knex migrate:make migration_name
knex migrate:make migration_name --env production

Run migrations

knex migrate:latest
knex migrate:latest --env production

Rollback

knex migrate:rollback
knex migrate:rollback --env production

See: Migrations

[Modifying] Delete

knex('users')
  .where({ id: 2 })
  .del()

See: Delete

[Modifying] Update

knex('users')
  .where({ id: 2 })
  .update({ name: 'Homer' })

See: Update

[Modifying] Insert

knex('users')

Insert one

  .insert({ name: 'John' })

Insert many

  .insert([
    { name: 'Starsky' },
    { name: 'Hutch' }
  ])

See: Insert

Modifying

{: .-three-column}

[Schema] Other methods

knex.schema
  .renameTable('persons', 'people')
  .dropTable('persons')

  .hasTable('users').then(exists => ···)
  .hasColumn('users', 'id').then(exists => ···)

See: Schema builder

[Schema] Alter table

knex.schema.table('accounts', table => {

Create

  table.string('first_name')

Alter

  table.string('first_name').alter()
  table.renameColumn('admin', 'is_admin')

Drop

  table.dropColumn('admin')
  table.dropTimestamps('created_at')

})

{: .-setup}

See: Schema builder

[Schema] Create table

knex.schema.createTable('accounts', table => {

Columns

  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()

Constraints

  table.unique('email')
  table.unique(['email', 'company_id'])
  table.dropUnique(···)

Indices

  table.foreign('company_id')
    .references('companies.id')
  table.dropForeign(···)

Variations

  table.integer('user_id')
    .unsigned()
    .references('users.id')

})
.then(() => ···)

{: .-setup}

See: Schema builder

[Select] Etc

knex('users')
  .pluck('id')
  .then(ids => { ··· })

knex('users')
  .first()
  .then(user => { ··· })

Booleans

  .count('active')
  .count('active as is_active')

Numbers

  .min('age')
  .max('age')
  .sum('age')
  .sumDistinct('age')
  .avg('age')

See: Query builder

[Select] Others

knex('users')
  .distinct()

Group

  .groupBy('count')
  .groupByRaw('year WITH ROLLUP')

Order

  .orderBy('name', 'desc')
  .orderByRaw('name DESC')

Offset/limit

  .offset(10)
  .limit(20)

Having

  .having('count', '>', 100)
  .havingIn('count', [1, 100])

Union

  .union(function() {
    this.select(···)
  })
  .unionAll(···)

See: Query builder

Reference

• Knex Cheat Sheet - Cheat Sheet Maker