How to type tables with dynamic names?

The MS SQL dialect only supports global temporary objects, that forces us to add random table suffix to prevent collisions between sessions.

Is it possible to type the table contents as a second query?

Conceptually my code runs like this:

const tableName = `##TMP_${someRandomHash(6)}`;
const stream = await db
  .connection()
  .execute(async (con) => {
    await sql`
      SELECT colA, colB INTO ${sql.raw(tableName)}
      FROM SomeTable;
    `.execute(con);

    return con.withTables<{
      [tableName]: {
        colA: string;
        colB: string;
      };
    }>()
      .selectFrom(tableName)
      .select(["colA", "colB"])
      .stream();
  });

for await (const row of stream) {
  // do stuff here
}

const tableName = `##TMP_${someRandomHash(6)}`;
const stream = await db
  .connection()
  .execute(async (con) => {
    await sql`
      SELECT colA, colB INTO ${sql.raw(tableName)}
      FROM SomeTable;
    `.execute(con);

    return con.withTables<{
      [tableName]: {
        colA: string;
        colB: string;
      };
    }>()
      .selectFrom(tableName)
      .select(["colA", "colB"])
      .stream();
  });

for await (const row of stream) {
  // do stuff here
}

My current road blocks:
1.

.withTables()

.withTables()

does not accept object type with dynamic keys

[tableName]

[tableName]

where it shows this error

A computed property name in a type literal must refer to an expression whose type is a literal type or a 'unique symbol' type.

A computed property name in a type literal must refer to an expression whose type is a literal type or a 'unique symbol' type.

,
2. A raw SQL approach via

sql

sql

literal does not provide a

.stream()

.stream()

method.

Solution

Hey

A workaround would be to use the same table name in

.withTables

.withTables

and in the queries, and implement a plugin that on-the-fly adds the random suffix to all references to that table.

Jump to solution

How to type tables with dynamic names?

Similar Threads