Examples PreparedStatement Insert

Prepared statements are the preferred way to construct SQL statements with parameters. They are used to reduce the risk of SQL injection attacks. See the Query Example for more information on why prepared statements are necessary.

These examples show how to use prepared statements for inserts and updates.

Inserts

Instead of constructing the SQL like this:

var sql = "INSERT INTO table1 (string_field_1, number_field_2, string_field_3) VALUES ('" + string_1 + "', " + number_2 + ", '" + string_3 + "')";
var statement = connection.createStatement(sql);
statement.execute()
.then(() => {
    // handle success
})
.catch((error) => {
    // handle error
});

A prepared statement allows you to simplify your code, and take advantage of the built-in escaping capabilities of database-js. Change the code to:

var statement = connection.prepareStatement("INSERT INTO table1 (string_field_1, number_field_2, string_field_3) VALUES (?, ?, ?)");
statement.execute(string_1, number_2, string_3)
.then(() => {
    // handle success
})
.catch((error) => {
    // handle error
});

Updates

In the same way, updates are cleaner to read and safer to execute when using prepared statements. This is the unsafe way:

var sql = "UPDATE table1 SET string_field_1 = '" + string_1 + "', number_field_2 = " + number_2 + ", string_field_3 = '" + string_3 + "' WHERE id = " + id;
var statement = connection.createStatement(sql);
statement.execute()
.then(() => {
    // handle success
})
.catch((error) => {
    // handle error
});

Converted to a prepared statement:

var statement = connection.prepareStatement("UPDATE table1 SET string_field_1 = ?, number_field_2 = ?, string_field_3 = ? WHERE id = ?");
statement.execute(string_1, number_2, string_3, id)
.then(() => {
    // handle success
})
.catch((error) => {
    // handle error
});

INSERT INTO OR UPDATE

PostgreSQL and MySQL have mechanisms built in to allow you to insert data or fail over cleanly to an update if a specified primary key already exists. This is specific to the database implementation. These examples will assume a string key / string value table in which the key is the primary key, so if the primary key collides with an existing entry we want to overwrite the existing entry instead of inserting a duplicate.

MySQL

We are going to use the INSERT ON DUPLICATE syntax from MySQL. The straight SQL might look like:

INSERT INTO data (key, value) VALUES ('template_path', '/home/me/templates') 
ON DUPLICATE KEY UPDATE value = VALUES(value)

Notice that the UPDATE portion does not repeat the passed in values. It's perfectly safe to do this:

INSERT INTO data (key, value) VALUES ('template_path', '/home/me/templates') 
ON DUPLICATE KEY UPDATE value = '/home/me/templates'

But this distinction is important as we create a paramaterized statement. We don't have to repeat the values in the parameter list of the .execute method.

The prepared statement implementation of this on database-js is:

var statement = connection.prepareStatement("INSERT INTO data (key, value) VALUES (?, ?) ON DUPLICATE KEY UPDATE value = VALUES(value)");
statement.execute('template_path', '/home/me/templates');

PostgreSQL

For PostgreSQL we use the INSERT ON CONFLICT syntax. The straight SQL would look like:

INSERT INTO data (key, value) VALUES('template_path', '/home/me/templates')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value

Notice that the syntax is very similar to MySQL. In PostgreSQL you must specify the field on which to test for conflict, and the UPDATE syntax is slightly different.

The prepared statement implmentation of this on database-js is:

var statement = connection.prepareStatement("INSERT INTO data (key, value) VALUES (?, ?) ON CONFLICT (key) UPDATE value = EXCLUDED.value");
statement.execute('template_path', '/home/me/templates');