I'm trying to send null to my MySQL database using Node.JS:

con.query("INSERT INTO Routes (routeTrigger) VALUES ( " + null + " )", {title: 'test'}, function(err, result) {
    if (err) throw err;
});

But when looking in the database, the updated value reads 'null'. The database's NULL is supposed to read NULL. If I send capital NULL instead, that doesn't even work (in Node.JS). If I send 'NULL' it sends the string "NULL", and not the database's NULL.

I'm trying to send null to my MySQL database using Node.JS:

con.query("INSERT INTO Routes (routeTrigger) VALUES ( " + null + " )", {title: 'test'}, function(err, result) {
    if (err) throw err;
});

But when looking in the database, the updated value reads 'null'. The database's NULL is supposed to read NULL. If I send capital NULL instead, that doesn't even work (in Node.JS). If I send 'NULL' it sends the string "NULL", and not the database's NULL.

• javascript
• mysql
• node.js
• null

• 6 Please don't use string concatenation to build your queries, ever. It's just a bad habit that will open you to SQL injection hacks when you make production stuff. – Paul Commented Oct 27, 2018 at 20:58

6 Answers 6

JavaScript's null is not the same as SQL's Null. You want (in your case, using raw SQL strings) a string:

con.query("INSERT INTO Routes (routeTrigger) VALUES ( null )", {title: 'test'}, function(err, result) {
    if (err) throw err;
});

But note that that creates a row that's entirely null. Which is possible, but it's unlikely what you want.

What are you actually trying to do?

If you want to assign the NULL value In the SQL tables, you can think differently.

Instead of assigning NULL, you can tweak the field (column) so that the default value (when no value is assigned) is : NULL.

Now, when you insert your new row, just don't assign any value to that field.

Example :

Let's say the table is structured like so :

MyTable

id: auto increment; ...

name: text

url: text; default = NULL

Then you could do:

INSERT INTO Mytable ( name ) VALUES ( "Joe" );

As you omit the url information, it gets set to NULL.

To pass null value in mysql using nodejs, we need a different kind of implementation of a query.

conn.query({
    sql: `INSERT INTO project values(?, ?, ?, ?);`,
    values: [projectId, name, startDate, endDate]}, (error, results) => {
        if (error) {
            console.log("Error executing sql: ", error.message);
            return res.send(error);
        }
    });
});

Official site says: "undefined / null are converted to NULL" and it works.

Use that above syntax to fire a query.

Using the Node mysql package I was able to insert a null value by doing the following:

let myValue = null;

Note the column configuration:

'myValue' varchar(15) DEFAULT NULL

This was used for PostgresQL table migration from JSON using Node.js; may help. Check the values coming from your source, and if it doesn't exist for that column, use "NULL" with double quotes. EG, if it's JSON:

const makeVal = (val) => {
    if (val === undefined) {
        return "NULL"
    } else {
        return "'" + JSON.stringify(val).replace(/'/g, "''") + "'" 
    }
}

See this gist https://gist.github.com/foureyedraven/ca90a7dcbfd0918c153b0c91ec9317c5 for full implementation.

it worked

if (my_var != null) {
    my_var = '"' + my_var + '"';
}                             
else {
    my_var = "NULL";
}
connection.query('insert into my_table(my_var) values(' + my_var + ')', (error, results) => {
    if (error) {
        throw error;
    }
    connection.end();
});