A journey to simplify debugging: Automate generating human-friendly database constraints using TypeORM
Tags: database, postgres, typeorm, productivity
Published: 2023-08-06 08:02:44

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](https://typeorm.io/). 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](https://docs.nestjs.com/), PostgreSQL, and TypeORM as usual. I aim to design a simple DB with only three tables: `Cohort`, `Role`, and `User`.

![ER diagram of the DB](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cs6eu02p6mrwsiqs9h13.png)


**Caption**: ER diagram of the DB

I simply followed the TypeORM [documentation](https://docs.nestjs.com/techniques/database) and managed to create entity classes like the ones below. 

```ts
@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[];
}
```

```ts
@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[];
}
```

```ts
@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](https://typeorm.io/migrations) 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](https://github.com/typeorm/typeorm/blob/master/src/naming-strategy/DefaultNamingStrategy.ts) and implement [NamingStrategyInterface](https://github.com/typeorm/typeorm/blob/master/src/naming-strategy/NamingStrategyInterface.ts). BTW, those two links will navigate to the TypeORM’s GitHub repository.

Let’s see the skeleton class first.

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

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

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

```ts
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:

```ts
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](https://typeorm.io/data-source) like the one below.

```ts
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](https://www.postgresql.org/docs/11/indexes-partial.html). 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.

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

Below SQL query was generated by the TypeORM.


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


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

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

![Database keys](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t5xql1696cqt7alv8eu2.png)

**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](https://github.com/emtiajium/typeorm-sql-db-naming-strategy-1/blob/master/src/common/persistence/DatabaseNamingStrategy.ts). 

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](https://www.postgresql.org/docs/current/limits.html).

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

```sql
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](https://github.com/emtiajium/typeorm-sql-db-naming-strategy-1) and run it locally. I have added [unit tests](https://github.com/emtiajium/typeorm-sql-db-naming-strategy-1/blob/master/test/unit/persistence/DatabaseNamingStrategy.ts) 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](https://www.goodreads.com/book/show/4959551).