Quoting added to db.update table name is incompatible with Postgres Schemas

[turbo]

Here's a quirky bug that I don't know how to actually resolve. The workaround is using raw parameterized queries.

In Postgres, to address a table in another schema, it's prefixed with schema.table. However, quoting rules say that schema.table references table in schema, while "schema.table" references public."schema.table".

So when you're trying to use e.g. db.update, the explicit quoting added by that function prevents it from working with schemas. For example:

DB.update "foo.bar", {
  count: .count
}, {
  location_id: .location_id
  product_id: .product_id
  category_id: .category_id
  skill_id: .skill_id
}

will fail with

UPDATE "foo.bar" SET "count" = 42 WHERE "location_id" = 18 AND "product_id" = 50 AND "skill_id" = 21 AND "category_id" = 1 
ERROR: relation "foo.bar" does not exist (8)

Even though bar exists in foo and executing the SQL myself without the quotes works just fine.

[leafo]

In order to use SQL syntax with the table name argument you can use db.raw("foo.bar") to inject it directly into the query:

db.update db.raw("foo.bar"), { ... }, { ... }

Keep in mind that no escaping will be happening, so you should either only use string literals you've typed, or if you are using input you can't fully trust:

db.raw(db.escape_identifier(first_part) .. "." .. db.escape_identifier(second_part))

[turbo]

Interesting. I guess the other DB methods also handle db.raw values differently? If so, it might be worth adding a note to the docs.

[leafo]

I wouldn't say differently, but consistently. Any default escaping can be avoided with raw. This definitely should be mentioned in the docs though!

[leafo]

Just noting now that db.clause supports a table_name option to allow you to automatically prefix a name to a field name. https://leafo.net/lapis/reference/database.html#database-primitives/db.clause