sqlizze

sqlite3 ORM for Node.JS/Electron

Installation

$ npm install sqlizze

Usage

const sqlite3 = require('sqlite3').verbose()
const Sqlizze = require('sqlizze')
const sqlize = new Sqlize('./test.db', sqlite3)

Init

• define model

let user = sqlize.define({
    name: {
      type: Sqlizze.STRING,
      field: 'name',
      allownull: false,
      defaultValue: 'yanglong',
      comment: 'name'
    },
    my_age: {
      type: Sqlizze.INTEGER,
      field: 'age',
      comment: 'age'
    },

  },{
    tableName: 'dt_user'
  })

• model's method(return Promise)

//create
user.create({
  name: 'longor',
  my_age: 28
})
//findAll
user.findAll({
  where: {
    my_age: 28
  }
})
//findAndCountAll
user.findAndCountAll({
  where: {
    my_age: 28
  }
})
//findOne
user.findOne({
  where: {
    my_age: 28
  }
})
//update
user.update({
  name: 'yanglong'
},{
  where: {
    my_age: 28
  }
})
//count
user.count({
  where: {
    my_age: 28
  }
})
//sum
user.sum('age',{
  where: {
    my_age: 28
  }
})
//delete
user.delete({
  where: {
    my_age: 28
  }
})

• Conditional

where: {
    ['$or']: [
      {name_ni: 2},
      {my_age: 2}
    ],
    my_age:{
      ['$or']: [11, 22], // 11 OR 12
      ['$gt']: 6, // > 6
      ['$gte']: 6, // >= 6
      ['$lt']: 6, // < 6
      ['$lte']: 6, // <= 6
      ['$ne']: 6, // != 6
      ['$between']: [6, 10], // BETWEEN 6 AND 10
      ['$notBetween']: [6, 10], // NOT BETWEEN 6 AND 10
      ['$in']: [1, 2], // IN [1, 2]
      ['$notin']: [1, 2], // NOT IN [1, 2]
      ['$like']: '123', // LIKE '%123%'
      ['$notLike']: '123' // NOT LIKE '%123%'
    },
    name_ni: 13
  }

  //eg: 
  let OP = Sqlizze.OP
  await user.fillAll({
    where: {
      my_age: {
        [OP.$gt]: 6 // age > 6
      },
      [OP.$or]: [
        {name: '111'},
        {name: '222'} // name = '111' or name = '222'
      ]
    }
  })
  // WHERE (age > 6) AND (name = '111' or name = '222')

• Order by

  //orderBy: ['id', 'asc'], // default desc
  orderBy: [['name_ni', 'asc'], ['my_age', 'desc']] // default desc

  //eg: 
  await user.fillAll({
    where: {
      my_age: 28
    },
    orderBy: [['name_ni', 'asc'], ['my_age', 'desc']]
  })

• Limit Offset

  limit: 1, //default 1000
  offset: 0 //default 0

  //eg:
  await user.fillAll({
    where: {
      my_age: 28
    },
    limit: 20, 
    offset: 20
  })