MySQL Builder

Support building MySQL statements

Table of contents

• Example Usage
• API
  • Model
    • all()
    • get()
    • first()
    • limit(value)
    • offset(value)
    • orderBy(key, [direction])
    • select(...keys)
    • select(keys)
    • select(key)
    • where(fn)
    • where(key, operator, value)
    • where(key, value)
    • whereIn(key, values)
    • whereNotIn(key, values)
  • Query
    • exec()
    • get()
    • first()
    • limit(value)
    • offset(value)
    • orderBy(key, [direction])
    • orWhere(fn)
    • orWhere(key, operator, value)
    • orWhere(key, value)
    • select(...keys)
    • select(keys)
    • select(key)
    • where(fn)
    • where(key, operator, value)
    • where(key, value)
    • whereIn(key, values)
    • whereNotIn(key, values)
• Interfaces
  • Direction
  • AlphaNumeric
  • Operator
  • InOperator
  • RangeOperator
  • ComparisonOperator
  • LikeOperator

Example Usage

const Post = new Model('posts')
Post.all() //  select * from `posts`
Post.where('id', 1).exec() // select * from `posts` where `id` = 1

API

Model

Model.prototype.all()

Return a query that retrieves all the records in the table

Return type: string

Post.all()
// select * from `posts`

Model.prototype.get()

Same as all() method

Return type: string

Post.get()
// select * from `posts`

Model.prototype.first()

Return a query that retrieves the first record in the table.

Return type: string

Post.first()
// select * from `posts` limit 1

Model.prototype.limit(value)

Specifies the maximum number of records the query will return.

Parameters:

• value (number): The number of records will return

Return type: Query

const query = Post.limit(10)

query.exec()
// select * from `posts` limit 10

Model.prototype.offset(value)

Specifies the number of records the query will skip.

Parameters:

• value (number): The number of records will skip

Return type: Query

const query = Post.limit(10).offset(20)

query.exec()
// select * from `posts` limit 10 offset 20

Model.prototype.orderBy(key, [direction])

Sort query result in ascending or descending order

Parameters:

• key (string): The specified column name to sort
• direction (Direction, optional): Sort direction (asc or desc). Default is asc

Return type: Query

const query = Post.orderBy('title', 'desc')

query.exec()
// select * from `posts` order by `title` desc

Model.prototype.select(...keys)

Specifies which column is selected

Parameters:

• ...keys (string[]): The list of columns to select

Return type: Query

const query = Post.select('id', 'title')

query.exec()
// select `id`, `title` from `posts`

Model.prototype.select(keys)

Specifies which column is selected

Parameters:

• keys (string[]): The list of columns to select

Return type: Query

const query = Post.select(['id', 'title'])

query.exec()
// select `id`, `title` from `posts`

Model.prototype.select(key)

Specifies which column is selected

Parameters:

• key (string): The column to select

Return type: Query

const query = Post.select(['title'])

query.exec()
// select `title` from `posts`

Model.prototype.where(key, operator, value)

Add condition to filter records

Parameters

• key (string): The column to use for filtering
• operator (Operator): The operator to use for filtering
• value (AlphaNumeric | AlphaNumeric[] | [AlphaNumeric, AlphaNumeric]): The value to use for filtering

Depending on the operator, the value takes a different type. As follows:

• InOperator: the value must be an AlphaNumeric[]
• RangeOperator: the value must be an [AlphaNumeric, AlphaNumeric]
• other: the value must be an AlphaNumeric

Return type: Query

/*** EXAMPLE 1 ***/
const query = Post.where('title', '=', 'test')

query.exec()
// select * from `posts` where `title` = 'test'

/*** EXAMPLE 2 ***/
const query = Post.where('title', 'in', ['test', 'test2'])

query.exec()
// select * from `posts` where `title` in ('test', 'test2')

/*** EXAMPLE 3 ***/
const query = Post.where('views', 'between', [100, 200])

query.exec()
// select * from `posts` where `views` between 100 and 200

Model.prototype.where(key, value)

Add condition to filter records. The operator is =

Parameters

• key (string): The column to use for filtering
• value (AlphaNumeric): The value to use for filtering

Return type: Query

Model.prototype.where(fn)

Sometimes you want to group several where clauses with parentheses, passing a parameter is a method, it will do it

const query = Post.where('id', 1).where((q) => {
  q.where('title', 'test').orWhere('title', 'test2')
})

query.exec()
// select * from `posts` where `id` = 1 and (`title` = 'test' or `title` = 'test2')

Model.prototype.whereIn(key, values)

Add condition to filter records. The operator is in

Parameters

• key (string): The column to use for filtering
• value (AlphaNumeric[]): The value to use for filtering

Return type: Query

Model.prototype.whereNotIn(key, values)

Add condition to filter records. The operator is not in

Parameters

• key (string): The column to use for filtering
• value (AlphaNumeric[]): The value to use for filtering

Return type: Query

Query

Query.prototype.exec()

Query.prototype.get()

Query.prototype.first()

Query.prototype.limit(value)

Query.prototype.offset(value)

Query.prototype.orderBy(key, direction = 'asc')

Query.prototype.orWhere(fn)

Query.prototype.orWhere(key, operator, value)

Query.prototype.orWhere(key, value)

Query.prototype.select(...keys)

Query.prototype.select(keys)

Query.prototype.select(key)

Query.prototype.where(fn)

Query.prototype.where(key, operator, value)

Query.prototype.where(key, value)

Query.prototype.whereIn(key, values)

Query.prototype.whereNotIn(key, values)

Interfaces

Direction

asc | desc

AlphaNumeric

string | number

Operator

InOperator | RangeOperator | ComparisonOperator | LikeOperator

InOperator

in | not in

RangeOperator

between | not between

ComparisonOperator

= | != | < | <= | > | >=

LikeOperator

like | not like