# mjs-mysql-builder

> Mysql query builder

Latest version **1.0.8** (published 2018-08-15) · ISC license · 0 weekly downloads

## Install

```sh
npm install mjs-mysql-builder
pnpm add mjs-mysql-builder
yarn add mjs-mysql-builder
bun add mjs-mysql-builder
```

## Health

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

Positive: no vulnerabilities.

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

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 1.0.8 |
| Published | 2018-08-15 |
| First published | 2018-08-12 |
| Weekly downloads | 0 |
| License | ISC |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 1 |
| Unpacked size | 46.2 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| Author | Kseniya kaprisa57@gmail.com |
| Maintainers | kaprisa57 |

## Links

- npm: https://www.npmjs.com/package/mjs-mysql-builder
- npm.io page: https://npm.io/package/mjs-mysql-builder

## Dependencies (1)

- [mysql](https://npm.io/package/mysql.md) ^2.16.0

## Recent versions

- 1.0.8 (latest) — 2018-08-15
- 1.0.7 — 2018-08-14
- 1.0.6 — 2018-08-14
- 1.0.5 — 2018-08-14
- 1.0.4 — 2018-08-14
- 1.0.3 — 2018-08-13
- 1.0.2 — 2018-08-12
- 1.0.1 — 2018-08-12

## README

## Mysql query builder

### DB

- Specify database env variables MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT

```js
import DB from 'mysql-builder'

DB.query(queryString, params) // -> Promise<Array<RowType>> - all rows
DB.queryRow(queryString, params) // -> Promise<?RowType> - first row
```

### Builders

- Field builder

```js
import { FieldBuilder } from 'mysql-builder';

const field = (new FieldBuilder('id')) // -> FieldBuilder
      .type('int(11)') // -> FieldBuilder
      .increment() // -> FieldBuilder
      .notNull() // -> FieldBuilder
      .primary() // -> FieldBuilder
      .get() // -> string
```

- This converts to 
```sql
`id` int(11) AUTO_INCREMENT NOT NULL,
PRIMARY KEY (`id`) 
```

- Schema builder

```js
import { SchemaBuilder } from 'mysql-builder';

const schema = (new SchemaBuilder('users'))
      .add('id', f => f.type('int(11)').increment().notNull().primary()) 
      .add('email', f => f.type('varchar(50)').notNull().unique())
      .add('name', f => f.type('varchar(50)').null())
      .add('currency', f => f.enum('RUB', 'USD', 'EUR').notNull().default('RUB'))
      .add('type', f => f.type('varchar(30)').index().using(INDEX_TYPES.BTREE))
      .add('book_id', f => f.type('int(11)').notNull().foreign().references('books', 'id').onDelete(REFERENCE_OPTIONS.CASCADE))
      .key('currency', 'type').unique().using(INDEX_TYPES.HASH)
      /* You can get Schema string */
      .build() // -> string
      /* Or Query to DataBase */
      .create() // -> Promise<void>
```

- This converts to

```sql
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) AUTO_INCREMENT NOT NULL ,PRIMARY KEY (`id`) ,
  `email` varchar(50) NOT NULL UNIQUE ,
  `name` varchar(50) NULL ,
  `currency` enum('RUB', 'USD', 'EUR') NOT NULL DEFAULT 'RUB' ,
  `type` varchar(30) ,
  INDEX (`type`) USING BTREE ,
  `book_id` int(11) NOT NULL ,
  FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ,
  UNIQUE KEY (`currency`, `type`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8  COLLATE=utf8_general_ci
```

- Query builder

```js
import { QueryBuilder } from 'mysql-builder'

const q = (new QueryBuilder('users'))
      .select('name')
      .aggregate(AGGREGATION.SUM, 'age', 'age')
      .where({ name: 'bob' })
      .groupBy('name')
      .having('SUM(age) > 100')
      .sortBy('age', DOWN)
      /* You can get query string */
      .build() // -> string
      /* Or Query to DataBase */
      .get() // -> Promise<Array<DBRow>> 
```

- This converts to

```sql
SELECT `name`, SUM(`age`) age FROM `users` 
WHERE `name`=`bob` 
GROUP BY `name` 
HAVING SUM(age) > 100 
ORDER BY `age` DESC
```

```js
(new QueryBuilder('users'))
      .select()
      .where('age', '>', 5)
      .where('name', 'LIKE', '%e%', 'AND')
      .build();
```

- This converts to

```sql
SELECT * FROM `users` 
WHERE `age` > 5 AND `name` LIKE '%e%'
```


### Models

- Table builder

```js
import { Table } from 'mysql-builder';

const table = new Table('users');

type RowType = {[string]: any};
type ConditionType = number | RowType;

/* all supported methods */
table.insert(params: RowType) // -> Promise<void>
table.replace(params: RowType) // -> Promise<void> (~ insert, on duplicate key update)
table.update(id: ConditionType, params: RowType) // -> Promise<void>
table.delete(id: ConditionType) // -> Promise<void>
table.set(id: ConditionType, field: string, value: any) // -> Promise<void>
table.find(id: ConditionType) // -> Promise<?RowType>
table.all(...fields: Array<string>) // -> Promise<Array<RowType>>
table.first(params: ?RowType) // -> Promise<{[string]: any}>
table.last(params: ?RowType) // -> Promise<{[string]: any}>

```

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