DEV Community: Emtiaj Hasan

I introduced the word guessing game in my vocabulary app

Emtiaj Hasan — Sun, 26 May 2024 08:02:31 +0000

A few years ago, I published a vocabulary-learning assistance app. Imagine you found an unknown word, you just googled it to find its meaning and continued what you were doing. However, what if you wanted to memorize it so that you can use it in future?

That is what my app can help. This app lets you create your own dictionary, make a vocabulary to a flashcard, and go through the process of the Leitner System aka spaced repetition, so that you can learn it effectively. Not only this, but you can also create a group and build and learn collaboratively.

Check it out on Android App or access the Web App. It is also a Progressive Web App (PWA) i.e., it can be installed on iOS giving you a native experience.

Exciting New Feature: Word Guessing Game!

After a few weeks of hard work, I'm thrilled to introduce a new version of the app featuring a word guessing game! In this exciting update, users are presented with a series of meanings and must guess the correct word for each one. Every day, a new game is generated to challenge vocabulary skills. With every correct guess, the score increases. Plus, if all the words are guessed correctly, an additional game can be unlocked to play on the same day.

For the tech-savvy, here are the back-end and front-end pull requests.

The Journey

The biggest challenge was to generate meanings randomly from the user-created vocabulary list. Fortunately, Postgres has a feature to select rows randomly. And guess what? I used it!

select meaning
from "Definition"
        tablesample bernoulli (10);

I had to apply proper filtering to get meanings belonging to the user cohort. But, as using the Bernoulli sampling, Postgres generates data randomly, and I don’t want to show the last fifteen days’ meanings to the user, I needed to do a tweak.

The idea is pretty simple. Generate using the Bernoulli sampling but the final result should skip previously selected meanings. It can be easily achieved to store the current selection to a cache service, and the query needs a bit of adjustment to filter out those.

Redis Alternative

I do not have the luxury of using Redis as you know, I wanted to avoid extra expenditure. And, Postgres rescued again!

Postgres has a concept of UNLOGGED table, in which data is not written to WAL, long story short, kinda Redis-like feature. Therefore, I created an UNLOGGED table to store a user’s last fifteen days guessing game-related data.

The final query looks like this.

Remove Old Data

If you notice the query to fetch data from the GuessingGame table to feed into another query mentioned above, you will see there is no time-related filtering. It looks like if a meaning is shown to a user, it will never be displayed to that user. But, my requirement was to avoid showing only the last fifteen days.

But, it works fine. Because, I created a cron job, which runs every hour to delete rows older than 15 days. In this way, I ensure to keep only fifteen days of older data in the GuessingGame table.

A Bit of an Extra Challenge

So far, a user gets meanings from his/her dictionary. To make the game more challenging, I fetch meaning from an external source. WordsApi has a cool collection of vocabulary and they have an API to fetch words randomly.

Now, every day, a user can get meanings from his own dictionary as well as from a different source, which makes it more enjoyable and challenging.

Front-end Implementation

Well, I wrote some codes to fetch data and feed those data to the SwiperJS so that the user can navigate to each card smoothly.

For each correct guess, a satisfactory sound gets played and a simple animation increases the count.

Despite my self-confessed lack of UI design skills, I'm proud of how these features turned out!

Here are codes to apply animation!

@keyframes bounce {
   0%,
   20%,
   50%,
   80%,
   100% {
       transform: translateY(0);
   }
   40% {
       transform: translateY(-30px);
   }
   60% {
       transform: translateY(-15px);
   }
}


.animate-count {
   display: inline-block;
   animation: bounce 1s ease-out;
}

animateCount(index: number): void {
   const element = document.getElementsByClassName('correct-count')[index];
   element.classList.add('animate-count');
   element.addEventListener(
       'animationend',
       () => {
           element.classList.remove('animate-count');
       },
       { once: true },
   );
},

I can keep going to share every detail, but let’s finish it. I hope you will enjoy the new features! Don’t forget to leave a review in the Play Store! Happy learning!

Exploring Advanced SQL Techniques: Aggregation Methods for Structured Data Retrieval

Emtiaj Hasan — Tue, 30 Apr 2024 12:57:23 +0000

Assume we have two tables, Vocabulary and Definition to store any unknown vocabulary. It is common for a vocabulary to have multiple definitions. In SQL terms, this means that there is a one-to-many relationship between the Vocabulary and Definition tables.

Let’s assume we need to retrieve vocabularies along with all of the definitions. The payload structure should be like the one below.

{
   id: string;
   word: string;
   definitions: {
       id: string;
       meaning: string;
       examples:  string[];
   }[];
}[]

We can quickly create tables after spinning up a Postgres container.

As both tables have been created we can insert rows into these two tables. A simple query can quickly insert 5 vocabularies.

If we see the above-mentioned snippet, except one (which is Cakewalk) all have at least one definition and the vocabulary Summon has two definitions. Let’s keep in mind.

Approach 1: External Processing

We can execute one of the simplest queries ever to retrieve all vocabularies.

select "Vocabulary".id as "vocabularyId",
      "Vocabulary".word,
      "Definition".id as "definitionId",
      "Definition".meaning,
      "Definition".examples
from "Vocabulary"
        left join "Definition" on "Vocabulary".id = "Definition"."vocabularyId"
order by "Vocabulary".word;

The query responses can be found here.

If we notice, for each definition, there is a single entry. Therefore, for Summon, we have two rows. But our response payload indicates, we need to aggregate all definitions into an array. That means we need to somehow process the response.

Also, as the Cakewalk does not have a definition, it returns null for definitionId, meaning, and examples. In this scenario, we need to return an empty array for simplicity.

Process retrieved response

We can write a neat JavaScript function to tidy up the SQL response to group all the meanings under each word, just like how we want it.

function process(queryResponses) {
   const vocabulariesMap = new Map();


   queryResponses.forEach(currentQueryResponse => {
       const existingVocabulary = vocabulariesMap.get(currentQueryResponse.vocabularyId);
       if (!existingVocabulary) {
           vocabulariesMap.set(currentQueryResponse.vocabularyId, {
               id: currentQueryResponse.vocabularyId,
               word: currentQueryResponse.word,
               definitions: currentQueryResponse.definitionId ? [{
                   id: currentQueryResponse.definitionId,
                   meaning: currentQueryResponse.meaning,
                   examples: currentQueryResponse.examples
               }] : []
           });
       } else {
           existingVocabulary.definitions.push({
               id: currentQueryResponse.definitionId,
               meaning: currentQueryResponse.meaning,
               examples: currentQueryResponse.examples
           });
       }
   });


   return [...vocabulariesMap.values()];
}


const vocabularies = process(queryResponse);

The method will return us the expected response and here it is.

[
 {
   "id": "d2daf563-ee43-439e-a8c0-6cfa325206dc",
   "word": "Cakewalk",
   "definitions": []
 },
 {
   "id": "7b33f8c7-1648-40e3-80df-3faec587f773",
   "word": "devil's advocate",
   "definitions": [
     {
       "id": "1a284263-c329-4876-bb66-14bb3b9e9a76",
       "meaning": "a person who expresses a contentious opinion in order to provoke debate or test the strength of the opposing arguments",
       "examples": [
         "The schoolmaster often played devil's advocate with his students so that they could have an interesting discussion and look at other points of views."
       ]
     }
   ]
 },
 {
   "id": "ac7a758d-bebe-4f6c-8642-11b30260e9df",
   "word": "Guerrilla marketing",
   "definitions": [
     {
       "id": "2bb85746-8915-4495-bff7-b63037a4ea09",
       "meaning": "creative, low cost, unconventional strategy to promote the products or services",
       "examples": [
         "A startup should use guerrilla marketing to promote their services."
       ]
     }
   ]
 },
 {
   "id": "ed6df6e5-2580-4c61-a05f-f390851974b3",
   "word": "Run round like a headless chicken",
   "definitions": [
     {
       "id": "c89966db-ca3a-4ea2-b638-fde91834f00a",
       "meaning": "to be very busy doing a lot of things, but in a way that is not very effective",
       "examples": [
         "Instead of running round like a headless chicken use your efforts in a more productive way."
       ]
     }
   ]
 },
 {
   "id": "bd5b5c91-5776-423c-8641-522110e98b29",
   "word": "Summon",
   "definitions": [
     {
       "id": "ce25ff39-c37f-426e-ba50-2311492184a1",
       "meaning": "order to be present",
       "examples": [
         "We were summoned to the headmaster's office."
       ]
     },
     {
       "id": "873cf807-3fe7-4322-8e61-465c87d66a40",
       "meaning": "to make an effort to produce a particular quality in yourself, especially when you find it difficult",
       "examples": [
         "It took me six months to summon (up) the courage to ask her out for a drink."
       ]
     }
   ]
 }
]

Approach 2: SQL with Postgres Functions

But, isn’t there any way to retrieve data as our desired structure using just the query?

I was looking into it and found two awesome Postgres functions json_build_object and json_agg.

Let’s apply those two!

select "Vocabulary".id,
      "Vocabulary".word,
      json_agg(json_build_object(
              'id', "Definition".id,
              'meaning', "Definition".meaning,
              'examples', "Definition".examples)) as definitions
from "Vocabulary"
        left join "Definition" on "Vocabulary".id = "Definition"."vocabularyId"
group by "Vocabulary".id, "Vocabulary".word
order by "Vocabulary".word;

Except for one minor issue, it returns a response as exactly as our structure.

If we notice the definitions for the Cakewalk, we will see the issue. It is like the below having a bunch of null.

{
 "id": "d2daf563-ee43-439e-a8c0-6cfa325206dc",
 "word": "Cakewalk",
 "definitions": [
   {
     "id": null,
     "meaning": null,
     "examples": null
   }
 ]
}

Filter null

Postgres has filter clause to filter out the null, which can rescue us.

If we change the query a bit it will not return null for each definition column.

json_agg(json_build_object(
      'id', "Definition".id,
      'meaning', "Definition".meaning,
      'examples', "Definition".examples)) filter (where "Definition".id is not null) as definitions

However, it looks like we still need to rely on the JS method to set an empty array as the query response for the Cakewalk is like the one below.

{
 "id": "d2daf563-ee43-439e-a8c0-6cfa325206dc",
 "word": "Cakewalk",
 "definitions": null
}

Being a bit disappointed, I started looking into Postgres-specific ways and found another beautiful function! coalesce accepts any arguments as well as a default one and if it does not find any non-null values, it returns the default value. For our scenario, we can pass the default value as an empty array.

So, here is the modified select clause.

coalesce(json_agg(json_build_object(
      'id', "Definition".id,
      'meaning', "Definition".meaning,
      'examples', "Definition".examples)) filter (where "Definition".id is not null), '[]') as definitions

And voila! We got our structured data straight from the database query, all spick and span!

So, which approach do you prefer? Classic SQL with a sprinkle of external methods or Super SQL straight from Postgres? Let me know, and we can dive deeper!

Cover image credit: postgresweekly

Track every PostgreSQL data change using Debezium

Emtiaj Hasan — Sun, 27 Aug 2023 03:16:32 +0000

Imagine we have a Customer Relationship Management (CRM), sales, and inventory systems. As every system's responsibility is specific, these systems only store domain-specific data in their database.

Now, assume we need to generate some reports. Data is scattered in multiple databases; therefore, we must somehow gather data in one place.

One idea can be to make API requests to each system and combine them later. It is definitely a bad idea to communicate systems synchronously. The good news is we can use an asynchronous Event-Driven approach.

Our goal is to keep all service data in the reporting service so that it does not need to ask for related service data. To achieve this, we can publish events when data, e.g., CRM service's customer-related info, is created/updated/deleted, and the reporting service consumes the event and syncs it.

Although it seems a good idea, it might be a headache when a service has a lot of tables and data gets changed using a lot of internal APIs. For simplicity, imagine our inventory service has Product, Location, Supplier, and Stock tables. We have CREATE, UPDATE and DELETE APIs for each table. Therefore, we need to publish the data change event from each API handler. On top of that, what if we have some scripts that we execute manually to change data? We also need to notify the reporting service about changes.

Change data capture

In databases, change data capture is a set of software design patterns used to determine and track the data that has changed so that action can be taken using the changed data. - Wikipedia

Light-bulb moment, right? We are basically trying to build a system to capture every data change so that the reporting service always has the updated data.

Then I found a beautiful tool Debezium.

Debezium

Just like the Watchers in the Marvel Comics universe, who observe and record events throughout the universe, Debezium watches the database, keeps track of changes, and makes it easy to send those changes to other systems in real time.

Whenever a new row is added, a row is updated, or a row is deleted, Debezium notices it immediately. It then packages up these changes and sends them as a continuous stream of events by leveraging the power of Apache Kafka.

PoC implementation

In our scenario, we need to integrate the Debezium in each service so that events get published continually for each row-level change. If there is a consumer in the reporting service, it can easily get the data and store it in its database.

But for a PoC project, we will assume there is only one service, and that service will consume the change. Although it is funny, we are going to build it. As my favourite database is PostgreSQL, I will use it.

Project setup

Before diving into depth, let’s give a tiny piece of info that all codes are available on GitHub, which is a NestJS-based service.

Debezium has a nice tutorial, and I will basically follow it. But I won’t use ZooKeeper as KRaft is production-ready, and Kafka 4.0 will remove ZooKeeper entirely in 2024. Moreover, I will use a docker-compose file to spin up the required containers in the simplest way ever.

version: '3.7'


networks:
   cdc-using-debezium-network:
       name: cdc-using-debezium-network
       driver: bridge
       external: false


services:
   cdc-using-debezium-postgres:
       image: debezium/postgres:11
       container_name: cdc-using-debezium-postgres
       hostname: cdc-using-debezium-postgres
       restart: always
       ports:
           - '5443:5432'
       environment:
           POSTGRES_PASSWORD: 123
           POSTGRES_USER: postgres
           POSTGRES_DB: cdc-using-debezium
       volumes:
           - 'cdc-using-debezium-postgres-data:/var/lib/postgresql/data'
       networks:
           - cdc-using-debezium-network


   cdc-using-debezium-kafka:
       image: bitnami/kafka:3.4
       container_name: cdc-using-debezium-kafka
       hostname: cdc-using-debezium-kafka
       restart: always
       ports:
           - '9092:9092'
       environment:
           KAFKA_CFG_NODE_ID: 1
           KAFKA_KRAFT_CLUSTER_ID: q0k00yjQRaqWmAAAZv955w # base64 UUID
           KAFKA_CFG_PROCESS_ROLES: controller,broker
           KAFKA_CFG_LISTENERS: INTERNAL://cdc-using-debezium-kafka:29092,CONTROLLER://cdc-using-debezium-kafka:29093,EXTERNAL://0.0.0.0:9092
           KAFKA_CFG_ADVERTISED_LISTENERS: INTERNAL://cdc-using-debezium-kafka:29092,EXTERNAL://localhost:9092
           KAFKA_CFG_LISTENER_SECURITY_PROTOCOL_MAP: CONTROLLER:PLAINTEXT,INTERNAL:PLAINTEXT,EXTERNAL:PLAINTEXT
           KAFKA_CFG_CONTROLLER_QUORUM_VOTERS: 1@cdc-using-debezium-kafka:29093
           KAFKA_CFG_INTER_BROKER_LISTENER_NAME: INTERNAL
           KAFKA_CFG_CONTROLLER_LISTENER_NAMES: CONTROLLER
       networks:
           - cdc-using-debezium-network


   cdc-using-debezium-connect:
       image: debezium/connect:2.3
       container_name: cdc-using-debezium-connect
       hostname: cdc-using-debezium-connect
       restart: always
       ports:
           - '8083:8083'
       environment:
           BOOTSTRAP_SERVERS: cdc-using-debezium-kafka:29092
           GROUP_ID: 1
           CONFIG_STORAGE_TOPIC: my_connect_configs
           OFFSET_STORAGE_TOPIC: my_connect_offsets
           STATUS_STORAGE_TOPIC: my_connect_statuses
           ENABLE_DEBEZIUM_SCRIPTING: 'true'
       links:
           - cdc-using-debezium-kafka
           - cdc-using-debezium-postgres
       networks:
           - cdc-using-debezium-network


volumes:
   cdc-using-debezium-postgres-data:
       name: cdc-using-debezium-postgres-data
       driver: local

The above docker-compose.yml file defines and configures a set of Docker services that work together to set up a development environment. It's being set up with PostgreSQL as the source database, Kafka as the message broker, and Debezium Connect as the connector. It also defines a custom network that will be used to communicate between services.

All the required services are up and running. We just get confirmed by executing the docker-compose ps command. However, we need to verify a few specifications.

| Name                        | State | Ports                                                        |
| --------------------------- | ----- | ------------------------------------------------------------ |
| cdc-using-debezium-postgres | Up    | 0.0.0.0:5443->5432/tcp,:::5443->5432/tcp                     |
| cdc-using-debezium-kafka    | Up    | 0.0.0.0:9092->9092/tcp,:::9092->9092/tcp                     |
| cdc-using-debezium-connect  | Up    | 0.0.0.0:8083->8083/tcp,:::8083->8083/tcp, 8778/tcp, 9092/tcp |

Verify PostgreSQL

By running the below command, let’s check the Write-Ahead Logging (WAL) value.

docker exec cdc-using-debezium-postgres psql --username=postgres --dbname=cdc-using-debezium --command='SHOW wal_level'

The value should be logical. Otherwise, the Debezium won’t manage to capture the data change (e.g., when the value is replica).

One more configuration needs to be checked.

docker exec cdc-using-debezium-postgres psql --username=postgres --dbname=cdc-using-debezium --command='SHOW shared_preload_libraries'

The above command should return decoderbufs,wal2json.

Don’t worry; our Docker setup already did it for us. By the way, to get detailed information about those specific configs, we can check the official documentation from here, and here. The mentioned articles also explain what happens behind capturing data changes. For us, it is just magic, but it’s the beauty of engineering. Worth reading!

Database setup

Our most simple DB will have only one table to keep users’ data, and the User table will have id, email, and name columns.

The query to create the table can be found in my Github repository.

Verify Kafka

By making an API request, we ensure the status of the Kafka.

curl localhost:8083 | jq '.'

I used jq just to prettify the JSON. Anyway, it will give the following response.

{
   "version": "3.4.0",
   "commit": "2e1947d240607d53",
   "kafka_cluster_id": "q0k00yjQRaqWmAAAZv955w"
}

Notice the cluster ID? It is the exact same value we defined in the docker-compose.yml file.

Deploying PostgreSQL connector

We are ready to deploy the Debezium PostgreSQL connector to start monitoring the cdc-using-debezium database.

The below curl command essentially sends a request to the Debezium Connect to create a connector that captures changes from a PostgreSQL database and publishes them to Kafka topics.

# IP = $(hostname -I | cut -d ' ' -f 1)
curl --location 'http://localhost:8083/connectors' \
   --header 'Accept: application/json' \
   --header 'Content-Type: application/json' \
   --data '{
   "name": "cdc-using-debezium-connector",
   "config": {
       "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
       "database.hostname": "192.168.1.110",
       "database.port": "5443",
       "database.user": "postgres",
       "database.password": "123",
       "database.dbname": "cdc-using-debezium",
       "database.server.id": "184054",
       "table.include.list": "public.User",
       "topic.prefix": "cdc-using-debezium-topic"
   }
}'

In the above, 192.168.1.110 is my machine’s IP address, so please replace this with your IP address. Anyway, the Debezium tutorial explains the above configuration that we send as the API payload; that’s why I am not going to repeat it here.

Verify Kafka

server=localhost:9092

docker exec cdc-using-debezium-kafka /opt/bitnami/kafka/bin/kafka-metadata-quorum.sh --bootstrap-server $server describe --status

docker exec cdc-using-debezium-kafka /opt/bitnami/kafka/bin/kafka-topics.sh --bootstrap-server $server --list

The first command is to get the status of the Kafka metadata quorum. The latter retrieves and displays a list of all the Kafka topics within the specified Kafka cluster, allowing us to see the names of all the topics in the Kafka cluster.

By running the Kafka Topics command, we should see the topic cdc-using-debezium-topic.public.User. The topic name is the combination of values of topic.prefix and table.include.list that we send as the API payload while creating the connector.

Monitoring database changes

Now, we can capture every row-level change of the User table. Let’s execute some queries.

INSERT INTO "User" (email, name)
VALUES ('ehasan+1@firecrackervocabulary.com', CONCAT('name_', (random() * 1000)::INTEGER::VARCHAR))
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING *;


INSERT INTO "User" (email, name)
VALUES ('ehasan+2@firecrackervocabulary.com', 'name_2');


UPDATE "User"
SET name = 'name_20'
WHERE email = 'ehasan+2@firecrackervocabulary.com';


DELETE
FROM "User"
WHERE email = 'ehasan+2@firecrackervocabulary.com';

Let's execute the Kafka-provided command to see all published events.

docker exec cdc-using-debezium-kafka /opt/bitnami/kafka/bin/kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic cdc-using-debezium-topic.public.User --from-beginning | jq '.'

Let's paste the generated output here, but as the produced output contains a lot of info, I am going to put curated data only. Don’t worry, a sample full JSON can be found here.

[
   {
       "payload": {
           "before": null,
           "after": {
               "id": "ad9c4382-db22-4635-a647-a13de4c5bdce",
               "email": "ehasan+1@firecrackervocabulary.com",
               "name": "name_882"
           },
           "source": {
               "db": "cdc-using-debezium",
               "table": "User"
           },
           "op": "c",
           "ts_ms": 1693046762871
       }
   },
   {
       "payload": {
           "before": null,
           "after": {
               "id": "4f259f0d-434d-4c73-90de-ee20f5635a3f",
               "email": "ehasan+2@firecrackervocabulary.com",
               "name": "name_2"
           },
           "op": "c",
           "ts_ms": 1693046762873
       }
   },
   {
       "payload": {
           "before": {
               "id": "4f259f0d-434d-4c73-90de-ee20f5635a3f",
               "email": "ehasan+2@firecrackervocabulary.com",
               "name": "name_2"
           },
           "after": {
               "id": "4f259f0d-434d-4c73-90de-ee20f5635a3f",
               "email": "ehasan+2@firecrackervocabulary.com",
               "name": "name_20"
           },
           "op": "u",
           "ts_ms": 1693046762874
       }
   },
   {
       "payload": {
           "before": {
               "id": "4f259f0d-434d-4c73-90de-ee20f5635a3f",
               "email": "ehasan+2@firecrackervocabulary.com",
               "name": "name_20"
           },
           "after": null,
           "op": "d",
           "ts_ms": 1693046762874
       }
   }
]

The JSON above represents a sequence of database changes – creations, updates, and deletions – in the "User" table. Cool!

An important note: We might not get the previous data (within the before key) when executing an UPDATE or a DELETE query if PostgreSQL’s REPLICA IDENTITY is DEFAULT. So, we need to change it to FULL by executing the below query.

ALTER TABLE "User"
   REPLICA IDENTITY FULL;

Implementing consumer

As we see, Kafka is publishing events for any changes, so our task is to subscribe to the event in the reporting service.

I will use the KafkaJS library to achieve it quickly. Let’s just paste the code here.

import { Injectable, OnApplicationBootstrap, OnApplicationShutdown } from '@nestjs/common';
import { Consumer, Kafka } from 'kafkajs';

enum OperationType {
    c = 'CREATE',
    u = 'UPDATE',
    d = 'DELETE',
}

@Injectable()
export class KafkaService implements OnApplicationShutdown, OnApplicationBootstrap {
    private readonly kafka: Kafka;

    private consumer: Consumer;

    constructor() {
        this.kafka = new Kafka({
            brokers: [`localhost:9092`],
        });
    }

    async onApplicationBootstrap(): Promise<void> {
        await this.listen();
    }

    async onApplicationShutdown(): Promise<void> {
        await this.consumer.disconnect();
    }

    private async listen(): Promise<void> {
        const consumer = this.kafka.consumer({
            groupId: 'console-consumer-24440',
        });
        this.consumer = consumer;

        await consumer.connect();
        await consumer.subscribe({ topics: ['cdc-using-debezium-topic.public.User'], fromBeginning: true });

        await consumer.run({
            eachMessage: async ({ topic, message }) => {
                const parsedMessage = JSON.parse(message.value.toString());
                const eventPayload = {
                    topic,
                    database: parsedMessage.payload.source.db,
                    table: parsedMessage.payload.source.table,
                    operationType: OperationType[parsedMessage.payload.op],
                    data: parsedMessage.payload.after,
                    previousData: parsedMessage.payload.before,
                };
                console.log({ eventPayload });
            },
        });
    }
}

The KafkaService class demonstrates how to consume messages from a Kafka topic and process them. The actual Kafka message consumption logic resides in the listen method.

When a message arrives, the eachMessage callback is triggered. The incoming message’s content is transformed into the eventPayload object. This object contains details about the event, including the topic, database, table, operation type, data after the operation, and previous data before the operation.

Voilà! Now, the reporting service can persist the data into its database, and as we have all the data, we can easily generate various types of reports and perform analytical operations.

Conclusion

Debezium may not be the panacea for all problems, but I believe we may use it to delegate all our responsibilities in scenarios where we need to propagate changed data so that we can just concentrate on our actual business logic.

I hope you enjoyed it. Thank you for reading it.

NB: The cover image is taken from here.

Demystifying ORM Queries: The Search for Optimized PostgreSQL Queries

Emtiaj Hasan — Sun, 13 Aug 2023 09:41:33 +0000

While TypeORM is a great library, I sometimes feel it does not always generate the most efficient queries. To evaluate my thought, I decided to use PostgreSQL's query execution plan to test different SQL queries to see how they perform.

Let's imagine there's this cool app that works like a personal dictionary. Whenever someone comes across a word they don't know, they can save it in the app along with its meaning, examples, notes, etc. This way, they can easily look it up later. Guess what? I'm going to create that app!

Designing the data model to keep the required data for this app is pretty straightforward. We need to create only three tables for this: User, Vocabulary, and Definition.

User

| Column | Type              | Nullable | Default            |
| ------ | ----------------- | -------- | ------------------ |
| id     | uuid              | not null | uuid_generate_v4() |
| email  | character varying | not null |                    |

Vocabulary

| Column | Type              | Nullable | Default            |
| ------ | ----------------- | -------- | ------------------ |
| id     | uuid              | not null | uuid_generate_v4() |
| word   | character varying | not null |                    |
| userId | uuid              | not null |                    |

Definition

| Column       | Type              | Nullable | Default            |
| ------------ | ----------------- | -------- | ------------------ |
| id           | uuid              | not null | uuid_generate_v4() |
| meaning      | character varying | not null |                    |
| vocabularyId | uuid              | not null |                    |

We all know a word might have multiple definitions, and users might have various vocabulary in their dictionary. From the SQL perspective, the relationship between Definition and Vocabulary is many to one. The exact relationship would be for tables Vocabulary and User.

Foreign Key

| Table Name | Column       | Constraint Name                          | Foreign Constraint Definition  |
| ---------- | ------------ | ---------------------------------------- | ------------------------------ |
| Vocabulary | userId       | FK_Vocabulary_userId_User_id             | REFERENCES "User" ("id")       |
| Definition | vocabularyId | FK_Definition_vocabularyId_Vocabulary_id | REFERENCES "Vocabulary" ("id") |

Besides creating or updating vocabulary, there should be a way to display all of it. I need to create an API responsible for fetching data from the database chunk by chunk. As we know, bringing it all at once could perform worse. There should also be a mechanism to retrieve another piece of data until received all of it. It can be achieved if the API returns the total number of vocabulary.

Let’s quickly show the input and output payload of the API request and response, respectively.

interface RequestPayload {
   userId: string;
   pageNumber: number;
   pageSize: number;
}

interface ResponsePayload {
   data: {
       id: string;
       word: string;
       definitions: {
           id: string;
           meaning: string;
       }[];
   }[];
   total: number;
}

The batch size will be 20, and I must ensure responses are sent in descending order of words.

TypeORM Approach (Version 0)

I can quickly achieve it using the TypeORM’s getManyAndCount method.

async fetch(userId: string, skip: number, pageSize: number): Promise<ResponsePayload> {
    // Imagine I calculated the value of the skip using pageSize and pageNumber
    const [vocabularies, total] = await this.createQueryBuilder('vocabulary')
        .leftJoin('vocabulary.definitions', 'definition')
        .where(`vocabulary.userId = :userId`, { userId })
        .orderBy(`vocabulary.word`, 'DESC')
        .skip(skip)
        .take(pageSize)
        .select(['vocabulary.id', 'vocabulary.word'])
        .addSelect(['definition.id', 'definition.meaning'])
        .getManyAndCount();

    return {
        data: vocabularies,
        total,
    };
}

The getManyAndCount method produces three queries before sending the data back. Let’s see those three queries.

Query 1

SELECT DISTINCT "distinctAlias"."vocabulary_id" AS "ids_vocabulary_id", "distinctAlias"."vocabulary_word"
FROM (SELECT "vocabulary"."id"      AS "vocabulary_id",
             "vocabulary"."word"    AS "vocabulary_word",
             "definition"."id"      AS "definition_id",
             "definition"."meaning" AS "definition_meaning"
      FROM "Vocabulary" "vocabulary"
               LEFT JOIN "Definition" "definition" ON "definition"."vocabularyId" = "vocabulary"."id"
      WHERE "vocabulary"."userId" = '41f89c90-7029-46a4-8211-5f8c6e527a2d') "distinctAlias"
ORDER BY "distinctAlias"."vocabulary_word" DESC, "vocabulary_id" ASC
LIMIT 20 OFFSET 4980;

Query 2

SELECT "vocabulary"."id"      AS "vocabulary_id",
       "vocabulary"."word"    AS "vocabulary_word",
       "definition"."id"      AS "definition_id",
       "definition"."meaning" AS "definition_meaning"
FROM "Vocabulary" "vocabulary"
         LEFT JOIN "Definition" "definition" ON "definition"."vocabularyId" = "vocabulary"."id"
WHERE ("vocabulary"."userId" = '41f89c90-7029-46a4-8211-5f8c6e527a2d')
  AND ("vocabulary"."id" IN
       ('41f89c90-7029-46a4-8211-5f8c6e527a2d', 'b19457a9-03fb-4c3b-b649-83001b6b9616',
        '17d84794-88a0-4e62-9f1c-88dea1fe8148', '9381945a-14d5-458b-b283-870ba3fa2057',
        'c00d27cb-7ad1-4fd4-9641-73d3484f3741', '34e8fc07-dec6-4dc8-b461-ed412a5f1cc8',
        'c5a52ddf-328f-4f1f-8c19-e38bc5d74ce1', '70de43d8-dbf2-45a4-86f1-1c4a0dc07512',
        '9150aff2-27b0-499e-82de-7a15373f35b0', '25aa3d73-5e50-410a-b16a-522246b54982',
        '3bf46a90-6982-46ac-9414-a5b3d36e9340', '05c26854-bfe2-48b3-ae2b-c3a76e26a473',
        '40808019-f2c1-4857-8bab-a1820cfb974f', '5cb2e93d-c64c-407c-bfd2-5b765f630298',
        '0fdf440c-37cd-46c3-a305-bd3b8937c377', '3fd8baf6-7e5e-4008-a8ef-81e30fcb6b0b',
        '9501f02f-b77c-4abc-b6ef-fe1a44a575f3', 'cf6c5111-9077-4c9f-9d1b-07eeb44a7160',
        '83c4e918-b14e-4848-ac16-e6e88fa6d774', 'ae156eb4-5a94-431a-8c1f-6b384d062e3b',
        'b44b3033-1b54-4d3f-93a0-c73477dddf68'))
ORDER BY "vocabulary_word" DESC;

Query 3

SELECT COUNT(DISTINCT ("vocabulary"."id")) AS "cnt"
FROM "Vocabulary" "vocabulary"
        LEFT JOIN "Definition" "definition" ON "definition"."vocabularyId" = "vocabulary"."id"
WHERE "vocabulary"."userId" = '41f89c90-7029-46a4-8211-5f8c6e527a2d';

To be honest, I am not impressed, as I think it can be made a bit better, e.g., we can remove the LEFT JOIN and the DISTINCT entirely from the first query because it needs to select only the vocabulary ID using the userId column so that the second query can use it.

As the library generated the query, and I could not modify codes unless forking the repository, I decided to try other approaches.

Query Version 1

SET SESSION my.userId = '41f89c90-7029-46a4-8211-5f8c6e527a2d';


SELECT vocabulary.id,
      vocabulary.word,
      JSON_AGG(JSON_BUILD_OBJECT('id', definition.id, 'meaning', definition.meaning)) AS definitions,
      (COUNT(*) OVER ())::INTEGER                                                     AS total
FROM "Vocabulary" AS vocabulary
        LEFT JOIN "Definition" AS definition ON vocabulary.id = definition."vocabularyId"
WHERE vocabulary."userId" = CURRENT_SETTING('my.userId')::UUID
GROUP BY vocabulary.id, vocabulary.word
ORDER BY vocabulary.word DESC
OFFSET 4980 LIMIT 20;

The above query aggregates the associated definitions of each vocabulary item into a JSON array using JSON_AGG and JSON_BUILD_OBJECT, ensuring I do not need to map it later. It also uses a window function COUNT(*) OVER () to calculate the total count of vocabulary created by the requested user. The fundamental difference between this one and the TypeORM version is that the latter gives us the data using only one query.

Query Version 2

There are trade-offs between single and multiple queries to fetch the required data. I will not debate over it. I decided to try another approach, as we can stop using OFFSET and leverage the ROW_NUMBER to get a subset.

SET SESSION my.userId = '41f89c90-7029-46a4-8211-5f8c6e527a2d';


SELECT vocabulary.id, vocabulary.word, vocabulary.definitions, vocabulary.total
FROM (SELECT vocabulary.id,
            vocabulary.word,
            JSON_AGG(JSON_BUILD_OBJECT('id', definition.id, 'meaning', definition.meaning)) AS definitions,
            (COUNT(*) OVER ())::INTEGER                                                     AS total,
            ROW_NUMBER() OVER (ORDER BY vocabulary.word DESC)                               AS "rowNumber"
     FROM "Vocabulary" AS vocabulary
              LEFT JOIN "Definition" AS definition ON vocabulary.id = definition."vocabularyId"
     WHERE vocabulary."userId" = CURRENT_SETTING('my.userId')::UUID
     GROUP BY vocabulary.id, vocabulary.word) vocabulary
WHERE vocabulary."rowNumber" BETWEEN 4981 AND 5000;

Pretty straightforward, as it just assigned the ranking to each vocabulary and filtered the correct batch in the end.

Query Version 3

I wanted to try LATERAL to avoid GROUP BY. With a slight effort, I wrote the query.

SET SESSION my.userId = '41f89c90-7029-46a4-8211-5f8c6e527a2d';


SELECT vocabulary.id,
      vocabulary.word,
      definitions,
      (COUNT(*) OVER ())::INTEGER AS total
FROM "Vocabulary" AS vocabulary
        LEFT JOIN LATERAL (
   SELECT JSON_AGG(JSON_BUILD_OBJECT('id', definition.id, 'meaning', definition.meaning)) AS definitions
   FROM "Definition" AS definition
   WHERE definition."vocabularyId" = vocabulary.id
   ) AS definitions ON TRUE
WHERE vocabulary."userId" = CURRENT_SETTING('my.userId')::UUID
ORDER BY vocabulary.word DESC
OFFSET 4980 LIMIT 20;

Query Version 4

The whole idea of calculating the total number of available vocabulary is to provide a way for the front-end app to decide whether to make one more API request to the back-end. The app won't ask for more if it has already received all vocabulary.

There is an elegant way to avoid retrieving the total number of vocabulary created by a user.

Keyset pagination, also known as the seek method, relies on the ordering of the columns to paginate through the data. It avoids using OFFSET. Instead of using the page number to determine how much data need to be skipped, we can use the previous chunk’s last word to fetch one more subset of data.

SET SESSION my.userId = '41f89c90-7029-46a4-8211-5f8c6e527a2d';

SELECT vocabulary.id,
       vocabulary.word,
       JSON_AGG(JSON_BUILD_OBJECT('id', definition.id, 'meaning', definition.meaning)) AS definitions
FROM "Vocabulary" AS vocabulary
         LEFT JOIN "Definition" AS definition ON vocabulary.id = definition."vocabularyId"
WHERE vocabulary."userId" = CURRENT_SETTING('my.userId')::UUID
  AND vocabulary.word < 'abcdef0105'
GROUP BY vocabulary.word, vocabulary.id
ORDER BY vocabulary.word DESC
LIMIT 20;

The above solution works as expected when we use an orderable column. Since the id column’s type is UUID, we cannot use it as ids are randomly generated. It is not a good approach if we want to change the sorting field from the word to id.

By the way, an awesome video by Hussein Nasser explains the limitation of using offset.

Performance Measurement

You probably noticed it already. All my queries are for page number 250, fetching 4981st to 5000th vocabulary.

Well, I wrote SQL queries to insert a good amount of data into the database.

The script mentioned above did the following things.

It created 50 users.
It created 5K vocabulary for each user, i.e., 250K rows in the Vocabulary table.
It created two definitions for each vocabulary. That means, in total, the amount of rows in the Definition table is 500K, and each user has 10K definitions.

I injected a good amount of data to have an idea of the performance of my queries so far. SQL EXPLAIN is a beautiful tool to measure the estimated execution cost.

Since there is a direct relation between the index and the performance, I will share the available indexes in this POC database. The majority of indexes are for primary keys, and I have created a composite unique constraint on the Vocabulary table so that inserting the same vocabulary by a user in the table can be prevented easily. Also, one more unique constraint has been added over the email column of the User table.

Index Key

| Table Name | Index Name                | Index Definition                                                                                    |
| ---------- | ------------------------- | --------------------------------------------------------------------------------------------------- |
| User       | PK_User_id                | CREATE UNIQUE INDEX "PK_User_id" ON public."User" USING btree (id)                                  |
| User       | UQ_User_email             | CREATE UNIQUE INDEX "UQ_User_email" ON public."User" USING btree (email)                            |
| Vocabulary | PK_Vocabulary_id          | CREATE UNIQUE INDEX "PK_Vocabulary_id" ON public."Vocabulary" USING btree (id)                      |
| Vocabulary | UQ_Vocabulary_word_userId | CREATE UNIQUE INDEX "UQ_Vocabulary_word_userId" ON public."Vocabulary" USING btree (word, "userId") |
| Definition | PK_Definition_id          | CREATE UNIQUE INDEX "PK_Definition_id" ON public."Definition" USING btree (id)                      |

Executing a query using EXPLAIN ANALYZE gives us a lot of crucial information, but here I want to focus only on the planning and the execution time.

Time Comparison

| Query Version |  Planing Time (Milliseconds)  |    Execution Time (Milliseconds)     |
| :-----------: | :---------------------------: | :----------------------------------: |
|       0       | 0.544 + 0.235 + 0.642 = 1.421 | 116.484 + 58.069 + 108.542 = 283.095 |
|       1       |             0.350             |               133.773                |
|       2       |             0.240             |               129.735                |
|       3       |             0.168             |              107734.892              |
|       4       |             0.190             |                49.012                |

As the TypeORM generated three queries, I applied summation to compare with other queries.

We see versions 1 and 2 are almost identical, the LATERAL-based version is the worst, and the TypeORM one is good enough. The best one is the keyset pagination-based query.

Let’s not make a concrete decision instantly. Execution time depends on many variables, e.g., the data volume, the hardware's nature, caching, concurrency, etc. Let me share a few scenarios.

I executed the same queries multiple times using the same user ID and page number. The time always differs.
Before creating multiple users, I checked performance with a different dataset with only one user, 5K vocabularies, and 10K definitions. In that scenario, TypeORM-generated queries outperformed versions one and two.
Using AWS RDS, Aurora (or other vendors) also affects performance. I haven’t tried this dataset, but once, I did it with another dataset in RDS.

Summary

We always need to make a decision carefully. Most importantly, we should not blindly rely on any ORM library, as those libraries are designed to support multiple databases. So, probably that’s why those are somewhat generic solutions, which produce unexpected, not-so-optimal queries (sometimes).

Do you know that randomly trying to memorise a word is like chasing shadows? Just like crafting queries, memorising words requires strategy. That is why scientists invented techniques to make it easy. The spaced repetition is one of the techniques to memorise anything in the long term. The Leitner system works based on this principle. It suggests creating a flashcard for each item and reviewing those at an interval.

(Cough, cough) I created the Firecracker Vocabulary Flashcards app based on the idea. You are invited to join the learning party!

To use or not to use ORM

Well, we wrote several SQL queries. As we integrate ORM into our project, we should make a way to execute the selected query. The easiest way is like the one below.

await this.query(/** paste the raw query here **/);

Some say using the raw query in an ORM is not a good approach. Whatever the solution is, it should be translated using the query builder or find options. But, not all types of query might be transformed. So, it is entirely up to the developer to decide based on their preference and the requirements and analysis.

Goodbye

Thank you for your patience. I hope you found it insightful and enjoyable!

NB: I have taken the cover image from here.

A journey to simplify debugging: Automate generating human-friendly database constraints using TypeORM

Emtiaj Hasan — Sun, 06 Aug 2023 08:02:44 +0000

One fine morning, I watched the observability service DataDog and noticed the database-related errors below.

insert or update on table "User" violates foreign key constraint "FK_eebe9258d224f7861dd124c1814"
duplicate key value violates unique constraint "UQ_b0f76d1d1fc4ef7f1e3b627574d"
duplicate key value violates unique constraint "PK_e03f3cb1b058a7463f8278a4f97"

Let’s try to analyze errors so that we can take action accordingly.

The first error message hints that we were trying to insert a user with an invalid value to a particular column, referencing another table. But we need to find out which column caused the error.

The Prefix UQ suggests that a row in the table already contains the same value we were trying to insert. Does it show for which column? Unfortunately, no.

The third message is the same as the unique constraint. It happened for the primary key. But it does not give a clue about the name of the primary column.

To get an elaborate idea, I turned to the database administrator tool, checked the entity-relationship diagram, and reviewed all the database constraints to decipher these error messages.

This process left me exasperated and seemed like a waste of time. But then a thought struck me – what if database constraints were more human-readable and provided meaningful information for easier debugging?

I started thinking about applying a convention on the DB keys.

Readable Naming Convention

I use PostgreSQL backed by TypeORM. The keys mentioned here were (auto)generated by the TypeORM. Although TypeORM uses a cryptic and weird naming convention, it provides a way to override the default naming strategy. That means I can use this feature.

I made a convention for myself. Each key should have a prefix, including the table name it belongs to and the column name it is created on. Let’s give an example of each type of DB key.

Primary Key

PK_User_id - Representing the primary key id in the User table.

Foreign Key

FK_User_roleId_Role_id - Signifying a foreign key roleId in the User table referencing the id column in the Role table.

Unique Key

UQ_Flashcard_userId_vocabularyId - The Flashcard table has a composite unique key on columns userId and vocabularyId.

Index Key

IDX_Product_code - Here, the Product table has an index over the column code.

Project setup

Before writing the codes to design a naming strategy, let’s do the basic setup.

I will use NestJS, PostgreSQL, and TypeORM as usual. I aim to design a simple DB with only three tables: Cohort, Role, and User.

Caption: ER diagram of the DB

I simply followed the TypeORM documentation and managed to create entity classes like the ones below.



@Entity('Cohort')
export class Cohort {
   @PrimaryGeneratedColumn('uuid')
   id: string;


   @Column({ type: 'varchar', nullable: false, unique: true })
   name: string;


   @OneToMany(() => User, (user) => user.role, { eager: false, cascade: false })
   users: User[];
}



@Entity('Role')
@Index(['name'])
export class Role {
   @PrimaryGeneratedColumn('uuid')
   id: string;


   @Column({ type: 'varchar', nullable: false })
   name: string;


   @Column({ type: 'jsonb', nullable: false })
   permission: RolePermission;


   @OneToMany(() => User, (user) => user.role, { eager: false, cascade: false })
   users: User[];
}



@Entity('User')
@Index(['reference'], {
   where: `"deletedAt" IS NULL`,
   unique: true,
})
export class User {
   @PrimaryGeneratedColumn('uuid')
   id: string;


   @Column({ type: 'varchar', nullable: false })
   name: string;


   @Column({ type: 'varchar', nullable: false })
   reference: string;


   @DeleteDateColumn({ type: 'timestamp with time zone', nullable: true })
   deletedAt: Date;


   @ManyToOne(() => Cohort, (cohort) => cohort.users, { eager: false, nullable: false })
   @JoinColumn({ name: 'cohortId' })
   cohort: Cohort;


   @ManyToOne(() => Role, (role) => role.users, { eager: false, nullable: false })
   @JoinColumn({ name: 'roleId' })
   role: Role;
}

After that, I ran commands to generate the migration scripts and create tables in the database.

As I didn’t use any custom naming strategy, the generated constraints are cryptic and non-readable, like the ones below.

FK_0b8c60cc29663fa5b9fb108edd7 for the roleId of the User table
IDX_b852abd9e268a63287bc815aab for the index over the Role table’s name

Customizing TypeORM Naming Strategy

But, as I said earlier, we can overcome it. All we need to do is to extend the DefaultNamingStrategy and implement NamingStrategyInterface. BTW, those two links will navigate to the TypeORM’s GitHub repository.

Let’s see the skeleton class first.



import { DefaultNamingStrategy, NamingStrategyInterface } from 'typeorm';


export class DatabaseNamingStrategy extends DefaultNamingStrategy implements NamingStrategyInterface {}

My goal is to override primaryKeyName, foreignKeyName, uniqueConstraintName, indexName methods one by one.

The simplest one is the primary key naming strategy. Each table can have only one primary key. But, we need to remember that a primary key can be composite. So, my pattern here is, PK_<table-name>_<column-1-name><column-2-name><...>. Here the prefix PK, the table name, and the columns are separated by the underscore syntax.



export class DatabaseNamingStrategy extends DefaultNamingStrategy implements NamingStrategyInterface {
   primaryKeyName(tableOrName: Table | string, columnNames: string[]): string {
       return `PK_${this.getTableName(tableOrName)}_${this.joinColumns(columnNames)}`;
   }


   private joinColumns(columnNames: string[]): string {
       return columnNames.join('_');
   }
}

We can design a unique key naming strategy like the primary keys. Let’s do it quickly.



uniqueConstraintName(tableOrName: Table | string, columnNames: string[]): string {
   return `UQ_${this.getTableName(tableOrName)}_${this.joinColumns(columnNames)}`;
}

Foreign keys naming strategy is a bit tricky as it involves multiple tables. To tackle this, I plan to attach each table’s name before the group of columns' names, separated by the underscore. That means it would be like FK_<referencing-table-name>_<referencing-column-name>_<referenced-table-name>_<referenced-column-name>.

Let’s jump into the codes.



foreignKeyName(
   referencingTableOrName: Table | string,
   referencingColumnNames: string[],
   referencedTablePath?: string,
   referencedColumnNames?: string[],
): string {
   const referencingTableName = this.getTableName(referencingTableOrName);


   const referencingReferencedGroup = referencingColumnNames.map((referencingColumn, index) => {
       return `${referencingTableName}_${referencingColumn}_${referencedTablePath}_${referencedColumnNames[index]}`;
   });


   return `FK_${referencingReferencedGroup.join('_')}`;
}

Finally, it is time to design the index key naming strategy. It is almost the same as the primary key naming strategy. And here it is:



indexName(tableOrName: Table | string, columnNames: string[]): string {
   return `IDX_${this.getTableName(tableOrName)}_${this.joinColumns(columnNames)}`;
}

The most challenging work has been accomplished. All we need to do is to pass the naming strategy to the TypeORM’s data source like the one below.



import { DatabaseNamingStrategy } from '@/common/persistence/DatabaseNamingStrategy';

export default new DataSource({
   // other properties
   namingStrategy: new DatabaseNamingStrategy(),
} as DataSourceOptions);

If we drop the DB (it is a proof of concept project, so we’re free to drop it!) and re-run the migration command, we will see our reflected naming strategy for all DB keys.

Congrats, everyone. We made it!

Partial Index

PostgreSQL has a concept of partial index. And I added a partial index over the User table’s reference column. It is, at the same a unique constraint. The idea is a user can be archived, and the reference of the archived user might be allocated to a new user. That is why I created the partial index.



@Index(['reference'], {
   where: `"deletedAt" IS NULL`,
   unique: true,
})

Below SQL query was generated by the TypeORM.



CREATE UNIQUE INDEX "IDX_User_reference" ON "User" ("reference")
   WHERE "deletedAt" IS NULL;

The problem is IDX_User_reference would give us a false impression that the reference is a global unique constraint, but it is not true. The same reference could belong to both an archived user and an active user simultaneously, depending on the value of the deletedAt column.

In my opinion, attaching the WHERE clause with the index name makes it more readable. Therefore, I modified the indexName like the one below to consider the conditions.



indexName(tableOrName: Table | string, columnNames: string[], where?: string): string {
   let indexName = `IDX_${this.getTableName(tableOrName)}_${this.joinColumns(columnNames)}`;


   if (where) {
       const suffix = this.getPartialIndexNameSuffix(tableOrName, columnNames, where);
       indexName = `${indexName}_${suffix}`;
   }


   return indexName;
}

private getPartialIndexNameSuffix(tableOrName: Table | string, columnNames: string[], where: string): string {
   const whereClauseMap: Record<string, string> = {
       '"deletedAt" IS NULL': `deletedAt_IS_NULL`,
   };


   if (whereClauseMap[where]) {
       return `WHERE_${whereClauseMap[where]}`;
   }


   const generatedIndexName = super.indexName(tableOrName, columnNames, where);
   const { 1: hash } = generatedIndexName.split('IDX_');


   return `WHERE_${hash}`;
}

The generated query is like the one below after applying the new strategy, which is our expected one.



CREATE UNIQUE INDEX "IDX_User_reference_WHERE_deletedAt_IS_NULL" ON "User" ("reference")
   WHERE "deletedAt" IS NULL;

Showtime! Look at the attachment below to view the names of all the constraints.

Caption: Database keys

This DB has only one partial index, so my naming strategy method considers only a single condition, and it is "deletedAt" IS NULL. For every other case, it will use the default naming strategy. For example, if there is a table named Product and we want to apply index over the price column only if its price is more than 50, instead of returning a readable name like IDX_Product_price_WHERE_GREATER_THAN_50, it will, unfortunately, return us IDX_Product_price_WHERE_ac51a34467871db246f40ee2be.

The summary here is we need to treat partial index case by case. It is difficult to make a generic solution.

Limitations

The finished DatabaseNamingStrategy class can be found in the GitHub repository.

If you carefully check the codes, you will see I used only the first 63 characters of the generated names. The reason is that PostgreSQL does not allow a constraint with more than 63 characters. The details are in their official documentation.

Therefore, the SQL query below generates the foreign key FK_WebhookConfiguration_integrationConfigurationId_IntegrationC.



ALTER TABLE "WebhookConfiguration"
   ADD CONSTRAINT "FK_WebhookConfiguration_integrationConfigurationId_IntegrationConfiguration_id" FOREIGN KEY ("integrationConfigurationId") REFERENCES "IntegrationConfiguration" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

This is also an exception, like the previously mentioned index name on the price column. Therefore, it needs to be handled in a specific way beyond the generic convention I designed.

Before saying Goodbye

You are welcome to clone the POC project and run it locally. I have added unit tests so that it is easier to get the idea by playing with it.

We should write automated integration tests to ensure whenever there is a change in the DB, it follows the custom naming strategy. Also, we can drop the DB or re-create keys after deleting it for a brand-new project, but there is no way to do it in a live project. So, we need to rename the existing constraints to ensure it follows the given strategy. That is why automated tests for all keys of all tables are essential.

Stay in touch! I will publish two more parts to cover those two scenarios.

Thank you for taking the time to read through this, and I hope you found it insightful and enjoyable!

NB: The cover image is taken from one of my favourite books The Manga Guide to Databases.

Leverage the power of PostgreSQL for validating data

Emtiaj Hasan — Sun, 30 Jul 2023 08:14:52 +0000

Having come from the MongoDB world, I had limited familiarity with SQL before I started using it professionally. As a student, I had some rudimentary ideas, but trust me, that was almost nothing to mention!

When I look back at the CRUD operation-related codes I used to write, my inner self laughs at me. I wish I could travel to the past and rewrite those codes.

I entered into the SQL world with PostgreSQL along with TypeORM. As an experienced MongoDB user, my created methods were similar to the NoSQL functionality.

Let’s dive into an example. Let’s give the full specification of what we need to implement.

We have to create a user. The input payload contains email and role ID.
We need to make sure the user’s email is unique. For an already allocated email, we need to throw the conflict exception.
We need to ensure the requester receives an unprocessable entity exception when they try to create a user with an invalid role ID. For that, we need to assume the role creation functionality is already available in the system.

Pretty simple, ha?

By the way, what is ORM (and TypeORM)? The scope of this post will be increased if I introduce those. I recommend checking out What is an ORM by Ihechikara Vincent Abba and TypeORM’s official documentation.

TypeORM provides a simple approach to quickly create the data model, allowing us to create tables and database constraints in just a few minutes.

Long story short, I created a tiny project using the Nest JS framework, designed the data model following the TypeORM approach and built the Role and User tables. The roleId column in the User table is the foreign key of the Role table. In addition, I made the email unique.

Screenshot: ER diagram of the DB along with primary and foreign keys of tables

Everything is set up. Now it is all about creating the method to accept input payload and persist the user into the DB.

My newbie self used to write code like the one below, which used to assume whatever I needed to achieve, I needed to do it by myself.



async saveUserV1(userSaveRequest: UserSaveRequest): Promise<string> {
    await this.validateRole(userSaveRequest.roleId);
    await this.validateEmail(userSaveRequest.email);

    const insertResult = await this.userRepository.insert({
        email: userSaveRequest.email,
        role: {
            id: userSaveRequest.roleId,
        },
    });

    return insertResult.generatedMaps[0].id;
}

private async validateRole(roleId: string): Promise<void> {
   const isRoleExist = await this.roleRepository.exist({ where: { id: roleId } });
   if (!isRoleExist) {
       throw new UnprocessableEntityException
(`Invalid role ID`);
   }
}


private async validateEmail(email: string): Promise<void> {
   const isUserWithEmailExist = await this.userRepository.exist({ where: { email: email } });
   if (isUserWithEmailExist) {
       throw new ConflictException(`Email is associated with another user`);
   }
}

Let’s review the codes. Here is my observation.

In the SQL world, databases like PostgreSQL handles validations automatically. With the unique constraint UQ_User_email on the email field and the referential integrity enforced by the foreign key FK_User_roleId_Role_id, there's no need for additional queries to validate the data. These built-in features make the manual validation redundant and reduce the hit into the database.

Therefore as a pull request reviewer, my current self will mark the PR as the Request Change and suggest delegating the validation responsibility to the database.

Okay, we need to refactor the codes.

I will leverage the database's built-in error-handling capabilities. My goal is to handle the exception so that the requester will get the proper error message.

Here is my changed code.



async saveUserV2(userSaveRequest: UserSaveRequest): Promise<string> {
    try {
        const insertResult = await this.userRepository.insert({
            email: userSaveRequest.email,
            role: {
                id: userSaveRequest.roleId,
            },
        });
        return insertResult.generatedMaps[0].id;
    } catch (error) {
        this.handleRepositoryException(error);
        throw error;
    }
}

private handleRepositoryException(repositoryException: RepositoryException): void {
   const errorMap: Record<string, Error> = {
       UQ_User_email: new ConflictException(`Email is associated with another user`),
       FK_User_roleId_Role_id: new UnprocessableEntityException
(`Invalid role ID`),
   };
   throw errorMap[repositoryException.constraint];
}

Pretty shiny, isn’t it? Unfortunately, I kept following the first approach of manually validating everything for a long time with SQL.

By the way, all codes are available on a GitHub repository. You are welcome to clone it and run it locally.

In conclusion, this shift in mindset has allowed me to embrace the power of SQL and make the most of its capabilities, reducing execution time and simplifying code. If the database can handle tasks for us, why not rely on it entirely?

Thank you for reading it. I hope you enjoyed it. I would like to share my teeny-tiny TypeORM and PostgreSQL experience with everyone; this is my first attempt.

NB: I have taken the cover image from here.