Tudo sobre o query build Knex.js para NodeJS

Knex.js é um query builder para NodeJs que é capaz de conectar com: Postgres, SqlServer, Mysql, Maria Db, Oracle, Amazon Redshift e SQLite3. Com o Knex obetemos controle de fluxo assíncrono respostas padronizadas entre diferentes bancos. Conta também com um sistema de migrations.

Tudo sobre Knex.js:

Conexão com o Knex

//Conexão
var knex = require('knex')({
client: 'mysql',
  connection: {
    host: '127.0.0.1',
    user: 'your_database_user',
    password: 'your_database_password',
    database: 'myapp_test'
  },
  pool: { min: 0, max: 7 }
})

Criar Tabela

//Criar Tabela
knex.schema.createTable('user', (table) => {
  table.increments('id')
  table.string('name')
  table.integer('age')
})
.then(() => ···)

Colunas

//Colunas
  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

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

Indices

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

Select Knex

//Select
knex('users')
  .where({ email: 'hi@example.com' })
  .then(rows => ···)

//Select fields
knex
  .from('books')
  .select('title', 'author', 'year')

//Basic join
  .join('contacts', 'users.id', '=', 'contacts.id')
  .join('contacts', {'users.id': 'contacts.id'})

Update Knex

//Update
knex('users')
  .where({ id: 135 })
  .update({ email: 'hi@example.com' })

Distinct

//Distinct
knex('users')
  .distinct()

Group by

//Group by
  .groupBy('count')
  .groupByRaw('year WITH ROLLUP')

Order by

//Order by
  .orderBy('name', 'desc')
  .orderByRaw('name DESC')

Offset / limit

//Offset/limit
  .offset(10)
  .limit(20)

Having

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

Union

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

Functions

//functions
  .count('active')
  .count('active as is_active')
  .min('age')
  .max('age')
  .sum('age')
  .sumDistinct('age')
  .avg('age')

Insert

//Insert
knex('users')
  .insert({ email: 'hi@example.com' })

Delete Knex

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