Feature proposal: type safe dynamic filters at runtime

[iswym]

Feature:

Generate limited type safe query builders using types retrieved from Postgres which would allow type safe run time construction of queries.

Documentation mentions: Instead of providing non-typesafe query composition, pgTyped forces you to move the dynamic logic into the SQL layer. - Slightly implying (although it could be my imagination) that it's not possible to provide type safe query composition/generation at runtime to some degree.

Since WHERE and ORDER BY clause don't affect return type of query - conceptually we could: 1) retrieve types of all possible WHERE/ORDER BY clause operands by asking Postgres about them (after some query parsing) 2) use types acquired in 1. for query builder generation to allow user to pass in arbitrary WHERE/ORDER BY clause during runtime in type safe way

With 1. being the hard part.

Simple example: 1)

select user.id, user.email, user_profile.send_subscriptions as "sendSubscriptions" from user
left join user_profile on user_profile.id = user.profile_id
where user.createdAt < :createdAt

// already generated by pgTyped
interface RowType {
  id: string;
  email: string;
  sendSubscriptions: boolean;
}

// already generated by pgTyped
interface Params {
  createdAt: Date | null | void;
}

// not generated by pgTyped
interface WhereConditions {
  // user columns
  id: string | null | void;
  email: string | null | void;
  handle: string | null | void;
  alternateEmail: string | null | void;
  passwordHash: string | null | void;
  // user_profile columns
  user_profile_id: boolean | null | void;
  user_profile_sendMonthlyReports: boolean | null | void;
  user_profile_sendSubscriptions?: boolean | null;
}

2) generate function similar to already generated but with different args, used like this:

myQuery.run({ createdAt: subDays(new Date(), 10) }, { handle: ILike('ade'), updatedAt: Gte(startOfDay(new Date()) })

Notice that last argument actually doesn't have type WhereConditions - it would be based on WhereConditions and it's omitted here for brevity.

this would generate following query (last 2 lines and :createdAt arg were added at run time, everything else was know at compile time):

select user.id, user.email, user_profile.send_subscriptions as "sendSubscriptions" from user
left join user_profile on user_profile.id = user.profile_id
where user.createdAt < '2020-12-15'
and user.handle ilike '%ade%'
and user."updatedAt" >= '2020-12-05'

Similar thing could be done for ORDER BY clause.

Above example doesn't allow arbitrary conditions (only AND operand for each column) for simplicity but arbitrary boolean expression could be implemented.

Benefits:

• arbitrary type safe WHERE and ORDER BY at runtime - useful for user defined filtering or anytime filter is not known at compile time
• removes noise from static part of SQL - dynamic WHERE filter in SQL layer adds noise to SQL (non type safe query builders just omit condition when there is no filter)

Some notes:

• haven't seen this feature anywhere (maybe because it's not worth it), parsing SQL to find possible WHERE and ORDER BY types won't be easy
• same could be done for delete, insert, update
• although possible, initially WHERE and ORDER BY should be added only to top level query (not to nested sub queries, common table expressions, etc.)
• not sure how to handle UNION - where do WHERE and ORDER BY clauses go?

Obviously interface should be improved and discussed but concept is there. Haven't studied codebase extensively but I'll guess that you don't parse SQL currently and only rely on Postgres to do it. If that's the case this feature could introduce a lot of complexity...

I'd like your opinion on this? Do you find it achievable? If you see pgTyped doing something like this in future I'd be willing to contribute.

Have a good one!

[wsporto]

I implemented a similar approach in typesql (similar to this library, but for mysql). You can have dynamic type safe ORDER BY but with some limitations. For example, you can't use SQL functions on the ORDER BY clause without having them on select. See the idea here.

[iswym]

Hi @wsporto, tnx for sharing your lib - looks great! That is (more or less) what I was thinking of. Haven't really thought about column derivatives (buy using SQL functions) and it seems that this is kind of limiting.

The more I think of this problem more it seems to me that these kind of libraries are workarounds for lack of missing tooling around existing SQL DBs and that SQL isn't really composable as much I'd wish it to be. As a result implementing all of this sounds like an uphill battle 😬

For example it would be nice if you could statically validate query (not just grammar but environment - table, column, function references, etc.) against local schema (from file) without spinning up DB instance and communicating with it. As far as I know current state of Postgres is that at max parser implementation can be extracted as library (which only validates grammar).

Anyway nice to see other people also interested in SQL compile time safety. 👋🏼