Knex

SQL query builder, install plugins like pg lib separately

npm install knex

Initialization

Initializing the Library

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});

Usage

Select

await knex.select('title', 'author').from('books') 
#Only first record
await knex.first('title', 'author').from('books')

select returns a list of json

first returns a single json

if no entry, returns undefined or empty list

Insert

await knex('_plugin_logs').insert(req.body);

Add .returning('*') to return data

Query

knex('users').where('id', 1)
const results = await knex.select()
                                                .from('_plugin_logs');
knex('accounts')
  .where('activated', false)
  .del()

knex({ a: 'table', b: 'table' })
  .select({
    aTitle: 'a.title',
    bTitle: 'b.title'
  })
  .whereRaw('?? = ??', ['a.column_1', 'b.column_2'])

Where

knex('users').where({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

knex('users')
  .where('votes', '>', 100)
  .andWhere('status', 'active')

Order

knex('users').orderBy('email')
knex('users').orderBy('name', 'desc')

Update

knex('books')
  .where('published_date', '<', 2000)
  .update({
    status: 'archived',
    thisKeyIsSkipped: undefined
  })

returns true on success confirmed, idk else

Add .returning('*') to return data

knex('moderator').update({...moderator}).where({id}).returning('*')

Count

const greetingCount = await knex('phrase')
    .count()
    .where('intent', GREETING);

//[ { count: '1' } ]

Join

knex('users')
  .join('contacts', 'users.id', '=', 'contacts.user_id')
  .select('users.id', 'contacts.phone')