Auto-quote names not working with Postgres

[dankohn]

The quote escaping necessary to make Squel work with Postgres is more painful then just writing pure SQL. I see you discussed auto-quote https://github.com/hiddentao/squel/issues/1#issuecomment-3630815, but the implementation doesn't seem to work. Instead, I needed to manually quote every field and table name to avoid an error. And, I still wasn't able to get a WHERE clause to work at all.

My apologies if I'm missing something obvious, but Squel doesn't seem usable with Postgres at the current time.

var squel = require("squel")
  , config = require(__dirname + "/config/config.js")
  , pg = require('pg')
  , conString = "postgres://" + config.username + ":" + (config.password || "") + "@" + config.host + ":" + config.port + "/" + config.database

console.log (conString)

var squelQuery = squel.select({usingPlaceholderValues: true})
  .from('"Variants"')
  .field('"color"')
  .field('"size"')
  .field('"childSku"')
  .field('"Variants"."updatedAt"')
  .field('"listPriceCents"')
  .field('"salePriceCents"')
  .field('"ProductId"')
  .left_join('"Products"', null, '"Variants"."ProductId" = "Products"."id"')
  .field('"Products"."parentSku"')
  .field('"Products"."title"')
  .field('"Products"."description"')
  .field('"Products"."CategoryId"')
  .left_join('"Categories"', null, '"Products"."CategoryId" = "Categories"."id"')
  .field('"Categories"."name"')
  .field('"Categories"."description"')
  .field('"Products"."BrandId"')
  .left_join('"Brands"', null, '"Products"."BrandId" = "Brands"."id"')
  .field('"Brands"."name"')
  .field('"Brands"."description"')
  .field('"Brands"."PartnerId"')
  .left_join('"Partners"', null, '"Brands"."PartnerId" = "Brands"."PartnerId"')
  .field('"Partners"."name"')
  // .where("`Variants`.`childSku` = 'puma-1234-red-large'")
  .toString();

console.log(squelQuery)

pg.connect(conString, function(err, client) {
  console.log ("connect error:",err)
  client.query(squelQuery, function(err, result) {
    console.log(JSON.stringify(result));
    console.log ("query error:",err);
    pg.end(); //terminate the client pool, disconnecting all clients
    })
  })

[hiddentao]

usingValuePlaceholders really only applies when using parameterized values for certain DB engines. For instance when doing an insert you would construct the query as:

squel.insert({ usingValuePlaceholders: true }).into('table').set('field', '?')

// INSERT INTO table (field) VALUES(?)

You would then use your db engine's preferred querying method to replace the ? parameter with an actual value.

[hiddentao]

As for why squel doesn't automatically quote table names and field names it's as I mentioned in a comment: squel needs to be able to support the following:

squel.select().from("db1.table1").field("DATE_FORMAT(db1.table1.when, '%Y-%M-%D')", "Start date")

But I take your and bminer's point that it would be useful to have an option which turns on auto-quoting when you want it. I'll see if I can do that as part of #1.

[hiddentao]

As for the WHERE clause generation not working in your above code I ran it myself against squel 1.0.5 and got the following output:

SELECT "color", "size", "childSku", "Variants"."updatedAt", "listPriceCents", "salePriceCents", "ProductId", "Products"."parentSku", "Products"."title", "Products"."description", "Products"."CategoryId", "Categories"."name", "Categories"."description", "Products"."BrandId", "Brands"."name", "Brands"."description", "Brands"."PartnerId", "Partners"."name" FROM "Variants" LEFT JOIN "Products" ON ("Variants"."ProductId" = "Products"."id") LEFT JOIN "Categories" ON ("Products"."CategoryId" = "Categories"."id") LEFT JOIN "Brands" ON ("Products"."BrandId" = "Brands"."id") LEFT JOIN "Partners" ON ("Brands"."PartnerId" = "Brands"."PartnerId") WHERE (`Variants`.`childSku` = 'puma-1234-red-large')

[hiddentao]

Version 1.0.6 has two new options:

autoQuoteTableNames autoQuoteFieldNames

Note: you will still need to add your own quotes around names inside where clauses, etc.

[hiddentao]

Am considering this done for now.

[dankohn]

Yep, thanks for resolving!