Interacting with data

In order to continue, you must understand Mandarine Query Language (MQL), Models & Repositories

Main

Mandarine repositories work directly with MQL, since MQL serves as the primary query statement writer.

@CustomQuery decorator

The @CustomQuery decorator allows you to write SQL queries in your repositories. While it is true that MQL & Mandarine's ORM aim to remove as much SQL & database code from your application's layer, it is also true that some use cases may require the use of complex SQL queries.

Syntax:

@CustomQuery(query: string, secure?: boolean)

• query: Query to execute

• secure: whether the query has secured parameters such as $1, $2, ...

  • Default: True

Usage

Users Model

// usersModel.ts

import { Table, Id, GeneratedValue, Column } from "https://deno.land/x/mandarinets/mod.ts";

@Table({ schema: "public" })
class Users {

    @Id()
    @GeneratedValue({strategy: "SEQUENCE"})
    @Column()
    public id: number;

    @Column()
    public firstname: string;

    @Column()
    public lastname: string;

    @Column()
    public country: string;

    constructor(firstname?: string, lastname?: string, country?: string) {
        this.firstname = firstname;
        this.lastname = lastname;
        this.country = country;
    }

}

Repository

// usersRepository.ts

import { Repository, MandarineRepository } from "https://deno.land/x/mandarinets/mod.ts";
import { Users } from "./usersModel.ts";

@Repository()
abstract class UsersRepository extends MandarineRepository<Users> {

    constructor() {
        super(Users);
    }
    
    public findByCountry(country: string) {}
    public findByFirstnameAndCountry(firstname: string, country: string) {}
    public countByCountry(country: string) {}
    public existsByLastname(lastname: string) {}

    @CustomQuery("SELECT * FROM public.users WHERE country = $1 AND firstname = 'Andres'")
    public myCustomQuery(country: string) {}
}

Test Controller

// controller.ts

import { Controller, GET } from "https://deno.land/x/mandarinets/mod.ts";
import { Users } from "./usersModel.ts";
import { UsersRepository } from "./usersRepository.ts";

@Controller()
export class MyController {

    constructor(private readonly repository: UsersRepository) {}

    @GET('/add-people')
    public async handleAddPeople() {
        let user1 = new Users("Andres", "Pirela", "United States");
        let user2 = new Users("Anastasia", "Skymonov", "Russia");
        let user3 = new Users("Bill", "Gates", "United States");

        await this.repository.save(user1);
        await this.repository.save(user2);
        await this.repository.save(user3);

        return true;
    }

    @GET('/get-people-from-united-states')
    public async handleGetPeopleFromUnitedStates() {
        return await this.repository.findByCountry("United States");
    }

    @GET('/count-all')
    public async handleCountAll() {
        return await this.repository.countAll();
    }

    @GET('/count-russia')
    public async handleCountRussia() {
        return await this.repository.countByCountry("Russia");
    }

    @GET('/find-anastasia')
    public async handleFindAnastasia() {
        return await this.repository.findByFirstnameAndCountry("Anastasia", "Russia");
    }

    @GET('/exists-by-last-name')
    public async existsByLastname() {
        return await this.repository.existsByLastname("Gates");
    }

    @GET('/custom-query')
    public async customQueryHandler() {
        return await this.repository.myCustomQuery("United States");
    }
}

Mandarine's MVC test application

import { MandarineCore } from "https://deno.land/x/mandarinets/mod.ts";

import { UsersRepository } from "./usersRepository.ts";
import { MyController } from "./controller.ts";

const controllers = [MyController];
const repositories = [UsersRepository];

new MandarineCore().MVC().run();

Results

# http://127.0.0.1:8080/get-people-from-united-states
# ==> [{"id":1,"firstname":"Andres","lastname":"Pirela","country":"United States"},{"id":3,"firstname":"Bill","lastname":"Gates","country":"United States"}]

##########

# http://127.0.0.1:8080/count-all
# ==> 3

##########

# http://127.0.0.1:8080/count-russia
# ==> 1

##########

# http://127.0.0.1:8080/find-anastasia
# ==> [{"id":2,"firstname":"Anastasia","lastname":"Skymonov","country":"Russia"}]

##########

# http://127.0.0.1:8080/exists-by-last-name
# ==> true

##########

# http://127.0.0.1:8080/custom-query
# ==> [{"id":1,"firstname":"Andres","lastname":"Pirela","country":"United States"}]

Note

• They keyword "await" should be used for all repository methods