Specified column's name.
Specified select column with alias name.
builder.column('id', 'user_id')
// `id` AS `user_id`
Create a new SQLBuilder instance.
const newBuilder = builder.createBuilder()
Optionaloptions: SQLBuilderToSQLInputOptionsOptional SQLBuilderToSQLInputOptions
Create a new Conditions instance for building complex condition groups.
const conditions = builder.createConditions()
.and('status', 'active')
.or('priority', 'high')
Specified table name.
builder.from('users')
// FROM `users`
Specified table name with alias.
builder.from('users', 'u')
// FROM `users` AS `u`
Specified group-by condition.
builder.groupBy('age')
Specified having condition using conditions instance.
const conditions = createConditions().and('value', '>', 10).or('value', '<', 2)
builder.having(conditions)
Specified having condition.
builder.having('value', 1)
builder.having('value', [1, 5, 10])
Specified having condition.
builder.having('value', '>=', 10)
builder.having('value', 'in', [1, 5, 10])
Specified join.
Specified join with direction.
Specified join with SQLBuilder subquery.
Sepcified left join
builder.leftJoin('passport', 'passport.id = user.passport_id')
// LEFT JOIN `passport` ON passport.id = user.passport_id
Specified left join with SQLBuilder subquery
builder.leftJoin(
createBuilder().from('user_logged').column('MAX(logged_at)', 'last_logged_at').where('user_id', 'u.id').groupBy('user_id'),
'ul',
'ul.user_id = u.id'
)
// LEFT JOIN (SELECT MAX(logged_at) AS `last_logged_at` FROM `user_logged` WHERE `user_id` = u.id GROUP BY `user_id`) AS `ul` ON ul.user_id = u.id
Specified left join with table alias.
builder.leftJoin('passport', 'p', 'p.id = user.passport_id')
// LEFT JOIN `passport` AS `p` ON p.id = user.passport_id
Specified limit value.
builder.limit(1)
Specified offset value.
builder.offset(1)
Specified order-by condition.
builder.orderBy('id', 'asc')
builder.orderBy('id', 'desc')
Set a custom SELECT statement.
builder.select('COUNT(*) AS total')
// SELECT COUNT(*) AS total
Custom SELECT statement
Override builder options.
builder.setOptions({quote: '`'})
Returns SQL and Binding values.
const [sql, bindings] = builder.toSQL()
Optionaloptions: SQLBuilderToSQLInputOptionsSpecified search condition.
builder.where('id', 1)
builder.where('id', [1, 2, 3]) // use `IN`
field name.
condition value.
Specified search condition with FieldPort value.
import { unescape } from 'coral-sql'
builder.where('field', unescape('other.field')) // `field` = other.field
Specified search condition with expression.
Example for IS NULL
import { is_null } from 'coral-sql'
builder.where('id`, is_null()) // `id` IS NULL
Specified search condition with operator.
builder.where('id', '!=', 1)
builder.where('id', 'in', [1, 2, 3])
builder.where('created_at', 'between', [start_at, end_at])
Specified where condition.
builder.where(createConditions().and('value', 1).or('value', 2))
WHERE condition with standalone expression
builder.where(exists(subquery))
// WHERE EXISTS(...)
WHERE condition with expression and value
builder.where(exists(subquery), true)
Specified select column.