TypeScript, GraphQL, Sequelize … OMG

For my new projectI I decided to use TypeScript in my new project instead. I got fed up with all those unexpected JavaScript run-time errors occuring in my previous projects. It was not a very pleasant journey – so many new things, steep learning curve, new syntax, but you get the type safety, which is nice.

All my previous project were using a REST API. I decided to give GraphQL a try. It was not a very pleasant journey – so many new things, steep learning curve, new syntax, Apollo, variables, no HTTP status codes to detect errors, etc., but you get the playground, which is nice.

Right now I doubt the benefits of using it.

That was true when I started writing this post.

The basic idea is not to reinvent the wheel, so I chose graphql-modules NPM package for “easier” adoption. The library encourages to create reusable modules encapsulating functionality, has dependency injection support, and the modules should be testable (do not know, I haven’t got there).

The most important part is the resolver which is a function to resolve value for a type of field in a schema, it will start at the root object that is being returned by the query and resolve all the required fields. This is one of the features of GraphQL – you get what you want, not less, not more. If you do not ask for the field, it will not appear in the result (compare that with REST). The fancy terms are under-fetching and over-fetching.

But, back to code, I have two entities User with many Role s. In User model the association is defined as

@BelongsToMany(() => Role, () => UserRole) roles: Role[];

Auto-generated schema from these types

@ObjectType()          
export class User {
  @Field(type => ID) id: number; 
  @Field() firstName: string;
  @Field() lastName: string;
  @Field() emailAddress: string;
  @Field() suspended: boolean;
  @Field() confirmed: boolean;
  @Field() deleted: boolean;
}          

@ObjectType()
export class Role {
  @Field() name: string;          
  @Field() description: string;
}          

@ObjectType()
export class QueryResult {
  @Field() page: number;
  @Field() rowsPerPage: number;
  @Field() totalCount: number;
  @Field(type => [User]) records: User[];
}          

@ArgsType()
export class SearchUsersArgs {
  @Field({ nullable: true }) sortBy?: string;          
  @Field(type => Boolean, { nullable: true }) sortDirection?: Boolean;
 
  @Field(type => Int, { defaultValue: 1, nullable: true })
  @Min(0) page: number = 1;          

  @Field(type => Int, { nullable: true })
  @Min(10)
  @Max(50)
  pageSize = 20;
}

and the resolver has two parametrized queries – one to return a list of users

@Query(returns => QueryResult)
async users(@Args() input: SearchUsersArgs) {
  return await this.UsersProvider.getUsers(input);
}

and another to find one user by id

@Query(returns => User)
async user(@Arg("id", type => Int) id: number) {
  const user = await this.UsersProvider.getUserById(id);
  if (user) { 
    return user;
   }
  throw new NotFoundError("User with given id was not found.");
}

The user properties like name , emailAddress , etc. are resolved by default just by accessing the object properties. For the roles the situation is different. The data is stored in different table in MySQL database. The first idea was to JOIN the data and to include the roles in the query, get the roles along with the users in single database roundtrip.

const users = await User.findAll({
 attributes: {
   exclude: ["password", "password_confirmation", "password_digest"]
 },
 include: [{ model: Role }],
 offset: (page - 1) * pageSize,
 limit: pageSize,
 order: [[sortBy, sortDirection]]
});

I have quickly dismissed this approach as I can’t tell in advance whether the user wants to include the roles in the result set and I may be loading the data from database and not use them later (over-fetching). Even though now there is only one association, this might change in the future, and I would have to include all of them which will result in query JOINing many tables to return names of the users.

The GraphQL-like solution to this problem is to create a resolver for the roles field:

@FieldResolver(returns => [Role])
async roles(@Root() root: User) { ... }

First I had a mismatch in what type was returned by the GraphQL query and because the instances returned by sequelize do have field accessors and the result was as expected for the basic queries to get the name , emailAddress , etc.

The value of parameter root with the @Root annotation is provided by the GraphQL engine. I have two types representing the database entity User and the data transfer object UserDTO . The root here was initially the instance of the model class, i.e. entity loaded from the database, I thought it should be the instance of the DTO class, and it fits better in the world of GraphQL. What I am losing here is the model capability and the access to its properties, fields and association collections (which I should not have access to because I don’t want to over-fetch). But it should return DTO because I don’t know what end-user wants. Returning database entity may leak some information to end-user.

The correct declaration for the resolver for roles field should then be

async roles(@Root() root: User): Promise<RoleDTO[]> { ... }

I query the database to find the corresponding user entity for the root passed in by GraphQL engine.

@FieldResolver(returns => [RoleDto])
async roles(@Root() root: User): Promise<RoleDTO[]> {
  const user = await this.UsersProvider.getUserById(root.id); // fetch the user again
  if (user) {
    const roles = (await user.$get<Role>("roles")) as Role[]; // get roles for current user
    return roles.map<RoleDTO>(r => { 
      return {
        name: r.name,
        description: r.name
      };
   }); 
 } 

 return [];
}

Now, I have a different problem, the SELECT N+1 problem. This resolver will query the database to get the roles for every user that will be returned in the GraphQL query (not to mention the initial query to get the users):

Executing (default): SELECT id, userName, firstName, lastName, email AS emailAddress, confirmed, suspended, deleted FROM m_user AS User WHERE User.id = 15;

Executing (default): SELECT id, userName, firstName, lastName, email AS emailAddress, confirmed, suspended, deleted FROM m_user AS UserWHERE User.id = 76;

Executing (default): SELECT id, userName, firstName, lastName, email AS emailAddress, confirmed, suspended, deleted FROM m_user AS User WHERE User.id = 6;

Executing (default): SELECT id, userName, firstName, lastName, email AS emailAddress, confirmed, suspended, deleted FROM m_user AS User WHERE User.id = 9;          

...          

Executing (default): SELECT Role.id, Role.shortname AS name, UserRole.roleId AS UserRole.roleId, UserRole.userId AS UserRole.userId FROM m_role AS Role INNER JOIN m_role_assignments AS UserRole ON Role.id = UserRole.roleId AND UserRole.userId = 15;          
Executing (default): SELECT Role.id, Role.shortname AS name, UserRole.roleId AS UserRole.roleId, UserRole.userId AS UserRole.userId FROM m_role AS Role INNER JOIN m_role_assignments AS UserRole ON Role.id = UserRole.roleId AND UserRole.userId = 76;      
Executing (default): SELECT Role.id, Role.shortname AS name, UserRole.roleId AS UserRole.roleId, UserRole.userId AS UserRole.userId FROM m_role AS Role INNER JOIN m_role_assignments AS UserRole ON Role.id = UserRole.roleId AND UserRole.userId = 6;          

Executing (default): SELECT Role.id, Role.shortname AS name, UserRole.roleId AS UserRole.roleId, UserRole.userId AS UserRole.userId FROM m_role AS Role INNER JOIN m_role_assignments AS UserRole ON Role.id = UserRole.roleId AND UserRole.userId = 9;

The problem has an easy solution, and it is to use dataloader – in my case sequelize-dataloader since I am using Sequelize to access the database.

The dataloader hooks itself into the Sequelize methods ( findByPk in this example) and hijacks them, replacing them with a smart caching mechanism. If you prime the dataloader context with objects and you later want to load an object by id from the database, then the dataloader will check its cache, and if the object is there, it will return it immediately thus avoiding database roundtrip.

import { createContext, EXPECTED_OPTIONS_KEY } from "dataloader-sequelize";

@Query(returns => UserQueryResult)
async users(
  @Args() input: UserSearchArgs,
  @Ctx() ctx: Context
): Promise<UserQueryResult> {
  const context = createContext(User.sequelize); // create dataloader context 
  const found = await this.usersProvider.getUsers(input); // get the users
  context.prime(found.records); // prime the context with found records
  ctx["dataloader-context"] = context; // remember the dataloader context in GraphQL context
  return {
    page: found.page,
    users: found.records.map(this.convert), 
    rowsPerPage: found.rowsPerPage,
    totalCount: found.totalCount
  };
}

And the roles resolver method:

@FieldResolver(returns => [RoleDTO])
async roles(@Root() root: UserDTO, @Ctx() ctx: Context): Promise<RoleDTO[]> {
  const context = ctx["dataloader-context"];
  const user: User = await User.findByPk(root.id, {
    attributes: {
      exclude: ["password", "password_confirmation", "password_digest"]
    },
    [EXPECTED_OPTIONS_KEY]: context // pass in the dataloader context
  });

  if (user) {
    const roles = (await user.$get<Role>("roles", { // nasty TypeScript workaround
      [EXPECTED_OPTIONS_KEY]: context // pass in the dataloader context
    })) as Role[];
    return roles.map<RoleDTO>(r => {
      return {
        id: r.id,
        name: r.name,
        description: r.name
      };
    });
  }
  throw new NotFoundError("User with given id was not found.");
}

Dataloader has another magic feature, it is batching similar database queries until the very last moment. Instead of loading the roles in N round trips for each user, only a single query is executed:

Executing (default): SELECT Role.id, Role.shortname AS name, UserRole.roleId AS UserRole.roleId, UserRole.userId AS UserRole.userId FROM m_role AS Role INNER JOIN m_role_assignments AS UserRole ON Role.id = UserRole.roleId AND UserRole.userId in (15, 76, 6, 9);

In total, only two queries are executed – one to get the users and one to get roles of all users from the previous query result set.

It works now, but the road to this point was quite bumpy. I have discussed the issues I have run into on GitHub with authors of the libraries I have used (for example here ). I discussed the problems that only existed on my machine (and were results of my misunderstanding of how the library works), tried and failed miserably to fix some issues ( here ).

Always create bidirectional relations in your entities; the dataloader will like you more.

我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章