How to Audit Tables in MySQL Using NestJS

In this tutorial, we’ll walk you through the steps to implement auditing for MySQL tables using NestJS, a popular Node.js framework for building scalable and maintainable server-side applications. We’ll assume you have basic knowledge of NestJS and have set up a NestJS project with a working MySQL database connection.

In many applications, it is crucial to keep track of changes made to certain database tables. This process of tracking and recording changes is called auditing. Auditing allows you to monitor and review modifications to data over time, providing transparency and security for your application.

Prerequisites

Before you begin, ensure you have the following installed on your system:

1. Node.js (https://nodejs.org)
2. NestJS CLI (you can install it globally using npm install -g @nestjs/cli)
3. MySQL Server (https://dev.mysql.com/downloads/installer/)

Set Up the NestJS Project

If you haven’t already created a NestJS project, you can do so using the following command:

nest new project-name

Navigate to the newly created project folder:

cd project-name

Install Required Dependencies

We need a couple of dependencies to implement auditing for MySQL tables:

• mysql2: The MySQL client for Node.js
• typeorm: A popular ORM (Object-Relational Mapping) library for TypeScript and JavaScript.

Install these dependencies using npm:

npm install @nestjs/typeorm mysql2 --save

Configure MySQL Connection

In the project’s root directory, locate the src/app.module.ts file and import the necessary modules:

// src/app.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'your-mysql-username',
      password: 'your-mysql-password',
      database: 'your-database-name',
      entities: [],
      synchronize: true, // Make sure to set this to false in production
    }),
  ],
})
export class AppModule {}

Replace 'your-mysql-username', 'your-mysql-password', and 'your-database-name' with your actual MySQL credentials and database name.

Create the Auditable Entity

Now, let’s create an abstract entity that will be the base for all the entities we want to audit. This entity will contain the necessary columns for auditing, such as created_at, updated_at, created_by, and updated_by.

// src/auditable.entity.ts

import { Column, CreateDateColumn, UpdateDateColumn } from 'typeorm';

export abstract class AuditableEntity {
  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;

  @Column({ name: 'created_by', nullable: true })
  createdBy: string;

  @Column({ name: 'updated_by', nullable: true })
  updatedBy: string;
}

Create Your Audited Entities

Now that we have the AuditableEntity, we can create other entities that will be audited automatically. For example, let’s create a simple User entity:

// src/user.entity.ts

import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';
import { AuditableEntity } from './auditable.entity';

@Entity({ name: 'users' })
export class User extends AuditableEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  // Add any other properties specific to your User entity
}

Implement the Auditing Logic

To automatically update the auditing columns (created_at, updated_at, created_by, and updated_by) when a new record is created or an existing one is updated, we can use NestJS’ BeforeInsert and BeforeUpdate decorators. These decorators allow us to define pre-save actions before entities are persisted to the database.

Modify the User entity as follows:

// src/user.entity.ts

import { Entity, PrimaryGeneratedColumn, Column, BeforeInsert, BeforeUpdate } from 'typeorm';
import { AuditableEntity } from './auditable.entity';

@Entity({ name: 'users' })
export class User extends AuditableEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  // Add any other properties specific to your User entity

  @BeforeInsert()
  updateDatesOnInsert() {
    this.createdAt = new Date();
    this.updatedAt = new Date();
  }

  @BeforeUpdate()
  updateDatesOnUpdate() {
    this.updatedAt = new Date();
  }
}

The BeforeInsert decorator sets the created_at and updated_at properties when a new record is created, while the BeforeUpdate decorator updates the updated_at property whenever a record is updated.

With all the changes in place, you can now run your NestJS application using the following command:

npm run start

Congratulations! You have successfully implemented auditing for MySQL tables using NestJS. Now, every time you create or update a record in your audited entities, the created_at, updated_at, created_by, and updated_by columns will be automatically updated. This approach helps you maintain a clear history of changes to your data, providing essential insights into your application’s activity.

Remember that this is a basic implementation, and you can customize it further to meet your specific requirements. Happy coding!