DEV Community

Lam
Lam

Posted on

Knex Cheat Sheet

[Migrations] Setting up

Create knexfile.js

./node_modules/.bin/knex init
Enter fullscreen mode Exit fullscreen mode

Create a migration

knex migrate:make migration_name
knex migrate:make migration_name --env production
Enter fullscreen mode Exit fullscreen mode

Run migrations

knex migrate:latest
knex migrate:latest --env production
Enter fullscreen mode Exit fullscreen mode

Rollback

knex migrate:rollback
knex migrate:rollback --env production
Enter fullscreen mode Exit fullscreen mode

See: Migrations

[Modifying] Delete

knex('users')
  .where({ id: 2 })
  .del()
Enter fullscreen mode Exit fullscreen mode

See: Delete

[Modifying] Update

knex('users')
  .where({ id: 2 })
  .update({ name: 'Homer' })
Enter fullscreen mode Exit fullscreen mode

See: Update

[Modifying] Insert

knex('users')
Enter fullscreen mode Exit fullscreen mode

Insert one

  .insert({ name: 'John' })
Enter fullscreen mode Exit fullscreen mode

Insert many

  .insert([
    { name: 'Starsky' },
    { name: 'Hutch' }
  ])
Enter fullscreen mode Exit fullscreen mode

See: Insert

Modifying

{: .-three-column}

[Schema] Other methods

knex.schema
  .renameTable('persons', 'people')
  .dropTable('persons')
Enter fullscreen mode Exit fullscreen mode
  .hasTable('users').then(exists => ···)
  .hasColumn('users', 'id').then(exists => ···)
Enter fullscreen mode Exit fullscreen mode

See: Schema builder

[Schema] Alter table

knex.schema.table('accounts', table => {
Enter fullscreen mode Exit fullscreen mode

Create

  table.string('first_name')
Enter fullscreen mode Exit fullscreen mode

Alter

  table.string('first_name').alter()
  table.renameColumn('admin', 'is_admin')
Enter fullscreen mode Exit fullscreen mode

Drop

  table.dropColumn('admin')
  table.dropTimestamps('created_at')
Enter fullscreen mode Exit fullscreen mode
})
Enter fullscreen mode Exit fullscreen mode

{: .-setup}

See: Schema builder

[Schema] Create table

knex.schema.createTable('accounts', table => {
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

Constraints

  table.unique('email')
  table.unique(['email', 'company_id'])
  table.dropUnique(···)
Enter fullscreen mode Exit fullscreen mode

Indices

  table.foreign('company_id')
    .references('companies.id')
  table.dropForeign(···)
Enter fullscreen mode Exit fullscreen mode

Variations

  table.integer('user_id')
    .unsigned()
    .references('users.id')
Enter fullscreen mode Exit fullscreen mode
})
.then(() => ···)
Enter fullscreen mode Exit fullscreen mode

{: .-setup}

See: Schema builder

[Select] Etc

knex('users')
  .pluck('id')
  .then(ids => { ··· })
Enter fullscreen mode Exit fullscreen mode
knex('users')
  .first()
  .then(user => { ··· })
Enter fullscreen mode Exit fullscreen mode

Booleans

  .count('active')
  .count('active as is_active')
Enter fullscreen mode Exit fullscreen mode

Numbers

  .min('age')
  .max('age')
  .sum('age')
  .sumDistinct('age')
  .avg('age')
Enter fullscreen mode Exit fullscreen mode

See: Query builder

[Select] Others

knex('users')
  .distinct()
Enter fullscreen mode Exit fullscreen mode

Group

  .groupBy('count')
  .groupByRaw('year WITH ROLLUP')
Enter fullscreen mode Exit fullscreen mode

Order

  .orderBy('name', 'desc')
  .orderByRaw('name DESC')
Enter fullscreen mode Exit fullscreen mode

Offset/limit

  .offset(10)
  .limit(20)
Enter fullscreen mode Exit fullscreen mode

Having

  .having('count', '>', 100)
  .havingIn('count', [1, 100])
Enter fullscreen mode Exit fullscreen mode

Union

  .union(function() {
    this.select(···)
  })
  .unionAll(···)
Enter fullscreen mode Exit fullscreen mode

See: Query builder

Reference

Top comments (0)