Knex RAW MySQL query adding new lines-CodePudding

I am new to MySQL and previously used PSQL the same way as I am using MySQL but the below code is erroring.

return await db
        .raw(
            `INSERT INTO users(firstName, 
            lastName, 
            email, 
            password, 
            type,
            addressLine1, 
            addressLine2, 
            addressLine3, 
            mobileNumber,
            townCity, 
            postcode)
            VALUES (
            '${firstName}',
            '${lastName}',
            '${email}',
            '${hashedPassword}', 
            '${mobileNumber}',
            ${type}, 
            '${addressLine1}', 
            '${addressLine2}', 
            '${addressLine3}', 
            '${townCity}', 
            '${postcode}'); 
            SELECT * from users WHERE id = LAST_INSERT_ID()`,
        )
        .then(() => ...)

Returning the below error.

  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "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 'SELECT * from users WHERE id = LAST_INSERT_ID()' at line 24",
  sql: 'INSERT INTO users(firstName, \n'  
    '            lastName, \n'  
    '            email, \n'  
    '            password, \n'  
    '            type,\n'  
    '            addressLine1, \n'  
    '            addressLine2, \n'  
    '            addressLine3, \n'  
    '            mobileNumber,\n'  
    '            townCity, \n'  
    '            postcode)\n'  
    '            VALUES (\n'  
    "            'Jane',\n"  
    "            'Doe',\n"  
    "            '[email protected]',\n"  
    "            'password', \n"  
    "            '07123456789',\n"  
    '            100, \n'  
    "            '1', \n"  
    "            'Test road', \n"  
    "            'Westminster', \n"  
    "            'London', \n"  
    "            'L1 1TG'); \n"  
    '            SELECT * from users WHERE id = LAST_INSERT_ID()'
}

I suspect it is related to the template literals but not sure of the best way to go about resolving this, it worked fine in PSQL (excluding the last line). Is there any better ways of using raw SQL, I am not interested in all the high-level ORM commands.

CodePudding user response：

Looks like you can't do too much in one query. Extracting SELECT * from users WHERE id = LAST_INSERT_ID() into it's own query after the insert worked fine

CodePudding user response：

You need to enable multipleStatements

const mysql = require('mysql')

connection = mysql.createConnection({
    host: 'localhost',
    user: 'youruser',
    password: 'yourpassword',
    database: 'yourdb',
    multipleStatements: true
})
connection.connect()

connection.query('INSERT INTO users(firstName, 
            lastName, 
            email, 
            password, 
            type,
            addressLine1, 
            addressLine2, 
            addressLine3, 
            mobileNumber,
            townCity, 
            postcode)
            VALUES (
            '${firstName}',
            '${lastName}',
            '${email}',
            '${hashedPassword}', 
            '${mobileNumber}',
            ${type}, 
            '${addressLine1}', 
            '${addressLine2}', 
            '${addressLine3}', 
            '${townCity}', 
            '${postcode}'); 
            SELECT * from users WHERE id = LAST_INSERT_ID();', function(err, results) {
    if (err) {
        throw err
    }

    console.log(results[0]])
})

connection.end()

CodePudding user response：

Try to execute first query seperately. If it didnt execute, check youre table structure. Seems like structure missing primary key settings or something on youre migration