mysqli/docs/3.x.md

Document

One or more configurations can be passed in for instantiation. When there is only one database, the default is the master library; when there is more than one database service, the first one is automatically used as the master library, and the other slave libraries.

let Mysqli = require('mysqli')

// one config
let conn = new Mysqli({
  host: '', // IP/domain  
  post: 3306, //port, default 3306  
  user: '', // username
  passwd: '', // password
  charset: '', // CHARSET of database, default to utf8 【optional】
  db: 'test' // the default database name  【optional】
})

// two or more configs
let conn = new Mysqli([
  {
    host: 'host1', // 
    ...
  },
  {
    host: 'host2', // 
    ...
  },
  ...
])

let db = conn.emit()  // use master library
// let db = conn.emit(false, 'test')
// let db = conn.emit(true, 'test')

db.table('test').getAll().then(list => {
  console.log(list)
})

Static Function

escape(val)

Various types of values ​​of sql can be safely escaped

Instance APIs

emit([slave], [db])

• slave <Boolean> [optional] use the slave libraries, default to false
• db <String> [optional] declare the database's name, default to this config passed.

This method will return a DB instance.

let db = conn.emit(false, 'test')

db.debug = true // debugger mode.    supported in ^3.1.0

db
  .tableList()
  .then(list => {
    console.log(list)
  })
  .catch(err => {
    console.log(err)
  })

DB APIs

All method will return a Promise if not declare.

query(sql)

• sql <String> sql 语句, [必传]

Execute the sql if you need.

db.query('select * from `student` limit 10').then(result => {
  console.log(result)
})

// if not declare a database before. you can code like that.
db.query('select * from `test`.`student` limit 10').then(result => {
  console.log(result)
})

drop(db)

• db <String> [optional] the database's name what you want to delete. It will delete the current connected database if not given.

delete the current connected database or what you want.

db.drop() // delete self connected

// delete foo
db.drop('foo')

dbList()

return all the databases, base current account.

db.dbList().then(list => {
  console.log(list)
})

tableList()

return all the tables of current connect database.

db.tableList().then(list => {
  console.log(list)
})

dbCreate(name, options)

• name <String> [required], database's name
• options <Object>, [optional], optional setting
  • charset <String> default to utf8mb4

create a database, return true if success.

db.dbCreate('foo', { charset: 'utf8' }) //  default to utf8mb4

tableCreate(name, columns ,options)

• name <String> [required], table name
• columns <Array>, [required], table columns
  • name <String> field name, Case sensitive. recommend lowercase and _
  • type <String> field type, Not case sensitive
  • primary <Boolean> is primary key or not.(Only one)
  • inc <Boolean> is auto_increment or not.(must be primary key and int)
  • notnull <Boolean> if value can be null
  • index <Boolean> if create index
  • unique <Boolean> if create unique index
  • default <Any> the default value
  • update <Boolean> auto update(only datetime | timestamp)
• options <Object>, [optional], other configs
  • charset <String> default to utf8mb4
  • engine <String> default to InnoDB

create a table, return true if success.

db.tableCreate('student',
  [
    {
      name: 'id',
      type: 'int(5)',
      primary: true,
      inc: true
    },
    {
      name: 'name',
      type: 'varchar(64)',
      index: true
    },
    {
      name: 'age',
      type: 'int(3)'
    },
    {
      name: 'sex',
      type: 'tinyint(1)',
      index: true
    },
    ...
  ])

table(name)

• name <String> [required], table name

select a table. return a Table instance.

let table = db.table('student') //

TABLE APIs

leftJoin(tables)

• tables <Array> left join one or more tables.
  • table <String> table name
  • on <String> condition

db.table('student')
  .leftJoin([
    {
      table: 'classroom',
      on: 'student.classroom = classroom.id'
    },
    ...
  ])

rightJoin(tables)

see leftJoin()

join(tables)

see leftJoin()

filter(options)

• options <Object>

Some keywords in options.

• $and
• $or
• $like fuzzy query
• $sql Use the result of a certain sql statement as the query condition
• $in
• $between
• $lt
• $lte
• $gt
• $gte
• $eq equal
• $ne not equal

db
  .table('student')
  .filter({ id: 1234 }) // a simple search. find the item which it's id is 1234


  // find the items which it's name starts with Lee
  .filter({ name: { $like: 'Lee%' } }) 

  // you can use sql to search items if needs
  .filter({ name: { $sql: 'IS NULL' } })
  .filter({ score: { $sql: 'score + 1' } })

  // find the items which it's id is 11,13,29
  .filter({ id: { $in: [11, 13, 29] } }) 


  // find all items which it's age is between 15 and 17
  .filter({ age: { $between: [15, 17] } }) 

  // find all items which it's age is smaller than 16
  .filter({ age: { $lt: 16 } })

  // find all items which it's age is smaller than 16 and elder than 13
  .filter({ age: { $lt: 16, $gt: 13 } })

  // ***** multi conditions *********
  // find all which it's name starts with Lee, and it's age is elder than 15, and it'sex is 1
  .filter({ name: { $like: 'Lee%' }, age: { $gt: 15 }, sex: 1 })

  // find all which it's name starts with Lee, or it's age is elder than 15
  .filter({
    $or: [{ name: { $like: 'Lee%' } }, { age: { $gt: 15 } }]
  })

  // find all which it's name starts with Lee, or it's age is elder than 15 but it'sex is 1
  .filter({
    $or: [
      { name: { $like: 'Lee%' } },  // condition 1
      { age: { $gt: 15 }, sex: 1 }  // condition 2
    ]
  })

  // find all which it's name starts with Lee or it's age is elder than 15,  but it'sex must be 1
  .filter({
    $and: [
      {
        $or: [{ name: { $like: '李%' } }, { age: { $gt: 15 } }]
      },
      { sex: 1 }
    ]
  })

sort(keys)

• keys <Object>

sort the result by fields.

db
  .table('student')
  .sort({ age: -1 }) // -1: Reverse sequence, 1: Positive sequence

  // sort the age(Reverse sequence), then sort the id(Positive sequence)
  .sort({ age: -1, id: 1 })

skip(num)

• num <Number>

skip the nums from match list.

db.table('student').skip(10) // return this 10th from match list

limit(num)

• num <Number>

limit the nums of the result.

db.table('student').limit(10) // only 10 items will be return 

slice(start, end)

• start <Number>
• end <Number>

truncate the result. a friendly method for skip and limit

db
  .table('student')
  .slice(11, 20)

  // equal to
  .skip(11)
  .limit(10)

withFields(fields)

• fields <Array> [optional], default to all fields;

declare the fields what you need.

db.table('student').withFields(['id', 'name', 'sex']) // 只取 学号,姓名,性别3个字段返回

// equal to
db.table('student').withFields('id', 'name', 'sex') // supported in ^3.1.0

getAll([ids])

• ids <Array> [optional], return all items which you search.

db
  .table('student')
  .withFields(['id', 'name', 'sex'])
  .getAll()
  .then(list => {
    console.log(list)
  })

// find the students which it's id is 11,13,28
db
  .table('student')
  .withFields(['id', 'name', 'sex'])
  .getAll([11, 13, 28])
  .then(list => {
    console.log(list)
  })

// equal to
db
  .table('student')
  .withFields(['id', 'name', 'sex'])
  .filter({ id: { $in: [11, 13, 28] } })
  .getAll()
  .then(list => {
    console.log(list)
  })

get(id)

• id <Any> [optional], the ID you want to search。

return only one item.

// find the student which it's id is 11
db
  .table('student')
  .withFields(['id', 'name', 'sex'])
  .getAll(11)
  .then(doc => {
    console.log(doc)
  })

// equal to 
db
  .table('student')
  .withFields(['id', 'name', 'sex'])
  .filter({ id: 11 })
  .get()
  .then(doc => {
    console.log(doc)
  })

count()

count the num of result.

// get the num which it's name starts with Lee
db
  .table('student')
  .filter({name: {$like: 'Lee%'}})
  .count()
  .then(total => {
    console.log(total)
  })

insert(doc)

• doc <Object>

insert data.

db
  .table('student')
  .insert({
    name: 'Tom',
    age: 18,
    sex: 1,
    ...
  })
  .then(res => {
    console.log(res)
    // {
    //   fieldCount: 0,
    //   affectedRows: 1,
    //   insertId: 4,
    //   serverStatus: 2,
    //   warningCount: 0,
    //   message: '',
    //   protocol41: true,
    //   changedRows: 0
    // }
  })

update(doc)

• doc <Object>

update the data.

// 
db
  .table('student')
  .filter({name: 'Tom'})
  .update({
    age: 17
  })
  .then(res => {
    console.log(res) 
  })

remove()

delete items.

db
  .table('student')
  .filter({name: {$sql: 'IS NULL'}})
  .remove()
  .then(res => {
    console.log(res) 
  })

drop()

delete current table. it is danger action.

// 
db
  .table('student')
  .drop()
  .then(result => {
    console.log(result) 
  })

renameTo(name)

rename current table.

db
  .table('student')
  .renameTo('student_bac')
  .then(result => {
    console.log(result) 
  })

indexList()

return the index list.

db
  .table('student')
  .indexList()
  .then(list => {
    console.log(list) 
    // {
    //   name,
    //   column,
    //   unique,
    //   cardinality,
    //   collation,
    // }
  })

indexDrop(name)

• name <String> index name

delete an index

// 
db
  .table('student')
  .indexDrop('name_idx')
  .then(result => {
    console.log(result) 
  })

indexCreate(name, options)

• name <String> 索引名
• options <Object> 索引的配置
  • field <String> 该索引绑定的字段
  • unique <Bollean> 是否是唯一索引

create an index for a field.

// 
db
  .table('student')
  .indexCreate('name_idx', {field: 'name'})
  .then(result => {
    console.log(result) 
  })