ExpressJS / MYSQL / Prisma - Save DB' entities changes-CodePudding

I'm looking for a way to save database entities changes for some entities. I mean I need to save in a database table all changes that are done on some tables (add, modify / delete) with ability to track user which did the change.

I'm working on NextJS with a custom ExpressJS server and MYSQL database were I use Prisma as ORM. I think it's maybe possible to write an ExpressJS middleware but I have yet no idea how to do it and asking myself if any library already exist.

Usually I work on PHP Symfony and used to manage this StofDoctrineExtensionsBundle which is great and works as expected. But my current project is a Typescript project only with Express/NextJS/React/Prisma/MYSQL.

Any feedback from your knowledge will be very appreciate.

Thank's in advance.

Regards,

Gulivert

EDIT: My current API which has to be moved to Express/NextJS is still running on Symfony and the table where all changes is logged looks like this :

{
    "id": 59807,
    "user": "ccba6ad2-0ae8-11ec-813f-0242c0a84005",
    "patient": "84c3ef66-548a-11ea-8425-0242ac140002",
    "action": "update",
    "logged_at": "2021-11-02 17:55:09",
    "object_id": "84c3ef66-548a-11ea-8425-0242ac140002",
    "object_class": "App\\Entity\\Patient",
    "version": 5,
    "data": "a:2:{s:10:\"birth_name\";s:2:\"--\";s:10:\"profession\";s:2:\"--\";}",
    "username": "johndoe",
    "object_name": "patient",
    "description": null
  }

Explanation about database columns:

user => relation to user table
patient => relation to patient table
action => can be "create"/"update"/delete"
logged_at => date time where the change was done
object_id => entity row ID where an entity get a change
object_class => the entity updated
version => how many time the object was change
data => all data changed during the modification
username => the username of logged user did the change
object_name => a string to identify the object modified without
using the namespace of object_class
description => a value that can be update on some specific change * during usually the action delete to keep a trace what was deleted for instance

CodePudding user response：

You might find prisma middleware useful for this.

Check out the example with session data middleware which is somewhat similar to what you're doing.

For your use-case the middleware might look like something like this:

const prisma = new PrismaClient()

const contextLanguage = 'en-us' // Session state

prisma.$use(async (params, next) => {
  if (params.model == '_modelWhereChangeIsTracked_' && (params.action == 'create' || params.action == "update")) {
    // business logic to create an entry into the change logging table using session data of the user. 
  }

  return next(params)
})

// this will trigger the middleware 
const create = await prisma._modelWhereChangeIsTracked_.create({
  data: {
    foo: "bar" 
  },
})

However, do note that there are some performance considerations when using Prisma middleware.

You can also create express middleware for the routes where you anticipate changes that need to be logged in the change table. Personally, I would prefer this approach in most cases, especially if the number of API routes where changes need to be logged is known in advance and limited in number.