Database
Database: query builder
The query builder writes your SQL for you, one chained call at a time, and binds every value so it is safe from SQL injection. It works the same on SQLite, PostgreSQL and MySQL.
Introduction#
DB.table() starts a query on a table. Each method adds to the query and returns it, so you chain them, and the
method that runs the query, such as get(), returns a promise:
const users = await DB.table('users').where('votes', '>', 100).orderBy('name').get()Your models use the same builder: User.where('votes', '>', 100).get() takes every method on this page
and returns models.
Running database queries#
Retrieving all rows from a table#
get() runs the query and returns the rows in a collection, as plain objects:
import { DB, view } from '@orieljs/framework'import { Controller } from './Controller'export class UserController extends Controller { /** Show a list of all of the application's users. */ async index() { const users = await DB.table('users').get() return view('user.index', { users: users.all() }) }}The rows are typed Record<string, unknown> unless you give their type, which is a cast, not a check:
DB.table<{ id: number; name: string }>('users').
Retrieving a single row or column from a table#
first() returns the first row, or undefined when there is none. value() returns one column of the first row,
and find() the row with that id:
const user = await DB.table('users').where('name', 'John').first()const email = await DB.table('users').where('name', 'John').value('email')const found = await DB.table('users').find(3)Retrieving a list of column values#
pluck() returns one column of every row, in a collection:
const titles = await DB.table('users').pluck('title')for (const title of titles) { // ...}Chunking results#
chunk() reads a large table a part at a time, and calls your function with each part. The query needs an
orderBy(), so the parts don't overlap:
await DB.table('users') .orderBy('id') .chunk(100, async (users) => { for (const user of users) { // ... } })Return false from the function to stop. When the function updates or deletes the rows it reads, use chunkById(),
which pages by the id column rather than by offset, so no row is skipped:
await DB.table('users') .where('active', false) .chunkById(100, async (users) => { for (const user of users) { await DB.table('users').where('id', user.id).update({ active: true }) } })each() calls your function with each row in turn, reading them a part at a time, 1,000 unless you give a number. It
needs an orderBy() too, as chunk() does.
Streaming results lazily#
lazy() returns a lazy collection, which reads the rows a part at a time as you go
through them with for await. It needs an orderBy() too; lazyById() pages by id:
for await (const user of DB.table('users').orderBy('id').lazy()) { // ...}Aggregates#
count(), max(), min(), avg() and sum() run an aggregate:
const users = await DB.table('users').count()const price = await DB.table('orders').max('price')const average = await DB.table('orders').where('finalized', 1).avg('price')count() and sum() return a number. avg() returns a number, or undefined for no rows, and max() and min() the
value, or undefined.
Determining if records exist#
if (await DB.table('orders').where('finalized', 1).exists()) { // ...}if (await DB.table('orders').where('finalized', 1).doesntExist()) { // ...}Select statements#
Specifying a select clause#
select() chooses the columns, and as renames one:
const users = await DB.table('users').select('name', 'email as user_email').get()distinct() returns only distinct rows, and addSelect() adds columns to the select:
const query = DB.table('users').select('name')const users = await query.addSelect('age').get()Raw expressions#
DB.raw() writes SQL into a query as it is:
const users = await DB.table('users') .select(DB.raw('count(*) as user_count, status')) .where('status', '<>', 1) .groupBy('status') .get()Raw methods#
selectRaw(), whereRaw(), orWhereRaw(), havingRaw() and orderByRaw() take SQL and its bindings:
const orders = await DB.table('orders').selectRaw('price * ? as price_with_tax', [1.0825]).get()const big = await DB.table('orders').whereRaw('price > IF(state = "TX", ?, 100)', [200]).get()const totals = await DB.table('orders') .select('department', DB.raw('SUM(price) as total_sales')) .groupBy('department') .havingRaw('SUM(price) > ?', [2500]) .get()const recent = await DB.table('orders').orderByRaw('updated_at - created_at DESC').get()Joins#
Inner join clause#
join() takes the table, then the two columns and the operator between them:
const users = await DB.table('users') .join('contacts', 'users.id', '=', 'contacts.user_id') .join('orders', 'users.id', '=', 'orders.user_id') .select('users.*', 'contacts.phone', 'orders.price') .get()Left join / right join clause#
const users = await DB.table('users').leftJoin('posts', 'users.id', '=', 'posts.user_id').get()const authors = await DB.table('users').rightJoin('posts', 'users.id', '=', 'posts.user_id').get()Cross join clause#
const sizes = await DB.table('sizes').crossJoin('colors').get()Advanced join clauses#
Give join() a function for a join with several conditions. on() and orOn() compare columns, and where() a
column with a value:
const users = await DB.table('users') .join('contacts', (join) => { join.on('users.id', '=', 'contacts.user_id').where('contacts.user_id', '>', 5) }) .get()Basic where clauses#
Where clauses#
where() takes a column, an operator and a value. Without an operator, it compares with =:
const users = await DB.table('users').where('votes', '=', 100).where('age', '>', 35).get()const same = await DB.table('users').where('votes', 100).get()The operators are =, <, >, <=, >=, <>, !=, like, not like, ilike and not ilike. Any other throws
"Illegal operator [...].", so an operator can never carry SQL.
An object gives several = conditions at once:
const users = await DB.table('users').where({ status: 1, subscribed: 1 }).get()A value of null or undefined compares with IS NULL: where('deleted_at', null) finds the rows without one.
Or where clauses#
orWhere() joins a condition with or. Give it a function to group conditions in parentheses:
const users = await DB.table('users') .where('votes', '>', 100) .orWhere((query) => { query.where('name', 'Abigail').where('votes', '>', 50) }) .get()select * from users where votes > 100 or (name = 'Abigail' and votes > 50)Where not clauses#
whereNot() and orWhereNot() negate a group of conditions:
const products = await DB.table('products') .whereNot((query) => { query.where('clearance', true).orWhere('price', '<', 10) }) .get()Where like clauses#
whereLike() matches a pattern, with % and _, ignoring case. true as a third argument makes it case-sensitive.
orWhereLike(), whereNotLike() and orWhereNotLike() are its other forms:
const users = await DB.table('users').whereLike('name', '%John%').get()const exact = await DB.table('users').whereLike('name', '%John%', true).get()It uses ilike on PostgreSQL and like on MySQL and SQLite to ignore case, and like, like binary or glob to
respect it.
Additional where clauses#
whereBetween / orWhereBetween / whereNotBetween
const users = await DB.table('users').whereBetween('votes', [1, 100]).get()const others = await DB.table('users').whereNotBetween('votes', [1, 100]).get()whereIn / whereNotIn / orWhereIn / orWhereNotIn
const users = await DB.table('users').whereIn('id', [1, 2, 3]).get()const others = await DB.table('users').whereNotIn('id', [1, 2, 3]).get()A query in place of the list matches the values it selects:
const activeUsers = DB.table('users').select('id').where('is_active', 1)const comments = await DB.table('comments').whereIn('user_id', activeUsers).get()whereNull / whereNotNull / orWhereNull / orWhereNotNull
const users = await DB.table('users').whereNull('updated_at').get()const others = await DB.table('users').whereNotNull('updated_at').get()whereDate / whereMonth / whereDay / whereYear / whereTime
These compare a part of a date column: its date, month, day of the month, year or time. Each takes a value, or an
operator and a value, and has an or form:
const users = await DB.table('users').whereDate('created_at', '2016-12-31').get()const december = await DB.table('users').whereMonth('created_at', 12).get()const recent = await DB.table('users').whereYear('created_at', '>=', 2025).get()const late = await DB.table('users').whereTime('created_at', '>', '11:20:45').get()A Date as the value compares in UTC, as Oriel stores dates.
whereColumn / orWhereColumn
whereColumn() compares two columns:
const users = await DB.table('users').whereColumn('first_name', 'last_name').get()const updated = await DB.table('users').whereColumn('updated_at', '>', 'created_at').get()Logical grouping#
Wrap conditions that belong together in a function, so their or doesn't reach the rest of the query:
const users = await DB.table('users') .where('name', '=', 'John') .where((query) => { query.where('votes', '>', 100).orWhere('title', '=', 'Admin') }) .get()select * from users where name = 'John' and (votes > 100 or title = 'Admin')Advanced where clauses#
Where exists clauses#
whereExists() takes a query, or a function that builds one, and matches the rows for which it finds a row:
const users = await DB.table('users') .whereExists((query) => { query.select(DB.raw('1')).from('orders').whereColumn('orders.user_id', 'users.id') }) .get()whereNotExists() and orWhereExists() are its other forms.
Subquery where clauses#
whereSub() compares the result of a query with a value:
const users = await DB.table('users') .whereSub( DB.table('memberships').select('type').whereColumn('memberships.user_id', 'users.id').orderByDesc('start_date').limit(1), '=', 'Pro', ) .get()Ordering, grouping, limit and offset#
Ordering#
orderBy() sorts by a column, asc unless you say desc; call it again to sort by more columns:
const users = await DB.table('users').orderBy('name', 'desc').orderBy('email', 'asc').get()orderByDesc() sorts descending. latest() and oldest() sort by created_at, or the column you give, newest or
oldest first. inRandomOrder() shuffles the rows, and reorder() removes the orders so far, or replaces them with
the one you give:
const user = await DB.table('users').latest().first()const randomUser = await DB.table('users').inRandomOrder().first()const byEmail = await DB.table('users').orderBy('name').reorder('email', 'desc').get()Grouping#
groupBy() and having() group the rows and filter the groups:
const users = await DB.table('users').groupBy('account_id').having('account_id', '>', 100).get()Limit and offset#
limit() (or take()) and offset() (or skip()) choose a slice of the rows, and forPage() the rows of a page:
const users = await DB.table('users').offset(10).limit(5).get()const page = await DB.table('users').forPage(3, 15).get()For pages with links, see Pagination.
Conditional clauses#
when() adds to the query only when its first argument is truthy, and passes that value to your function. A third
function runs when it is falsy:
const role = request().string('role').toString()const users = await DB.table('users') .when(role, (query, role) => { query.where('role_id', role) }) .get()unless() does the opposite.
Insert statements#
insert() inserts a row, or a list of rows:
await DB.table('users').insert({ email: 'kayla@example.com', votes: 0 })await DB.table('users').insert([ { email: 'picard@example.com', votes: 0 }, { email: 'janeway@example.com', votes: 0 },])insertOrIgnore() skips the rows that would break a unique index, and returns how many it inserted.
Auto-incrementing IDs#
insertGetId() inserts a row and returns its new id:
const id = await DB.table('users').insertGetId({ email: 'john@example.com', votes: 0 })Upserts#
upsert() inserts the rows, and updates those that already exist. The second argument lists the columns that tell
whether a row exists, which must have a unique index, and the third the columns to update:
await DB.table('flights').upsert( [ { departure: 'Oakland', destination: 'San Diego', price: 99 }, { departure: 'Chicago', destination: 'New York', price: 150 }, ], ['departure', 'destination'], ['price'],)Update statements#
update() changes the rows the query matches, and returns how many it changed:
const affected = await DB.table('users').where('id', 1).update({ votes: 1 })update() and delete() refuse a query with joins or an offset, and with a limit except on MySQL: they throw rather
than change rows you didn't mean.
Update or insert#
updateOrInsert() updates the row that matches its first argument with its second, or inserts both together when
there is none:
await DB.table('users').updateOrInsert({ email: 'john@example.com', name: 'John' }, { votes: '2' })Increment and decrement#
increment() and decrement() add to or take from a column, by 1 or by the amount you give, and can set other
columns at the same time:
await DB.table('users').increment('votes')await DB.table('users').increment('votes', 5)await DB.table('users').decrement('votes', 1, { name: 'John' })Delete statements#
delete() deletes the rows the query matches, and returns how many it deleted. Given an id, it deletes that row.
truncate() empties the table:
const deleted = await DB.table('users').delete()await DB.table('users').where('votes', '>', 100).delete()await DB.table('users').truncate()Pessimistic locking#
sharedLock() keeps the selected rows from being changed until your transaction commits, and lockForUpdate() from
being changed or locked by anyone else. Use them in a transaction:
await DB.transaction(async () => { const sender = await DB.table('users').lockForUpdate().find(1) const receiver = await DB.table('users').lockForUpdate().find(2) // Move money from the sender to the receiver... return { sender, receiver }})SQLite has no row locks: there, both do nothing, and SQLite's write lock covers the transaction.
Debugging#
toSql() returns the query's SQL, with a placeholder for each value (?, or $1, $2 on PostgreSQL), and
getBindings() the values:
const query = DB.table('users').where('votes', '>', 100)const sql = query.toSql()const bindings = query.getBindings()