TypeORM

TypeORM - data-mapper ORM for TypeScript and JavaScript (ES7, ES6, ES5). Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, WebSQL databases. Works in Node.JS and Browser.

Query Builder

Query Builder is one of the most important part of TypeORM - it allows you to build queries to fetch the data from the database.

Examples how to use QueryBuilder

Let's say we have a Post entity:


import {Entity, PrimaryGeneratedColumn, Column, ManyToMany, JoinTable} from "typeorm";

@Entity()
export class Post {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    title: string;

    @Column()
    text: string;

    @ManyToMany(type => Category, category => category.posts)
    @JoinTable()
    categories: Category[];

    activeCategories: Category[];

    myCategory: Category;

}
        

If we want to load all posts:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .getMany();
        

If we want to load a single post:


const post = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .getOne();
        

If we want to load all posts and order by title:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .orderBy("post.title", "ASC")
            .getMany();
        

If we want to load all posts where title contains "hello" word:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .where("post.title LIKE :keyword", { keyword: "%hello%" })
            .getMany();
        

If we want to load all posts with multiple where conditions:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .where("post.title LIKE :keyword")
            .andWhere("post.text LIKE :keyword")
            .setParameter("keyword", "%hello%")
            .getMany();
        

If we want to load all posts where title having "hello" word:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .having("post.title LIKE :keyword", { keyword: "%hello%" })
            .getMany();
        

If we want to load all posts with multiple having conditions:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .having("post.title LIKE :keyword")
            .orHaving("post.text LIKE :keyword")
            .setParameter("keyword", "%hello%")
            .getMany();
        

If we want to load all posts and group posts by title:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .groupBy("post.title")
            .getMany();
        

If we want to load limited number of posts using database limit/offset:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .offset(5)
            .limit(10)
            .getMany();
        

If we want to load limited number of posts using paginator options (recommended approach):


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .setFirstResult(5)
            .setMaxResults(10)
            .getMany();
        

If we want to load post with all its categories:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .leftJoinAndSelect("post.categories", "categories") // second parameter is an alias for joined data
            .getMany();
        

If we want to load post with all its categories using inner join:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .innerJoinAndSelect("post.categories", "categories") // second parameter is an alias for joined data
            .getMany();
        

If we want to load post and join something, but don't select the data:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .innerJoin("post.categories", "categories")
            .getMany();
        

If we want to load post with specific of its categories:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .innerJoinAndSelect("post.categories", "category", "category.name=:myCategory", { myCategory: "world" })
            .getMany();
        

If we want to load post with specific of its categories, but map these categories into separate property:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .innerJoinAndMapMany("post.activeCategories", "post.categories", "category", "category.isActive=:isActive", { isActive: true })
            .getMany();
        

If we want to load post with only one specific category and map this category into separate property:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .innerJoinAndMapOne("post.myCategory", "post.categories", "category", "category.currentUser=:currentUser", { currentUser: currentUser.id })
            .getMany();
        

If we want to join entity without a direct relation:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .leftJoinAndSelect(Image, "image", "image.post=:post.id")
            .getMany();
        

If we want to join a custom table:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .leftJoinAndSelect("my_images", "image", "image.post=:post.id")
            .getMany();
        

If we want to get a count of entities returned by a query:


const postsCount = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .getCount();
        

If we want to get a count of entities and entities at the same time:


const [posts, count] = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .getManyAndCount();
        

If we want to get a raw results (not parsed to entity):


const rawResults = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .getRawMany();
        

If we want to get a single (first) raw result (not parsed to entity):


const rawResult = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .getRawOne();
        

If we want to simply execute a query:


const result = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .execute();
        

If we want to execute update query:


const result = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .update({
                title: "Hello",
                text: "World"
            })
            .execute();
        

If we want to execute delete query:


const posts = await connection
            .getRepository(Post)
            .createQueryBuilder("post")
            .delete()
            .where("post.id = id", { id: 1 })
            .execute();
        

QueryBuilder API:

Here is a public API of QueryBuilder that you can use:

Fork me on GitHub