Mysql query builder
DB
- Specify database env variables MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT
import DB from 'mysql-builder'
DB.query(queryString, params)
DB.queryRow(queryString, params)
Builders
import { FieldBuilder } from 'mysql-builder';
const field = (new FieldBuilder('id'))
.type('int(11)')
.increment()
.notNull()
.primary()
.get()
`id` int(11) AUTO_INCREMENT NOT NULL,
PRIMARY KEY (`id`)
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)').nullable())
.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))
.build()
.create()
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)
.build()
.get()
SELECT `name`, SUM(`age`) age FROM `users`
WHERE `name`=`bob`
GROUP BY `name`
HAVING SUM(age) > 100
ORDER BY `age` DESC
(new QueryBuilder('users'))
.select()
.where('age', '>', 5)
.where('name', 'LIKE', '%e%', 'AND')
.build();
SELECT * FROM `users`
WHERE `age` > 5 AND `name` LIKE '%e%'
Models
import { Table } from 'mysql-builder';
const table = new Table('users');
type RowType = {[string]: any};
type ConditionType = number | RowType;
table.insert(params: RowType)
table.update(id: ConditionType, params: RowType)
table.delete(id: ConditionType)
table.set(id: ConditionType, field: string, value: any)
table.find(id: ConditionType)
table.all(...fields: Array<string>)
table.first(params: ?RowType)
table.last(params: ?RowType)