Documentation chapters
You're looking at legacy documentation.
New multi-language documentation available at https://docs.deepkit.io
ORM

Query

A query is an object that describes how data should be fetched from the database or modified. It has multiple methods to describe your query and termination methods that execute it. The database adapter can enhance the query API in many ways to support database specific features.

You can create a query by using Database.query(T) or Session.query(T). We recommend sessions as it improves performance.

@entity.name('user')
class User {
    id: number & PrimaryKey & AutoIncrement = 0;
    created: Date = new Date;
    birthdate?: Date;
    visits: number = 0;
        
    constructor(public username: string) {
    }
}

const database = new Database(...);

//[ { username: 'User1' }, { username: 'User2' }, { username: 'User2' } ]
const users = await database.query(User).select('username').find();

Filter

A filter can be applied to limit the result set.

//simple filters
const users = await database.query(User).filter({name: 'User1'}).find();

//multiple filters, all AND
const users = await database.query(User).filter({name: 'User1', id: 2}).find();

//range filter: $gt, $lt, $gte, $lte (greater than, lower than, ...)
//equivalent to WHERE created < NOW()
const users = await database.query(User).filter({created: {$lt: new Date}}).find();
//equivalent to WHERE id > 500
const users = await database.query(User).filter({id: {$gt: 500}}).find();
//equivalent to WHERE id >= 500
const users = await database.query(User).filter({id: {$gte: 500}}).find();

//set filter: $in, $nin (in, not in)
//equivalent to WHERE id IN (1, 2, 3)
const users = await database.query(User).filter({id: {$in: [1, 2, 3]}}).find();

//regex filter
const users = await database.query(User).filter({username: {$regex: /User[0-9]+/}}).find();

//grouping: $and, $nor, $or
//equivalent to WHERE (username = 'User1') OR (username = 'User2')
const users = await database.query(User).filter({
    $or: [{username: 'User1'}, {username: 'User2'}]
}).find();


//nested grouping
//equivalent to WHERE username = 'User1' OR (username = 'User2' and id > 0)
const users = await database.query(User).filter({
    $or: [{username: 'User1'}, {username: 'User2', id: {$gt: 0}}]
}).find();


//nested grouping
//equivalent to WHERE username = 'User1' AND (created < NOW() OR id > 0)
const users = await database.query(User).filter({
    $and: [{username: 'User1'}, {$or: [{created: {$lt: new Date}, id: {$gt: 0}}]}]
}).find();

Select

There are multiple select methods that execute the query.

count()

count() returns a number of records.

has()

has() returns a boolean of true when there is at least one record found, and false when no records are found.

find()

find() always returns an array. If the result is empty then an empty array is returned.

const users = await database.query(User).find();

findOne()

findOne() returns the first entity instance, and throws an ItemNotFound if none are found.

const user = await database.query(User).findOne();

findOneOrUndefined()

findOneOrUndefined() returns the first entity instance or undefined when not found.

const user = await database.query(User).findOneOrUndefined();
if (!users) {
    throw new Error('Sorry, no user found.');
}

findField()

findField() returns an array of fields. If the result is empty then an empty array is returned.

const usernames = await database.query(User).findField('username');

findOneField()

findOneField() returns a field of a single entity instance and throws an ItemNotFound if not found.

const username = await database.query(User).findOneField('username');

findOneFieldOrUndefined()

findOneFieldOrUndefined() returns a field of a single entity instance or undefined when not found.

Ordering

With orderBy(field, order) the order of returned records can be changed.

const users = await session.query(User).orderBy('created', 'desc').find();

Paging

With the methods itemsPerPage() and page(), the results can be paginated. Page starts at 1.

const users = await session.query(User).itemsPerPage(50).page(1).find();

Alternative methods limit and skip allow you to paginate manually.

const users = await session.query(User).limit(5).skip(10).find();

Joining

By default, references from the schema are neither included in queries nor loaded. To include a join in the query without loading the reference, use join() (left join) or innerJoin. To include a join in the query and load the reference use joinWith or innerJoinWith.

All following examples assume these model schemas:

@entity.name('group')
class Group {
    id: number & PrimaryKey & AutoIncrement = 0;
    created: Date = new Date;
        
    constructor(public username: string) {
    }
}
        
@entity.name('user')
class User {
    id: number & PrimaryKey & AutoIncrement = 0;
    created: Date = new Date;
    
    group?: Group & Reference;
        
    constructor(public username: string) {
    }
}
//select only users with a group assigned (INNER JOIN)
const users = await session.query(User).innerJoin('group').find();
for (const user of users) {
    user.group; //error, since reference was not loaded
}
//select only users with a group assigned (INNER JOIN) and load the relation
const users = await session.query(User).innerJoinWith('group').find();
for (const user of users) {
    user.group.name; //works
}

To change join queries, use the same methods but with the use prefix: useJoin, useInnerJoin, useJoinWith, or useInnerJoinWith. To end the modification of the join query, use end() to get the parent query back.

//select only users with a group with name 'admins' assigned (INNER JOIN)
const users = await session.query(User)
    .useInnerJoinWith('group')
        .filter({name: 'admins'})
        .end()  // returns to the parent query
    .find();

for (const user of users) {
    user.group.name; //always admin
}

Aggregating

Aggregation methods allow you to count records and aggregate fields.

The following examples assume this model schema:

@entity.name('file')
class File {
    id: number & PrimaryKey & AutoIncrement = 0;
    created: Date = new Date;
        
    downloads: number = 0;

    category: string = 'none';
        
    constructor(public path: string & Index) {
    }
}

groupBy allows you to group the result into the given field.

await database.persist(
    cast<File>({path: 'file1', category: 'images'}),
    cast<File>({path: 'file2', category: 'images'}),
    cast<File>({path: 'file3', category: 'pdfs'})
);

//[ { category: 'images' }, { category: 'pdfs' } ]
await session.query(File).groupBy('category').find();

There are several aggregation methods: withSum, withAverage, withCount, withMin, withMax, withGroupConcat. Each requires a field name as first argument, and an optional second argument to change the alias.

// first let's update some of the records:
await database.query(File).filter({path: 'images/file1'}).patchOne({$inc: {downloads: 15}});
await database.query(File).filter({path: 'images/file2'}).patchOne({$inc: {downloads: 5}});
        
//[{ category: 'images', downloads: 20 },{ category: 'pdfs', downloads: 0 }]
await session.query(File).groupBy('category').withSum('downloads').find();

//[{ category: 'images', downloads: 10 },{ category: 'pdfs', downloads: 0 }]
await session.query(File).groupBy('category').withAverage('downloads').find();

//[ { category: 'images', amount: 2 }, { category: 'pdfs', amount: 1 } ]
await session.query(File).groupBy('category').withCount('id', 'amount').find();

Lifting

Lifting a query means adding new functionality to it. This is usually used by either plugins or complex architectures in order to split bigger query classes up into multiple handy reusable ones.

import { FilterQuery, Query } from '@deepkit/orm';

class UserQuery<T extends {birthdate?: Date}> extends Query<T>  {
    hasBirthday() {
        const start = new Date();
        start.setHours(0,0,0,0);
        const end = new Date();
        end.setHours(23,59,59,999);
        
        return this.filter({$and: [{birthdate: {$gte: start}}, {birthdate: {$lte: end}}]} as FilterQuery<T>);
    }
}
        
await session.query(User).lift(UserQuery).hasBirthday().find();

Patching

Patch is a modification query that patches records that the query describes. The methods patchOne and patchMany terminate the query and execute the patch.

await database.query(User).filter({username: 'User1'}).patchOne({birthdate: new Date});
await database.query(User).filter({username: 'User1'}).patchOne({$inc: {visits: 1}});

await database.query(User).patchMany({visits: 0});

Returning

To return the new value for changed fields, use returning.

await database.query(User).patchMany({visits: 0});

//{ modified: 1, returning: { visits: [ 5 ] }, primaryKeys: [ 1 ] }
const result = await database.query(User)
    .filter({username: 'User1'})
    .returning('username', 'visits')
    .patchOne({$inc: {visits: 5}});

All official database adapters support the returning feature. It's a way to return changed fields from a patch query.

Deleting

Delete is a modification query that deletes records that the query describes. The methods deleteOne and deleteMany terminate the query and execute the delete.

const result = await database.query(User)
    .filter({visits: 0})
    .deleteMany();

const result = await database.query(User).filter({id: 4}).deleteOne();
Made in Germany