View on GitHub

ant-sql

SQL extension for AntJS

3. Adding simple queries (Typescript)

We are now able to define multiple models and find entities by ids. ¿What if we want to perform a more complex search? Don’t worry, here comes queries to the rescue

AntJS based libraries allow to use query managers to define queries. Defining queries is a good way to perform search. The benefits of defining queries through the query manager are the following ones:

  1. The AntJs model manager takes care of cache search and cache sync in an efficient way. You can learn more about queries in the AntJs queries doc
  2. The AntSql model manager (an extension of the previous one) takes care of requesting data to the database trying to minimize the number of queries to perform and data to request.

In this tutorial we will learn to define simple queries. Simple queries allow us to configure queries in an easy way. We won’t be able to define some kind of queries using simple queries, but AntSQL provide us complementary tools to define those ones.

¿Which kind of queries can I define as simple queries?

You can define simple queries implemented in the configuration factory. The configuration factory is accesible from the cfgGen property of an AntSQL model manager.

The simple queries currently implemented are the following ones:

Let’s put this on practise. In this tutorial we are implementing to search users whose username is a certain string.

The idea is to inject the queries into the manager. It could be a good idea to provide an interface for injecting queries and then coding a class that implements the interface:

src/provider/IQueryInjector.ts

import { ApiMultipleResultQueryManager, ApiSingleResultQueryManager, Entity } from '@antjs/ant-js';
import { ApiSqlModelManager, SqlModel } from '@antjs/ant-sql';
import * as Knex from 'knex';

export interface IQueryInjector<TEntity extends Entity> {

  /**
   * Injects queries in an AntJS model manager.
   * @param knex Knex instance.
   * @param antModelManager ant model manager where the queries will be injected.
   * @param model Queries model.
   */
  injectQueries(
    knex: Knex,
    antModelManager: ApiSqlModelManager<TEntity>,
    model: SqlModel,
  ): { [key: string]: ApiMultipleResultQueryManager<TEntity> | ApiSingleResultQueryManager<TEntity> };
}

Now, let’s create our query injector:

src/provider/UserQueriesProvider.ts

import { ApiMultipleResultQueryManager, ApiSingleResultQueryManager } from '@antjs/ant-js';
import { ApiSqlModelManager, SqlModel } from '@antjs/ant-sql';
import * as Knex from 'knex';
import { IUser } from '../entity/IUser';
import { IQueryInjector } from './IQueryInjector';

export class UserQueriesProvider implements IQueryInjector<IUser> {

  public injectQueries(
    _: Knex,
    antModelManager: ApiSqlModelManager<IUser>,
    model: SqlModel,
  ): { [key: string]: ApiMultipleResultQueryManager<IUser> | ApiSingleResultQueryManager<IUser>; } {
    return {
      userByUsernameQuery: this._addUserByUsernameQuery(
        antModelManager, model,
      ),
    };
  }

  private _addUserByUsernameQuery(
    userManager: ApiSqlModelManager<IUser>,
    userModel: SqlModel,
  ): ApiSingleResultQueryManager<IUser> {
    return userManager.query<number>(
      userManager.cfgGen.byUniqueField<number>(userModel.getColumn('username')),
    );
  }
}

Let’s explain the process:

Once we have our provider, we can just use it in out AntSqlProvider:

src/provider/AntSqlProvider.ts

...

const { userByUsernameQuery } = new UserQueriesProvider().injectQueries(
  knex, userManager, userModel,
);

...

Don’t forget to export the query managers, they are the objects we will use to perform queries.

...

export {
  manager,
  userManager,
  userByUsernameQuery,
};

That’s all, we are ready to work with the query managers!

You can access the tutorial repository in order to see the code in action. If you have Docker installed, you will be able to run the code with the following command:

npm run docker-test-user-simple-queries-ts

Next tutorial: Adding queries (Javascript or Typescript).