GraphQL Queries by Postgres Functions
Tags: graphql, postgres, node
Published: 2020-05-18 14:35:11

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](https://typegraphql.ml) as our GraphQL solution to implement the type and schema, besides that [typeorm](http://typeorm.io/) 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](https://www.youtube.com/playlist?list=PLN3n1USn4xlma1bBu3Tloe4NyYn9Ko8Gs) series by [Ben Awad](https://dev.to/benawad).

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](https://hasura.io/) in action within hours 😅.

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

![The Sample of Work Card View](https://dev-to-uploads.s3.amazonaws.com/i/u7tqfpz5qv0cxfx822hk.png)

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.

```typescript
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.

```javascript
{
  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

![Database Schema](https://dev-to-uploads.s3.amazonaws.com/i/y5sotj4qsngweiv9x9wh.png)

## 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](https://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-optimize-performance/) and also get help from [this website](https://pgtune.leopard.in.ua/) 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**.

```sql
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.

```sql
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.

```sql
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](https://www.citusdata.com/blog/2016/10/12/count-performance/).

### 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.

```sql
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](https://typegraphql.ml) and use [node-postgres as Postgres client](https://github.com/brianc/node-postgres) to run queries, so our method resolver has been written like this.

![GraphQL Resolve Method](https://dev-to-uploads.s3.amazonaws.com/i/48tc9jtsbvymo1rm2c25.png)

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.