ipgof ipgof - 1 year ago 54
MySQL Question

Postgres equivalent of MySQL's SET?

I am trying to migrate to Postgres from MySQL and have run into a little bit of a problem. I have a form where the user fills out about 40 fields, and these values are inserted into the database. With MySQL I'm used to doing it like this:

INSERT INTO table_name SET name="John Smith", email="jsmith@gmail.com", website="jsmith.org";

I'm also using the
module with nodejs, and this is what my code currently looks like:

var data = {
name: req.body.name,
email: req.body.email,
website: req.body.website,

var query = connection.query('INSERT INTO table_name SET ?', data)

isn't valid SQL, if I wanted to use Postgres, the query would look like this using the

client.query('INSERT INTO table_name (name, email, website) VALUES ($1, $2, $3)', req.body.name, req.body.email, req.body.website)

This would turn out to be very tedious considering that I have almost 40 fields.

Is there any better way to do this or am I stuck with having to write the query manually?

Thank you.

Answer Source

Since you already have an object containing the relevant data

var data = {
  name: req.body.name,
  email: req.body.email,
  website: req.body.website,

you could write a query function that wraps the pg client's query method and adds object-as-values support:

function insert(client, table, values) {
    const keys = Object.keys(values),
          // IMPORTANT: escape column identifiers. If `values` should come
          // from an uncontrolled source, naive concatenation would allow
          // SQL injection.
          columns = keys.map(k => client.escapeIdentifier(k)).join(', '),
          placeholders = keys.map((k, i) => '$' + (i + 1)).join(', ');

    return client.query(`INSERT INTO ${client.escapeIdentifier(table)} 
                         (${columns}) VALUES (${placeholders})`,
                        // According to docs `query` accepts an array of
                        // values, not values as positional arguments.
                        // If this is not true for your version, use the
                        // spread syntax (...args) to apply values.
                        keys.map(k => values[k]));


insert(client, 'table_name', data);

Alternatively you probably could rewrite your queries with sql-template-strings, which seems rather nice.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download