Support of SQL template combination typing

[nfroidure]

This project is very interesting (I wish I had more time to work on it). I do not use ORM and this approach could help me to reach more robustness.

But for a real world usage, I think we should be able to type all combinations of complex SQL queries since in the real world, we can't have a single query for each db call. We can have things like that:

const with: 'all' | 'organisation' | 'users' = 'all';
const query1 = sql`SELECT * FROM users WHERE id=${1} AND type=${'admin'}`;
const query2 = sql`SELECT * FROM organisations WHERE id=${1} AND type=${'admin'}`;
const query3 = sql`SELECT x.id, x.name, x.kind FROM (
  ${
    with  == 'users' ?
    query1 :
    with  == 'organisations' ?
    query2 :
    sqlFragment`${query2} UNION ${query3}`
  }
) AS x WHERE name LIKE ${'test'}`;

More examples here: https://github.com/nfroidure/pgsqwell/blob/main/src/lib.test.ts

To be type, the above query need the following process to be accomplished:

• retrieve the AST
• create as many SQL queries than possible combinations
• create the type corresponding to them
• merge those types in a single declaration

It should give something like:

type Rows = ({
  id: Organisation['Id'],
  name: Organisation['Name'],
} | {
  id: User['Id'],
  name: User['Name'],
})[];

Or with (way?) more work:

type Rows<T extends 'all' | 'organisation' | 'users'> =
  T extends 'all'
  ? ({
    id: Organisation['Id'],
    name: Organisation['Name'],
  } | {
    id: User['Id'],
    name: User['Name'],
  })[]
  : T extends 'organisations'
  ? {
    id: Organisation['Id'],
    name: Organisation['Name'],
  }[] 
  : T extends 'users' ? ;
  {
    id: User['Id'],
    name: User['Name'],
  }[] 
  : never

I known the road is long to this point, but I think it would be a real game changer.

What do you think, is it your final goal and if yes, what's the path to this for you ?

[JasonShin]

Hey there, this is an interesting idea.

SQL Fragment

Thanks for the suggestion, and it is a valid input. I prefer to continue moving toward the compile-time approach over the suggested runtime approach. I'd imagine the sqlFragment idea similar to GraphQL's query fragment https://graphql.org/learn/queries/#inline-fragments

As a result, when sqlx-ts runs, it would go ahead, search and register all query fragments found within the codebase. You can then write a SQL such as

// another-file.ts

const someFragment = sqlFragment`WHERE id = ?`

// main-file.ts

const someQuery = sql`
SELECT *
FROM items
...someFragment
` << potentially using ttypescript, it replaces ...someFragment with the actual string found in another-file.ts 

^^^ sqlx-ts will attach `someFragment` to this query >> then runs query validation & type generation

Custom query parameters

Let me think about this and come back. To keep it simple, I have completely avoided introducing any custom query parameters such as

SELECT *
FROM items
WHERE id = :id

1. Initial design heavily leverages database natives as much as possible - so we don't have to worry about SQL injection and so on
2. Such custom query parameters are not guaranteed to work on all DB drivers or ORM (DB driver wrappers such as sequelize)

Conditional logics

I can see that you are combining runtime level conditional logic as part of query construction. I recommend taking a look at query level conditional functions such as

https://www.w3schools.com/sql/func_mysql_if.asp
And CASE

---

I will continue working through enhancements of these points and several other things in 2025, thank you for the inputs