GraphQL Queries by Postgres Functions

I've recently been working as a full-stack developer on an MVP product that is similar to Instagram, but it used by architectures in the building industry. The user can post his/her sample of works with some particular information, and the other users can like or comment on them.

We choose TypeGraphQL as our GraphQL solution to implement the type and schema, besides that typeorm help us as a code-first ORM solution.

I don't want to describe how these libraries work together, there are tons of tutorials and samples of code out there. You could watch TypeGraphQL series by Ben Awad.

For the mutation stuff, TypeGraphQL and TypeORM are perfect matches since you could define your Input with custom validation easily. We are so excited to choose this stack for the project. On the other hand, there are some complex GraphQL queries that we can't fetch by TypeORM query solutions!

In GraphQL API design, I'm against to design a GraphQL API which consumers can reach to any deep relational objects. This approach comes with complexity for the backend side, and only GraphQL engines are good at this because they assume the table as a type!

You could try Hasura in action within hours 😅.

Let's move to our problem; We want to represent portfolios like this in our PWA.

We need this information as well:

• Is bookmarked by the current user?
• Is liked by the current user?
• The author's data such as name, image, and username.
• The portfolio's data.
• How many likes does it have?

So we define SampleOfWorkView model as well. The SampleOfWorkAdditionalData and UserBriefProfile has been written as well.

import { ObjectType, Field } from 'type-graphql'
import { Position, Point } from 'geojson'

import { PositionScalar } from '../scalars/positionScalar'
import { SampleOfWorkAdditionalData } from './sampleOfWorkAdditionalData'
import { UserBriefProfile } from './userBriefProfile'

@ObjectType('SampleOfWorkView')
export class SampleOfWorkView {
    @Field()
    id: string

    @Field()
    title: string

    @Field()
    employer: string

    @Field()
    usage: string

    @Field()
    madeAt: Date

    @Field(() => [String])
    images: string[]

    location?: Point | null

    @Field(() => PositionScalar, { name: 'locationPosition', nullable: true })
    getLocationPosition(): Position | undefined {
        if (this.location) {
            return this.location.coordinates
        }
        return undefined
    }

    // just define it for type checking 'locationPosition' field in GraphQL response
    locationPosition: Position | undefined 

    @Field({ nullable: true })
    description?: string

    @Field(() => SampleOfWorkAdditionalData, { defaultValue: {} })
    additionalData: SampleOfWorkAdditionalData

    @Field()
    createdAt: Date

    @Field()
    updatedAt: Date

    @Field(() => UserBriefProfile)
    userBriefProfile: UserBriefProfile

    @Field()
    bookmarked: boolean

    @Field()
    liked: boolean

    @Field()
    numberOfLikes: number
}

We expect to run this GraphQL to get the latest sample of works like this.

{
  sampleOfWorks(limit: 10, offset: 0) {
    id
    title
    employer
    images
    createdAt
    bookmarked
    liked
    numberOfLikes
    userBriefProfile {
      id
      name
      username
      imageObjectPath
    }
  }
}

The limit and offset arguments are optional.

An Overview of Our Database Schema

Problem

For our query, we need data from these four tables, but how can we do that in one query?

TypeORM

Honestly, I'm a noob person in the SQL world, so I've tried to find a solution with TypeORM for some hours. Finally, I disappointed and have no idea how can I write a query in an optimum way.

Postgres Function

In college, I learned about stored procedures SQL many years ago, Search about SP in Postgres and find out SP has been replaced by Functions, In short, we can say the function is the same as SP, but we should declare the return type of function!

Describe What Data We Need to Have for a Single Row

• Sample of work information fields + some of the user fields that create this sample of work, 👉 So we need an INNER JOIN.
• Is the current user liked this post? 👉 a select query from like_sample_of_work.
• Is the current user bookmarked this post? 👉 a select query from the bookmarked_sample_of_work table.
• How many users liked this post? 👉 A count query on the like_sample_of_work table.

Tuning The Postgres Database

Before jumping to the solution, you need to tune your Postgres database based on your resource; You could read this helpful article and also get help from this website to obtain configuration base on the OS type and the Postgres version.

Your Postgres database is tuning now. We can jump into the solution and start with the easiest one 🤓.

Solution

Is the Current User Bookmarked this Post?

It's just a select query from the bookmak_sample_of_work's table with a two where clauses, user_id, and sample_of_work_id. But we want to be dynamic based on arguments. So turn it into Functions.

CREATE OR REPLACE FUNCTION isBookmarked (swId uuid, currentUserId uuid) RETURNS boolean
  AS $$
BEGIN
  IF (currentUserId IS NULL) THEN
    RETURN FALSE;
  ELSE
    RETURN EXISTS (
      SELECT 1 FROM bookmark_sample_of_work AS b WHERE
      b.sample_of_work_id = swId AND b.user_id = currentUserId);
  END IF;
END;
$$
LANGUAGE plpgsql;

Indexes come to play on large datasets, so for this query should add index on the user_id column.

CREATE INDEX bookmark_sample_of_work_user_id_idx ON
bookmark_sample_of_work(user_id uuid_ops);

NOTE: The isLiked function would be the same as isBookmarked!

Get Number of Users Who Likes this Sample Of Work

It's just count number of rows in the like_sample_of_work table where the sample_of_work_id matched to the swId variable.

CREATE OR REPLACE FUNCTION numberOfLikes (swId uuid)
  RETURNS bigint
  AS $$
BEGIN
  RETURN (
    SELECT count(*) FROM like_sample_of_work
    WHERE like_sample_of_work.sample_of_work_id = swId);
END;
$$
LANGUAGE plpgsql;

Although we create an index on the sample_of_work_id column, the count(*) is not optimum in a large dataset. There are some techniques to have faster counting. You can read more about here.

Getting Latest Sample of Works Function

To retrieve user information, we should join the sample_of_work table with the user table, and to get the latest records, we add ORDER BY statement based on the created_at column.

CREATE OR REPLACE FUNCTION getLatestSampleOfWorkView (
    nLimit bigint DEFAULT 10, 
    nOffset bigint DEFAULT 0, 
    currentUserId uuid DEFAULT NULL
  ) RETURNS TABLE (
    id uuid,
    title character varying,
    employer character varying,
    images text[],
    "createdAt" timestamp WITH time zone,
    "userBriefProfile" json,
    bookmarked boolean,
    liked boolean,
    "numberOfLikes" bigint
  )
  AS $$
BEGIN
  RETURN QUERY
  SELECT
    sw.id,
    sw.title,
    sw.employer,
    string_to_array(sw.images, ',') AS images,
    sw.created_at AS "createdAt",
    json_build_object(
        'id', u.id, 
        'firstName', 
        u.first_name, 
        'lastName', u.last_name, 
        'type', u.type, 
        'imageObjectPath', u.image_object_path, 
        'username', u.username
    ) AS "userBriefProfile",
    isBookmarked (sw.id, currentUserId) AS bookmarked,
    isLiked (sw.id, currentUserId) AS liked,
    numberOfLikes (sw.id) AS "numberOfLikes"
  FROM
    sample_of_work AS sw
    INNER JOIN public.user AS u ON sw.user_id = u.id
  ORDER BY
    sw.created_at DESC
  limit nLimit offset nOffset;
END;
$$
LANGUAGE plpgsql;

• Postgres be treated as case insensitive, to have the camelCase, the column should surround it by double-quotes.
• The function returns a table, so we should define table column one by one according to select columns.
• Using our custom functions like isBookmarked and pass necessary columns or variables.
• We assume userBriefProfile as JSON, Postgres has a builtin function named json_build_object.
• The images store as a TEXT with , separator, to get it as an array we use the string_to_array builtin function.
• The nLimit, nOffset and currentUserId arguments are optional and we asume default values as well.
• we create an index for the created_at to speed up ORDER BY.

Running the Function on GraphQL Resolver Method

We write our GraphQL resolver based on TypeGraphQL and use node-postgres as Postgres client to run queries, so our method resolver has been written like this.

Our GraphQL query is now ready for the clients 🎉.

Summary

SQL functions are an excellent choice to retrieve a custom data model based on your GraphQL queries. To write an SQL function, you should split the queries and assume the builtin SQL functions based on your database.