The database query builder in OstroJS provides a simple, intuitive interface for generating and performing database queries. It works with all of OstroJS's supported database systems and may be used to conduct most database operations in your application.
Prepare parameter binding is used by the OstroJS query builder to safeguard your application from SQL injection threats. Strings supplied to the query builder as query bindings do not need to be cleaned or sanitised.
To start a query, utilise the table
method given by the DB
facade. The table
method produces a fluent query builder object for the specified table, enabling you to add extra restrictions to the query and then use the get
method to receive the query's results:
const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')
class UserController extends Controller {
/**
* Show a list of all of the application's users.
*
*/
async index({ view }) {
let users = await DB.table('users').get();
view('user.index', {users});
}
}
module.exports = UserController
The get
function returns an instance containing the query's results, each of which is a NodeJS object instance. You may get
the value of each column by using the column as a property of the object:
const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')
class UserController extends Controller {
/**
* Show a list of all of the application's users.
*
*/
async index({ view }) {
let users = await DB.table('users').get();
for(let user of users) {
console.log(user.name);
}
}
}
module.exports = UserController
To start a query, utilise the table method given by the DB
facade. The table method produces a fluent query builder object for the specified table, enabling you to add extra restrictions to the query and then use the get
method to receive the query's results:
const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')
class UserController extends Controller {
/**
* Show a list of all of the application's users.
*
*/
async index({ view }) {
let users = await DB.table('users').get();
view('user.index', {users});
}
}
module.exports = UserController
The get
function returns an @ostro/support/collection
instance containing the query's results, each of which is a NodeJS object instance. You may get the value of each column by using the column as a property of the object:
const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')
class UserController extends Controller {
/**
* Show a list of all of the application's users.
*
*/
async index({ view }) {
let users = await DB.table('users').get();
for(let user of users) {
console.log(user.name);
}
}
}
module.exports = UserController
You may use the first
method of the DB
facade to obtain a single row from a database table. A single object will be returned by this method:
let user = await DB.table('users').where('name', 'John').first();
console.log(user.email);
The pluck
method may be used to get an @ostro/support/collection
object containing the values of a single column. We'll obtain a list of user titles in this example:
const DB = require('@ostro/support/facades/database')
let titles = await DB.table('users').pluck('title');
for (let title of titles) {
console.log(title);
}
By passing a second parameter to the pluck
function, you may define which column the resultant collection should use as its keys:
let titles = await DB.table('users').pluck('title','name');
for (let title of titles) {
console.log(title);
}
It's possible that you'll need to put an arbitrary string into a query at some point. You may utilise the raw
method offered by the DB
facade to construct a raw string expression:
let users = await DB.table('users')
.select(DB.raw('count(*) as user_count, status'))
.where('status', '<>', 1)
.groupBy('status')
.get();
To inject a raw "where" clause into your query, use the whereRaw
and orWhereRaw
methods. As a second input, these methods take an optional array of bindings:
let orders = await DB.table('orders')
.whereRaw('price > IF(state = "TX", ?, 100)', [200])
.get();
To inject a raw "where" clause into your query, use the whereRaw and orWhereRaw methods. As a second input, these methods take an optional array of bindings:
To supply a raw string as the value of the "having" clause, use the havingRaw
and orHavingRaw
methods. As a second input, these methods take an optional array of bindings:
let orders = await DB.table('orders')
.select('department', DB.raw('SUM(price) as total_sales'))
.groupBy('department')
.havingRaw('SUM(price) > ?', [2500])
.get();
To supply a raw string as the value of the "order by" clause, use the orderByRaw
method:
let orders = await DB.table('orders')
.orderByRaw('updated_at - created_at DESC')
.get();
To supply a raw string as the value of the group by clause, use the groupByRaw
method:
let orders = await DB.table('orders')
.select('city', 'state')
.groupByRaw('city, state')
.get();
You may also use the query builder to add join
clauses to your queries. You may use the join
method on a query builder instance to execute a rudimentary "inner join." The name of the table you want to connect to is the first parameter supplied to the join function, and the subsequent arguments indicate the join's column constraints. You may even use a single query to connect many tables:
const DB = require('@ostro/support/facades/database');
let 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();
Use the leftJoin
and rightJoin
methods instead of the innerJoin
method to do a "left join" or "right join" instead of a "inner join." The signature of these methods is the same as that of the join
method:
let users = await DB.table('users')
.leftJoin('posts', 'users.id', '=', 'posts.user_id')
.get();
let users = await DB.table('users')
.rightJoin('posts', 'users.id', '=', 'posts.user_id')
.get();
To make a "cross join," use the crossJoin
method. Between the first table and the linked table, cross joins produce a cartesian product:
let sizes = await DB.table('sizes')
.crossJoin('colors')
.get();
You may even utilise more complex join clauses. To get started, call the join
function with a closure as the second parameter.
await DB.table('users')
.join('contacts', function ($join) {
$join.on('users.id', '=', 'contacts.user_id').orOn(...);
})
.get();
If you want to employ a "where" clause in your joins, you may use the JoinClause
instance's where and orWhere methods. Rather of comparing two columns, these approaches compare a column to a value:
await DB.table('users')
.join('contacts', function ($join) {
$join.on('users.id', '=', 'contacts.user_id')
.where('contacts.user_id', '>', 5);
})
.get();
The query builder also has a feature that allows you to "union" two or more queries. You might, for example, make an initial query and then use the union
method to combine it with others:
const DB = require('@ostro/support/facades/database');
let first = DB.table('users')
.whereNull('first_name');
let users = DB.table('users')
.whereNull('last_name')
.union(first)
.get();
The query builder also has a unionAll method in addition to the union method. Duplicate results from queries coupled with the unionAll
technique will not be eliminated. The method signature of the unionAll
method is the same as that of the union
method.
You may use the where method in the query builder to add "where" clauses to the query. Three parameters are required for the most basic call to the where
function. The first input is the column's name. The operator, which can be any of the database's available operators, is the second parameter. The value to compare to the column's value is the third parameter.
For example, the following query returns people whose votes
column value is equal to 100
and whose age
column value is more than 35
:
let users = await DB.table('users')
.where('votes', '=', 100)
.where('age', '>', 35)
.get();
If you wish to check if a column is equal
to a certain value, you may send the value as the second parameter to the where
method for convenience. OstroJS will make the assumption that you want to utilise the =
operator:
let users = await DB.table('users').where('votes', 100).get();
As previously stated, you may use any operator that your database system supports:
var users = await DB.table('users')
.where('votes', '>=', 100)
.get();
var users = await DB.table('users')
.where('votes', '<>', 100)
.get();
var users = await DB.table('users')
.where('name', 'like', 'T%')
.get();
The "where" clauses will be combined together using the and operator when chaining together calls to the query builder's where
function. The orWhere
function, on the other hand, may be used to link a clause to the query using the or operator. The parameters for the orWhere
method are the same as for the where
method:
let users = await DB.table('users')
.where('votes', '>', 100)
.orWhere('name', 'John')
.get();
If you need to group an "or
" condition within parentheses, you may pass a closure as the first argument to the orWhere
method:
let users = await DB.table('users')
.where('votes', '>', 100)
.orWhere(function($query) {
$query.where('name', 'Abigail')
.where('votes', '>', 50);
})
.get();
The following SQL will be generated as a result of the preceding example:
select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
whereBetween / orWhereBetween
The whereBetween
method checks whether the value of a column is between two values:
let users = await DB.table('users')
.whereBetween('votes', [1, 100])
.get();
whereNotBetween / orWhereNotBetween
The whereNotBetween
method checks that the value of a column is not between two values:
let users = await DB.table('users')
.whereNotBetween('votes', [1, 100])
.get();
whereIn / whereNotIn / orWhereIn / orWhereNotIn
The whereIn
function checks that the value of a given column is contained inside the array:
let users = await DB.table('users')
.whereIn('id', [1, 2, 3])
.get();
The whereNotIn
function checks if the value of a given column is not present in the provided array:
let users = await DB.table('users')
.whereNotIn('id', [1, 2, 3])
.get();
whereNull / whereNotNull / orWhereNull / orWhereNotNull
The whereNull
function checks that the provided column's value is NULL:
let users = await DB.table('users')
.whereNull('updated_at')
.get();
The whereNotNull
function checks that the value of a column isn't NULL:
let users = await DB.table('users')
.whereNotNull('updated_at')
.get();
In order to accomplish the logical grouping requested by your query, you may need to group numerous "where
" clauses between parenthesis. In fact, to avoid unexpected query behaviour, you should always combine calls to the orWhere
function in parenthesis. You may do this by passing a closure to the where
method:
let users = await DB.table('users')
.where('name', '=', 'John')
.where(function ($query) {
$query.where('votes', '>', 100)
.orWhere('title', '=', 'Admin');
})
.get();
Passing a closure to the where function tells the query builder to start a constraint group, as you can see. A query builder object will be returned to the closure, which you can use to specify the constraints that should be included within the parenthesis group. The following SQL will be generated as a result of the preceding example:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')
You may write "where exists" SQL clauses with the whereExists
function. The whereExists
method takes a closure that returns a query builder object, enabling you to specify the query to be used inside the "exists
" clause:
let users = await DB.table('users')
.whereExists(function ($query) {
$query.select(DB.raw(1))
.from('orders')
.whereColumn('orders.user_id', 'users.id');
})
.get();
The query above will produce the following SQL:
select * from users
where exists (
select 1
from orders
where orders.user_id = users.id
)
A "where
" clause that compares the results of a subquery to a specific value is sometimes required. You may do this by calling the where function with a closure and a value. The following query, for example, will return all users with a recent "membership" of a specific kind;
let users = await DB.table('users').where(function($query) {
$query.select('type')
.from('membership')
.where('membership.user_id', '123')
.orderBy('membership.start_date', 'desc')
.limit(1);
}).get()
You may use the orderBy
method to arrange the query results by a specific column. The orderBy
method's first parameter should be the column you want to sort by, while the second argument sets the sort direction, which may be either asc
or desc
:
let users = await DB.table('users')
.orderBy('name', 'desc')
.get();
You may use orderBy
as many times as you like to sort by several columns:
let users = await DB.table('users')
.orderBy('name', 'desc')
.orderBy('email', 'asc')
.get();
You may quickly arrange results by date using the most recent
and oldest
ways. The result will be arranged by the table's created at column by default. You may also give the name of the column you want to sort by:
let user = await DB.table('users')
.latest()
.first();
To sort the query results in a random order, use the inRandomOrder
function. You might, for example, use this way to get a random user:
$randomUser = await DB.table('users')
.inRandomOrder()
.first();
The groupBy
and having
methods may be used to group the query results, as you might imagine. The signature of the having
method is identical to that of the where method:
let users = await DB.table('users')
.groupBy('account_id')
.having('account_id', '>', 100)
.get();
You can give the group numerous parameters. To group by many columns, use the following method:
let users = await DB.table('users')
.groupBy('first_name', 'status')
.having('account_id', '>', 100)
.get();
In addition, the query builder has an insert
function for inserting records into the database table. An array of column names and values is sent to the insert
method:
await DB.table('users').insert({
'email' : 'kayla@example.com',
'votes' : 0
});
By supplying an object
of arrays
, you may insert several records at once. Each object
corresponds to a record to be added into the table:
await DB.table('users').insert([
{'email' : 'picard@example.com', 'votes' : 0},
{'email' : 'janeway@example.com', 'votes' : 0},
]);
The upsert
method will create new records if they don't already exist
and update existing records with new values you specify. The first input to the method is a list of values to insert
or update
, and the second argument is a list of the column(s) that uniquely identify entries in the related table. If a matched record already exists
in the database, the method's third and final parameter is an array of columns that should be updated:
await DB.table('flights').upsert([
{'departure' : 'Oakland', 'destination' : 'San Diego', 'price' : 99},
{'departure' : 'Chicago', 'destination' : 'New York', 'price' : 150}
], ['departure', 'destination'], ['price']);
OstroJS will attempt to insert two records in the example above. If a record with the identical departure and destination column values already exists, OstroJS will change the price column of that record.
The query builder can use the update method to edit existing entries in addition to adding them into the database. The update
method, like the insert
method, takes an array of column and value pairs to specify which columns should be modified. You may use where clauses to limit the update
query:
let affected = await DB.table('users')
.where('id', 1)
.update({'votes' : 1});
You might wish to edit an existing database record or create one if there isn't one there. The updateOrInsert
technique might be used in this case. The updateOrInsert
method takes two arguments: an array of search criteria to discover the record and an array of column and value pairs to update the columns.
Using the first argument's column and value pairs, the updateOrInsert
function will try to find a matching database record. If the record already exists, the values in the second parameter will be modified. If the record cannot be found, a new record with the combined characteristics of both parameters will be created:
await DB.table('users')
.updateOrInsert(
{'email' : 'john@example.com', 'name' : 'John'},
{'votes' : '2'}
);
To delete records from the table, use the delete method in the query builder. Before executing the delete
function, you can include "where
" clauses to confine remove statements:
await DB.table('users').delete();
await DB.table('users').where('votes', '>', 100).delete();