als-sqlite v0.5.2

Als-sqlite

being tested

• Als-sqlite
  • About
    • New in 0.5.2
    • New in 0.5.0
    • New in 0.4.2
    • New in 0.4.1
    • New in 0.4
    • New in 0.3.5
  • Schema
    • Example of schema file
    • Schema types
    • Schema params
    • Schema methods
  • Connect
    • Example for connect file
    • Cli file
  • Model
    • Model statics
    • Insert
    • insertMany
    • find, findById and select
    • populate
    • Compare
    • Count
    • Last
    • delete
    • update

About

Als-sqlite is a package for simpler way to use better-sqlite . The idea tooked from mongoose package.

New in 0.5.2

• model.drop now is 'DROP TABLE IF EXISTS...' so if table not exists, no error

New in 0.5.0

• Model.table(tableName,schema)
  • schema can be object or instance of schema
• schema.get = function(obj,model)
• schema.presave = function(obj,model)
  • if return null - not saved

New in 0.4.2

• added query to error
• fixed: return null (instead undefined) if no result on get query

New in 0.4.1

• converting 'true'/'false' to boolean on create/update and to bollean on find

New in 0.4

• bug for insertMany fixed

New in 0.3.5

• insertMany method

So far, als-sqlite has: 1. Schema class - for define table columns 2. connect function - for connect/create 3. Model class - for manipulating each table

Schema

Example of schema file

let {Schema} = require('als-sqlite')

let userSchema = new Schema({
   name:{type:'text',default:''},
   age:{type:'integer'},
   email:{type:'text',default:'',unique:true},
   adress:{type:'integer',ref:'adresses'},
   friends:{type:'array',ref:'users',default:[]}, // 5,8,6
   password:{type:'text',encrypt:true,default:Schema.genPassword},
})


userSchema.presave = function(obj) {
   if(obj.age < 5) obj.age = obj.age+20
   return obj // if retrun null, not saving
}

module.exports = userSchema

Schema types

You have to specify which type you will use for every column. The sqlite has only those types: INTEGER,TEXT,BLOB,REAL,NUMERIC. But you can use few another types that has been added so far: array and json.

Then you specify array and json type:

• on every save the value will be converted with JSON.stringify
• on every read, the value will be converted with JSON.parse

Schema params

sqlite types

• default - default value, can be function or string
• encrypt - boolean. It true, value will be encrypted
• unique - boolean. If true, value will be checked with unique validation of sqlite.

Schema methods

• presave(obj) - get and object before save (example above)
  • if return null, not saving
• get(obj,model) - has to return new object for saving
• all(array) - has to return array for saving

Connect

Example for connect file

// db.js file
let {connect,Model} = require('als-sqlite')
Model.db = connect(require('path').join(__dirname,'db.sqlite'))

Model.migrate = false
Model.restore = false

let userSchema = require('./models/User')
let User = new Model('users',userSchema)
let adressSchema = require('./models/Adress')
let Adress = new Model('adresses',adressSchema)

module.exports = {User,Adress,Model}

Cli file

// cli.js
let {Model} = require('./db') // Model from code above
Model.cli()

By running file with code above you can use the folowing: 1. node cli restore tablename 2. node cli drop tablename 3. node cli migrate tablename

Model

Model statics

Model.db
Model.migrate = true // false by default. if true, table will be created (if not exists)
Model.restore = true // false by default. if true, table will be droped and created
Model.errors  // array with errors if there are

Creating new table

Model.table(tableName:string,schema:object,dev:boolean): null | model
// tableName - table name for new table
// shcema - can be object for creating schema or object of Schema class
// dev - if true console.log(error). false by default

Insert

let newUser = User.insert({name:'Ela',age:40,password:'12345',email:'test@mail.com',friends:[25,18]})
console.log(newUser) // saved user object or null

If array has ref parameter and elements inside array are not integers, value will be set to null.

insertMany

let error = User.insertMany([{name:'Ela',age:40},{name:'Alex',age:45}])
console.log(error) // null if no error or error

find, findById and select

findById(id)

User.findById(4) // return object

find(object)

User.find({age:'>9'}) // return array, by default =, >, <, >=, <=, <>
User.find({age:'BETWEEN 11 AND 40'})
User.find({name:"LIKE '%al%'"}) 
// https://www.sqlitetutorial.net/sqlite-like/ 
// https://www.w3schools.com/sql/sql_like.asp

User.find({name:['Alex','Ela']}) // WHERE name IN ('Alex','Ela')

select(string)

User.select('-name -createdAt')
   .where({age:'>10'})
   .orderBy('-age')
   .limit(5)
   .skip(10) // must to come last
   .all() // return array

User.select('name age').get() // return object

populate

Then you specify ref parameter in shcema with name of table, that tells that value of this key will contain id or ids(in case of array) from another table.

let user = User.select()
.where({id:5})
.pupulate('friends adress')
.get()

In example above, friend will return object of friends by ids stored in array and adress will return adress object by id store in user.adress.

if reference no longer exists, but appear in array, on reading, not existing element will be deleted from array. You can disable it by setting Model.deleteMissing = false. For example, if friends has array of [4,5,258] and ther is no user with id 258, or read friends will updated to [4,5].

Compare

let user = User.findById(5)
User.compare('12345',user.password) // true if match, false if not

Count

User.count().where({age:'>3'}).all() // return number of documents

Last

User.last() // return {id} of last element in db

delete

User.deleteById(2) // return true if deleted, false if not
User.delete({name:'Niki'}) // return number of changes

update

User.updateById(4,{name:'Niki'}) // return updatedObject if success or null
User.update({id:4},{name:'Niki'}) // return { changes: 1, lastInsertRowid: 0 }

If array has ref parameter and elements inside array are not integers, value will not be updated.