Knex Cheat Sheet
Tags: knex, cheatsheet
Published: 2021-05-13 10:49:05


### [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](http://knexjs.org/#Migrations)
### [Modifying] Delete

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

See: [Delete](http://knexjs.org/#Builder-del)

### [Modifying] Update

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

See: [Update](http://knexjs.org/#Builder-update)

### [Modifying] Insert

```js
knex('users')
```

#### Insert one

```js
  .insert({ name: 'John' })
```

#### Insert many

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

See: [Insert](http://knexjs.org/#Builder-insert)

### Modifying
{: .-three-column}

### [Schema] Other methods

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

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

See: [Schema builder](http://knexjs.org/#Schema)

### [Schema] Alter table

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

#### Create

```js
  table.string('first_name')
```

#### Alter

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

#### Drop

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

```js
})
```
{: .-setup}

See: [Schema builder](http://knexjs.org/#Schema)

### [Schema] Create table

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

#### Columns

```js
  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

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

#### Indices

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

#### Variations

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

```js
})
.then(() => ···)
```
{: .-setup}

See: [Schema builder](http://knexjs.org/#Schema)

### [Select] Etc

```js
knex('users')
  .pluck('id')
  .then(ids => { ··· })
```
```js
knex('users')
  .first()
  .then(user => { ··· })
```

#### Booleans

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

#### Numbers

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

See: [Query builder](http://knexjs.org/#Builder)

### [Select] Others

```js
knex('users')
  .distinct()
```

#### Group

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

#### Order
```js
  .orderBy('name', 'desc')
  .orderByRaw('name DESC')
```

#### Offset/limit

```js
  .offset(10)
  .limit(20)
```

#### Having

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

#### Union

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

See: [Query builder](http://knexjs.org/#Builder)

### Reference
* [Knex Cheat Sheet](https://cheatsheetmaker.com/knex) - [Cheat Sheet Maker](https://cheatsheetmaker.com)