DEV Community

Lam
Lam

Posted on

8 1

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs