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:
- Node.js (https://nodejs.org)
- NestJS CLI (you can install it globally using
npm install -g @nestjs/cli
) - 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.jstypeorm
: 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!