Query Builder

The query builder makes SQL for you. You chain methods, and Dox runs the query with bound values. It works on Postgres, MySQL/MariaDB and SQLite.

• Set up the database first: see Database.
• For transactions, see Transactions.

Good to know

• Table and column names are quoted. On Postgres, userId and userid are two names.
• orderBy throws an ArgumentError when the direction is not asc or desc.
• toSql() wraps the where part in brackets.
• count() ignores limit, offset and orderBy. With groupBy it returns the number of groups.

create

Create

await Actor().create(
    {'name': 'John Wick', 'age': 60}
);

insert

InsertMultiple insert

await Actor().insert(
    {'name': 'John Wick', 'age': 60}
);

await Actor().insertMultiple([
    {'name': 'John Wick', 'age': 60},
    {'name': 'John Doe', 'age': 25},
]);

Info

insert works with any primary key type, for example a uuid string. insertMultiple uses the keys from all rows. When a row does not have a key, that column gets its default value.

update

Update rows

await Actor()
    .where('id', 3)
    .where('status', 'active')
    .update({
        "name": "Updated AJ",
        "age": 120,
    });

count

Count allCount with condition

await Actor().count();

await Actor().where('age', '>=' , 23).count();

Info

count() does not use limit, offset and orderBy. With groupBy, count() returns the number of groups.

find

Find by idFind by custom column

await Actor().find(id);

await Actor().find('name', 'John Wick');

getFirst

Get first row

Actor actor = await Actor().getFirst(); // limit = 1

all

Ge all rows

List<Actor> actors = await Actor().all();
for(Actor actor in actors) {
    print(actor.id)
}

get

Get

List<Actor> actors = await Actor().where('name', 'John Wick').get();
    for(Actor actor in actors) {
    print(actor.id)
}

toSql

To SQL query

String query = Actor().where('name', 'John Wick').toSql();
print(query)

delete

Delete a row

await Actor().where('name', 'John Wick').delete();

forceDelete

Force delete

await Actor().where('name', 'John Wick').forceDelete();

Info

This is for models with softDeletes. It also deletes rows that are already soft deleted, so you do not need withTrash().

withTrash

Get from trash

List actors = await Actor().where('name', 'John Wick').withTrash().get();
for(Actor actor in actors) {
    print(actor.id)
}

Info

This work only with softDeletes. On a model without softDeletes it does nothing.

select

Multiple selectSelect with array of columnsSelect with comma separated columnsSelect raw

await Actor()
    .select('id')
    .select('name')
    .where('name', 'John Wick').get();

await Actor()
    .select(['id', 'name', 'age']).where('name', 'John Wick').get();

await Actor()
    .select('id, name, age').where('name', 'John Wick').get();

// nothing is quoted
await Actor().selectRaw('DISTINCT name').get();

whereIn

Where in conditionWhere not inOr where in / not in

await Actor().whereIn('id', ['1', '2']).get();

await Actor().whereNotIn('id', [1, 2]).get();

await Actor().where('age', 60).orWhereIn('id', [1, 2]).get();
await Actor().where('age', 60).orWhereNotIn('id', [1, 2]).get();

Info

Each value is sent as a bound parameter. An empty list matches no rows for whereIn, and all rows for whereNotIn.

whereNull

Where null / not nullOr where null / not null

await Actor().whereNull('deleted_at').get();
await Actor().whereNotNull('deleted_at').get();

await Actor().where('age', 60).orWhereNull('deleted_at').get();
await Actor().where('age', 60).orWhereNotNull('deleted_at').get();

where

whereWhere with custom conditionWhere null

await Actor().where('name', 'John Wick').get();

await Actor().where('name', '=', 'John Wick').get();
await Actor().where('age', '>=', 23).get();

// builds `deleted_at IS NULL`
await Actor().where('deleted_at', null).get();

orWhere

orWhereorWhere with custom conditionwhere and orWhere

await Actor().orWhere('name', 'John Wick').get();

await Actor().orWhere('name', '=', 'John Wick').get();
await Actor().orWhere('age', '>=', 23).get();

await Actor()
.where('name', 'John Doe').orWhere('name', 'John Wick').get();

whereRaw

whereRaw

await Actor().whereRaw('name = @name', {'name', 'John Wick'}).get();

orWhereRaw

Or where raw

await Actor().orWhereRaw('name = @name', {'name', 'John Wick'}).get();

limit, take, offset

LimitTakeoffset

await Actor().limit(10).get();

await Actor().take(10).get();

await Actor().limit(10).offset(10).get();

Info

Limit and take are the same function.

groupBy

Group by

await Actor()
    .select('count(*) as total, name').groupBy('name').get();

orderBy

defaultdescascmultiple columns

await Actor().orderBy('name').get();

await Actor().orderBy('name', 'desc').get();

await Actor().orderBy('name', 'asc').get();

await Actor()
    .orderBy('name', 'asc')
    .orderBy('id', 'desc')
    .get();

Info

The direction must be asc or desc. Any other value throws an ArgumentError. The column name is quoted, but a name with ( is kept as it is. Do not pass user input as the column name.

join

Join

await Actor()
    .join('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

leftJoin

Left join

await Actor()
    .leftJoin('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

rightJoin

Right join

await Actor()
    .rightJoin('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

joinRaw

Join raw

await Actor()
    .joinRaw('actor_info on actor_info.admin_id = admin.id')
    .get();

leftJoinRaw

Left join raw

await Actor()
    .leftJoinRaw('actor_info on actor_info.admin_id = admin.id')
    .get();

rightJoinRaw

Right Join Raw

await Actor()
    .rightJoinRaw('actor_info on actor_info.admin_id = admin.id')
    .get();

debug

Debug

await Actor().debug(true).all();