als-model v0.8.27

als-model

als-model has three files: 1. Model - for connecting and sql queries with mySql and sqLite 2. Migration and fake - for migrating tables and inserting fake data 3. Types - for field types for building sql tables

You can contact me for comments and suggestions: alexsorkin1980@gmail.com

#Model

Mysql

let conData = {
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'myDb',
}

let model = new Model(conData,tableName)

Sqlite

const path = require('path')
let conData = path.join(__dirname,'db','data.sqlite3')

let model = new Model(conData,tableName)

Set a table for model

There are few options to set table for requests: 1. With constructor - set table to object

let model = new Model(conData,tableName)

2. With model.table - set table to object

let model = new Model(conData)
model.table = tableName

3. With model.model(tableName) - return new Model object
   • This options suitable if you need to use few tables. The method creates new object for each table.

let model = new Model(conData)
model = model.model(tableName)

Methods

Model object, has methods for sql queries. The methods are:

• all(tableName = this.table)
• where(where,tableName = this.table)
• delete(where,tableName = this.table)
• update(set,where,tableName = this.table)
• insert(array,tableName = this.table)
• createTable(tableName,fields)
• dropTable(tableName)
• query(sqls,fn)
• queryAsync(sql)

Each method, except query and queryAsync returns two types of object with methods and sql command. 1. all and where return object {sql,order,limit,run,first,async} 2. delete,update,insert,createTable,dropTable return object {sql,run,async}

Here the explanation for object methods:

• sql - return the sql syntax
• order: (field,order = 0) - return the object (this)
  • field - field for order (for example id)
  • order - 0 (default) - order by asc, 1 - order by desc
• limit:(mount) - return the object (this)
  • mount - mount for limit
• run: (fn(result)) - run sql command
  • fn - callback function with result parameter. The result is an object with err(error) and rows. If no errors, err=null.
• first: (fn(result)) - run sql command and return only first result
• async() - runs the sql command in async mode and return promise

For example:

let tableName = 'user'
let model = new Model(conData,tableName)

// sql
model.update({text:'Alex'},{id:5}).sql // return UPDATE user SET text='Alex' WHERE id='5';

// run method
model.all().first().run(data=>{
  if(data.err == null) console.log(data.rows)
})

// async method
async function get() {
  let result = await model.where({id:5}).limit(5).async()
  if(result.err == null) return result.rows
}

all(tableName)

all method run 'SELECT *' sql command. You can leave tableName parameter empty if you specified the tableName on object.

Examples:

model.all().order('id',1).limit(10).sql
//will return SELECT * FROM test ORDER BY "id" DESC LIMIT 10;

model.all().order('id',1).limit(10).run(function(result) {
    if(result.err) throw err
    console.log(result.rows)
})

where()

Syntax:

model.where(condition,tableName = this.table)

Condition parameter:

• condition parameter is an object. Each key is a field name, and value is a condition for this field
• Equation
  • {id:5} - id = 5
  • {id:'>5'} - id > 5
  • {id: '<=5} - id <= 5
• Due to the fact that condition is an object, you can use few conditions, like this:
  • AND - {id:5,name:'Alex'} - id="5" AND name="Alex"
  • OR - {id:5,name:'|Alex'} - id="5" OR name="Alex"

Example:

// The folowing is: SELECT * FROM test WHERE active='1' AND age>'25';
model.where({active:1,age:'>25'}).sql

// The folowing is: SELECT * FROM test WHERE active='1' OR age>'25';
model.where({active:1,age:'>|25'}).run(function(result) {
  console.log(result.rows)
})

You can use order and limit

// SELECT * FROM test WHERE active='1' OR age>'25' ORDER BY "id" DESC LIMIT 5;
model.where({active:1,age:'>|25'}).order('id',1).limit(5).sql

delete()

The syntax:

model.delete(where)

Parameters works in the same way as in where.

Example:

model.delete({id:8}).sql
// DELETE FROM test WHERE id='8';

model.delete({id:8}).run( result => {
    if(result.err) throw result.err
    console.log(result.rows)
})

update()

Syntax:

update(set,where)

The new parameter set is an object for set values. For example

model.update({text:'Alex'},{id:5}).run(result=>{
  if(result.err) throw result.err
  console.log(result.rows)
})

insert()

Syntax:

model.insert(array)

The array parameter is and array of objects to insert For example:

model.insert([{text:'Alex'}]).run(result=>{
  if(result.err) throw result.err
  console.log(result.rows)
})

let data = [
    {text:'super'},
    {text:'puper'},
]
model.insert(data).run(result=>{
  if(result.err) throw result.err
  console.log(result.rows)
})

createTable()

Syntax:

model.createTable(tableName,fields)

Example:

let fields = {
    id: "INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY",
    text: "TEXT NOT NULL",
    text1: "TEXT NOT NULL",
    timestamp: "TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
}
You can use types for shorter syntax - further. 

model.createTable('users',fields).run(result=>{
  if(result.err) throw result.err
  console.log(result.rows)
})

dropTable()

Syntax:

model.dropTable(tableName): {sql,run}

Example:

model.dropTable('users').run(result=>{
  if(result.err) throw result.err
  console.log(result.rows)
})

query()

Syntax:

model.query(
    sqls:array,
    fn:calback(result)
)

Example:

model.query([
  model.insert([{text:'super'}]).sql,
  //INSERT INTO test (text) VALUES ("super");
    model.all().order('id').limit(15).sql,
  //SELECT * FROM test ORDER BY "id" ASC LIMIT 15;  
    model.update({text:'some text'},{id:5}).sql,
  // UPDATE test SET text='some text' WHERE id='5';
    model.lastId()
  // SELECT * FROM test ORDER BY "id" DESC LIMIT 1; 
],(results) =>{
    console.log(results)
})

The result in sqlite:

[
  { rows: Statement { lastID: 6, changes: 1 }, err: null }, // insert result
  {
    rows: [ [Object], [Object], [Object], [Object],
    err: null
  }, // all result - each Object has data from row
  { rows: Statement { lastID: 6, changes: 1 }, err: null }, // update result
  {
    rows: { id: 6, text: 'super', timestamp: '2021-02-06 09:39:31' },// lastId result
    err: null
  }
]

The result in mysql:

[
  {
    rows: OkPacket {
      fieldCount: 0,
      affectedRows: 1,
      insertId: 20,
      serverStatus: 2,
      warningCount: 0,
      message: '',
      protocol41: true,
      changedRows: 0
    }, // insert result
    err: null
  },
  {
    rows: [
      [RowDataPacket], [RowDataPacket],[RowDataPacket], [RowDataPacket]
    ],
    err: null
  }, // all result - each Object has data from row
  {
    rows: OkPacket {
      fieldCount: 0,
      affectedRows: 1,
      insertId: 0,
      serverStatus: 2,
      warningCount: 0,
      message: '(Rows matched: 1  Changed: 0  Warnings: 0',
      protocol41: true,
      changedRows: 0
    },
    err: null
  },// update result
  {
    rows: RowDataPacket {
      id: 1,
      text: 'Ms. Jody Lowe',
      timestamp: 2021-02-07T07:29:50.000Z
    },
    err: null
  } // lastId result
]

--

#Migration

Instalation

let model = new Model(conData) // create model (previous section)
let Migration = require('als-model/migration') // import Migration
let tablesPath = path.resolve(__dirname,'db','tables') // folder for migration files
let migrate = new Migration(tablesPath,model) // new migrate object
migrate.cli() // run cli which listen to folowing commands

cli commands

• table tableName
• migrate
• delete tableName
• restore tableName
• fake tableName times
• query

Examples with file ./migration.js:

Creating new migration file

The folowing command will create db\tables\test.js with data for migration and fake

node migration table test

The file containes table object, fake function and first data to insert.

• The table object will include all fields for migration.
• The fake function will create fake data for all fields you specify.
• The data will insert the data on migration
  • data is an array that will contain objects with data for insert

Example:

let {id,timestamp,update,text,num,float,def} = require('../../mw/model/types')

let table = {
    id,
    username:text(50) + def(),
    password:text('1b') + def(),
    email: text('1b') + def(),
    name: text(50),
    last_name: text(50),
    middle_name: text(50),
    status: num(1) + def(0), // 1-active, 0-not active
    is_admin:num(1) + def(0), // 1-admin, 0-not not admin
    timestamp
}

const faker = require('faker')
// Faker documentation - https://www.npmjs.com/package/faker
let fake = () => { return {
    username:faker.internet.userName(),
    password:faker.internet.password(),
    email:faker.internet.email(),
    name: faker.name.firstName(),
    last_name: faker.name.lastName(),
    middle_name: faker.name.middleName(),
}}

let data =  [{
    username:require('../../env').adminUserName,
    password:require('../../env').adminPass,
    status:1,
    is_admin:1
}]


module.exports = {table, fake, data}

Migrate

node migration migrate

Now all migration files you created before, will create tables inside you db with model from constructor.

Delete table

The folowing command will delete test table from db

node migration delete test

restore tableName

The folowing command will delete and then create again test table

node migration restore test

fake tableName times

The folowing command will create fake data inside test table for 5 rows

node migration fake test 5

query

Then you run node migration query you get console for queries. Each query has to be started with table name. For example users.all().

• users is a table name and all() is a method to run. Each query will return the result to console.

Here another example:

> node migrate query
> users.where({id:'>20'})
> _

--

#Types

Types is an object with sql types inside. You can use it for short syntax for sql fields in tables.

Example:

let {id,timestamp,update,text,num,float,def} = require('als-model/types')
id=id[0]

let table = {
    id,
    text: text('1b') + def(),
    some: num('2g') + def(28684),
    update,
    timestamp
}

Result:

table = {
  id: 'INTEGER DEFAULT AUTO_INCREMENT PRIMARY KEY',
  text: 'TINYTEXT NOT NULL',
  some: 'BIGINT(137438953471) DEFAULT "28684"',
  timestamp: 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
}

id

[
  'INTEGER DEFAULT AUTO_INCREMENT PRIMARY KEY', // for sqlite
  'INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY', // for mysql
]

timestamp

TIMESTAMP DEFAULT CURRENT_TIMESTAMP

text

text is a function

text(size = '16b')  
// byte = 256, min 1, max 4g

• size (minimum is 1, maximum is 4g):
  • CHAR(size): 1-254
  • TINYTEXT: 255 1b+
  • VARCHAR(size): 256-65535 or 1b-256b
  • TEXT: 65536 or 256b+
  • MEDIUMTEXT: 65537-16777215 or 256b+ - 64k (by default)
  • LONGTEXT: 16777216-4294967295 or 64k+ - 16m

num

syntax:

num(size = '64m') 
// minimum = 1 
// maximum = 268435456g

• size
  • BOOL: 0-2
  • BIT(size): 0-64 or 1b
  • TINYINT(size): 65-255 or 1b-4b
  • SMALLINT(size): 256-65535 or 4b+ - 1k
  • MEDIUMINT(size): 65536-16777215 or 1k+ - 256k
  • INT(size): 16777216-4294967295 or 256k - 64m
  • BIGINT(size): 4294967296-9223372036854775807 or 64m+ - 268435456g

float

syntax:

float(size=24,d)

• size
  • FLOAT(size): If size is from 0 to 24, the data type becomes FLOAT(). If size is from 25 to 53, the data type becomes DOUBLE()
  • DOUBLE(size, d): if d not undefined. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter

def

def is a function for define default value.

Syntax:

def(defaultValue = null)

• defaultValue
  • null - defailt value NOT NULL - by default
  • Any string - default value for this field

Other types

DATE,DATETIME(fsp),TIME(fsp) and YEAR, not included because their syntax is simple and clear.

Althought that you can use those types with def() function.

For example:

'DATE' + def()
'DATETIME' + def()