Having Issues writting raw query with typeorm using mysql driver

Question

Everyone, i have been trying to write an SQL Query in typeorm to clear certain tables. i want to first disable foreign key checks before clearing the tables. But i keep getting "syntax Error on mysql query". below is the code:

const connection: Connection = getConnection();

const queryRunner: QueryRunner = connection.createQueryRunner();
await queryRunner.connect(); // performs connection
const data = await queryRunner.query(`SET FOREIGN_KEY_CHECKS=0 TRUNCATE TABLE marital_statuses;`);
await queryRunner.release();

return data;

i have also tried adding semicolon, still get the same error

const connection: Connection = getConnection();

const queryRunner: QueryRunner = connection.createQueryRunner();
await queryRunner.connect(); // performs connection
const data = await queryRunner.query(`SET FOREIGN_KEY_CHECKS=0; TRUNCATE TABLE marital_statuses;`);
await queryRunner.release();

return data;

ERROR:

[Nest] 9386  - 07/27/2022, 9:39:21 AM   ERROR [ExceptionsHandler] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUNCATE TABLE marital_statuses' at line 1

please can anyone tell me what i am doing wrong thanks.

Answer 1

I later found the issue with why my queries weren't working, thanks to @webdev_jj.

in my typeorm config file all I did was to enable multipleStatements, like so:

const connectionOptions: ConnectionOptions = {
  type: 'mysql',
  host: config.DB_HOST,
  port: config.DB_PORT,
  username: config.DB_USERNAME,
  password: config.DB_PASSWORD,
  database: config.DB_NAME,
  entities: [join(__dirname, '**', '*.entity.{ts,js}')],
  synchronize: false,
  dropSchema: false,
  migrationsRun: false,
  multipleStatements: true,
  logging: ['warn', 'error'],
  migrations: [join(__dirname, 'src/migrations/*{.ts,.js}')],
  cli: {
    migrationsDir: 'src/migrations',
  },
};