# als-model

> Instruments for mysql and sqlite queries and migration

Latest version **0.8.27** (published 2021-05-01) · ISC license · 0 weekly downloads

## Install

```sh
npm install als-model
pnpm add als-model
yarn add als-model
bun add als-model
```

## Health

**Score 15/100 (F)** — status: abandoned.

Positive: no vulnerabilities.

Warnings: low downloads; no types; no esm support; pre 1.0.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 0.8.27 |
| Published | 2021-05-01 |
| First published | 2021-02-08 |
| Weekly downloads | 0 |
| License | ISC |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 3 |
| Unpacked size | 30.4 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| Author | Alex Sorkin |
| Maintainers | alexsorkin |
| Keywords | model, sqlite, mysql, migration, sql, types |

## Links

- npm: https://www.npmjs.com/package/als-model
- npm.io page: https://npm.io/package/als-model

## Dependencies (3)

- [faker](https://npm.io/package/faker.md) ^5.2.0
- [mysql](https://npm.io/package/mysql.md) ^2.18.1
- [sqlite3](https://npm.io/package/sqlite3.md) ^5.0.1

## Alternatives

- [@openai/codex-sdk](https://npm.io/package/@openai/codex-sdk.md) — 731.4K weekly downloads
- [babel-plugin-transform-react-jsx](https://npm.io/package/babel-plugin-transform-react-jsx.md) — 565.0K weekly downloads
- [babel-helper-remove-or-void](https://npm.io/package/babel-helper-remove-or-void.md) — 508.5K weekly downloads
- [@pnpm/store-controller-types](https://npm.io/package/@pnpm/store-controller-types.md) — 186.9K weekly downloads
- [react-native-signature-canvas](https://npm.io/package/react-native-signature-canvas.md) — 155.6K weekly downloads

## Recent versions

- 0.8.27 (latest) — 2021-05-01
- 0.8.26 — 2021-05-01
- 0.8.25 — 2021-04-29
- 0.8.24 — 2021-04-28
- 0.8.23 — 2021-04-22
- 0.8.22 — 2021-04-22
- 0.8.21 — 2021-03-10
- 0.8.2 — 2021-02-23
- 0.8.1 — 2021-02-19
- 0.8.0 — 2021-02-19
- 0.7.0 — 2021-02-15
- 0.6.0 — 2021-02-11
- 0.5.3 — 2021-02-09
- 0.5.2 — 2021-02-08
- 0.5.1 — 2021-02-08
- … 1 more at https://npm.io/package/als-model/versions

## README

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

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

# **#Model**

## **Mysql**
```javascript
let conData = {
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'myDb',
}

let model = new Model(conData,tableName)
```

## **Sqlite**

```javascript
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
```javascript
let model = new Model(conData,tableName)
```
2. With model.table - set table to object
```javascript
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. 
```javascript
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:
```javascript
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:
```javascript
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:
```javascript
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:
```javascript
// 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
```javascript
// 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: 
```javascript
model.delete(where)
```
Parameters works in the same way as in where.

Example:
```javascript
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: 
```javascript
update(set,where)
```

The new parameter set is an object for set values. 
For example
```javascript
model.update({text:'Alex'},{id:5}).run(result=>{
  if(result.err) throw result.err
  console.log(result.rows)
})
```

### **``insert()``**
Syntax: 
```javascript
model.insert(array)
```

The array parameter is and array of objects to insert
For example:
```javascript
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:
```javascript
model.createTable(tableName,fields)
```
Example:
```javascript
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:
```javascript
model.dropTable(tableName): {sql,run}
```
Example:
```javascript
model.dropTable('users').run(result=>{
  if(result.err) throw result.err
  console.log(result.rows)
})
```

### **``query()``**
Syntax:
```javascript
model.query(
    sqls:array,
    fn:calback(result)
)

```
Example:
```javascript
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:
```javascript
[
  { 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:
```javascript
[
  {
    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**
```javascript
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
```console
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:
```javascript
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**
```console
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
```console
node migration delete test
```

### **restore tableName**
The folowing command will delete and then create again test table
```console
node migration restore test
```

### **fake tableName times**
The folowing command will create fake data inside test table for 5 rows
```console
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:
```console
> 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:**
```javascript
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:
```javascript
table = {
  id: 'INTEGER DEFAULT AUTO_INCREMENT PRIMARY KEY',
  text: 'TINYTEXT NOT NULL',
  some: 'BIGINT(137438953471) DEFAULT "28684"',
  timestamp: 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP'
}
```


## **id**
```javascript
[
  '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 
  ```javascript
  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:
  ```javascript
  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:
  ```javascript
  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:
  ```javascript
  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:
```javascript
'DATE' + def()
'DATETIME' + def()
```

---
_Source: https://npm.io/package/als-model · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._