Unexpected error when doing conditional query

I'm not sure how I feel about nested template expressions. Doesn't it overcomplicate the query? I think CASE WHEN is the correct approach for conditions

To me fragments (including template expressions with ternaries) seem like an elegant way of piecing together queries conditionally.

Also, regardless of that, it has the benefits that it:

• allows for doing this at any part of the query, not just where CASE WHEN is allowed
• uses a language which is more familiar to some developers

If SafeQL absolutely does not want to support them, then maybe there should at least be a recommendation for how to deal with this in the codebase (ideally something better than disabling ESLint).

I agree that support for non-conditional fragments might be beneficial:

const cols = sql("id", "name", "body");
const q = sql`select ${cols} from tbl order by ${cols}`;

I feel like conditions can go out of hand very quickly.
Each condition in the query will include multiple the number of "forms" the query can be.
For instance, a query with three conditions will have eight forms (2^3). Now, if you have a query with ten conditions, you'll have 1024 forms.
This means, the linter will have to check 1024 different forms of the query, which is not feasible:

const q = (a, b, c, d) => sql`
  select from tbl
  where true
    ${a ? sql`and a = ${a}` : sql``}
    ${b ? sql`and b = ${b}` : sql``}
    ${c > 1 ? sql`and d > 0` : sql`and d < 0`}
`;

1. select from tbl where true and a = $1 and b = $2 and d > 0
2. select from tbl where true and a = $1 and b = $2 and d < 0
3. select from tbl where true and a = $1 and d > 0
4. select from tbl where true and a = $1 and d < 0
5. select from tbl where true and b = $1 and d > 0
6. select from tbl where true and b = $1 and d < 0
7. select from tbl where true and d > 0
8. select from tbl where true and d < 0

Let's go to the next level - What if erros in multiple "forms"?

const q = (x) => sql`
  select
    id
  from
    tbl ${x
      ? sql`join tbl2 on tbl.id = tbl2.id`
      : sql`join tbl3 on tbl.id = tbl3.id`
    }
`;

1. select id from tbl join tbl2 on tbl.id = tbl2.id # Error! duplicate column "id" (tbl, tbl2)
2. select id from tbl join tbl3 on tbl.id = tbl3.id # Error! duplicate column "id" (tbl, tbl3)

Personally, I believe that rewriting the query above in the "PostgreSQL way" will make it more readable and maintainable, but that's just my opinion:

const q = (a, b, c, d) = > sql`
  select from tbl
  where true
    and case when ${a} then a = ${a} end
    and case when ${b} then b = ${b} end
    and case when ${c > 1} then d > 0 else d < 0 end
`;

To summarize:

• non-conditional fragments should be supported.
• conditional fragments should be noted as not supported due to the above reasons.
• add a recommendation of how to rewrite the query conditions in a standard way.

If it weren't due to the complexity (performance and product-wise), I would've added support for it (I'm open to suggestions).

Edit - It also seems that pgTyped encourages moving the conditions to the SQL layer.

Hm, but even if there are a lot of queries to run, aren't they all pretty fast? I mean, bailing out after a certain complexity seems ok (kind of like a max recursion depth that TS also has), but maybe up to 100 possible generated queries per query could be supported? (Or even 50 or even 20 would probably be enough for a lot of cases)

They are. Running a query that has 5 conditions will cost 2^5 * 5ms~ = 160ms~ each time you type, this is pretty noticeable. I could probably let the user decide how deep SafeQL should go, but that's not the only issue I wrote.

Ok, understood. 160ms on every keystroke (just for a single query) is indeed a long time, in case a query really takes 5ms to run. If there were 5 or 10 queries each with conditions in a single file, this would be a lot of waiting. I guess TypeScript is also kind of slow sometimes - so maybe the DX would still be somewhat acceptable, but I understand not wanting to go down this path.

The other concern I assume you're referring to was was what to do with the multiple error messages:

What if erros in multiple "forms"?

I guess either just the first error would need to be returned (fail fast) or they would all need to be returned in a combined error message.

I would be interested how you would recommend doing such a query, which I assume is not possible in CASE WHEN:

function updateUser(id: number, { firstName: string, lastName?: string })
  const [user] = sql<User[]>`
    UPDATE
      users
    SET
      first_name = ${firstName}
      ${!lastName ? sql`` : sql`, last_name = ${lastName}`}
      -- and so on, for other properties
    RETURNING *
  `;
  return user;
}

Writing and maintaining multiple duplicate queries? Or maybe I'm not right about this not being possible with CASE WHEN...?

Ok, good to hear that I'm wrong. 👍 Syntax is not so bad.

Maybe for many (most?) use cases, the CASE WHEN syntax would work...? I guess it would be fine, if there's a way of doing this.

And for those cases that are impossible, I guess using multiple tagged template literals with a full separate query would still be possible, even though it means a bit of duplication:

const [user] = someCondition ?
  sql`SELECT ...` :
  sql`SELECT ...`;

I can't think of a case where you won't be able to achieve your goal using just Postgres. But yeah, on general, if you are across a situation where your query gets too complex, it might be a good idea to split it into smaller queries. Who knows, maybe you'll do a favor to your query planner.

@Newbie012 , Thanks for the detailed explanations, I am going to look into having the conditional queries rewritten using the suggested CASE...WHEN
👍

Happy to hear that! I'm closing this issue for now.