fixing prisma dynamic table name with date

Question

I have a function for querying MySQL with Prisma in Express.js, and currently, I am stuck with one issue. Here is the function:

      console.log('Operation successfully.');
      return roleMenu;
    } catch (error) {
      console.error('Error occurred:', error);
      throw error;
    }
  };

The issue there is that currentDate seems to be interpreted as a parameter, or its format is not accepted by Prisma. The error in the vscode terminal is:

[2024-03-21 15:37:10] error: {"timestamp":"2024-03-21T08:37:10.512Z","message":"\nInvalid `prisma.$executeRaw()` invocation:\n\n\nRaw query failed. Code: `1064`. Message: `You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? LIKE app_menu' at line 1`","target":"$executeRaw"}

When I try to use a static name, it works. For example, app_menu_20240321.

I would appreciate any help with this. Thank you.

Answer 1

$executeRaw doesn't support using template variables (documentation).

However, you do have the option to use $executeRawUnsafe:

const roleMenu = await prisma.$transaction([
  prisma.$executeRawUnsafe`CREATE TABLE IF NOT EXISTS ${tableName} LIKE app_menu;`,
  // other statements
      ]);

See documentation here: https://www.prisma.io/docs/orm/prisma-client/queries/raw-database-access/raw-queries#executerawunsafe

As always, the warning against SQL injection attacks and using raw user input in any SQL statement applies. But if you're forming the table name yourself, you should be good to go.